This discussion is archived
6 Replies Latest reply: Jul 18, 2012 11:47 AM by 943235 RSS

SQLState HY000 No Query Has Been Executed With That Handle

943235 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

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