Jump to content

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


Go to solution Solved by Luke94,

Recommended Posts

Hi All,

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

#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.

image.png.b3b42e984345e331b5e4821b5ac5cc1a.png

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

image.png.6866ecada54c56654dcd8829654a673a.png

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

Thanks in advance!

Link to comment
Share on other sites

  • Solution

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.

#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

😌

Link to comment
Share on other sites

  • 2 weeks later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...