1 2 3 Previous Next 41 Replies Latest reply: Jul 5, 2012 6:33 AM by user1888509 Go to original post RSS
      • 30. Re: How to connect Oracle DB to Mysql DB
        Kgronau-Oracle
        The connect string still shows:
        HS_FDS_CONNECT_INFO = "BKPB"

        But from the previous post where you've shown the odbc.ini registry export the DSN name is:
        [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\BPKB]


        Please make sure HS_FDS_CONNECT_INFO uses the DSN "BPKP":
        HS_FDS_CONNECT_INFO = BPKB

        Edited by: kgronau on Jul 4, 2012 1:34 PM

        HS_FDS_CONNECT_INFO must match the name of the SYSTEM ODBC DSN which is shown in the MS ODBC Administrator
        • 31. Re: How to connect Oracle DB to Mysql DB
          user1888509
          Hi, I have changed to BPKB, i got this error :

          ERROR at line 1:
          ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
          [MySQL][ODBC 5.1 Driver]Access denied for user 'oracle'@'172.18.29.42' (using
          password: YES) {HY000,NativeErr = 1045}
          ORA-02063: preceding 2 lines from LINK2MYSQLTEST




          ========================
          in trace file :
          -----------------------------------------------------



          Oracle Corporation --- THURSDAY JUL 05 2012 09:25:01.609


          Heterogeneous Agent Release
          11.2.0.1.0




          Oracle Corporation --- THURSDAY JUL 05 2012 09:25:01.609

          Version 11.2.0.1.0

          Entered hgogprd
          HOSGIP for "HS_FDS_TRACE_LEVEL" returned "255"
          Entered hgosdip
          setting HS_OPEN_CURSORS to default of 50
          setting HS_FDS_RECOVERY_ACCOUNT to default of "RECOVER"
          setting HS_FDS_RECOVERY_PWD to default value
          setting HS_FDS_TRANSACTION_LOG to default of HS_TRANSACTION_LOG
          setting HS_IDLE_TIMEOUT to default of 0
          setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
          setting HS_NLS_NCHAR to default of "UCS2"
          setting HS_FDS_TIMESTAMP_MAPPING to default of "DATE"
          setting HS_FDS_DATE_MAPPING to default of "DATE"
          setting HS_RPC_FETCH_REBLOCKING to default of "ON"
          setting HS_FDS_FETCH_ROWS to default of "100"
          setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"
          setting HS_FDS_RSET_RETURN_ROWCOUNT to default of "FALSE"
          setting HS_FDS_PROC_IS_FUNC to default of "FALSE"
          setting HS_FDS_CHARACTER_SEMANTICS to default of "FALSE"
          setting HS_FDS_MAP_NCHAR to default of "TRUE"
          setting HS_NLS_DATE_FORMAT to default of "YYYY-MM-DD HH24:MI:SS"
          setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of "FALSE"
          setting HS_LONG_PIECE_TRANSFER_SIZE to default of "65536"
          setting HS_SQL_HANDLE_STMT_REUSE to default of "FALSE"
          setting HS_FDS_QUERY_DRIVER to default of "TRUE"
          setting HS_FDS_SUPPORT_STATISTICS to default of "FALSE"
          Parameter HS_FDS_QUOTE_IDENTIFIER is not set
          setting HS_KEEP_REMOTE_COLUMN_SIZE to default of "OFF"
          setting HS_FDS_GRAPHIC_TO_MBCS to default of "FALSE"
          setting HS_FDS_MBCS_TO_GRAPHIC to default of "FALSE"
          Default value of 32 assumed for HS_FDS_SQLLEN_INTERPRETATION
          setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics;gtw$:SQLGetInfo"
          setting HS_FDS_DELAYED_OPEN to default of "TRUE"
          setting HS_FDS_WORKAROUNDS to default of "0"
          Exiting hgosdip, rc=0
          ORACLE_SID is "tst"
          Product-Info:
          Port Rls/Upd:1/0 PrdStat:0
          Agent:Oracle Database Gateway for ODBC
          Facility:hsa
          Class:ODBC, ClassVsn:11.2.0.1.0_0008, Instance:tst
          Exiting hgogprd, rc=0
          hostmstr: 2057621504:      HOA After hoagprd
          hostmstr: 2057621504:      HOA Before hoainit
          Entered hgoinit
          HOCXU_COMP_CSET=1
          HOCXU_DRV_CSET=178
          HOCXU_DRV_NCHAR=1000
          HOCXU_DB_CSET=178
          HOCXU_SEM_VER=102000
          Entered hgolofn at 2012/07/05-09:25:02
          Exiting hgolofn, rc=0 at 2012/07/05-09:25:02
          HOSGIP for "HS_OPEN_CURSORS" returned "50"
          HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"
          HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
          HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"
          HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "OFF"
          HOSGIP for "HS_FDS_DELAYED_OPEN" returned "TRUE"
          HOSGIP for "HS_FDS_WORKAROUNDS" returned "0"
          HOSGIP for "HS_FDS_MBCS_TO_GRAPHIC" returned "FALSE"
          HOSGIP for "HS_FDS_GRAPHIC_TO_MBCS" returned "FALSE"
          Invalid value of 32 given for HS_FDS_SQLLEN_INTERPRETATION
          treat_SQLLEN_as_compiled = 1
          Exiting hgoinit, rc=0 at 2012/07/05-09:25:03
          hostmstr: 2057621504:      HOA After hoainit
          hostmstr: 2057621504:      HOA Before hoalgon
          Entered hgolgon at 2012/07/05-09:25:03
          reco:0, name:oracle, tflag:0
          Entered hgosuec at 2012/07/05-09:25:03
          Exiting hgosuec, rc=0 at 2012/07/05-09:25:03
          HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
          HOSGIP for "HS_FDS_TRANSACTION_LOG" returned "HS_TRANSACTION_LOG"
          HOSGIP for "HS_FDS_TIMESTAMP_MAPPING" returned "DATE"
          HOSGIP for "HS_FDS_DATE_MAPPING" returned "DATE"
          HOSGIP for "HS_FDS_CHARACTER_SEMANTICS" returned "FALSE"
          HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
          HOSGIP for "HS_FDS_RESULTSET_SUPPORT" returned "FALSE"
          HOSGIP for "HS_FDS_RSET_RETURN_ROWCOUNT" returned "FALSE"
          HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
          HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
          using oracle as default value for "HS_FDS_DEFAULT_OWNER"
          HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
          Entered hgocont at 2012/07/05-09:25:04
          HS_FDS_CONNECT_INFO = "BPKB"
          RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
          Entered hgogenconstr at 2012/07/05-09:25:04
          dsn:BPKB, name:oracle
          optn:
          Entered hgocip at 2012/07/05-09:25:04
          dsn:BPKB
          Exiting hgocip, rc=0 at 2012/07/05-09:25:04
          ##>Connect Parameters (len=30)<##
          ## DSN=BPKB;
          #! UID=oracle;
          #! PWD=*
          Exiting hgogenconstr, rc=0 at 2012/07/05-09:25:04
          Entered hgopoer at 2012/07/05-09:25:06
          hgopoer, line 233: got native error 1045 and sqlstate HY000; message follows...
          [MySQL][ODBC 5.1 Driver]Access denied for user 'oracle'@'172.18.10.42' (using password: YES) {HY000,NativeErr = 1045}
          Exiting hgopoer, rc=0 at 2012/07/05-09:25:06
          hgocont, line 2753: calling SqlDriverConnect got sqlstate HY000
          Exiting hgocont, rc=28500 at 2012/07/05-09:25:06 with error ptr FILE:hgocont.c LINE:2773 ID:Something other than invalid authorization
          Exiting hgolgon, rc=28500 at 2012/07/05-09:25:07 with error ptr FILE:hgolgon.c LINE:781 ID:Calling hgocont
          hostmstr: 2053578752:      HOA After hoalgon
          RPC Calling nscontrol(0), rc=0
          hostmstr: 2053578752: RPC Before Exit Agent
          hostmstr: 2053578752:      HOA Before hoaexit
          Entered hgoexit at 2012/07/05-09:25:07
          Exiting hgoexit, rc=0
          hostmstr: 2057355264:      HOA After hoaexit
          hostmstr: 2057355264: RPC After Exit Agent
          • 32. Re: How to connect Oracle DB to Mysql DB
            Kgronau-Oracle
            That's a MySQL permission issue. Your user oracle is not allowed to connect from the machine with IP address 172.18.10.42

            You need to grant the permission to the user to be able to log on from this machine. The syntax that allows the user to log on from every machine is:

            grant all on *.* TO 'oracle'@'%' IDENTIFIED BY '<the password of the oracle user>';
            • 33. Re: How to connect Oracle DB to Mysql DB
              user1888509
              Hi, can u explain how it works ?

              grant all on . TO 'oracle'@'%' IDENTIFIED BY '<the password of the oracle user>'; ==> where i have to setup ?


              You need to grant the permission to the user to be able to log on from this machine. ==> Which mechine i have to grant ?

              i.e : 10.173.30.2 ==> DB mechine
              10.173.30.3 ==> MYsql Mechine.


              Thanks and regards,
              Iwanto
              • 34. Re: How to connect Oracle DB to Mysql DB
                Kgronau-Oracle
                Have a look at the MySQL manual: http://dev.mysql.com/doc/refman/5.1/en/grant.html

                The syntax provided earlier "grant all on . TO 'oracle'@'%' IDENTIFIED BY '<the password of the oracle user>'; " grants "all" permissions to the oracle user and allows this user to connect from any host '%' to all "." MySQL databases located on the MySQL Server.
                • 35. Re: How to connect Oracle DB to Mysql DB
                  user1888509
                  I got this error msg.

                  ==================================================
                  SQL> select * from "nama"@link2mysqltest;
                  select * from "nama"@link2mysqltest
                  *
                  ERROR at line 1:
                  ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
                  [MySQL][ODBC 5.1 Driver][mysqld-5.1.41]You have an error in your SQL syntax;
                  check the manual that corresponds to your MySQL server version for the right
                  syntax to use near '"nama",A1."no_kontrak" FROM "nama" A1' at line 1
                  {42000,NativeErr = 1064}
                  ORA-02063: preceding 2 lines from LINK2MYSQLTEST
                  ==================================================

                  in mysql, the table name is "nama"
                  that table consists of 2 columns (nama and no_kontrak)

                  is my select statement wrong ?

                  Thanks and regards,
                  Iwanto
                  • 36. Re: How to connect Oracle DB to Mysql DB
                    Kgronau-Oracle
                    The MySQL database is not supporting ANSI standard which requires a back tick instead of double quotes.

                    Set in the gateway init file:
                    HS_FDS_QUOTE_IDENTIFIER="`"

                    Edited by: kgronau on Jul 5, 2012 10:11 AM

                    See also Oracle Support Note:
                    Problems Selecting Mysql Data Using 11.1.0.6 or 11.2.0.2 DG4ODBC - Error In Your SQL Syntax (Doc ID 553030.1)
                    • 37. Re: How to connect Oracle DB to Mysql DB
                      user1888509
                      I have added HS_FDS_QUOTE_IDENTIFIER="`"

                      but still the same, can not connect.
                      • 38. Re: How to connect Oracle DB to Mysql DB
                        Kgronau-Oracle
                        Did you check out the Oracle note I mentioned above?
                        It states when the back tick setting fails you could also use:
                        HS_FDS_QUOTE_IDENTIFIER=FALSE
                        So please test this setting instead.
                        • 39. Re: How to connect Oracle DB to Mysql DB
                          user1888509
                          I have tried to select * , but it return error :

                          ================
                          SQL> select * from "nama"@link2mysqltest;
                          ERROR:
                          ORA-28528: Heterogeneous Services datatype conversion error
                          [MySQL][ODBC 5.1 Driver][mysqld-5.1.41] {01004}
                          ================

                          but, if i use select count(1) , it return value


                          SQL> select count(1) from "nama"@link2mysqltest;

                          COUNT(1)
                          ----------
                          92707


                          How do i do the conversion ? should i convert all to number?

                          Thanks
                          • 40. Re: How to connect Oracle DB to Mysql DB
                            Kgronau-Oracle
                            Best would be to close now this set up thread and start the data type issue in a new, separate thread as this one here is getting very long....

                            In the new thread, please post the MySQL table description ( the way the table is defined at the MySQL Server).
                            • 41. Re: How to connect Oracle DB to Mysql DB
                              user1888509
                              Hi Kgronau,

                              Thanks for your help,

                              I will post the new thread.

                              regards,
                              Iwanto
                              1 2 3 Previous Next