Call us! 512-349-0334 or (877) INDUSOFT

How to Create an Ad Hoc SQL Query in InduSoft Web Studio

Do you need to create the ability for your Engineering or IT Staff to have direct database access or to otherwise create an ad hoc database query within an application?  Here’s how to do it in InduSoft Web Studio SCADA software.

In order to create a SQL Query in InduSoft, it is a matter of simply using the DBExecute() function and creating a database connection in the project explorer tasks.  The following figures and descriptions along with the sample application should make setting this up quite easy.

Setting Up the Database Connection

The database connection is pretty simple. Simply right-click on Database/ERP and add a new connection, then browse to the database using the necessary driver, put in your credentials and you’re finished.  Figures 1 through 4 show connections to an Access Database and a SQL Server Database, respectively.

Figure 1: NORTHWIND Access DB (located in the project root folder) Database Connection

Figure 2: SQL Server DB Database Connection Configuration (if you are using one; not required for the sample application).

Figure 3: For Access Databases, use the MS Office 12.0 Access DB Engine (OLE DB Provider)

Figure 4: Using the function {$GetAppPath()}DBName.accdb to locate an Access database in the root folder will show an error if “Test Connection” is pushed.  This is due to a limitation in the driver trying to process the function, but it otherwise operates correctly.

Using the DBExecute Function and the Sample Application

The DBExecute() function has the following structure:

iReturnValueTag = DBExecute(strDBConn, strSQL, optStrTags, optNumMaxRows, optStrErrorTag)

Where:

strDBConn

The name of the database connection. Connections are configured in the Database/ERP folder in the Project Explorer.

strSQL

A complete, syntactically correct SQL statement.

Note: In SQL, curly brackets ({}) are typically used to enclose an expression that must be evaluated before the entire SQL statement is executed. For example:

SELECT * INTO inmates FROM OPENROWSET (‘MSDASQL’,’Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=C:\;Extensions=CSV;’,’SELECT * FROM flat.csv’)

In IWS, however, curly brackets are used to reference project tags in text fields that are not normally evaluated (e.g., in the caption of a Button object). If you pass a SQL statement that includes such an expression to DBExecute, then the project will try to evaluate the expression as a tag reference and the function will fail.

To pass the SQL statement so that the project can correctly evaluate the expression, create a new string tag that contains the text of the expression and then reference that tag in the SQL statement. For example:

AuxTag = “{Microsoft Text Driver (*.txt; *.csv)}

DBExecute ( “inmates”, “SELECT * INTO inmates FROM OPENROWSET (‘MSDASQL’,’Driver={AuxTag};DEFAULTDIR=C:\;Extensions=CSV;’,’SELECT * FROM flat.csv’)”)

optStrTags

A comma-delimited list of the names of array tags in your project, to which the columns of a SQL SELECT result set will be mapped. The database values will be copied to these array tags, with the first row of the result set being copied to array index 0. Make sure the arrays are large enough to receive all of the rows in the result set.

This parameter is required only when strSQL contains a SQL SELECT statement. For all other types of statements, this parameter is ignored and can be omitted. However, if you need to maintain the syntax of the function in order to continue through to optStrErrorTag, then give this parameter an empty string (“”).

optNumMaxRows

The maximum number of rows to be copied from a SQL SELECT result set. In most cases, to copy all of the rows, specify a number greater than the expected number of rows in the result set.

This parameter is required only when strSQL contains a SQL SELECT statement. For all other types of statements, this parameter is ignored and can be omitted. However, if you need to maintain the syntax of the function in order to continue through to optStrErrorTag, then give this parameter a value of 0.

optStrErrorTag

The name of a String tag that will receive detailed error messages, if errors occur during runtime.

Note: The tag name must be enclosed in quotes, as shown in the syntax diagram, or else the project will try to get the value of the named tag.

This is an optional parameter.

Returned value

This function returns the total number of rows affected by the SQL statement. If an error occurs, then it returns a negative number.

Please note this is the value returned by the function itself. In the case of a SQL SELECT statement, the database values are copied to the array tags specified by optStrTags.

More information about the function is in the Help Manual in Contents >> Appendix: Built-In Scripting Language >> Database/ERP functions >> DBExecute.

The sample InduSoft Web Studio AdHoc Query Application application which can be downloaded here, is shown and described in Figures 5 through 9.

Figure 5: Alarm Test Screen– Used to create various alarms in the ALARMHISTORY table of the project database, which is located in the project root folder.  The “Clear Alarms” button will delete all alarms from the table.

Figure 6: SQL Test Screen—On this screen, you can enter queries and get data returned to the table (Max 10 Columns and 100 records).  This figure shows a query to the project database, ALARMHISTORY table.  Note that databases are chosen by the radio buttons, which write the correct DB Connection Worksheet name to the DBExecute() function. (See Fig. 8)

Figure 7: SQL Test Screen—This query is to the NORTHWIND Access database, products table, which is in the project root folder.

Figure 8: The “Run Query” On-Down button script—The first routine in the “For-Next” loop clears all the storage locations in the class tag cQueryResultTag, then the DBExecute() function returns results of the query back into those locations, up to 10 columns wide and 100 rows.

Figure 9: The grid Object displays the class tag cQueryResultsTag by simply entering the name of the class tag in the Class Tag field of the Data Source.

Comments are closed.