This discussion is archived
9 Replies Latest reply: Aug 6, 2012 10:07 PM by 954212 RSS

Unable to coonect via ODBC driver

926113 Newbie
Currently Being Moderated
Work on Win7 x32, Oracle 11g

DB name: mydb
DB user: user
DB pass: pass

I am trying to connect via ODBC using the following strings but unsuccessful:

Driver=(Oracle in XEClient);dbq=127.0.0.1:1521/XE;Uid=user;Pwd=psw;
Driver=(Oracle in XEClient);dbq=mydb/XE;Uid=user;Pwd=psw;

I have successfully connected via MS ODBC driver: Driver={Microsoft ODBC for Oracle};Server=mydb;Uid=user;Pwd=pass;

But this driver does not understand queries with asterisk symbol (*) like SELECT * FROM, it returns error.

Please, help me to connect using oracle ODBC driver, or may be there is some config option which makes MS ODBC driver work with asterisk symbol in queries.
  • 1. Re: Unable to coonect via ODBC driver
    orafad Oracle ACE
    Currently Being Moderated
    923110 wrote:
    Work on Win7 x32, Oracle 11g
    You said "11g" and XEClient, which is not possible, som omething is missing from the picture. Exactly what do you have installed and what version(s) (full five position version string, please)?
    I am trying to connect via ODBC using the following strings but unsuccessful:
    Unsuccessful... how?

    Please post full copy-paste of connection attempt and result, including error codes returned.

    Driver=(Oracle in XEClient);dbq=127.0.0.1:1521/XE;Uid=user;Pwd=psw;
    I have successfully connected via MS ODBC driver: Driver={Microsoft ODBC for Oracle};Server=mydb;Uid=user;Pwd=pass;
    So try DBQ=mydb in the Oracle driver config.
  • 2. Re: Unable to coonect via ODBC driver
    926113 Newbie
    Currently Being Moderated
    You said "11g" and XEClient, which is not possible, som omething is missing from the picture. Exactly what do you have installed and what version(s) (full five position version string, please)?
    Oracle Database Express Edition 11g Release 2 for Windows x32
    Please post full copy-paste of connection attempt and result, including error codes returned.
    SQLSTATE[IM002] SQLDriverConnect: 0 [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
    So try DBQ=mydb in the Oracle driver config.
    When I do a test connection from ODBC Data Source Administrator it returns: Connection successful, but If I connect using the string from PHP PDO it fails.

    $h=new PDO('odbc:Driver=(Oracle in XEClient);dbq=mydb;Uid=user;Pwd=psw;');
  • 3. Re: Unable to coonect via ODBC driver
    orafad Oracle ACE
    Currently Being Moderated
    >
    When I do a test connection from ODBC Data Source Administrator it returns: Connection successful, but If I connect using the string from PHP PDO it fails.

    $h=new PDO('odbc:Driver=(Oracle in XEClient);dbq=mydb;Uid=user;Pwd=psw;');
    Verify correctness of dsn string/PDO construct syntax. (Also it seems PDO has implemented a OCI/Oracle interface, however experimental, so maybe you could do without ODBC layer.)

    Verify installed driver names, since you should not have "XEClient", not from XE 11.2.

    Edited by: orafad on Apr 2, 2012 11:36 AM
  • 4. Re: Unable to coonect via ODBC driver
    926113 Newbie
    Currently Being Moderated
    Thank you for your reply.
    Also it seems PDO has implemented a OCI/Oracle interface, however experimental, so maybe you could do without ODBC layer.
    I tried this first of all but it does not support CLOB columns, so I decided to use ODBC.
    Verify installed driver names, since you should not have "XEClient", not from XE 11.2.
    Where? In the registry:

    ODBCINST.INI/Oracle in XE/Driver: D:\Installed\Server\Oracle\app\oracle\product\11.2.0\server\BIN\sqora32.dll

    It must be another file? Which one?
  • 5. Re: Unable to coonect via ODBC driver
    orafad Oracle ACE
    Currently Being Moderated
    Name of the driver entry is found on the Drivers tab in Windows Odbc admin tool. In registry, the name of the key (or "folder") under odbcinst.ini, for the driver you intend to use, should correspond to this entry. E.g. "Oracle in ...".

    Verify driver names and their driver file locations e.g. path to driver dll file. I believe the path is not visible via ODBC admin.
  • 6. Re: Unable to coonect via ODBC driver
    orafad Oracle ACE
    Currently Being Moderated
    >
    ODBCINST.INI/Oracle in XE/Driver: D:\Installed\Server\Oracle\app\oracle\product\11.2.0\server\BIN\sqora32.dll
    Sorry, at first I missed the driver name. As you can see it is "Oracle in XE", not ...XEClient.
  • 7. Re: Unable to coonect via ODBC driver
    926113 Newbie
    Currently Being Moderated
    It works now, thanx
  • 8. Re: Unable to coonect via ODBC driver
    954204 Newbie
    Currently Being Moderated
    Had the same connection issue. Good to see this.

    Aperez
    E-commerce specialist

    Edited by: 951201 on 06-ago-2012 20:21
  • 9. Re: Unable to coonect via ODBC driver
    954212 Newbie
    Currently Being Moderated
    Thank you for the post i have same issue

Legend

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