Parameter queries

Many ODBC Servers will accept PARAMETERS in a Call statement so that PARAMETERS can be defined in a Query Definition on the server and their values supplied by ODBC Clients at run time. Unfortunately, the Access Jet Engine uses Parameter Markers which are not supported in the standard Call statement. The method outlined here can be used as a work-around.

For each query that requires PARAMETERS, design a arguments table with the same name as the query but with a different prefix. For example, if the query is qryParamTest, the TABLE could be called argParamTest.

The TABLE could have, say, five fields called Param1, Param2, Param3, Param4, Param5.

Add this table to the Access Query Definition for qryParamTest. Then the field names can be used as PARAMETERS anywhere in the Query Definition.

A simple Cicode function can be written to which the query name (without the prefix) and PARAMETERS are passed. The function inserts "arg" in front of the query name and executes a DELETE from the TABLE (to verify that it is empty) and then performs an INSERT to leave the table containing ONE RECORD with the desired PARAMETERS in the appropriate fields. The function then prefixes the query Name with "qry" and calls the query.

Function SQLCall(INT hSQL, STRING sQueryName, STRING sArg1 = " ", STRING sArg2 = " ", 
		STRING sArg3 = " ", STRING sArg4 = " ", STRING sArg5 = " ")

	 STRING sTable, sQuery;

	sTable = "arg" + sQueryName;

	sQuery = "qry" + sQueryName;

	SQLExec(hSQL, "DELETE FROM " + sTable);

   SQLExec(hSQL, "INSERT INTO " + sTable + " (Param1, Param2, Param3, Param4,
		 Param5) VALUES ('" + sArg1 + "', '" + sArg2 + "', '" + sArg3 + "',
		 '" + sArg4 + "', '" + sArg5 + "')");

	 SQLExec(hSQL, "{Call " + sQuery + "}");

END

SQLCall(hSQL, "ParamTest", "2000", "4000");

The default parameters for SQLCall needs to be SPACES if "Allow Zero Length" is "No" in the Access table Definitions for fields Param1, Param2 etc.

An advantage of this work-around is that, even after CitectSCADA has been shut down, the query can be called from Access and, because the PARAMETERS are still stored in the arguments table, the resulting Recordset can be viewed in Access.

Another method is to design queries that perform any necessary joins, sorting and field selection the call them using a WHERE clause to select the desired rows (records).