Error when attempting to read resultset with VARCHAR column

[LightBender]

Using the latest ODBC 17 Driver from Microsoft, the following error occurs when attempting to read a query ResultSet that contains a column of type VARCHAR. (n) or (MAX) does not make a difference.

SQL QUERY: SELECT ss.[schema_id], [Name] = CONVERT(VARCHAR(8000), ss.[name]) FROM [sys].[schemas] AS ss WHERE ss.[name] <> 'sys' AND ss.[name] <> 'guest' AND ss.[name] <> 'INFORMATIONSCHEMA' AND ss.[name] NOT LIKE 'db[]%'

STACK TRACE: 2023-04-24T01:33:03.004 [info] C:\Users\AdamWilson\AppData\Local\dub\packages\ddbc-0.5.7\ddbc\source\ddbc\drivers\odbcddbc.d:428:this Driver=ODBC Driver 17 for SQL Server;Server=tcp:192.168.0.6;Uid=sa;Pwd=;Database=SERVICEINSIGHT-DEV 2023-04-24T01:33:03.209 [error] C:\Users\AdamWilson\AppData\Local\dub\packages\ddbc-0.5.7\ddbc\source\ddbc\drivers\odbcddbc.d:110:check odbc.sql.SQLGetData(1DDFB9974A0, 2, 1, null, 5, null) : SQL_ERROR

object.Exception@C:\Users\AdamWilson\AppData\Local\dub\packages\ddbc-0.5.7\ddbc\source\ddbc\drivers\odbcddbc.d(850): HY009:1:0 [Microsoft][ODBC Driver Manager] Invalid argument value

0x00007FF7572CAC87 in d_throwc 0x00007FF7571383B4 in ddbc.drivers.odbcddbc.extractError at C:\Users\AdamWilson\AppData\Local\dub\packages\ddbc-0.5.7\ddbc\source\ddbc\drivers\odbcddbc.d(151) 0x00007FF75713C4D8 in ddbc.drivers.odbcddbc.check!(SQLGetData, "C:\Users\AdamWilson\AppData\Local\dub\packages\ddbc-0.5.7\ddbc\source\ddbc\drivers\odbcddbc.d", 850LU).check at C:\Users\AdamWilson\AppData\Local\dub\packages\ddbc-0.5.7\ddbc\source\ddbc\drivers\odbcddbc.d(119) 0x00007FF75711EE0E in ddbc.drivers.odbcddbc.ODBCStatement.checkstmt!(SQLGetData, "C:\Users\AdamWilson\AppData\Local\dub\packages\ddbc-0.5.7\ddbc\source\ddbc\drivers\odbcddbc.d", 850LU).checkstmt at C:\Users\AdamWilson\AppData\Local\dub\packages\ddbc-0.5.7\ddbc\source\ddbc\drivers\odbcddbc.d(587) 0x00007FF7570F83E2 in ddbc.drivers.odbcddbc.ODBCStatement.ColumnInfo.readValue!(char[]).readValue at C:\Users\AdamWilson\AppData\Local\dub\packages\ddbc-0.5.7\ddbc\source\ddbc\drivers\odbcddbc.d(850) 0x00007FF7570DE2DB in ddbc.drivers.odbcddbc.ODBCStatement.ColumnInfo.readValueAsVariant at C:\Users\AdamWilson\AppData\Local\dub\packages\ddbc-0.5.7\ddbc\source\ddbc\drivers\odbcddbc.d(932) 0x00007FF7570DE058 in ddbc.drivers.odbcddbc.ODBCStatement.ColumnInfo.read at C:\Users\AdamWilson\AppData\Local\dub\packages\ddbc-0.5.7\ddbc\source\ddbc\drivers\odbcddbc.d(809) 0x00007FF75711E767 in ddbc.drivers.odbcddbc.ODBCStatement.fetch.__lambda2 at C:\Users\AdamWilson\AppData\Local\dub\packages\ddbc-0.5.7\ddbc\source\ddbc\drivers\odbcddbc.d(768) 0x00007FF7570F7B97 in ddbc.drivers.odbcddbc.ODBCStatement.fetch.each!(ddbc.drivers.odbcddbc.ODBCStatement.ColumnInfo[]).each at C:\D\dmd2\windows\bin64....\src\phobos\std\algorithm\iteration.d(1000) 0x00007FF7570DDE8A in ddbc.drivers.odbcddbc.ODBCStatement.fetch at C:\Users\AdamWilson\AppData\Local\dub\packages\ddbc-0.5.7\ddbc\source\ddbc\drivers\odbcddbc.d(768) 0x00007FF7570E1CE5 in ddbc.drivers.odbcddbc.ODBCResultSet.next at C:\Users\AdamWilson\AppData\Local\dub\packages\ddbc-0.5.7\ddbc\source\ddbc\drivers\odbcddbc.d(1416) 0x00007FF7573A6C47 in sqlbinding.mssql.schemareader.ReadSchemata at C:\Users\AdamWilson\Projects\EllipticBit\SqlBinding\source\mssql\schemareader.d(30)

