SQL Class Methods
In this section, we discuss the SQL class methods. The methods are discussed in alphabetical order.
Syntax
Close()
Description
The Close method closes the SQL object. This terminates any incomplete fetching of select result rows, completes any buffered operations (that is, using BulkMode), and disassociates the SQL object from any SQL statement that was open on it.
After BulkMode operations, the RowsAffected property is not valid.
Parameters
None
Returns
True on successful completion, False if there was a duplicate record error. Any errors associated with buffered operations (that is, using BulkMode), other than duplicate record errors, cause termination.
Example
&SQL = CreateSQL("%Delete(:1)");
While /* Still something to do */
/* Set key field values of &ABS_HIST */
&SQL.Execute(&ABS_HIST);
End-While;
&SQL.Close();
Syntax
Execute(paramlist)
Where paramlist is an arbitrary-length list of values in the form:
inval1 [, inval2] ...
Description
The Execute method executes the SQL statement of the SQL object. The SQL object must be open and unbound on a delete, insert, or update statement. That is, the CreateSQL, GetSQL, or Open preceding the Execute must have specified a delete, insert, or update statement with bind placeholders and must not have supplied any input values.
The values in paramlist are used to bind the SQL statement before it gets executed.
When using the optional BulkMode, the Execute operations may be buffered and are not guaranteed to have been presented to the database until a Close is done. Thus, in BulkMode, errors that arise may not be reported until later operations are done.
Parameters
|
Field or Control |
Definition |
|---|---|
| paramlist |
Specify input values for the SQL string. |
Returns
True on successful completion, False for "record not found" and "duplicate record" errors. Any other errors cause termination.
Example
The following example creates a SQL object for inserting. The statement isn’t automatically executed when it’s created because there aren’t any bind variables. The Execute occurs after other processing is finished. The name of the record is passed in as the bind variable in the Execute method.
&SQL = CreateSQL("%Insert(:1)");
While /* Still something to do */
/* Set all the field values of &ABS_HIST. */
&SQL.Execute(&ABS_HIST);
End-While;
&SQL.Close();
The following example creates two SQL objects, one to be used for fetching, the other for updating the record. The first SQL object selects all the records in the &ABS_HIST record that match &EMPLID. The data is actually retrieved using the Fetch method. After values are set on the record, the update is performed by the Execute.
&SQL1 = CreateSQL("%Select(:1) where EMPLID = :2", &ABS_HIST, &EMPLID);
&SQL_UP = CreateSQL("%Update(:1)");
While &SQL1.Fetch(&ABS_HIST);
/* Set some field values of &ABS_HIST. */
&SQL_UP.Execute(&ABS_HIST);
End-While;
&SQL_UP.Close();
The following is an example of inserting an array of records:
Local SQL &SQL;
Local array of Record &RECS;
/* Set up the array of records. */
. . .
/* Create the SQL object open on an insert */
/* statement, and unbound */
&SQL = CreateSQL("%Insert(:1)");
/* While the array has something in it */
While &RECS.Len
/* Insert the first record of the array, */
/* and remove it from the array. */
&SQL.Execute(&RECS.Shift());
End-While;
Syntax
Fetch(paramlist)
Where paramlist is an arbitrary-length list of values in the form:
outvar1 [, outvar2] ...
Description
The Fetch method retrieves the next row of data from the SELECT that is open on the SQL object. Any errors result in termination of the PeopleCode program with an error message.
If there are no more rows to fetch, Fetch returns as False, the outvars are set to their default PeopleCode values, and the SQL object is automatically closed.
Using Fetch with a closed SQL object is processed the same as when there are no more rows to fetch.
Note: If you want to fetch only a single row, the SQLExec function can perform better, as it fetches only a single row from the server.
The return of Fetch is not optional, that is, you must check for the value of the fetch.
Setting Data Fields to Null
This method will not set Component Processor data buffer fields to NULL after a row not found fetching error. However, it does set fields that aren’t part of the Component Processor data buffers to NULL. It does set work record fields to NULL.
Parameters
|
Field or Control |
Definition |
|---|---|
| paramlist |
Specify output variables from the SQL Select statement. |
Returns
The result of Fetch is True if a row was fetched. If there are no more rows to fetch, the result is False.
Example
In the following example, the Fetch method is used first to process a single row, then to process the ABS_HIST record.
Local SQL &SQL;
Local Record &ABS_HIST;
&ABS_HIST = CreateRecord(RECORD.ABSENCE_HIST);
&SQL = GetSQL(SQL.SEL27, 15, "Smith");
While &SQL.Fetch(&NAME1, &BIRTH_DT)
/* Process NAME1, BIRTHDT from the selected row. */
End-While;
&SQL.Open(SQL.SEL_ABS_HIST, &NAME1, "Smith");
While &SQL.Fetch(&ABS_HIST)
/* Process ABS_HIST record. */
The following is an example of reading in an array of record objects:
Local SQL &SQL;
Local Record &REC;
Local Array of Record &RECS;
/* Get the SQL object open and ready for fetches. */
&SQL = CreateSQL("%SelectAll(:1) where EMPLID = :2", RECORD.ABSENCE_HIST, &EMPLID);
/* Create the first record. */
&REC = CreateRecord(RECORD.ABSENCE_HIST);
/* Create an empty array of records. *
&RECS = CreateArrayRept(&REC, 0);
While &SQL.Fetch(&REC)
/* We got a record, add it to the array */
/* and create another.*/
&RECS.Push(&REC);
&REC = CreateRecord(RECORD.ABSENCE_HIST);
End-While;
Syntax
Open(sql [, paramlist])
Where paramlist is an arbitrary-length list of values in the form:
inval1 [, inval2] ...
Description
The Open method associates the sql statement with the SQL object. The sql parameter can be either:
A string value giving the SQL statement.
A reference to a SQL definition in the form SQL.sqlname.
If the SQL object was already open, it is first closed. This terminates any incomplete fetching of select result rows, completes any buffered operations (that is, using BulkMode), and disassociates the SQL object from any SQL statement that was open on it.
Opening and Processing sql
If sql 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 sql 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 more effective. If you want to delete, insert, or update a record object, use the record Delete, Insert, or Update methods with the record object.
If sql 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.
Setting Data Fields to Null
This method will not set Component Processor data buffer fields to NULL after a row not found fetching error. However, it does set fields that aren’t part of the Component Processor data buffers to NULL.
Parameters
|
Field or Control |
Definition |
|---|---|
| sql |
Specify either a SQL string or a reference to a SQL definition in the form SQL. sqlname. |
| paramlist |
Specify input values for the SQL string. |
Returns
None.
Example
Generally, you use the Open method only after you’ve already gotten a reference to another SQL object. SELECT and SEL_ABS_HIST are the names of the SQL definitions created in Application Designer.
Local SQL &SQL;
&SQL = GetSQL(SQL.SELECT);
/* do other processing */
/* get next SQL statement for additional processing */
/* The open automatically closes the previous */
/* SQL statement */
&SQL.Open(SQL.SEL_ABS_HIST, &NAME1, "Smith");
While &SQL.Fetch(&ABS_HIST)
/* Process ABS_HIST record. */
End-While;