Using SQL Access Manager to populate wcControls in InTouch

Overview

Take advantage of QuickScripts to populate wcControl combo and list boxes via the built in SQL Access Manager module in InTouch. This assumes that the SQL Access Manager module has been installed on the PC and that tag value returned from the list box will be used elsewhere either in InTouch, Ind. Application Server or elsewhere.

Resolution

Since InTouch 7.11 P01, the SQL Access Manager has used ADO and therefore does not require the use of ODBC connections any more saving the configuration of ODBC on each PC where it is used. All that is now required is an OLEDB connection string (message) to connect to the database.
 

Microsoft OLE DB Provider use in InTouch Connection Strings:

• For an MS Access database stored in the InTouch Application/Project Folder use:

mConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + InfoInTouchAppDir() + "\<MyDBName>.mdb;";

• For a SQL Server Database use:

mConnectString = "Provider=SQLOLEDB;Data Source=<MyServerName>;Initial Catalog=<MyDBName>;User Id=<MyUserID>;Password=<MyPassword>;"

NOTE: Replace the braces and what is in them "<...>" appropriately ... don't leave the "<" and ">" in the string.

In order to capture error that may be generated when querying databases it is appropriate to both display them to an operator and log them to the SMC Logger as follows:

1. Create an analog integer memory tag "iRC" (representing the Return Code generated from the SQL Script Functions (iRC = 0 indicates a successful script execution)
2. Build an InTouch Window "Warning" to display the SQL Error message and a memory message tag "mErrMsg"
3. Next construct a InTouch Data Change Script for the tag "iRC" that

mErrMsg = SQLErrorMsg( iRC );
LogMessage( "SQL Error: " + mErrMsg );
Show "Warning";
iRC = 0;

4. Now construct a single InTouch QuickFunction to populate a variety of wcControl combo or list boxes called "SQLLoadwcControl" this the following parameter list:

• Parameter "mWcControl" as a Message data type (for wcControl name ... case sensitive)
• Parameter "mDbColumn" as a Message data type (for database column name being queried)
• Parameter "mDbTables" as a Message data type (for database table name)
• Parameter "mDbFilter" as a Message data type (for conditional filter in SQL Where statement)
• Parameter "mdbBindList" as a Message data type (the InTouch bind list for SQL query)

The BindList is stored in the SQL.DEF file in application and looks something like this. You will manage this in InTouch via a dialog screen.

:BindListName,RecipeList
mValue,RecipeName

• The QuickFunction script looks like this:

DIM ivRows AS INTEGER;
DIM ivIndex AS INTEGER;
DIM mvSQL AS MESSAGE;
DIM mvWhere AS MESSAGE;

mvWhere = "";
IF mDbFilter <> "" THEN
mvWhere = "WHERE " + mDbFilter;
ENDIF;

mvSQL = "SELECT "+ mDbColumn +" FROM "+ mDbTables +" "+ mvWhere +" ORDER BY " + mDbColumn;

wcClear ( mWcControl );

iRC = SQLSetStatement(iConnectionId, mvSQL);

{
In the example below the SQLHandle is set to zero so the statement does not have to
call SQLPrepare(Connect_Id, SQLHandle) before the execute statement. Because the
SQLHandle was not created by the SQLPrepare to properly end this select use the
SQLEnd function instead of the SQLClearStatement().
}
iRC = SQLExecute( iConnectionId, mdbBindList, 0 );

ivRows = SQLNumRows( iConnectionId );

IF ivRows > 0 THEN
FOR ivIndex = 1 TO ivRows
SQLNext( iConnectionId );
wcAddItem ( mWcControl, mValue );
NEXT;
ENDIF;

iRC = SQLEnd( iConnectionId );

5. Now call it from the window script hosting the wcControl to be loaded. Note: the connection and disconnect functions are called externally from the QuickFunction. This is because you may have multiple wcControls to load with the same script and it is inefficient to connect and reconnect multiple times.

iRC = SQLConnect(iConnectionId, mConnectString);

mWcControl = "RecipeCombo";
mDbColumn = "RecipeName";
mDbTables = "Recipe";
mDbFilter = "RecipeNo = 1";
mdbBindList = "RecipeList";
SQLLoadwcControl( mWcControl, mDbColumn, mDbTables, mDbFilter, mdbBindList);

iRC = SQLDisconnect( iConnectionId );

J.B.T.