Skip to content
Ask A Question
  • There are no suggestions because the search field is empty.

DataHub ODBC Query Scripting Example

Download attached script file below.

Step 1 – Open the DSN Administrator from the DataHub and create your DSN to your database.
 
Step 2 – Change the DSN, user, password, and tablename in the attached script. Starting at Line 12.
DSN = "SQLHawk";
user = "";
password = "";
tablename = "tbl_BaseValues";
 
Step 3 – Change the query to be the query you want to run on the database. Line 69.
local query =  format("select * from tablename where CASTNUM=33463 AND LADELNUM=8");
 
Step 4 – Run the script and open the script log to see it print the output from the query.
Once this is working we can customize our query to be dynamic and pass a tag value as part of the query.  For this example I have a tag in DataHub called "default:LadelNum" which represents the Label Number.  We will pass it instead of the value 8.
local query =  format("select * from tablename where CASTNUM=33463 AND LADELNUM=%a", eval(symbol("default:LadelNum")));
 
You also might want to write the results of the query out to a DataHub item.  First step is to find where the value is in the 2-D array SQLResult.Result.rows.  This is done by printing the different values out to the script log using the following existing code.
princ(SQLResult.Result.rows, "\n");
princ(SQLResult.Result.rows[0][0], "\n");
princ(SQLResult.Result.rows[0][1], "\n");

 

If, for example, the value you need to write is in position [0][1], then you could do the following to write the value from the query out to a DataHub tag named "default:queryResult".
datahub_write("default:queryResult", SQLResult.Result.rows[0][1]);