SQL Class

This chapter provides an overview of the SQL class and discusses the following topics:

• Using Record class SQL.

• Creating a SQL definition.

• Binding and executing of SQL statements.

• Fetching from a SELECT.

• Using an array of any for bind values or fetch results.

• Reusing a cursor.

• Understanding SQL objects and Application Engine programs.

• Declaring a SQL object.

• Scope of a SQL object.

• SQL class built-in functions.

• SQL class methods.

• SQL class properties.

See Also

Understanding SQL Objects and Application Engine Programs

Record Class

Accessing the Data Buffer

Understanding SQL Class

You can create SQL definitions in Application Designer. These can be entire SQL programs, or just fragments of SQL statements that you want to re-use. PeopleCode provides the SQL class for accessing these SQL definitions in your PeopleCode program at runtime.

The SQL class provides capability beyond that offered by SQLExec. Unlike SQLExec, which fetches just the first SELECTed row, operations for the SQL class allow iteration over all rows fetched. This can dramatically improve performance if you’re doing a million operations and you’ve set the BulkMode property to True.

A list of input (bind) values, and a list of output variables are supported, as they are in SQLExec. The input and output variables are limited to the same PeopleCode types that can be used with SQLExec, with the addition of a new class called Record.

At runtime, you instantiate a record object from the Record class. A record object is a "one row" instantiation of a record definition.

• When used as an output from an SQL object, each next field in the record is populated with the next column returned.

• When used as an input, several fields in the record are used to replace a single bind marker in the SQL statement. The bind marker for a record describes the type of substitution to be done.

Both records and other PeopleCode types can be mixed in both the output and input.

At runtime, you instantiate a SQL object from the SQL class. The SQL object is loaded by either a constructor for the object, or an explicit Open method call. Optionally, a SQL constructor and the Open method support setting the SQL statement through a string parameter. This capability enables the creation and execution of ad-hoc SQL statements.

The SQL class has a Fetch method for iterating through the rows fetched by a select. A cursor is used to control this connection between the runtime SQL object and the database. The cursor is closed automatically when the object goes out of scope. The cursor can be closed before that by using the Close method. The status of the connection is available from the Boolean IsOpen property.

The general status of operations is available from function or method return values or properties. Detailed status is not available, as the operations are designed to terminate on unexpected errors or errors that cannot be reasonably recovered from by application level logic.

Considerations for Extra Spaces

On the DB2 UDB for OS/390 and z/OS platform, when the zparm option is set to decimal equals comma (so comma is used as the database decimal separator), extra blanks are added after commas to ensure that they are not mistaken for decimal separators.

Considerations for Case Sensitivity

When processing a SQL statement, the system automatically casts all fieldnames and possibly record names to uppercase when processing a SQL statement. When processing records from a third party, fields that are lowercase get cast into uppercase, which can create a runtime issue on case sensitive platforms.

To prevent this, use the %NoUppercase meta-SQL statement at the beginning of the SQL statement.

See Also

%NoUppercase

Using Record Class SQL

There are some SQL type operations that you can do with the Record class, such as INSERT, DELETE and UPDATE. The advantage of using the Record class methods is ease of use, re-use of code, and so on. However, if you’re doing many iterations of the same operation (like a million UPDATEs) you should use the SQL object with the BulkMode property set to True.

The SQL object maintains a state (that is, a cursor). Hence, if your database can take advantage of BulkMode, instead of a million operations, the commands are "bulked up" and committed only once. This can improve performance dramatically.

Creating a SQL Definition

You can create SQL definitions in Application Designer, using the SQL editor. These SQL statements can be entire SQL programs, or just fragments that you want to re-use. After you have created a SQL definition, you can use it to populate a SQL object (using FetchSQL, Open, or GetSQL)

You can also create a SQL statement in PeopleCode (using CreateSQL), save it as a SQL definition (StoreSQL), then access it in Application Designer.

See Also

Open

Using the SQL Editor

FetchSQL

CreateSQL

GetSQL

StoreSQL

Binding and Executing of SQL Statements

The processing of an SQL statement involves a series of steps.

