Get Result Set from Database

[jtsy]

Hi we'd like to ask if there's a way to get a list of results from the database using a Select Statement.

Below is a part of our code, which we think only gives us one result field, whereas what we need is a list of results for us to display onto the macro scheduler dialog. Thanks in advance.

.
.
.
Let>SQLQuery="Select * From Database Where"
Let>SQLQuery={%SQLQuery%+" datetaken >= '" + %date1% +"' AND datetaken GetData("%SQLQuery%"),time_taken
.
.
.
VBSTART
Function GetData(SQLQuery)
Dim ConnectionString : ConnectionString="Driver={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=projectA; USER=root; PASSWORD=*****; OPTION=3;"
Dim MyDB
Set MyDB=CreateObject("ADODB.Connection")

MyDB.Open ConnectionString

Dim rs : rs = MyDB.Execute(SQLQuery)

If Not rs.EOF then
GetData = rs.Fields("timetaken")
Else
GetData = "Not Found"

End if


MyDB.Close
End Function

VBEND


VBEval>GetData("%SQLQuery%"),time_taken

[Marcus Tettmar]

Two ways - either split your VBS up so that you have a function to connect, one to "getnext" and one to disconnect, then set up the loop in native MacroScript. Or simply return a list:

Code: Select all

VBSTART
...
...
  Function GetData(....
...
...
...
  list=
  While Not rs.EOF
    list = list & rs.Fields("timetaken") & ";"
    rs.MoveNext
  Wend
  GetData = list
End Function
etc

Then you can explode the list into a native array with:

VBEval>GetData("%.....),MyList
Separate>MyList,;,items

etc