Sign in to follow this  
Followers 0
Jat421

Looping through SQL data

5 posts in this topic

Hi, I am trying to build a asset tracking application with Mysql. I have selected all from the tables but how can I loop through the columns?

Func Search()
   Local $sql = Connect()
   $string = "select * from assets"
   $i = _Query($sql, $string)
  
  
With $i
$a = .Fields("asset_no").value
MsgBox(0, "test", $a)
EndWith

Will I have to use multiple with statements and assign them to each variable or is their a easier way to loop through them all?

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

good place to start:

http://www.w3schools.com/ado/ado_ref_recordset.asp

example of a query that may/may not return some records

Func WGe_SQLStatement($sCallersConnectionString, $sCallersSQLStatement)
$ado = ObjCreate("ADODB.Connection")
If @error = 1 Then
;Call("Var_SetLogAndActOnState", 0, 1, "WGe_SQLStatement()", "Func=[ObjCreate]: Unable to create ADODB.Connetion Object.", False, False)
Return False
EndIf
$adors = ObjCreate("ADODB.RecordSet")
If @error = 1 Then
;Call("Var_SetLogAndActOnState", 0, 1, "WGe_SQLStatement()", "Func=[ObjCreate]: Unable to create ADODB.RecordSet Object.", False, False)
Return False
EndIf
$ado.Open($sCallersConnectionString)
$adors.Open($sCallersSQLStatement, $ado)
; Only get records if there are records to receive
If $adors.Fields.Count > 0 Then
Dim $aReturn[1][1]
$iField = 0
; Create the First record of the array with the Field names
For $Field In $adors.Fields
ReDim $aReturn[1][$iField + 1]
$aReturn[0][$iField] = $Field.name
$iField += 1
Next
$iRow = 1
While Not $adors.EOF
$iField = 0
For $Field In $adors.Fields
    $sValue = $adors.Fields($Field.name).value
    ReDim $aReturn[$iRow + 1][UBound($aReturn, 2)]
    $aReturn[$iRow][$iField] = $sValue
    ;Call("Var_SetLogAndActOnState", 2, 2, "SqlAndSubmitExtRpt()", "Submitted data fro patient#=[" & $iPatient & "]; MRN=[" & $sPID & "]; PatientName=[" & $sLast & " ," & $sFirst & "].", False, False)
    $iField += 1
Next
$adors.MoveNext
$iRow += 1
WEnd
; Close the recordset
$adors.Close
$adors = 0
;Call("Var_SetLogAndActOnState", 1, 2, "WGe_SQLStatement()", "Func=[WGe_SQLStatement]: Returning Record(s)=[" & UBound($aReturn) - 1 & "], each containing Fileds=[" & UBound($aReturn, 2) & "].", False, False)
Else
;Call("Var_SetLogAndActOnState", 1, 2, "WGe_SQLStatement()", "Func=[Insert|Update]: Statement ran successfully.", False, False)
$aReturn = True
EndIf
; Close ADODB connection
$ado.Close
$ado = 0
If IsArray($aReturn) Then
If UBound($aReturn) > 1 Then
$giSubFunctionCounter -= 1
Return $aReturn
EndIf
EndIf
Return True
EndFunc ;==>WGe_SQLStatement

specifically, to answer your question:

For $Field In $adors.Fields
    $sValue = $adors.Fields($Field.name).value
 
   Consolewrite ( $sValue & @crlf )
Next
Edited by jdelaney

IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window.

Share this post


Link to post
Share on other sites

Good example. OTOH, you can also retrieve a 2D array containing all columns of all rows by using:

Func Search()
Local $sql = Connect()
$string = "select * from assets"
$i = _Query($sql, $string)
With $i
Local $a = .GetRows()         ; <=== here
_ArrayDisplay($a)
EndWith

BTW, you should probably not have to make a brand new connection for every invokation of Search().

1 person likes this

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

Thanks that does the trick!

Jdelany, the w3 school link that you posted are all the properties and methods supported that are on the website. I was trying to get the "Find" method working but that no luck. Thanks!

Share this post


Link to post
Share on other sites

You can spend some time reading MSDN about ADO to see what methods/properties are available for which objects. Still, there is no firm guarantee that everything will be implemented in a given ADO layer. Experiments will tell you what mileage you can expect running a given ADO interface.


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  
Followers 0