[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'. |
Trace log shows:
odbcte32 3e68-1b48 ENTER SQLExecDirect odbcte32 3e68-1b48 EXIT SQLExecDirect with return code -1 (SQL_ERROR) DIAG [S1000] [Cache ODBC][State : S1000][Native Code 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 odbcte32 3e68-1b48 EXIT SQLExecDirect with return code 0 (SQL_SUCCESS) |
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.