6 Replies Latest reply: Jul 18, 2012 1:47 PM by 943235 RSS

    SQLState HY000 No Query Has Been Executed With That Handle

    943235
      Hello everyone. I have been trying to get a DBLINK working between Oracle 11.2 & PostgreSQL. We've had a plethora of errors... However, instead of trying to explain them I'm just going to post all of the errors, the trace & all my settings. ALl though first, here is a quick paste of the error I'm getting.
      I had to cut off part of the trace for length. But it kinda repeats itself
      Thanks in advance for any help.

      This is the error we're getting from the Oracle Developer program

      SQL> /
      select * from ods_md__product@denodo_dblink
      *
      ERROR at line 1:
      ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
      No query has been executed with that handle {HY000,NativeErr = 1}No query has
      been executed with that handle {HY000,NativeErr = 1}
      ORA-02063: preceding 2 lines from DENODO_DBLINK

      And this is a rough idea of what comes from the trace.

      Entered hgopoer at 2012/07/12-16:12:35
      hgopoer, line 231: got native error 1 and sqlstate HY000; message follows...
      No query has been executed with that handle {HY000,NativeErr = 1}No query has been executed with that handle {HY000,NativeErr = 1}
      Exiting hgopoer, rc=0 at 2012/07/12-16:12:35
      hgopars, line 526: calling SQLNumResultCols got sqlstate HY000
      Exiting hgopars, rc=28500 at 2012/07/12-16:12:35 with error ptr FILE:hgopars.c LINE:556 FUNCTION:hgopars() ID:Preprocess number of columns
      hostmstr: 0: HOA After hoxpars
      hostmstr: 0: RPC After SQL Bundling



      I'm using Oracle for Heterogeneous environments 11.2 on RHEL 5.4
      Connecting to Denodo on Windows which uses PostgreSQL drivers
      unixODBC 2.2.12
      psqlodbc drivers 08.04.0200


      odbc.ini


      [VDP_acme_DSN]
      Description = VDP connection
      Driver = postgreSQLDriver
      Trace = no
      TraceFile = /tmp/trace.log
      Debug = no
      DebugFile = /tmp/debug.log
      Database = aaa_ncnu
      Servername = *.*.*.*
      UserName = admin
      Password = admin
      Port = 9996
      Protocol = 7.4
      ReadOnly = No
      RowVersioning = No
      ShowSystemTables = No
      ShowOidColumn = No
      FakeOidIndex = No
      LFConversion = Yes
      UpdatableCursors = Yes
      BoolsAsChar = Yes
      UniqueIndex = Yes
      ServerType = Postgres
      ConnSettings =
      UseServerSidePrepare= 0
      ByteaAsLongVarBinary= 1
      Optimizer = 0
      Ksqo = 0

      [ODBC]
      TraceFile=/tmp/sql.log
      Trace=1

      odbcinst.ini
      [postgreSQLDriver]
      Description = PostgreSQL 08.04.0200 driver
      Driver = /usr/local/lib/psqlodbcw.so
      UsageCount = 1


      initVDP_acme_DSN.ora
      HS_FDS_CONNECT_INFO = VDP_acme_DSN
      HS_FDS_TRACE_LEVEL = DEBUG
      #HS_FDS_TRACE_LEVEL = 255
      HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so
      HS_LANGUAGE = american_america.we8mswin1252

      #
      # ODBC specific environment variables
      #
      set ODBCINI=/usr/local/etc/odbc.ini


      tnsnames.ora
      ETLD =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ETLD)
      )
      )

      VDP_acme_DSN =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Localhost)(PORT = 1527))
      (CONNECT_DATA =
      (SERVICE_NAME = VDP_acme_DSN)
      )
      (HS=OK)
      )

      boldlistener.ora*bold*



      LISTENER_TG =
      (ADDRESS_LIST=
      (ADDRESS = (PROTOCOL = IPC)(KEY = PNPKEY))
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1527))
      )
      SID_LIST_LISTENER_TG =

      (SID_LIST =
      (SID_DESC =
      (SID_NAME = VDP_acme_DSN)
      (ORACLE_HOME =/app/oracle/product/11.2/tghome_1 )

      (ENV="LD_LIBRARY_PATH=/usr/local/lib:/app/oracle/product/11.2/tghome_1/lib")
      (PROGRAM = dg4odbc))
      )


      I've posted a copy of my oracle trace on pastebin cause of length

      http://pastebin.com/2qbgJL03

      Same with my ODBC trace

      http://pastebin.com/2FU9nRCS
        • 1. Re: SQLState HY000 No Query Has Been Executed With That Handle
          Mkirtley-Oracle
          Hi,
          The error looks to be coming from the non-Oracle database.
          As as check can you try -

          - use ISQL or something similar to check if a select works without Oracle involved
          - are objects names on the remote database case sensitive ? What happens if you try -
          select * from "ods_md__product"@denodo_dblink ;
          and use the correct case for the table name on the remote side ?

          Regards,
          Mike
          • 2. Re: SQLState HY000 No Query Has Been Executed With That Handle
            Kgronau-Oracle
            From the odbc trace which shows:
            [ODBC][6347][SQLPrepare.c][192]
            Entry:
            Statement = 0xde51420
            SQL = [SELECT A1."prd_cd",A1."prd_dsc",A1."ld_dt",A1."updt_dt",A1."prd_ln_dsc",A1."prd_ln_cd",A1."ln_bus_cd",A1."ln_bus_dsc",A1."co_num...][length = 201]
            [ODBC][6347][SQLPrepare.c][367]
            Exit:[SQL_SUCCESS]
            [ODBC][6347][SQLNumResultCols.c][149]
            Entry:
            Statement = 0xde51420
            Column Count = 0xde16fa8
            [ODBC][6347][SQLNumResultCols.c][234]
            Exit:[SQL_ERROR]
            Count = 0xde16fa8 -> 0
            DIAG [HY000] No query has been executed with that handle


            => it looks like the Postgres ODBC driver you're using has some problems with the SQLNumResultCols function. Your current ODBC driver version is psqlodbcw.so, DriverVer:08.04.0200 and there are newer drivers available. Can you please get the latest ODBC driver and check again?
            • 3. Re: SQLState HY000 No Query Has Been Executed With That Handle
              943235
              I actually upgraded the PSQLODBC drivers and now it actually works perfectly when we run the query the first time. HOwever, any subsequent queries will fail.

              But... If we disconnect & then reconnect it will work again for the first query then fail after that. Here is a copy of that Oracle trace & ODBC trace. The file is too big to put on pastebin without a paid account so I'm just gonna put it on my server for download.

              Thanks again for your help.

              So we're having network issues so I can't get the Oracle trace right now. However, here is a link to the ODBC trace. It's quite long...

              edit Sorry I put the wrong URL up*edit*
              www.keithshannon.net/odbctrace.log

              Will post rest as soon as it's available.

              Edited by: 940232 on Jul 13, 2012 12:55 PM
              • 4. Re: SQLState HY000 No Query Has Been Executed With That Handle
                943235
                Ok,

                So I'm not 100% sure which tracefile of these two are the proper ones so I"m posting both of them.

                I'm also posting a later version of the ODBC trace. Unfortunately it doesn't create a separate file for each trace like Oracle does. (is there a way to make it do this??) So I'm posting two traces and the one ODBC trace. The oracle traces will be on pastebin. The ODBC trace on my personal webserver. The config files will be posted here in this post.

                Thank you again everyone for spending the time to help out someone you don't even know. It is much appreciated.

                Oracle Trace #1
                http://pastebin.com/cucd8kwS

                Oracle Trace #2
                http://pastebin.com/hHJWt7DU

                ODBC Trace
                http://www.keithshannon.net/odbctrace.log

                If you wish to download the log rather than read on the page
                http://www.keithshannon.net/odbctrace.tar.gz

                Just to re-iterate & make clear any changes.

                Versions I am using:

                unixODBC 2.2.12 (not latest)
                PostgreSQL ODBC 09.01.0100 (latest)
                Oracle 11.2.0.3.0 Heterogeneous Agent Release

                *odbc.ini*

                [VDP_acme_DSN]
                Description = VDP connection
                Driver = postgreSQLDriver
                Trace = Yes
                TraceFile = /tmp/trace.log
                Debug = Yes
                DebugFile = /tmp/debug.log
                Database = aaa_ncnu
                Servername = 10.11.12.35
                UserName = admin
                Password = admin
                Port = 9996
                Protocol = 7.4
                ReadOnly = No
                RowVersioning = No
                ShowSystemTables = No
                ShowOidColumn = No
                FakeOidIndex = No
                LFConversion = Yes
                UpdatableCursors = Yes
                BoolsAsChar = Yes
                UniqueIndex = Yes
                ServerType = Postgres
                ConnSettings =
                UseServerSidePrepare= 0
                ByteaAsLongVarBinary= 1
                Optimizer = 0
                Ksqo = 0

                [ODBC]
                TraceFile=/tmp/sql.log
                Trace=1

                *odbcinst.ini*
                [postgreSQLDriver]
                Description = PostgreSQL 09.01.0100 driver
                Driver = /usr/local/lib/psqlodbcw.so
                UsageCount = 1

                *listener.ora*

                LISTENER_TG =
                (ADDRESS_LIST=
                (ADDRESS = (PROTOCOL = IPC)(KEY = PNPKEY))
                (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1527))
                )
                SID_LIST_LISTENER_TG =

                (SID_LIST =
                (SID_DESC =
                (SID_NAME = VDP_acme_DSN)
                (ORACLE_HOME =/app/oracle/product/11.2/tghome_1 )

                (ENV="LD_LIBRARY_PATH=/usr/local/lib:/app/oracle/product/11.2/tghome_1/lib")
                (PROGRAM = dg4odbc))
                )

                *tnsnames.ora*

                ETLD =
                (DESCRIPTION =
                (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
                (CONNECT_DATA =
                (SERVER = DEDICATED)
                (SERVICE_NAME = ETLD)
                )
                )

                VDP_acme_DSN =
                (DESCRIPTION =
                (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1527))
                (CONNECT_DATA =
                (SERVICE_NAME = VDP_acme_DSN)
                )
                (HS=OK)
                )

                *initVDP_acme_DSN.ora*

                HS_FDS_CONNECT_INFO = VDP_acme_DSN
                #HS_FDS_TRACE_LEVEL = DEBUG
                HS_FDS_TRACE_LEVEL = 255
                HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so
                HS_LANGUAGE = american_america.we8mswin1252
                HS_FDS_SQLLEN_INTERPRETATION = 32


                #
                # ODBC specific environment variables
                #
                set ODBCINI=/usr/local/etc/odbc.ini


                I believe thats it! Please let me know if you need anymore information to help me & thanks again!
                • 5. Re: SQLState HY000 No Query Has Been Executed With That Handle
                  Kgronau-Oracle
                  Everything looks again like an ODBC issue (http://pastebin.com/hHJWt7DU):
                  The first select is processed correctly (see following trace snippets):

                  464:
                  SQL text from hgopars, id=1, len=201 ...
                  00: 53454C45 43542041 312E2270 72645F63 [SELECT A1."prd_c]
                  10: 64222C41 312E2270 72645F64 7363222C [d",A1."prd_dsc",]
                  20: 41312E22 6C645F64 74222C41 312E2275 [A1."ld_dt",A1."u]
                  30: 7064745F 6474222C 41312E22 7072645F [pdt_dt",A1."prd_]
                  40: 6C6E5F64 7363222C 41312E22 7072645F [ln_dsc",A1."prd_]
                  50: 6C6E5F63 64222C41 312E226C 6E5F6275 [ln_cd",A1."ln_bu]
                  60: 735F6364 222C4131 2E226C6E 5F627573 [s_cd",A1."ln_bus]
                  70: 5F647363 222C4131 2E22636F 5F6E756D [_dsc",A1."co_num]
                  80: 222C4131 2E227372 635F7379 735F6364 [",A1."src_sys_cd]
                  90: 222C4131 2E227372 635F7379 735F6B65 [",A1."src_sys_ke]
                  A0: 79222C41 312E2264 656C5F66 6C672220 [y",A1."del_flg" ]
                  B0: 46524F4D 20224F44 535F4D44 5F5F5052 [FROM "ODS_MD__PR]
                  C0: 4F445543 54222041 31 [ODUCT" A1]
                       
                  and then it starts the fetch:     
                  577: SQLFetch: row: 1, column 1, bflsz: 256, bflar: 3     
                  ...

                  All following selects get:
                  Entered hgopoer at 2012/07/13-01:28:37
                  hgopoer, line 231: got native error 1 and sqlstate HY000; message follows...
                  No query has been executed with that handle {HY000,NativeErr = 1}No query has been executed with that handle {HY000,NativeErr = 1}

                  The ODBC trace shows that also:
                  The first select is being prepared:
                  [ODBC][11602][SQLPrepare.c][192]
                            Entry:
                                 Statement = 0x145334d0
                                 SQL = [SELECT A1."prd_cd",A1."prd_dsc",A1."ld_dt",A1."updt_dt",A1."prd_ln_dsc",A1."prd_ln_cd",A1."ln_bus_cd",A1."ln_bus_dsc",A1."co_num...][length = 201]
                  [ODBC][11602][SQLPrepare.c][367]
                            Exit:[SQL_SUCCESS]
                  [ODBC][11602][SQLNumResultCols.c][149]
                            Entry:
                                 Statement = 0x145334d0
                                 Column Count = 0x144fa068
                  [ODBC][11602][SQLNumResultCols.c][234]
                            Exit:[SQL_SUCCESS]
                                 Count = 0x144fa068 -> 12
                  ...
                  next time the same information is request again, the driver returns:
                  [ODBC][11602][SQLPrepare.c][192]
                            Entry:
                                 Statement = 0x1455d1d0
                                 SQL = [SELECT A1."prd_cd",A1."prd_dsc",A1."ld_dt",A1."updt_dt",A1."prd_ln_dsc",A1."prd_ln_cd",A1."ln_bus_cd",A1."ln_bus_dsc",A1."co_num...][length = 201]
                  [ODBC][11602][SQLPrepare.c][367]
                            Exit:[SQL_SUCCESS]
                  [ODBC][11602][SQLNumResultCols.c][149]
                            Entry:
                                 Statement = 0x1455d1d0
                                 Column Count = 0x144fa068
                  [ODBC][11602][SQLNumResultCols.c][234]
                            Exit:[SQL_ERROR]
                                 Count = 0x144fa068 -> 0
                            DIAG [HY000] No query has been executed with that handle

                  Not sure if it has an impact, but in your odbc.ini you have the setting:
                  Protocol = 7.4
                  Shouldn't it be 8.x to match the database release?


                  Not sure if the odbc.ini parameter
                  UseDeclareFetch=1
                  and the gateway parameter:
                  HS_FDS_FETCH_ROWS=1

                  will change the behavior, but it might be worth a try.


                  If it continues to fail, you might also provide an ODBC trace file to the ODBC vendor to check it out.
                  • 6. Re: SQLState HY000 No Query Has Been Executed With That Handle
                    943235
                    I'm marking this as correct because it got us to the point where the answer was found. The issue was with Denodo... They had to apply some patches to their software and it worked correctly. unixODBC was not the issue. However, if the psqlODBC drivers had not been upgraded we may not have figured that out.

                    Thank you for your help.