|
For an introduction on how to handle columns in a VALUES clause of an INSERT, see page 15-27. This section describes how to use a system-descriptor area to handle the INSERT...VALUES statement.
The following steps outline how to execute a simple INSERT statement with a system-descriptor area:
The sample program described on the following pages shows how to execute a dynamic INSERT statement. The program inserts two TEXT values into the txt_a table. It reads the text values from a named file called desc_ins.txt. The program then selects columns from this table and stores the TEXT values in two named files, txt_out1 and txt_out2. The program illustrates the use of a system-descriptor area to handle the columns that are in the column list. This INSERT statement is not associated with an insert cursor.
These lines declare host variables to hold the column values to insert (obtained from the user).
These lines assemble the character string for the statement and prepare it as the sid statement identifier. The input parameter specifies the missing columns of the INSERT statement. The INSERT statement is hard coded here, but it can be created at runtime. For more information on these steps, see Assembling and Preparing the SQL Statement.
To be able to use a system-descriptor area for the columns, you must first allocate the system-descriptor area. This ALLOCATE DESCRIPTOR statement allocates a system-descriptor area named desc.
The DESCRIBE statement describes the columns for the prepared INSERT that sid identifies. This DESCRIBE statement includes the USING SQL DESCRIPTOR clause to specify the desc system-descriptor area as the location for these column descriptions.
The GET DESCRIPTOR statement obtains the number of columns (COUNT field) found by the DESCRIBE. This number is stored in the cnt host variable.
This for loop goes through the item descriptors for the columns of the INSERT statement. It uses the cnt variable to determine the number of item descriptors that are initialized by the DESCRIBE. For each item descriptor, the prsysdesc() function saves information such as the data type, length, and name in host variables. For a description of prsysdesc(), see lines 58 to 77 on page 16-21.
To insert a TEXT value, the program must first locate the value with an ESQL/C locator structure. The loc1 locator structure stores a TEXT value for the t1 column of the txt_a table; loc2 is the locator structure for the t2 column of txt_a. (See line 13.) The program includes the ESQL/C locator.h header file (line 1) to define the loc_t structure.
Both TEXT values are located in a named file (loc_loctype = LOCFNAME) called desc_ins.txt. When you set the loc_size fields to -1, the locator structure tells ESQL/C to send the TEXT value to the database server in a single operation. For more information about how to locate TEXT values in named files, see Inserting a Simple Large Object from a Named File.
The first SET DESCRIPTOR statement sets the TYPE and DATA fields in the item descriptor of the t1 column (VALUE 1). The data type is CLOCATORTYPE (defined in the ESQL/C sqltypes.h header file) to indicate that the column value is stored in an ESQL/C locator structure; the data is set to the loc1 locator structure. The second SET DESCRIPTOR statement performs this same task for the t2 column value; it sets its DATA field to the loc2 locator structure.
The database server executes the INSERT statement with the EXECUTE...USING SQL DESCRIPTOR statement to obtain the new column values from the desc system-descriptor area.
The program uses the loc1 and loc2 locator structures to select the values just inserted. These TEXT values are read into named files: the t1 column (in loc1) into txt_out1 and the t2 column (in loc2) into txt_out2. The loc_oflags value of LOC_WONLY means that this TEXT data will overwrite any existing data in these output files.
The FREE statement (line 45) releases the resources allocated for the sid prepared statement. Once a prepared statement was freed, it cannot be used again in the program. The DEALLOCATE DESCRIPTOR statement (line 46) releases the memory allocated to the desc system-descriptor area. For more information, see Freeing Memory Allocated to a System-Descriptor Area.
The chkerr() function is a very simple exception-handling routine. It checks the global SQLCODE variable for a nonzero value. Since zero indicates successful execution of an SQL statement, the printf() (line 58) executes whenever a runtime error occurs. For more detailed exception-handling routines, see Chapter 11, Exception Handling.
Your ESQL/C program must still use the DESCRIBE and SET DESCRIPTOR statements (Handling an Unknown Column List) to use a system-descriptor area for column-list values of an INSERT statement that inserts rows from an insert buffer. It must also use the PUT...USING SQL DESCRIPTOR statement with an insert cursor, as follows:
You handle the insert cursor in much the same way as you handle the cursor associated with a SELECT statement (Handling an Unknown Select List). For more information on how to use an insert cursor, refer to the PUT statement in the Informix Guide to SQL: Syntax.