ADODB.Connection Execute Returns 1 Row (SQL WHILE Loop)

[Luke94]

Hi All,

I'm using the following code to execute an SQL query and return the rows into an array.

expandcollapse popup

#include <Array.au3>
#include <String.au3>

Global $g_aRows

$g_aRows = SQL("SET NOCOUNT ON DECLARE @a AS INT = 0 DECLARE @b AS INT = 10 WHILE (@a < @b) BEGIN SELECT @a, @b SET @a = (@a + 1) END")
If @ERROR Then Exit
_ArrayDisplay($g_aRows)

Func SQL($sQuery)
    Local $oError = ObjEvent('AutoIt.Error', 'OnAutoItError')
    Local $oADODB = ObjCreate('ADODB.Connection')
    If @ERROR Then Return SetError(1)
    $oADODB.Open('-Snip-')
    If @ERROR Then Return SetError(2)
    Local $oRecordSet = $oADODB.Execute($sQuery)
    If @ERROR Then
        $oADODB.Close
        Return SetError(3)
     EndIf
    Local $oErrorArray[0][2]
    $aArray = $oRecordSet.GetRows()
    $oADODB.Close
    Return $aArray
EndFunc

Func OnAutoItError($oError)
    ConsoleWrite(StringFormat('ERROR: Description: %s', StringStripWS($oError.Description, ($STR_STRIPLEADING + $STR_STRIPTRAILING))) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: HelpContext: %s', $oError.HelpContext) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: HelpFile: %s', $oError.HelpFile) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: LastDllError: %s', $oError.LastDllError) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: Number: 0x%s', Hex($oError.Number)) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: RetCode: 0x%s', Hex($oError.RetCode)) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: ScriptLine: %s', $oError.ScriptLine) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: Source: %s', $oError.Source) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: WinDescription: %s', StringStripWS($oError.WinDescription, ($STR_STRIPLEADING + $STR_STRIPTRAILING))) & @CRLF)
EndFunc

The GetRows() method only returns the first row of the while statement.

Is there anyway I can get all the rows from the query? When I execute the same query in SSMS it works fine.

I've tried the code by @GaRydelaMer in this post but it still only seems to return the first row.

Thanks in advance!

[Luke94]

Managed to find a solution. Within the SQL query I can create a temporary table and insert each row into it. Then after the while statement I can select all the rows from the temporary table.

expandcollapse popup

#include <Array.au3>
#include <String.au3>

Global $g_aRows

$g_aRows = SQL("SET NOCOUNT ON DECLARE @a AS INT = 0 DECLARE @b AS INT = 10 DECLARE @TempTable AS TABLE(CurrentNumber INT, MaximumNumber INT) WHILE (@a < @b) BEGIN INSERT INTO @TempTable SELECT @a, @b SET @a = (@a + 1) END SELECT * FROM @TempTable")
If @ERROR Then Exit
_ArrayDisplay($g_aRows)

Func SQL($sQuery)
    Local $oError = ObjEvent('AutoIt.Error', 'OnAutoItError')
    Local $oADODB = ObjCreate('ADODB.Connection')
    If @ERROR Then Return SetError(1)
    $oADODB.Open('-Snip-')
    If @ERROR Then Return SetError(2)
    Local $oRecordSet = $oADODB.Execute($sQuery)
    If @ERROR Then
        $oADODB.Close
        Return SetError(3)
     EndIf
    Local $oErrorArray[0][2]
    $aArray = $oRecordSet.GetRows()
    $oADODB.Close
    Return $aArray
EndFunc

Func OnAutoItError($oError)
    ConsoleWrite(StringFormat('ERROR: Description: %s', StringStripWS($oError.Description, ($STR_STRIPLEADING + $STR_STRIPTRAILING))) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: HelpContext: %s', $oError.HelpContext) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: HelpFile: %s', $oError.HelpFile) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: LastDllError: %s', $oError.LastDllError) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: Number: 0x%s', Hex($oError.Number)) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: RetCode: 0x%s', Hex($oError.RetCode)) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: ScriptLine: %s', $oError.ScriptLine) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: Source: %s', $oError.Source) & @CRLF)
    ConsoleWrite(StringFormat('ERROR: WinDescription: %s', StringStripWS($oError.WinDescription, ($STR_STRIPLEADING + $STR_STRIPTRAILING))) & @CRLF)
EndFunc

😌

[Zedna]

Here is another way how to get more rows from ADO Recordset by While loop:

 

Edited May 10, 2022 by Zedna