SQLParamsSetAsInt

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.

Syntax

SQLParamsSetAsInt(hSQL, ParamName, ParamValue)

hSQL:

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.

ParamName:

The name of the parameter to add or change.

ParamValue:

The value of the parameter as an integer.

Return Value

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

Related Functions

SQLSet, SQLAppend, SQLExec, SQLGetRecordset, SQLCall, SQLGetScalar, SQLEnd, SQLParamsSetAsReal, SQLParamsSetAsString, SQLParamsClearAll

Examples

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

PEOPLE

SURNAME FIRSTNAME AGE HEIGHT

MARTIAN MARVIN 27 1.78

CASE CARRIE 18 1.73

ODBC

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);

OleDb

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 Access, user may have another option: parameter name with "@" prefix. Following examples are specific for communicating with Microsoft Access 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, "");

The result will be:

TABLE

COLUMN0 COLUMN1 COLUMN2 COLUMN3 COLUMN4

Value0 Value0 Value1 Value1 Value0

SQLClient

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);

By the given Cicode examples, the table will be updated to:

PEOPLE

SURNAME FIRSTNAME AGE HEIGHT

MARTIAN MARVIN 27 1.78

JACKSON DAVID 28 1.85

See Also

SQL Functions