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:-

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:-

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

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.

INT
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 + "}");
END

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.