Adds or replaces a parameterized query's parameter and its value in the specified connection. The value of the parameter is given as a string.
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
SQLParamsSetAsString(hSQL, ParamName, ParamValue, nStrType)
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 a string.
nStrType:
The index specifying the type of the string:
0 - Allowing ADO engine to use the default string type of the protocol. For SQLClient, OleDB and ODBC, the default string type is NVarChar (A variable-length stream of Unicode characters ranging between 1 and 4,000 characters);
1 - Forcing the string type to be NVarChar;
2 - Forcing the string type to be NChar (A fixed-length stream of Unicode characters ranging between 1 and 4,000 characters);
3 - Forcing the string type to be VarChar (A variable-length stream of non-Unicode characters ranging between 1 and 8,000 characters. VarChar is used when the database column is varchar(max));
4 - Forcing the string type to be Char (A fixed-length stream of non-Unicode characters ranging between 1 and 8,000 characters).
The default value of nStrType is 0.
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, SQLParamsSetAsInt, SQLParamsSetAsReal, SQLParamsSetAsString, SQLParamsClearAll
Example
Published June 2018