Back to all How-tos

Query a database from Workflow

These script samples will help if you need to connect to, and query, a database from within a script in Workflow.

Here are a few situations where this can be very useful:

  • You need to do many queries or updates in a database inside a single connection session (instead of using a loop and the Database Query plugin which would be inneficient)
  • You need to create a custom condition based on data from a database with the Run Script task as a Condition.
  • You need to make more than one query one after the other (where the second is based on data from the first, for example) and don’t want to use multiple Database Query tasks
  • You want to complement your metadata from a script, maintaining the Metadata and Database objects open throughout the script to enhance performance and eliminate overhead.

These scripts are written in JavaScript unless otherwise noted. To use them in the Run Script action, make sure you use the Language -> JScript option in the menu.

Sample 1: Connect to an MDB (Microsoft Access) database

// Initiate the ADODB Connection object
var conn = new ActiveXObject("ADODB.Connection");
 
// Initiate a recordset to receive results. If multiple queries are made, you'll need to initiate more than one.
var rs = new ActiveXObject("ADODB.Recordset");
 
// Driver configuration, in this case an MDB file. Change dbloc to the path to your file.
var dbloc = "c:/path-to-file/my-access-database.mdb";
var src = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + dbloc + ";";
 
// Initiate the connection to the database
conn.Open(src);
 
// Example SQL query to retrieve from a table called "user". Change for your needs.
var strQuery = "SELECT * FROM users WHERE username = 'user1'";
 
// Execute the query.
rs.Open(strQuery,conn);
 
// Optional: move to the first record
rs.MoveFirst();
 
// Debug: displays the number of rows. Can be used as condition to check if results were actually received
Watch.Log("Number of records retrieved: " + rs.Fields.Count , 2);
 
// Loop through each record in the recordset
while (!rs.EOF) {
    // Do your thing! Save the file, put it in an XML or CSV, local variables, metadata, whatever
    Watch.Log("Output for 'field1': " + rs("field1"), 2);
 
    // At the end of the loop, move to the next record before the loop restarts (has to be at the end!)
    rs.MoveNext();
}
 
// Close the recordset and connection cleanly to free up memory and prevent errors.
rs.Close();
conn.Close();

Sample 2: Connect to a Microsoft SQL Server (MSSQL) server

Most of the sample is the same as above except that the “dbloc” and “src” lines are replaced by a single line:

// Driver configuration, in this case Microsoft SQL Server.
// Change: server1 (server host or IP), db1 (database name) , uid (database username), password
var src = "driver={sql server};server=server1;database=db1;uid=username;password=password";

Sample 3: Connect to a MySQL Server

Again only the connection string changes. Note that this requires the installation of the MySQL ODBC Driver (32-bit) on the machine before attempting to use this script.

// Driver configuration, in this case MySQL.
// Change: server1 (server host or IP), db1 (database name) , uid (database username), password
var src = "Driver={MySQL ODBC 5.3 Unicode Driver};Server=server1;" +\
                   "Database=db1;User=uid;Password=password;"

Need more?

Obviously, it becomes apparent that only the connection string changes if you need to connect to a different database type. If you need to connect to a different database than the ones you see here, all you need to do is to find a dsn-less connection string for your own database, ensure you have the appropriate driver installed, and you’re good to go. Here’s a list of possible strings that you can use!

Leave a Reply

Your email address will not be published. Required fields are marked *