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