9 Replies Latest reply: Aug 7, 2012 12:07 AM by 954212 RSS

    Unable to coonect via ODBC driver

    926113
      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
          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
            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
              >
              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
                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
                  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
                    >
                    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
                      It works now, thanx
                      • 8. Re: Unable to coonect via ODBC driver
                        954204
                        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
                          Thank you for the post i have same issue