WinSQL does not load table indices, or sometimes loads SQL Storage map as indices
A client recently upgraded from Cache2010 to Cache2017 and now reports that for certain tables, WinSQL does not show the indices which were previously visible in Cache2010. Has anyone heard of this type of behavior before? Is there a remedy or some change which needs to be made to the table definitions? Thanks.
Comments
Was the normal procedure done after the upgrade?
e.g. $system.OBJ.CompileAllNamespaces("cru")
Yes, we run $System.OBJ.Upgrade() followed by $System.OBJ.CompileAll() on a namespace by namespace basis, without any flags.
Does WinSQL use ODBC? If so, does the ODBC driver need to be upgraded?
The client was upgraded to the 2018.1.3.414 ODBC Driver to resolve other ODBC issues they were having. We've obtained an ODBC log from them and the indices are shown being passed as part of the table definition as a whole, but prior to that inside the ODBC log is this:
--> SQLFreeStmt: (09:24:33:669) hstmt = 0x0732cfa0 option = 1
>> Sent: (09:24:33:669)
0000: 00 00 00 00 04 00 00 00 01 00 00 00 43 55 ............CU
<-- SQLFreeStmt: (09:24:33:669) Returning 0
--> SQLSetConnectOptionW: (09:24:33:669) hdbc = 0x07319f40 fOption = 102 vParam = 1
<-- SQLSetConnectOptionW: (09:24:33:669) Returning 0
--> SQLGetConnectOptionW: (09:24:33:810) hdbc = 0x072ec9b0 fOption = 109
ERROR: SQLGetConnectOption: (09:24:33:810) Option not supported
<-- SQLGetConnectOptionW: (09:24:33:810) pvParam = 0 returning -1
--> SQLAllocStmt: (09:24:33:811) hdbc = 0x072ec9b0
<-- SQLAllocStmt: (09:24:33:811) hstmt = 0x0734f478 returning 0
--> SQLTablesW: (09:24:33:811) hstmt = 0x0734f478
TableQualifier: %
TableOwner:
TableName:
TableType:
ERROR: SQLTables: (09:24:33:811) Catalogs not supported
<-- SQLTablesW: (09:24:33:811) Returning -1
I don't have anything like this running the latest version of WinSQL in house with the same ODBC driver, so this opens the question of whether it's their version of WinSQL.
While WinSQL does not offer direct support for InterSystems products, out of the box, and as far as I know, no plugins exist. I would recommend switching to something with better support, e.g. Datagrip or DBeaver.
Or if it's required using WinSQL, you may write a plugin for it or order it (I can help with it), which will offer better support.