Jump to content
Sign in to follow this  
Gorby7

SQL - GetRows method fails when no records are returned

Recommended Posts

Gorby7

I'm using 3 easy SQL functions written by Locodarwin found here: They work great, I use the GetRows method to extract the results into a 2D array.

The problem comes when there are no records/rows returned. The GetRows method fails with a console output error of "The requested action with this object has failed" and "$aResults=$oResults.GetRows()^ ERROR". I cannot figure out a way to check to see if there no records before calling the GetRows method. I tried using the RecordCount method but it always returns -1 because the recordset is forward-only/read-only. I tried to UBound the results object, but of course that didn't work. Any ideas? Here's my code:

$oOpen=_SQLConnect("server","database",1,"sa","password")
$oResults=_SQLQuery($oOpen,"EXEC Stored_Procedure_Name")
$iColumns=$oResults.Fields.Count
MsgBox(0,"","Rows: " & UBound($oResults))
MsgBox(0,"","Rows: " & $oResults.RecordCount)
$aResults=$oResults.GetRows()
$iIndex=UBound($aResults)
_SQLDisconnect($oOpen)

Share this post


Link to post
Share on other sites
jchd

A standard way to avoid hitting this wall if doing something like:

If Not $oResults.EOF Then
$aResult = $oResults.GetRows()
...
EndIf

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
Gorby7

That works perfectly! I can't believe I didn't think to try something with the EOF property after a whole day of Googling. Thanks a bunch.

Share this post


Link to post
Share on other sites
jchd

