SQL Select\Insert from workflow Script

1

Hi

I have a job where I’m getting a TXT file which contains URN’s and need to do a select based on each URN to get values from a SQL table and insert them into another temp SQL table. I was thinking it would be much more efficient to do this within a workflow script rather than a datamapper\SQL module within workflow as I don’t have to keep opening and closing the connection to the SQL table.

However I can’t find\figure out the syntax, the closest I found was the below which can be used within datamapper but obviously db isn’t defined so this doesn’t work. (I know there is much more code required here to loop through the TXT file etc but just wanted to do a simple one for now) Could someone point me in the right direction here.

image001
image0011171×338 8.57 KB

2

Something like the following should get you going:

var connect, sql, resultSet, pth, txt, fso;

fso = new ActiveXObject("Scripting.FileSystemObject");
connect = new ActiveXObject("ADODB.Connection");
connect.ConnectionString = MY_CONNECTION_STRING;
connect.Open();

sql="select * from MY_TABLE";
resultSet = connect.Execute(sql);

pth = Watch.ExpandString("%t\output.csv");
txt = fso.CreateTextFile(pth, true);

resultSet.MoveFirst();
while (!resultSet.eof) {
  txt.WriteLine(resultSet.fields(MY_FIRST_FIELD)+","+resultSet.fields(MY_SECOND_FIELD));
  resultSet.MoveNext();
}

resultSet.Close()
connect.Close()
3

Thanks Phil as always, this works perfectly