Macro Scheduler 15

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