DBQuery

Not supported in Macro Scheduler Lite.

DBQuery>database_reference,SQL_Statement,recordset_array,num_recs,num_fields[,fieldnames]

Use DBQuery to perform a SQL query which returns a recordset, such as a SELECT statement.  

database_reference is a reference variable returned by a previous call to DBConnect.  Specify the SQL statement in SQL_Statement.

Specify a variable name in recordset_array in which to return the recordset.  The array is constructed in the format recordset_rownum_fieldnum.  So a recordset with 2 rows and 3 fields per row would create an array as follows:

variablename_1_1

variablename_1_2

variablename_1_3

variablename_2_1

variablename_2_2

variablename_2_3

Specify variables for num_recs and num_fields to store the number of records and number of fields returned in the recordset.

Optionally set fieldnames to 1 to return the names of the fields rather than the index.  This would return an array like:

variablename_1_FIELD1

variablename_1_FIELD2

variablename_1_FIELD3

variablename_2_FIELD1

variablename_3_FIELD2

variablename_3_FIELD3

Where FIELD1, FIELD2, FIELD3 were the actual field names of the fields returned.

To set a timeout for the command set the DB_COMMANDTIMEOUT variable to a number of seconds.

For more advanced database manipulation use VBScript.  See:

http://www.mjtnet.com/blog/2006/02/20/accessing-databases

Abbreviation: DBQ

See also: DBConnect, DBClose, DBExec

Example

//Connect to Datasource
 Let>str=Driver={MySQL ODBC 3.51 Driver};Server=someserver.com;Port=3306;Database=example;User=admin;Password=xxxx;Option=3;
 DBConnect>str,dbH
  
 //Perform SELECT query
 Let>SQL=select * from customers where custID='abc123'
 DBQuery>dbh,SQL,CUSTOMERS,numrecs,numfields
  
 //loop through returned recordset
 Let>r=0
 Repeat>r
   Let>r=r+1
   Let>f=0
   Repeat>f
     Let>f=f+1
     Let>this_field=CUSTOMERS_%r%_%f%
     Message>this_field

    Wait>0.5
   Until>f=numfields
 Until>r=numrecs
  
 //Close database connection
 DBClose>dbH