CreateSQL function

Syntax

CreateSQL([{sqlstring | SQL.SqlName}[, paramlist]])

Where paramlist is an arbitrary-length list of values in the form: 

inval1 [, inval2] ...

Description

Use the CreateSQL function to instantiate a SQL object from the SQL class and opens it on the given sqlstring and input values. sqlstring is a PeopleCode string value giving the SQL statement.

Any errors in the SQL processing cause the PeopleCode program to be terminated with an error message.

You can use CreateSQL with no parameters to create an empty SQL object that can be used to assign properties before being populated and executed.

Parameters

Parameter Description

sqlstring| SQL.SqlName

Specify either a SQL string containing the SQL command to be created or a reference to an existing SQL definition. This string can include bind variables, inline bind variables, and meta-SQL.

paramlist

Specify input values for the SQL string.

Returns

A SQL object.

Example

This SQL object should be used in a series of Fetch method calls:

Local SQL &SQL; 
&SQL = CreateSQL("%SelectAll(:1) where EMPLID = :2", RECORD.ABSENCE_HIST, &EMPLID);

This SQL object has been opened, bound, and is already closed again:

&SQL = CreateSQL("Delete from %Table(:1) where EMPLID = :2", RECORD.ABSENCE_HIST, &EMPLID);

This SQL object should be used in a series of Execute method calls:

&SQL = CreateSQL("Delete from %Table(:1) where EMPLID = :2");

This SQL object is created as an empty object in order to set properties before being executed:

&SQL = CreateSQL(); 
&SQL.Tracename = "SQL1"; 
&SQL.ReuseCursor = True; 
&SQL.Open(......);

Opening and Processing sqlstring

If sqlstring is a SELECT statement, it is immediately bound with the inval input values and executed. The SQL object should subsequently be the subject of a series of Fetch method calls to retrieve the selected rows. If you want to fetch only a single row, use the SQLExec function instead. If you want to fetch a single row into a PeopleCode record object, use the record Select method.

If sqlstring is not a SELECT statement, and either there are some inval parameters, or there are no bind placeholders in the SQL statement, the statement is immediately bound and executed. This means that there is nothing further to be done with the SQL statement and the IsOpen property of the returned SQL object will be False. In this case, using the SQLExec function would generally be better. If you want to delete, insert or update a record object, use the record Delete, Insert, or Update methods.

If sqlstring is not a SELECT statement, there are no inval parameters, and there are bind placeholders in the SQL statement, the statement is neither bound nor executed. The resulting SQL object should subsequently be the subject of a series of Execute method calls to affect the desired rows.

Using Arrays with paramlist

You can use a parameter of type "Array of Any" in place of a list of bind values or in place of a list of fetch result variables. This is particularly useful when fetching an unknown number of results.

&SQL_1 = CreateSql("Select * from " | &TableName); 
&AAny = CreateArrayAny(); 
While &SQL_1.Fetch(&AAny) 
   /* Process the row in &AAny. */ 
   ... 
End-While;

Because the Array of Any promotes to absorb any remaining select columns, it must be the last parameter for the SQL object Fetch method or (for results) SQLExec. For binding, it must be the only bind parameter, as it is expected to supply all the bind values needed.

Related Topics