SQLParamsSetAsInt | SQL Functions

Adds or replace a parameterized query parameter and its value in the specified connection. The value of the parameter is given as integer.

Each database provider (Odbc, OleDb, SQL Server) uses parameterized queries in a different way. It is recommended that you look at documentation and examples included with your database.

Building queries from pieces (SQLSet, SQLAppend) or adding parameters to either queries or connections (SQLParam functions) requires a few calls to respective CiCode functions. If a few functions try to manipulate the same connection in the same time some conflicts and unintended operations may occur. It is a typical multithreading problem.

To avoid this, instead of manipulating connections, consider using locally created and locally disposed queries. For example:

int function SAFE_SQL_CICODE_MULTITHREAD_USE()
  //locally created query
  int hStmt = SQLQueryCreate(hConnection);

  //Set the query
  SQLSet(hStmt, "select * from TAB where NAME=@Name");

  //Add parameters to the query
  SQLParamsSetAsString(hStmt, "Name", "Aaa");

  //Execute the query
  SQLGetRecordset(hStmt, "");

  //the locally created query is disposed
  SQLQueryDispose(hStmt);
End

This function is a blocking function and should not be called from a foreground task.

The handle to the DB connection object, returned from either SQLCreate() or SQLConnect() function. The handle identifies the DB connection object where details of the associated SQL connection are stored.

0 (zero) if successful, otherwise an error number is returned. (For details of the 307 error code, call the SQLErrMsg function).

The following examples assume that the following table is setup and opened for the three data providers, ODBC, OleDb and SQLClient, respectively:

A parameter is identified by a character "?" in the SQL query. Since the protocol uses a sequential approach for statement parameterization, the parameters order matters. In that case, to confirm that the correct parameters are picked up by the query, it is suggested to clear all the parameters after a query is executed and configure new ones in a correct order for another query unless the parameters are exactly the same in terms of value and position in the sequence between the two queries:

INT nError = 0;
STRING sValue0 = "";
STRING sValue1 = "";
INT nIsNull = 0;

INT hQueryDelete = SQLQueryCreate(hSQL);

SQLSet(hQueryDelete, "delete from PEOPLE where SURNAME=? and FIRSTNAME=?");

SQLParamsClearAll(hSQL);
//the name of the parameter does not matter
SQLParamsSetAsString(hSQL, "sName", "CASE");
SQLParamsSetAsString(hSQL, "fName", "CARRIE");

SQLCall(hQueryDelete, "");

SQLParamsClearAll(hSQL);
SQLParamsSetAsString(hSQL, "sName", "JACKSON");
SQLParamsSetAsString(hSQL, "fName", "DAVID");
SQLParamsSetAsInt(hSQL, "nAge", 28);
SQLParamsSetAsReal(hSQL, "nHeight", 1.85);

SQLCall(hSQL, "insert into PEOPLE (SURNAME, FIRSTNAME, AGE, HEIGHT) values (?,?,?,?)");

SQLParamsClearAll(hSQL);
SQLParamsSetAsString(hSQL, "fName", "DAVID");
SQLParamsSetAsString(hSQL, "sName", "JACKSON");

sValue0 = SQLGetScalar(hSQL, "select AGE from PEOPLE where FIRSTNAME=? and SURNAME=?", nIsNull);

sValue1 = SQLGetScalar(hSQL, "select HEIGHT from PEOPLE where FIRSTNAME=? And SURNAME=?", nIsNull);

Same to ODBC, the association and later substitution is based on order of the parameters in a query statement and "?" is used as the mark. Thus the last example for ODBC also works for general cases of OleDb. Additionally, for some databases, i.e. Microsoft Acess, user may have another option: parameter name with "@" prefix. Following examples are specific for communicating with Microsoft Acess through OleDb data protocol.

INT nError = 0;
STRING sValue0 = "";
STRING sValue1 = "";
INT nIsNull = 0;

INT hQueryDelete = SQLQueryCreate(hSQL);

SQLSet(hQueryDelete, "delete from PEOPLE where SURNAME=@sName and FIRSTNAME=@fName");

SQLParamsClearAll(hSQL);
SQLParamsSetAsString(hSQL, "sName", "CASE");
SQLParamsSetAsString(hSQL, "fName", "CARRIE");

SQLCall(hQueryDelete, "");

SQLParamsClearAll(hSQL);
SQLParamsSetAsString(hSQL, "sName", "JACKSON");
SQLParamsSetAsString(hSQL, "fName", "DAVID");
SQLParamsSetAsInt(hSQL, "nAge", 28);
SQLParamsSetAsReal(hSQL, "nHeight", 1.85);

SQLCall(hSQL, "insert into PEOPLE (SURNAME, FIRSTNAME, AGE, HEIGHT) values (@sName, @fName, @nAge, @nHeight)");

SQLParamsClearAll(hSQL);
SQLParamsSetAsString(hSQL, "fName", "DAVID");
SQLParamsSetAsString(hSQL, "sName", "JACKSON");

sValue0 = SQLGetScalar(hSQL, "select AGE from PEOPLE where FIRSTNAME=@fName and SURNAME=@sName", nIsNull);

sValue1 = SQLGetScalar(hSQL, "select HEIGHT from PEOPLE where FIRSTNAME=@fName And SURNAME=@sName", nIsNull);

Note: If you want to use a parameter more than once in the same query, there is no need to define it multiple times. However, the parameters have to be prepared in proper order based on their occurrence order in the query statement.

Example

INT nError = 0;
STRING sValue0 = "";
STRING sValue1 = "";
INT nIsNull = 0;

INT hQueryInsert = SQLQueryCreate(hSQL);

SQLSet(hQueryInsert, "insert into TABLE (COLUMN0, COLUMN1, COLUMN2, COLUMN3, COLUMN4) values (@param0, @param0, @param1, @param1, @param0");

SQLParamsClearAll(hSQL);
SQLParamsSetAsString(hSQL, "param0", "Value0");
SQLParamsSetAsString(hSQL, "param1", "Value1");

SQLCall(hQueryInsert, "");

SQL server uses a named parameter approach for parameterization. Parameters in queries are preceded by the '@' character. The order of the parameters does not matter.

INT nError = 0;
STRING sValue0 = "";
STRING sValue1 = "";
INT nIsNull = 0;

INT hQueryDelete = SQLQueryCreate(hSQL);

SQLSet(hQueryDelete, "delete from PEOPLE where SURNAME=@sName and FIRSTNAME=@fName");

SQLParamsSetAsString(hSQL, "sName", "CASE");
SQLParamsSetAsString(hSQL, "fName", "CARRIE");

SQLCall(hQueryDelete, "");

//Order does not matter
SQLParamsSetAsInt(hSQL, "nAge", 28);
SQLParamsSetAsReal(hSQL, "nHeight", 1.85);
//If a parameter has been defined already, setting the value again //will replace the old value.
SQLParamsSetAsString(hSQL, "sName", "JACKSON");
SQLParamsSetAsString(hSQL, "fName", "DAVID");

SQLCall(hSQL, "insert into PEOPLE (SURNAME, FIRSTNAME, AGE, HEIGHT) values (@sName, @fName, @nAge, @nHeight)");

sValue0 = SQLGetScalar(hSQL, "select AGE from PEOPLE where FIRSTNAME=@fName and SURNAME=@sName", nIsNull);

sValue1 = SQLGetScalar(hSQL, "select HEIGHT from PEOPLE where FIRSTNAME=@fName And SURNAME=@sName", nIsNull);