SQL Class Properties

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

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;

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;

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;

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.

Related Links

Reusing a Cursor

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;

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:

Numeric Value

Constant Value

Description

0

%SQLStatus_OK

No Errors.

1

%SQLStatus_NotFound

Record not found.

2

%SQLStatus_Duplicate

Duplicate Record Found

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;

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;

Image: Trace Timings Example

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

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;