DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Error of Oracle Database Gateways for ODBC for MySQL

  1. #1
    Join Date
    Mar 2007
    Posts
    21

    Error of Oracle Database Gateways for ODBC for MySQL

    Hi,

    I installed and configured Oracle Database Gateways for ODBC for MySQL.
    Everything seems ok.

    - When I tried inserting row to MySQL ==> OK :

    SQL> insert into "b"@mysql values(to_char(1));

    1 row created.


    - But when I tried inserting by loop ==> error

    SQL> begin
    2 for i in 1..5
    3 loop
    4 insert into "b"@mysql values(to_char(i));
    5 end loop;
    6 end;
    7 /
    for i in 1..5
    *
    ERROR at line 2:
    ORA-04052: error occurred when looking up remote object sinh.b@MYSQL
    ORA-00604: error occurred at recursive SQL level 1
    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
    [MySQL][ODBC 5.1 Driver][mysqld-5.0.45]SELECT command denied to user
    'sinh'@'localhost' for table 'b'
    ORA-02063: preceding 2 lines from MYSQL


    Anyone know reason and how to fix this error? Please help me.

    Thanks in advance,
    Sinh

  2. #2
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    ""SELECT command denied to user
    'sinh'@'localhost' for table 'b'""

    do u have the permission??

  3. #3
    Join Date
    Mar 2007
    Posts
    21
    Hi,
    User 'sinh' is owner of table 'b'. We can insert into table 'b' like :

    SQL> insert into "b"@mysql values(to_char(1));

    1 row created.


    But when I used loop to insert a lot of values ==> get problem.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    You have bumped into a nice one.

    I'm not a MySQL expert but at first sight it looks like a timming or contention issue... could it be such a thing? If you do not have a better thing to do I would suggest to try troubleshoot it by...

    1- adding a "commit" statement in between "insert into" and "end loop"
    and
    2- adding a delay after inserting each row.

    If issue remains at least you know this is not the cause.

    Also, what happens if your script submits five consecutive insert into statements instead of doing it inside a loop?
    Last edited by PAVB; 08-06-2008 at 05:23 AM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Mar 2007
    Posts
    21
    Hi,
    Thanks for your reply.
    I have modified as your suggestion, but the same error is happening.

    I think this ODBC Driver have problem, they are not stable, they are not support completely for convesion between oracle and mysql.

    Currently, I'm using ODBC Driver : mysql-connector-odbc-5.1.4-0 and unixODBC-2.2.11-7.1

    Do you know any ODBC Drivers that is better?

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Sorry, I really don't know. I graceously let somebody who really knows about the matter to help you.

    Having said that here is a document proposing to use Oracle Heterogeneous Services with ODBC/OLEDB: http://www.scribd.com/doc/3146805/Or...with-ODBCOLEDB
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Mar 2007
    Posts
    21
    Hi,

    I configured Oracle Gateways for MSSQL. I can select MSSQL table from oracle with syntax :

    SQL>SELECT * FROM TEST@MSSQL;

    But, I don't know how to call MSSQL Store Procedures from Oracle through gateway. Do you know syntax for this? Can we do this?

    Thanks,
    Sinh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width