1. The binding process is the replacement of (variable) input values in the statement, in places indicated by bind placeholders.

   The input values are substituted into the SQL statement in place of the bind placeholders. These placeholders have the form ":number", or "%bindop(:number [,  parm]. . .)" where the number indicates which input value is to be substituted, and the bindop and parm strings indicate what meta-SQL binding function is to be performed.

   Note. There must be no spaces between the bindop and the left parenthesis.

   The following binding meta-SQL functions are used with record objects to substitute various forms of fieldnames and values into the SQL statement. The goal of these binding functions is to enable the writing of SQL and PeopleCode that can manipulate records without dependencies on the exact fields in the records.

   • %DTTM

   • %InsertValues

   • %KeyEqual

   • %KeyEqualNoEffDt

   • %List

   • %OldKeyEqual

   • %Table

   • %UpdatePairs

2. The execution of an SQL statement is the carrying out of the operation of the statement by the database engine.

   This view of SQL statement processing is actually simpler than what actually occurs. In actual practice, the binding occurs in two distinct phases. The database engine (or its supporting routines) is aware of only the latter phase. For some operations, some database engines are able to delay the second stage of binding and the execution of an SQL statement, so the statement can be rebound and re-executed (with different input values). The advantage of this is that these bindings and executions can be accumulated and transmitted across the network to the database server, with several database operations being done in one network operation. This is sometimes referred to as bulk mode. Because the network time consumes most database time operations, the performance advantages of bulk mode can be significant.

   However, in bulk mode, individual operations might not be executed immediately by the database engine. The result is that the application might not see errors that arise until later operations are performed.

   SQL SELECT statements are not bound multiple times, rather the retrieved rows are accumulated and sent across the network many rows at a time, also decreasing the effect of network delays.

See Also

Meta-SQL Elements

Fetching From a SELECT

Some operations fetch a row of data into a list of output variables. The values of the fields from the select row are assigned in order to the given output variables. If one of these is a reference to a PeopleCode record object, the fields in the record are assigned the successive values from the row of the select, until all the fields are assigned. Assignment then continues with the next output variable, if any. The number of output fields and variables must equal the number of fields in the row of the select.

See Also

Accessing the Data Buffer

Using Array of Any for Bind Values or Fetch Results

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 for the Execute, Fetch, and Open methods. This is generally used when you don't know how many values are needed until the code runs.

For example, suppose that you had a SQL definition INSERT_TEST, that dynamically (that is, at runtime) generated the following SQL statement:

"INSERT INTO PS_TESTREC (TESTF1, TESTF2, TESTF3, TESTF4, . . . ​N) VALUES (:1, :2,⇒
 %DateTimeIn(:3), %TextIn(:4)), . . . ​N";

Suppose you have placed the values to be inserted into an Array of Any, say &AAny:

&AAny = CreateArrayAny("a", 1, %DateTime, "abcdefg", . . . ​N);

You can execute the insert with the following:

&AAny = CreateArrayAny("a", 1, %DateTime, "abcdefg", . . . ​N);
&SQL = GetSQL(SQL.INSERT_TEST);
While /* Still something to do */
   &SQL.Execute(&AAny);
End-While;
&SQL.Close();

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.

Reusing a Cursor

Reusing a cursor means compiling a SQL statement just once, but executing it more than once. When a SQL statement is compiled, the database checks the syntax and chooses a query path. By doing this only once, but executing this statement several times, you can obtain an improvement in performance.

In PeopleCode, you can reuse a cursor either by using the ReuseCursor property or by following certain restrictions in your code. In this section, we discuss how to:

• Use the ReuseCursor property.

• Reuse a cursor without the ReuseCursor property.

Using the ReuseCursor Property

If you specify the ReuseCursor property as True, the cursor isn't closed until either the SQL object is explicitly closed or re-opened. This provides greater control over the cursor associated with your SQL object. However, when you set ReuseCursor to True, you're essentially pledging to do the right thing in your program. There are some considerations for how you use this property:

In a Application Engine program, if your program can be restarted, you mustcheck for a restart after a checkpoint by testing to see if the SQL object is open after the checkpoint. If it isn't open, that means a restart has happened, and you must reopen the SQL object. In most cases, on checkpoints, your open SQL objects aren't closed, saving the overhead of re-establishing the SQL object after the checkpoint. If the SQL object is open on a select statement at a checkpoint it isn't restored to the open state because you cannot reliably establish the state of the execute-fetch sequence.

In the following example the SQL object is established on a select statement which is executed twice with different bind parameters but is compiled only once. Without the ReuseCursor property the SQL object would be closed after the first fetch cycle completes.

