MS SQL Server query via ADODB.Connection object doest not show result when specified combination of returned values

[maniootek]

I just discovered very weird behavior with data return when I query MS SQL server using ADODB.Connection object in Autoit.

When database table cell contains xml code as varchar and other cell contains text like '+OK' it simply does not show the result.

Here is sample code:

#include <Debug.au3>

MsSqlQueryTest()

Func MsSqlQueryTest()
    Local $sDatabase = 'YourBASENAME' ; change this string to YourDatabaseName
    Local $sServer = 'localhost\SQLExpress' ; change this string to YourServerLocation
    Local $sUser = 'sa' ; change this string to YourUserName
    Local $sPassword = 'AutoIt' ; change this string to YourPassword

    Local $sConnectionString = 'DRIVER={' & 'SQL Server' & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

    Local $oConnection = ObjCreate("ADODB.Connection")
    $oConnection.ConnectionString = $sConnectionString
    $oConnection.Open

    Local $sQuery
    $sQuery &= "SET NOCOUNT ON" & @CRLF
    $sQuery &= "DECLARE @MyTable TABLE (id INT, myValue varchar(max), pdata varchar(max))" & @CRLF
    $sQuery &= "INSERT INTO @MyTable VALUES (123, '+OK', '<root><receipts><receipt><command name=" & '"REPORTDAY"' & " /></receipt></receipts></root>')" & @CRLF
    $sQuery &= "SELECT id, myValue, pdata FROM @MyTable"

    Local $oRecordSet = $oConnection.Execute($sQuery)
    
    Local $array = $oRecordSet.GetRows
    _DebugArrayDisplay($array)
EndFunc

this is the result:

Col1 is empty but there is '+OK' value

and this is the result from Microsoft SQL Server Management Studio 17:

The wierd thing is if I change in query

Quote

SELECT id, myValue, pdata FROM @MyTable

to

Quote

SELECT id, myValue FROM @MyTable

then it works properly

Any idea what is wrong?

Edited April 4 by maniootek

[SOLVE-SMART]

Hi @maniootek 👋 ,

what happens when you use nvarchar for the "pdata" column? Maybe this could help, otherwise I totally agree, it's a strange behavior.

Best regards
Sven

[maniootek]

8 minutes ago, SOLVE-SMART said:

what happens when you use nvarchar for the "pdata" column? Maybe this could help, otherwise I totally agree, it's a strange behavior.

no change

but I just started to play with the query and I even changed to this:

    Local $sQuery
    $sQuery &= "SET NOCOUNT ON" & @CRLF
    $sQuery &= "DECLARE @MyTable TABLE (id INT, string1 varchar(max), string2 varchar(max), string3 varchar(max))" & @CRLF
    $sQuery &= "INSERT INTO @MyTable VALUES (1, 'some string 1', 'some string 2', 'some string 3'), (2, 'some string 12', 'some string 22', 'some string 32')" & @CRLF
    $sQuery &= "SELECT id, string1, string2, string3 FROM @MyTable"

and this is result:

[SOLVE-SMART]

That's even more confusing to me 😵 , because the SQL looks totally valid and understandable to me.
I cannot reproduce it until next week or so. But maybe you will get someone who can help earlier.

Best regards
Sven

Edited April 4 by SOLVE-SMART

[jchd]

Does this work?

"SELECT * FROM @MyTable"

[Andreik]

If I remember well some drivers don't handle well varchar(max) so if you have control over table definition just use varchar(8000). I tested with ODBC Driver 17 for SQL Server and works well.

#include <Debug.au3>

MsSqlQueryTest()

Func MsSqlQueryTest()
    Local $sDatabase = 'master' ; change this string to YourDatabaseName
    Local $sServer = '(local)' ; change this string to YourServerLocation
    Local $sUser = 'sa' ; change this string to YourUserName
    Local $sPassword = 'test' ; change this string to YourPassword
    Local $sDriver = 'ODBC Driver 17 for SQL Server'

    Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

    Local $oConnection = ObjCreate("ADODB.Connection")
    $oConnection.ConnectionString = $sConnectionString
    $oConnection.Open

    Local $sQuery
    $sQuery &= "SET NOCOUNT ON" & @CRLF
    $sQuery &= "DECLARE @MyTable TABLE (id INT, myValue varchar(8000), pdata varchar(8000))" & @CRLF
    $sQuery &= "INSERT INTO @MyTable VALUES (123, '+OK', '<root><receipts><receipt><command name=" & '"REPORTDAY"' & " /></receipt></receipts></root>')" & @CRLF
    $sQuery &= "SELECT id, myValue, pdata FROM @MyTable"

    Local $oRecordSet = $oConnection.Execute($sQuery)

    Local $array = $oRecordSet.GetRows
    _DebugArrayDisplay($array)
EndFunc

Alternatively some drivers support DataTypeCompatibility in connection string, so you might give a try.

If you don't have control over table definition, you can cast data in the query:

#include <Debug.au3>

MsSqlQueryTest()

Func MsSqlQueryTest()
    Local $sDatabase = 'master' ; change this string to YourDatabaseName
    Local $sServer = '(local)' ; change this string to YourServerLocation
    Local $sUser = 'sa' ; change this string to YourUserName
    Local $sPassword = 'test' ; change this string to YourPassword
    Local $sDriver = 'ODBC Driver 17 for SQL Server'

    Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';'

    Local $oConnection = ObjCreate("ADODB.Connection")
    $oConnection.ConnectionString = $sConnectionString
    $oConnection.Open

    Local $sQuery
    $sQuery &= "SET NOCOUNT ON" & @CRLF
    $sQuery &= "DECLARE @MyTable TABLE (id INT, myValue varchar(max), pdata varchar(max))" & @CRLF
    $sQuery &= "INSERT INTO @MyTable VALUES (123, '+OK', '<root><receipts><receipt><command name=" & '"REPORTDAY"' & " /></receipt></receipts></root>')" & @CRLF
    $sQuery &= "SELECT id, CAST(myValue as varchar(8000)), CAST(pdata as varchar(8000)) FROM @MyTable"

    Local $oRecordSet = $oConnection.Execute($sQuery)

    Local $array = $oRecordSet.GetRows
    _DebugArrayDisplay($array)
EndFunc

 

Edited April 4 by Andreik

[Zedna]

Also have in mind ListView limits:

 

[maniootek]

1 hour ago, Andreik said:

If you don't have control over table definition, you can cast data in the query:

thank you, casting data in the select query as varchar(1000) fix the problem

[jchd]

Still weird!

[Andreik]

15 minutes ago, jchd said:

Still weird!

Indeed. I remembered this because I encountered this problem with the old SQLNCLI11 driver but it's unexpected to see the same stupid behavior with the newer drivers.