When processing arrays of data from an ESL application to an ODBC database, you must perform the following tasks:

1.Using astmt, issue an SQL SELECT, INSERT, UPDATE, or DELETE statement, as you wish, using placeholders "?" instead of the value. The database will attempt to prepare the statement, then wait for the placeholders to be replaced by values before execution.

2.Using a aval commands, specify the data values you want to apply with the statement specified in step 1.

3.Issue aexec to execute the SQL statement with the last set of values specified in step 2.

4.Repeat steps 2 and 3 as required.

If the batch commands are used in the wrong order the following warning message will be issued and the batch process terminated.

ERROR: Array mode sequence error; command not valid

ERROR: Array Mode terminated

or,

ERROR: Not in Array mode; command not valid

If a non batch command is used before the SQL statement being executed, then the following error message will be returned:

ERROR: Command not allowed in array mode

ERROR: Array Mode terminated

 

Example

subroutine ProcessSQLCommand(string: Command_SV) is

 begin guarded

         response to start

                 send Command_SV to SQL

         response to "SQL>"

                 leave block

 end

 

response to BeginInsert 

 copy "astmt insert into emp ( ename, enum) values (?, ?)\n" to SQLCommand_ASV

 call ProcessSQLCommand(SQLCommand_ASV)

 copy "aval JONES, 9000\n" to SQLCommand_ASV

 call ProcessSQLCommand(SQLCommand_ASV)

 copy "aexec\n" to SQLCommand_ASV

 call ProcessSQLCommand(SQLCommand_ASV)

 copy "aval SMITH, 9001\n" to SQLCommand_ASV 

 call ProcessSQLCommand(SQLCommand_ASV)

 copy "aexec\n" to SQLCommand_ASV

 call ProcessSQLCommand(SQLCommand_ASV)

 copy "aval SCOTT, 9002\n" to SQLCommand_ASV

 call ProcessSQLCommand(SQLCommand_ASV)

          copy "aexec\n" to SQLCommand_ASV

 call ProcessSQLCommand(SQLCommand_ASV)

 

In this example:

•astmt insert ... specifies the SQL statement you want executed. Note that ? are ODBC placeholders in the statement for parsing. For each placeholder, a buffer is allocated dependent on the size of the field that the placeholder is associated with.

•The aval commands copies character strings values based on the number of variables indicated by the statement. The values supplied are stored in character string variables.

•The aexec command executes the astmt statement, so the application must wait until the processing on the statement is completed, before continuing with the next row of values or any other command.