Local SQL &Sql;
Local String &Company1 = "CCB";
Local String &Company2 = "CCF";

&Sql = CreateSQL("SELECT A.COMPANY, %Timeout(A.EFFDT) FROM %Table(COMPANY_TBL) A⇒
 WHERE A.COMPANY=:1", &Company1);
/* commenting this out should make the subsequent Execute fail */
&Sql.ReuseCursor = True;

While &Sql.Fetch(&Company1, &Effdt)
   /* processing for the first Company */

End-While;

&Sql.Execute(&Company2);

While &Sql.Fetch(&Company1, &Effdt)
/* processing for the second company */

End-While;

&Sql.Close();

Reusing a Cursor Without the ReuseCursor Property

If you want to reuse a cursor in your program, there are several conditions:

• You have to use the SQL object. Only SQL objects retain SQL cursor information.

• For INSERT, DELETE, and UPDATE statements, you automatically reuse the cursor as long as you don't change the SQL statement as part of the binding process. If the SQL statement is textually the same, so only the binds have changed, you get reuse. For example, you won't get reuse if you first bind one kind of record object to %Insert(), then bind another, different kind of record object.

• For SELECTs, you mustuse the meta-SQL shortcuts (%SelectAll, %SelectByKey, or %SelectByKeyEffDt), and the CreateSQL or GetSQL functions without supplying any bind or buffer parameters. The bind parameters are supplied in the Execute function. The Fetch parameter must be the fetch buffer, and be the same as the first Execute parameter. You can supply WHERE clauses, ORDER-BY, and so on, on the end of the SQL string containing the meta-SQL.

Note. BulkMode doesn't reuse the cursor in the same way as the SQL object. BulkMode requests that, when the system can reuse the cursor, it also holds all the changes so they can be communicated to the database in batches.

By using one of the forms of the Select meta-SQL, you're guaranteeing the resulting fetched values are all put into one record object (buffer). This means the implementation doesn't have to ask the database for the length and type of each column: the record buffer is already defined. So this sample code uses the SQL object to maintain the state of the connection with the database, and the record object, to maintain a series of fields suitable for database operations.

Local SQL &SQL;
Local Record &REC;

&REC = CreateRecord(Record.KP_KPI_DFN);

/* start with select statement, no bind refs, no */
/* bind parameters */

&SQL = CreateSQL("%Selectall(:1) Where SETID = :2 and KPI_ID = :3 and EFFDT = ⇒
(SELECT MAX(EFFDT) FROM PS_KP_KPI_DFN WHERE SETID = :4 AND KPI_ID = :5 AND ⇒
EFFDT <= %DateIn(:6))");

Start Loop

/* bind and execute the statement */

&SQL.Execute(&REC, &SETID_KPI, &COMPID, &SETID_KPI, &COMPID, &EFFDT); 

/* Note record parameter for Fetch statement must be 
the same as the first Execute parameter 
the results are in this record */

If &SQL.Fetch(&REC) Then

   /*process this record */

End-If;

End Loop

&SQL.Close(); /* there is no implicit close on a Fetch returning False */

The following example comes from a Application Engine program. Because the loop goes in and out of PeopleCode, you must declare the SQL object as Global. This example is in three parts.

1. Program called by Application Engine before the loop:

   Global SQL &SQL;
   
   &SQL = CreateSQL("INSERT INTO %Table(MY_WORK) (TRANS , REGISTERWRITE) VALUES (:1, ⇒
   :2)");
   
   &SQL.BulkMode = True; /* not required for reuse, but will get better performance⇒
    on platforms that support bulk insert */
   

2. Program called in the Application Engine loop on SELECT 'X' FROM PSRECDEFN WHERE RECNAME LIKE '%A':

   Global SQL &SQL;
   
   &var1 = "X";
   &var2 = "Y";
   
   &SQL.Execute(&var1, &var2);
   

3. Program called by Application Engine after the loop:

   Global SQL &SQL;
   
   &SQL.Close();

Understanding SQL Objects and Application Engine Programs

A global variable won’t go out of scope until a Application Engine program finishes. However, all SQL objects are forced closed (that is, the cursor closed) sooner than that. Any open SQL object is forced closed just before any checkpoint in an Application Engine program.This is to ensure that the application can be restarted successfully from the checkpoint. After the SQL object is closed, you can reopen the SQL object, or query its properties, (such as Status, IsOpen). The simplest way to avoid unnecessary closing of the SQL object is to set the ReuseCursor property to True. A restartable program should always check that SQL objects are open before using them in steps where it's expected they're open. In the absence of an intervening checkpoint, an open SQL object remains open until the Application Engine program finishes.

Declaring a SQL Object

SQL objects are declared as type SQL. For example:

Local SQL &MYSQL;

Global SQL &MySql = CreateSQL(SQL.MySql);

Scope of an SQL Object

An SQL object can be instantiated only from PeopleCode.

This object can be used anywhere you have PeopleCode, that is, in an application class, Application Engine PeopleCode, record field PeopleCode, and so on.

Your SQL statements sometimes change the database. When your SQL changes the database, your code should be only in one of the following events:

• SavePreChange

• WorkFlow

• SavePostChange

• Message Subscription

• FieldChange

• Application Engine PeopleCode action

SQL Class Built-in Functions

CreateSQL

DeleteSQL

FetchSQL

GetSQL

StoreSQL

SQL Class Methods

In this section, we discuss the SQL class methods. The methods are discussed in alphabetical order.

Close

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

See Also

Open.

CreateSQL

Execute

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

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;

See Also

Close, BulkMode.

CreateSQL

Fetch

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

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;

See Also

Open.

GetSQL

SQLExec

Open

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

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;

See Also

CreateSQL, GetSQL.

SQL Class Properties

In this section, we discuss the SQL class properties. The properties are discussed in alphabetical order.

BulkMode

Description

This property controls the use of bulk mode. Setting this property to True enables the use of bulk mode, and hence removes any guarantee of the synchronous presentation of error status.

Bulk mode is used only with those database connections and operations that support it. Bulk mode can be used with any SQL operation, that is, with INSERTs, DELETEs, or UPDATEs.

If you’re using a Application Engine program, and have set this property to True, the rows inserted in BulkMode are committed at the next database commit in your program.

After BulkMode operations, the RowsAffected property is not valid.

The default value for BulkMode is False.

This property is read-write.

Example

The following code is an example of inserting an array of records using bulk mode:

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)");
   /* Try for bulk mode.  */
   &SQL.BulkMode = True;
   /* While the array has something in it… */
   While &RECS.Len
      /* Insert the first record of the array, */
      /* and remove it from the array.  */
      If not &SQL.Execute(&RECS.Shift) then
         /* A duplicate record found, possibly */
         /* in bulk mode.  There is no way to  */
         /* tell which record had the problem. */
         /* One approach to recovery is to fail*/
         /* the transaction and retry it with a*/
         /* process that does only one record  */
         /* at a time, that is, doesn’t use    */
         /* bulk mode.*/
         .  .  .;
      End-If;
End-While;

See Also

RowsAffected

Binding and Executing of SQL Statements

IsOpen

Description

This property returns as True if the SQL object is open on some SQL statement.

This property is read-only.

Example

You might use the following in a Application Engine program, after a checkpoint. MYSELECT is the name of a SQL definition created in Application Designer:

If Not &MYSQL.IsOpen Then
   &MYSQL.Open(SQL.MYSELECT);
End-if;

LTrim

Description

This property specifies whether values read by the Fetch method are trimmed of blanks on the left, except in the following cases:

• For long columns.

• The record is directly used to buffer the incoming data. In the following example, the values aren't LTrimmed - regardless of the setting of the LTrim property.

  Local Record &Rec = CreateRecord(Record.QA_TEST);
  Local SQL &Sql = CreateSQL("%SelectAll(:1)");
  &Sql.LTrim = True; /* the default */
  &Sql.Execute(&Rec);
  While &Sql.Fetch(&Rec)
     /* do processing */
  End-While;
  

This property takes a Boolean value. The default value is True. If this property is set to False, the selected values are not trimmed of blanks on the left.

This property is read-write.

Note. The removal of blanks from the right end of fetched values (RTrimming) still occurs for non-long columns.

Example

Local Record &Rec = CreateRecord(Record.QA_TEST);
Local SQL &Sql = CreateSQL(%SelectAll(:1)", &Rec);
&Sql.LTrim = False;
While &Sql.Fetch(&Rec)
   /* do processing */
End-While;

ReuseCursor

Description

This property specifies whether the SQL object tries to reuse the open cursor. This property takes a Boolean value, True, to reuse the SQL cursor.

If specified as True, the SQL object won't be closed at checkpoints, and any non-SELECT SQL is restored. In addition the SQL object is not closed after a fetch cycle completes. It remains open ready to be executed, perhaps with different bind parameters.

If you use this property in your application program, you must close the SQL object explicitly or it is closed when the object goes out of scope (that is, when the program finishes.)

You must instantiate a SQL object first before you can reuse it.

This property is read-write.

See Also

Reusing a Cursor.

RowsAffected

Description

This property returns the number of rows affected by the last INSERT, UPDATE, or DELETE statement of the SQL object. After BulkMode operations, the RowsAffected property is not valid.

This property is read-only.

Example

The following code is an example that determines if a delete statement actually deleted anything:

Local SQL &SQL;

/* Create the SQL object and do the deletion.  */
   &SQL = CreateSQL("Delete from %Table(:1) where EMPLID = :1", RECORD.ABSENCE_⇒
HIST, &EMPLID);
   If &SQL.RowsAffected = 0 Then
   /* We did not delete any rows.  */
   .  .  .
End-If;

See Also

BulkMode.

Status

Description

This property returns the status of the last statement executed. You can use either the constant or the numeric value for this property. The values for this property are:

This property is read-only.

Example

The following example determines what went wrong after an update:

Local SQL &SQL;
Local Record &NEWREC, &OLDREC;

/* Create and initialize &OLDREC with the keys of the 
record to be updated.  Create and initialize &NEWREC 
with the new field values for the record.  */
...;

/* Create and execute the update.  */
&SQL = CreateSQL("%Update(:1, :2)", &NEWREC, &OLDREC);
Evaluate &SQL.Status
When = %SQLStatus_OK
   /* It worked.  */
When = %SQLStatus_NotFound
   /* The OLDREC keys were not found.  */
When = %SQLStatus_Duplicate
   /* The NEWREC keys were already there.  */
End-Evaluate;

TraceName

Description

This property enables you to assign a name to a SQL statement that has been created in PeopleCode using CreateSQL. This name is used in the Application Engine timings trace. This property takes a string value.

Note. You cannot associate the TraceName property with the execution of a simple SELECT statement created with CreateSQL. This is because the SELECT is executed when the SQL is created, before it has the TraceName assigned. To do this, create a SQL object instead.

If this property isn't set, it defaults to a substring of the SQL statement, indicating the operation and table, (for example, SELECT PS_VOUCHER_LINE.) It may be useful to set TraceName to indicate the origin of the SQL statement.

This property is read-write.

Example

&REC = CreateRecord(Record.VOUCHER_LINE);
&SQL = CreateSQL("%selectall(:1) WHERE BUSINESS_UNIT =:2 AND VOUCHER_ID =:3 AND⇒
 VOUCHER_LINE_NUM = :4");
&SQL.TraceName = "AEPROG.SECT1.STEP1.SQL2";
&SQL.Execute(&REC, MATCHING_AET.BUSINESS_UNIT, MATCHING_AET.VOUCHER_ID, &count);
If &SQL.Fetch(&REC) Then
      &count2 = &count2 + 1;
End-If;

The previous example would produce the following in the timings trace.

Trace Timings Example

You can use this parameter with the Open statement also. The following is an example of how this works:

&Sql1 = CreateObject("SQL");
&Sql1.TraceName = "sql1";
&Sql1.Open("Select %FirstRows(1) 'x' FROM psstatus");
&Sql1.Fetch(&Temp);

&Sql2 = CreateObject("SQL");
&Sql2.TraceName = "sql2";
&Sql2.Open("Select 'x' FROM psstatus");
&Sql2.Fetch(&Temp);

Value

Description

This property returns the SQL statement associated with the SQL object as a string.

This property is read-only.

Example

To report an error in a SQL definition, including the actual SQL executed, use the Value property to get the text of the SQL statement:

Local SQL &SQL;

/* Execute some SQL.  */
&SQL = CreateSQL(SQL.SOMESQL, &EMPLID);
If &SQL.Status = %SQLStatus.NotFound Then
   /* Get the SQL string used.  */
   &SQLSTR = &SQL.Value;
   /* Report the error.  */
   .  .  .;
End-If;