Applies To:
  • CitectSCADA 1.11 1.20 2.00 2.01 3.00

Calling Access queries from Cicode, with parameters.  This article has been superseded by Q1843. 

Any queries that are defined in an Access data database are stored in the MDB file and call be executed Cicode on the JET Engine via ODBC without running Access. This means that queries can be developed and tested in Access then called from Cicode using the statement:-

SQLExec(hSQL, "{Call qryMyQuery}")

For a parameter queries a little more work is required but it is worth the effort as it avoids the need to build up SQL statements in Cicode.  Suppose the table to be queried is called tblProduction containing the following data:-

Date Production Product
19960101 0
19960102 1000 Soap
19960103 1200 Detergent
19960104 3000 Detergent
19960105 4500 Soap
19960106 3300 Soap
19960107 2490 Detergent
19960108 3000 Soap
19960109 5000 Soap
19960110 4000 Detergent
19960111 3500 Detergent
19960112 2500 Soap
19960113 4000 Detergent

The requirement is to find all Soap production between 3/1/96 and 8/1/96. The parameters are stored in a companion single record table called tblArgProductionReport as follows:-

Param1 Param2 Param3
19960103 19960108 Soap

To allow the Cicode function (described below) to always pass string parameters, the dates have been stored in a string format in the form YYYYMMDD to allow sorting and selection in date order.

Prefixing all such tables with tblArg means that they are easily identified, they are automatically grouped together in the MS Access database container and a common Cicode function can use simple string manipulation to find the parameter table associated with any query.

A query called qryArgProductionReport is defined as follows in Access:-

qryArgProductionReport: Select Query

Field: Date Production Product  
Table: tblProduction tblProduction lblProduction tblArgProductionReport
Show X X X O
Criteria: Beteeen [Param1} And [Param2]   [Param3}  

When the qryArgProductionReport is called, the JET Engine finds fields Param1, Param2 and Param3 in tblArgProductionReport and therefore, even though no parameters are passed in the call from Cicode, no error is generated.

To prove it works, the following result set was returned by MS Query (which also uses ODBC):-

19960105 4500 Soap
19960106 3300 Soap
19960108 3000 Soap

A common Cicode function along the following lines can be used to make this a simple Cicode call such as:-

SQLCallQuery(sSQL, "ProductionReport", "19960103", "19960108", "Soap")

The following code gives an idea of a simple Cicode function that could be used. IT HAS NOT BEEN TESTED (but is based on code that has been tested) and the usual error checking has been omitted for brevity.

FUNCTION SQLCallQuery(INT hSQL, STRING sQueryName, STRING sArg1 = " ",
   STRING sArg2 = " ", STRING sArg3 = " ")
   STRING sTable, sQuery;
   sTable = "tblArg" +sSQueryName;
   sQuery = "qryArg" + sQueryName;
   SQLExec(hSQL, "DELETE * FROM " + sTable);
   SQLExec(hSQL, "INSERT INTO " + sTable + " ([Param1], [Param2], [Param3]) VALUES (' + sArg1 + "','" + sArg2 + "','" + sArg3 + "')");
   SQLExec(hSQL, "{Call " + sQuery + "}");

Note that a maximum of three parameters has been shown (again for brevity). Probably up to five is a good practical limit. For however many parameters are defined in function the equivalent fields must be defined in each tblArg????????.

In case anyone is wondering why we can't simply use:-

SQLExec(hSQL, "{Call qryArgProductionReport('19960103', '19960108', 'Soap')}";

to avoid having to write SQLCallQuery at all, the answer is - it doesn't seem to work.