SQLSet | SQL Functions

Cicode Programming Reference > Cicode Function Categories > SQL Functions > SQLSet

SQLSet

Sets a query string in the SQL buffer. Cicode cannot send an SQL query that is longer than 255 characters. If you have an SQL query that is longer than the 255 character limit, you can split the query into smaller strings, and use this function and the SQLAppend() function to append the query in the SQL buffer.

This function can be called in the foreground or background.

Queries which are built on the basis of user data, for example inputed by users via graphics pages or forms, may be prone to SQL Injection attacks. In such case, try to limit the risk by using CiCode functions from parameterized queries group and refer to a professional advice in this matter.

NOTICE

SECURITY BREACH VIA SQL INJECTION

- Validate all textbox entries using validation controls, regular expressions and code
- Use parameterized SQL or stored procedures
- Use a limited access account to connect to the database

Failure to follow these instructions can result in equipment damage.

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

Syntax

SQLSet(hGeneral, sString)

hGeneral:

The handle either to the DB connection object (returned from either SQLCreate() or SQLConnect() function) or to the query handle (returned from SQLQueryCreate()). When it is the connection handle, the operation is performed on the first query in that DB connection object. When it is the query handle, the operation is performed on that query through the DB object which is associated to it.

sString:

The query string to set in the SQL buffer. The string needs to contain the first part of an SQL query.

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

SQLCreate, SQLOpen, SQLClose, SQLDispose, SQLConnect, SQLDisconnect, SQLInfo, SQLSet, SQLAppend, SQLExec, SQLGetRecordset, SQLCall, SQLGetScalar, SQLEnd

Example

hSQL = SQLConnect("DSN=QEDBF");
nError = SQLBeginTran(hSQL);
nError = SQLSet(hSQL, "SELECT *");
nError = SQLAppend(hSQL, " FROM EMP");
nError = SQLAppend(hSQL, " ORDER BY last_name");
hRec = SQLGetRecordset(hSQL, "");