When transferring data from an ESL application to an ORACLE database, you must perform the following tasks:

1.        Using asize, specify the number of rows of data you want transferred as a unit.

2.        Using astmt, issue an SQL INSERT, UPDATE, or DELETE statement, as you wish, naming the variables you want to use to hold outgoing data.

3.        Using one or more aval commands, specify the data values you want to apply with the statement specified in step 2.

4.        Issue aexec to transfer any rows left after the last batch transfer and terminate the batch transfer mode.

 

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 "asize 10\n" to SQLCommand_ASV

 call ProcessSQLCommand(SQLCommand_ASV)

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

 call ProcessSQLCommand(SQLCommand_ASV)

 copy "aval JONES, 9000\n" to SQLCommand_ASV

 call ProcessSQLCommand(SQLCommand_ASV)

 copy "aval SMITH, 9001\n" to SQLCommand_ASV 

 call ProcessSQLCommand(SQLCommand_ASV)

 copy "aval SCOTT, 9002\n" to SQLCommand_ASV

 call ProcessSQLCommand(SQLCommand_ASV)

   . . . . . . . . . 

 copy "aval SMOLLETT, 9009\n" to SQLCommand_ASV

 call ProcessSQLCommand(SQLCommand_ASV)

 copy "aval JOHNSON, 9010\n" to SQLCommand_ASV

 call ProcessSQLCommand(SQLCommand_ASV)

 copy "aval SMYTHE, 9011\n" to SQLCommand_ASV

 call ProcessSQLCommand(SQLCommand_ASV)

 copy "aval WILBERFORCE, 9012\n" to SQLCommand_ASV

 call ProcessSQLCommand(SQLCommand_ASV)

   . . . . . . 

 copy "aval WODEHOUSE, 9020\n" to SQLCommand_ASV

 call ProcessSQLCommand(SQLCommand_ASV)

 copy "aexec\n" to SQLCommand_ASV 

 call ProcessSQLCommand(SQLCommand_ASV)

 

In this example:

•asize 10 allocates enough memory to hold 10 rows of data at a time.

•astmt insert ... specifies the SQL statement you want executed. Note that :x and :y are ORACLE placeholders in the statement for parsing. Note that you may provide actual data values instead of placeholders, but if you do, use only single quotes (' ') around the data value; e.g., 'SMITH'. Refer to the ORACLE documentation for a discussion of the use of placeholders.

•The aval commands allocate arrays of pointers to character strings based on asize and the number of variables indicated by the statement. The values supplied are stored in character string variables.

•The aexec command executes the astmt statement for any remaining aval statements. You must issue an aexec statement to properly terminate the batch transfer mode.

When ESL has received a number of aval statements equal to the asize specification, it executes the astmt statement. Upon completion, it continues, expecting either additional aval statements or an aexec statement.