You're welcome; glad it works as expected in your case.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

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
Sign in to follow this  

  • Similar Content

    • Bilgus
      By Bilgus
      ; NetFirewallPolicy2 COM UDF Library for AutoIt3
      ; AutoIt Version : 3.3.14.5
      ; Description ...: Windows Firewall Policy2 Interface, Provides access to the firewall policy for Windows Vista+
      Including Test Script 
      UDF:
      Test Script:
       
    • Bilgus
      By Bilgus
      I was Playing around With AutoIt this evening and wondered how hard it would be to get typeinfo like the COM Viewers do only using AutoIt
      Turns out it was pretty easy.
      A Few Notes:
      CAarray info is unfinished I didn't have any objects to test it on so I left it Limited.
      The Object must have IDispatch exposed (ITypeInfo is derivative)
      Its Just a proof of concept Run with it but don't carry scissors
      ITypeInfoCOM.au3
      ITypeInfoTest.au3
       
      Output IWebBrowserApp
       
      Output ObjCreate(MediaPlayer.MediaPlayer.1)
       
    • dangr82
      By dangr82
       
      I have created this function for a database, but I can not make it work. I always have two error messages: "not an error" ... and the file created, in the script directory, does not contain anything.
      Global $sDBName = "Hen.db" Func DatabaseTable() Local $sConnDB _SQLite_Startup() If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite.dll Can't be Loaded!") Exit -1 EndIf $sConnDB = _SQLite_Open($sDBName) If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't Load Database!") Exit -1 EndIf If Not _SQLite_Exec($sDBName, 'CREATE TABLE Animal ("Name", "Age");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) If Not _SQLite_Exec($sDBName, 'INSERT INTO Animale VALUES ("Charlie","5");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) _SQLite_Close($sConnDB) _SQLite_Shutdown() EndFunc  
    • Bilgus
      By Bilgus
       IGroupPolicyObject interface
      ;;IGroupPolicyObject #RequireAdmin #include-once #include <WinAPIConstants.au3> ; $S_OK #include <WinAPIReg.au3> ;_WinAPI_GetRegKeyNameByHandle Global Enum $GPO_SECTION_ROOT = 0x0, $GPO_SECTION_USER, $GPO_SECTION_MACHINE Global Enum $GPO_OPEN_LOAD_REGISTRY = 0x1, $GPO_OPEN_READ_ONLY Global Enum $GPO_OPTION_DISABLE_USER = 0x1, $GPO_OPTION_DISABLE_MACHINE Global Enum $GPOTypeLocal = 0x0, $GPOTypeRemote, $GPOTypeDS, $GPOTypeLocalUser, $GPOTypeLocalGroup Global Const $sCLSID_GroupPolicyObject = "{EA502722-A23D-11D1-A7D3-0000F87571E3}" Global Const $sIID_IGroupPolicyObject = "{EA502723-A23D-11D1-A7D3-0000F87571E3}" Global Const $dtag_IGroupPolicyObject = _ "New hresult(wstr;wstr;dword);" & _ ; Creates a new GPO in the Active Directory with the specified display name. "OpenDSGPO hresult(wstr;dword);" & _ ; Opens the specified GPO and optionally loads the registry information. "OpenLocalMachineGPO hresult(dword);" & _ ; Opens the default GPO for the computer and optionally loads the registry information. "OpenRemoteMachineGPO hresult(wstr;dword);" & _ ; Opens the default GPO for the specified remote computer and optionally loads the registry information. "Save hResult(bool;bool;ptr;ptr);" & _ ; Saves the specified registry policy settings to disk and updates the revision number of the GPO. "Delete hresult();" & _ ; Deletes the GPO. "GetName hResult(wstr;int);" & _ ; Retrieves the unique name for the GPO. "GetDisplayName hResult(wstr;int);" & _ ; Retrieves the display name for the GPO. "SetDisplayName hresult(wstr);" & _ ; Sets the display name for the GPO. "GetPath hResult(wstr;int);" & _ ; Retrieves the path to the GPO. "GetDSPath hresult(dword;wstr;int);" & _ ; Retrieves the Active Directory path to the root of the specified GPO section. "GetFileSysPath hresult(dword;wstr;int);" & _ ; Retrieves the file system path (UNC format) to the root of the specified GPO section. "GetRegistryKey hresult(dword;handle);" & _ ; Retrieves a handle to the root of the registry key for the specified GPO section. "GetOptions hResult(dword*);" & _ ; Retrieves the options for the GPO. "SetOptions hresult(dword;dword);" & _ ; Sets the options for the GPO. "GetType hResult(dword*);" & _ ; Retrieves type information for the GPO being edited. "GetMachineName hResult(wstr;int);" & _ ; Retrieves the computer name of the remote GPO. "GetPropertySheetPages hresult(ptr;uint*);" ; Retrieves the property sheet pages associated with the GPO. Test() Func Test() Local $iResult Local $oIGroupPolicy Local $aGpoType[5] = ["Local", "Remote", "Active Directory", "LocalUser", "LocalGroup"] $oIGroupPolicy = ObjCreateInterface($sCLSID_GroupPolicyObject, $sIID_IGroupPolicyObject, $dtag_IGroupPolicyObject) While True If Not IsObj($oIGroupPolicy) Then ConsoleWrite("Failed To Retrieve Interface") $iResult = $E_NOINTERFACE ExitLoop Else ConsoleWrite("Success: " & ObjName($oIGroupPolicy, 1) & @CRLF) EndIf Local $sLoc, $sPath, $sName, $iType $tKey = DllStructCreate("handle hKey") $iResult = $oIGroupPolicy.OpenLocalMachineGPO(BitOR($GPO_OPEN_LOAD_REGISTRY, $GPO_OPEN_READ_ONLY)) If $iResult <> $S_OK Then ExitLoop $iResult = $oIGroupPolicy.GetDisplayName($sLoc, 65535) If $iResult <> $S_OK Then ExitLoop $iResult = $oIGroupPolicy.GetName($sName, 65535) If $iResult <> $S_OK Then ExitLoop ConsoleWrite($sLoc & " : " & $sName & @CRLF) $iResult = $oIGroupPolicy.GetPath($sPath, 65535) If $iResult <> $S_OK Then ExitLoop $iResult = $oIGroupPolicy.GetType($iType) If $iResult <> $S_OK Then ExitLoop ConsoleWrite($sPath & @CRLF) $iResult = $oIGroupPolicy.GetType($iType) If $iResult <> $S_OK Then ExitLoop ConsoleWrite("Type: " & $aGpoType[$iType] & @CRLF) $iResult = $oIGroupPolicy.GetRegistryKey($GPO_SECTION_USER, DllStructGetPtr($tKey)) If $iResult <> $S_OK Then ExitLoop ConsoleWrite(_WinAPI_GetRegKeyNameByHandle(DllStructGetData($tKey, "hKey")) & @CRLF) ExitLoop WEnd Return SetError($iResult, 0, ($iResult = $S_OK)) EndFunc ;==>Test Note: Not well tested..
    • Fenzik
      By Fenzik
       Hello all"
      I have curious problem with com object implementation of Sapi 5.1.
      In some cases }Some Voice engines] the metods for retrieve the voice parameters fails with error :Member not exists:.
      But the Retrieved Voice object can speak the given text, so It exists and work.
      Example of this type of Engine can be this one: http://download.kobavision.be/KobaSpeech3/KobaSpeech 3 With Vocalizer Serena - English (Great Britain).exe (can work as demo)
      So my question is> Is there some way to workaround or solve this issue?
      What i tryed:
      1. Typical use of Sapi.spvoice object:
      $oMyError = ObjEvent("AutoIt.Error","MyErrFunc"); Install a custom error handler
       
        $spvoice = ObjCreate("sapi.spvoice")
      for $voice in $spvoice.getvoices()
        msgbox(0, "Voice", $voice.getdescription())
      next
      Func MyErrFunc()
      $HexNumber = hex($oMyError.number, 8)
      Msgbox(0,"","We intercepted a COM Error !" & @CRLF &"Number is: " & $HexNumber & @CRLF &"Windescription is: " & $oMyError.windescription)
      SetError(1)
      Endfunc

      2. Implement workaround based on Nvda Screen reader sapi5 Library at https://github.com/nvaccess/nvda/blob/master/source/synthDrivers/sapi5.py
      Thys code in Pascal should work, so i tryed to reproduce it in Autoit.
      Pascal code just as example:
                   SOTokens:=SpVoice.GetVoices('','');
                   for i:=0 to SOTokens.Count-1 do
                   try
                        SOToken:=SOTokens.Item(I); s:=SOToken.GetDescription(0);
      end
      In Autoit I tryed it like this:
      $oMyError = ObjEvent("AutoIt.Error","MyErrFunc"); Install a custom error handler
        $spvoice = ObjCreate("sapi.spvoice")
      for $i = 0 to $spvoice.getvoices.count-1
      $name = $spvoice.getvoices.item($i).getdescription
      msgbox(0,"Voice", $name)
      next
      Func MyErrFunc()
      $HexNumber = hex($oMyError.number, 8)
      Msgbox(0,"","We intercepted a COM Error !" & @CRLF &"Number is: " & $HexNumber & @CRLF &"Windescription is: " & $oMyError.windescription)
      SetError(1)
      Endfunc
      Both of this methods returning same Error ("Member not exists.").
      Thanks a lot for help.
      Znefyg
×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.