[SingingBush]

that's odd. The CI does test with SQL Server using the msodbcsql18 driver and SQL Server 2022 and the table structure for the test certainly uses VARCHAR: https://github.com/buggins/ddbc/blob/266d9355c0d4ed024a017764b5fb1a2ba9cfbc7d/example/source/testddbc.d#L328-L360

Does the SQL run correctly when run directly against the database?

[LightBender]

Yes, it works in SSMS. I am using msodbcsql17 and SQL Server 2019.

After further review it appears that the test only attempts to use a '*' SELECT list and doesn't test named select lists.

When I use a * selector I get the following error:

std.variant.VariantException@std\variant.d(1832): Variant: attempting to use incompatible types wchar[] and immutable(char)[]

0x00007FF7DA93AC87 in d_throwc 0x00007FF7DA76B73A in std.variant.VariantN!20LU.VariantN.get!string.get at C:\D\dmd2\windows\bin64....\src\phobos\std\variant.d(862) 0x00007FF7DA7529BB in ddbc.drivers.odbcddbc.ODBCResultSet.getString at C:\Users\AdamWilson\AppData\Local\dub\packages\ddbc-0.5.7\ddbc\source\ddbc\drivers\odbcddbc.d(1573) 0x00007FF7DAA16C9A in sqlbinding.mssql.schemareader.ReadSchemata at C:\Users\AdamWilson\Projects\EllipticBit\SqlBinding\source\mssql\schemareader.d(34) 0x00007FF7DA6A7A8A in D main at C:\Users\AdamWilson\Projects\EllipticBit\SqlBinding\source\app.d(92)*

I assume this error is because every string in the information schema is NVARCHAR and DDBC doesn't support NVARCHAR yet? The trouble is that because named select lists don't work, I can't do a CONVERT(VARCHAR) to get something that it can read.

[SingingBush]

yeah #73 was created to add support for NVARCHAR but it didn't get implemented.

There are other select statements that are tested. it does:

• SELECT * FROM ddbct1
• SELECT id,comment FROM ddbct1 WHERE id = 2
• SELECT id, comment, ts FROM ddbct1 ORDER BY id DESC
• SELECT id, name, comment, ts FROM ddbct1 WHERE ts IS NULL
• SELECT id, name name_alias, comment, ts FROM ddbct1 WHERE id >= ?

as well as select statements that are generated via pod support. The error you're seeing indicates that the issue is with the variable type being used in the D code. Are you retrieving the data as a ResultSet then calling getString or using a pod?

[LightBender]

I'm using ResultSet.getString(n). It's actually code that I ported from C#/ADO.NET which has a very similar model to DDBC/JDBC ResultSets.

So I did some more digging, and here is what I came up with. The original error posted disappears when my SELECT list columns are in the same order as the original table column order. When I use the select list to reorder it, I get that HY009 error.

[SingingBush]

that's interesting, especially as those tests differ in columns used. perhaps the alias for the table name makes a difference. It may be worth adding test cases for variations of the SELECT id, name, comment, ts FROM ddbct1 WHERE ts IS NULL statement such as:

• SELECT id, ts, comment, name FROM ddbct1 WHERE ts IS NULL
• SELECT t1.id, t1.name, t1.comment, t1.ts FROM ddbct1 t1 WHERE ts IS NULL

[LightBender]

Those look good. I can work around this by reordering my select lists, but I am puzzled as to why DDBC/ODBC even cares because I'm using column indexes for my getters.