This discussion is archived
1 2 3 Previous Next 41 Replies Latest reply: Jul 5, 2012 4:33 AM by 722994 Go to original post RSS
  • 30. Re: How to connect Oracle DB to Mysql DB
    kgronau Guru
    Currently Being Moderated
    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
    722994 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    722994 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    722994 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    722994 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    722994 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    722994 Newbie
    Currently Being Moderated
    Hi Kgronau,

    Thanks for your help,

    I will post the new thread.

    regards,
    Iwanto
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points