Question
Stephen Carter · Nov 30, 2020

[Cache ODBC][State : S1000][Native Code 400]

I'm running Windows 10 x64 Pro 20H2 with Intersystems Cache ODBC driver v2018.01.00.184. I've setup a System DSN using the 64-bit of ODBC Administrator.

I've been getting inconsistent results using my regular application (Microsoft Power BI) which I use through ODBC to query my hosted TrakCare T2017 instance.

Using Microsoft ODBC Test Tool (part of MDAC 2.8 SDK) I can verify I get the same errors as in Power BI. They seem to be ODBC driver related but I can't pin it down.

ODBC Test Tool GUI shows:

Full Connect(Default)

    Successfully connected to DSN 'Test DB'.
SQLExecDirect:
In:                hstmt = 0x00000000022030D0, 
szSqlStr = "select ctloc_rowid from ct_loc", cbSqlStr = -3
Return:    SQL_ERROR=-1

Trace log shows:

odbcte32        3e68-1b48    ENTER SQLExecDirect 
        HSTMT               0x00000000022030D0
        UCHAR *             0x0000000000552110 [      -3] "select ctloc_rowid from ct_loc\ 0"
        SDWORD                    -3

odbcte32        3e68-1b48    EXIT  SQLExecDirect  with return code -1 (SQL_ERROR)
        HSTMT               0x00000000022030D0
        UCHAR *             0x0000000000552110 [      -3] "select ctloc_rowid from ct_loc\ 0"
        SDWORD                    -3

        DIAG [S1000] [Cache ODBC][State : S1000][Native Code 400]
[C:\Program Files (x86)\Microsoft Data Access SDK 2.8\Tools\amd64\odbcte32.exe]
[SQLCODE: <-400>:<Fatal error occurred>]
[Cache Error: <<UNDEFINED>CQPrivCheck+8^%SYS.SQLSRV ^SYS("Security","RolesD","%db_trakdb")>]
[Location: <Prepare>] (400)


 To make matters worse, within this same session I then run this query instead, which works?

ODBC Test tool shows:

SQLExecDirect:
                In:                hstmt = 0x00000000022030D0, 
                                        szSqlStr = "select * from CT_LOC", cbSqlStr = -3
                Return:    SQL_SUCCESS=0

Trace log says:

odbcte32        3e68-1b48    ENTER SQLExecDirect 
        HSTMT               0x00000000022030D0
        UCHAR *             0x0000000000552110 [      -3] "select * from CT_LOC\ 0"
        SDWORD                    -3

odbcte32        3e68-1b48    EXIT  SQLExecDirect  with return code 0 (SQL_SUCCESS)
        HSTMT               0x00000000022030D0
        UCHAR *             0x0000000000552110 [      -3] "select * from CT_LOC\ 0"
        SDWORD                    -3

I then tried the first query with same case sensitivity as I see in the DB, and that works:

SQLExecDirect:
                In:                hstmt = 0x00000000022030D0, 
                                        szSqlStr = "select CTLOC_RowId from CT_LOC", cbSqlStr = -3
                Return:    SQL_SUCCESS=0

 

So clutching at straws I have access to another username and password for the same namespace which I use for my reporting service and for whatever reason, this time the same failed query works. Now I'm stumped.

SQLExecDirect:
                In:                hstmt = 0x00000000021FCBD0, 
                                        szSqlStr = "select ctloc_rowid from ct_loc", cbSqlStr = -3
                Return:    SQL_SUCCESS=0
 

As a final test I rebooted my PC, to potentially clear out any caching on my end. Same results.

Why would the exact same query that fails under one account to what appears to be case sensitivity as it works with I changed the case, then work using a different ODBC login?

Is there some type of ODBC query caching going on that persists reboots?

Maybe there are some Intersystems CACHE ODBC options I'm unaware of I could enable to check?

Any guidance would be great because this has me scratching my head.

Product version: Caché 2018.1
0
0 996
Discussion (1)0
Log in or sign up to continue

Closing out this post it turns out the Intersystems CACHE Database had stale query cache. DB admin had to clear from their side, then all queries ran successfully