Select value from MySQL into a variable

OL Connect Professional Workflow
1

Hey guys

is there a way to select a field from a MySQL table into a variable?

I have a script that’s going to insert values :

var connection = new ActiveXObject(“ADODB.Connection”);
connection.Open(“connection stuff”);
var n = 0;
var a1 = [“Monday”,“Tuesday”,“Wednesday”,“Thursday”,“Friday”,“Saturday”,“Sunday”]
for (n = 0 ; n<7 ; n++){
var insertStatement = ‘insert into tablename (fieldname) values ("’+a1[n].toUpperCase()+‘");’;
connection.Execute(insertStatement);
}
connection.Close;

ignore the logic I’m just testing… Ideally, I would like to store the result of a select query into a variable while I’m in this loop and use it for other conditional logic, something like:

myVariable = ‘select field from table where pk = someValue;’

it would only ever return 1 field not a table

I don’t know how to write it … in my sql its :

set @myVariable := (select fieldname from tablename where pk = someValue);

can it be done?
Thanks, Richard

2

The following code stores in JobInfo 8 a value in the form of fieldName=fieldValue, as retrieved with a SQL Query

var ConnectObj = new ActiveXObject("ADODB.Connection");
var RS = new ActiveXObject("ADODB.Recordset");
ConnectObj.Open("some connection string");
RS.Open("SELECT MyField FROM MyTable;",ConnectObj,0,1,1);

RS.MoveFirst;
Watch.SetJobInfo(8,RS.Fields(0).Name + "=" + RS.Fields(0).Value)

RS.Close();
ConnectObj.Close();

The RS.Open() statement generates a recordset, which is the actual result of the query.
You can checkout Microsoft’s documentation for the recordset object here.

That should help get you started.

PS: note that the fields can also be accessed by name instead of specifying the index (i.e. RS.Fields("CustomerID").Value)