Handling an Unknown Column List
For an introduction on how to handle columns in a VALUES clause of an INSERT, see page 15-25. This section describes how to use a system-descriptor area to handle the INSERT...VALUES statement.
To use a system-descriptor area to handle input parameters in an INSERT
1. Prepare the INSERT statement (with the PREPARE statement) to give it a statement identifier. For more information, see "Assembling and Preparing the SQL Statement".
2. Allocate a system-descriptor area with the ALLOCATE DESCRIPTOR statement. For more information, see "Allocating Memory for a System-Descriptor Area".
3. Determine the number and data types of the columns with the DESCRIBE...USING SQL DESCRIPTOR statement. The DESCRIBE statement fills an item descriptor for each column in the select list. For more information on DESCRIBE, see "Initializing the System-Descriptor Area".
4. Save the number of unknown columns in a host variable with the GET DESCRIPTOR statement, which obtains the value of the COUNT field.
5. Set the columns to their values with the SET DESCRIPTOR statement, which sets the appropriate DATA and VALUE fields. The column values must be compatible with the data type of their associated column. If you insert a null value, set the appropriate INDICATOR field to -1. For more information on SET DESCRIPTOR, see "Assigning and Obtaining Values from a System-Descriptor Area".
6. Execute the INSERT statement to insert the values into the database.
The following sections demonstrate how to execute a simple INSERT statement that inserts only one row and one that uses an insert cursor to insert several rows from an insert buffer.
7. Deallocate the system-descriptor area with the DEALLOCATE DESCRIPTOR statement. See "Freeing Memory Allocated to a System-Descriptor Area".
Executing a Simple Insert
The following steps outline how to execute a simple INSERT statement with a system-descriptor area:
1. Prepare the INSERT statement (with the PREPARE statement) and give it a statement identifier.
2. Set the columns to their values with the SET DESCRIPTOR statement.
3. Execute the INSERT statement with the EXECUTE...USING SQL DESCRIPTOR statement.
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.
1 EXEC SQL include locator;
2 EXEC SQL include sqltypes;
3 main()
4 {
5 EXEC SQL BEGIN DECLARE SECTION;
6 int i;
7 int cnt;
8 loc_t loc1;
9 loc_t loc2;
10 EXEC SQL END DECLARE SECTION;
11 EXEC SQL create database txt_test;
12 chkerr("CREATE DATABASE txt_test");
13 EXEC SQL create table txt_a (t1 text not null, t2 text);
14 chkerr("CREATE TABLE t1");
15 /* The INSERT statement could have been created at runtime. */
16 EXEC SQL prepare sid from 'insert into txt_a values (?, ?)';
17 chkerr("PREPARE sid");
Lines 5 to 10
These lines declare host variables to hold the column values to insert (obtained from the user).
Lines 15 to 17
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 could be created at runtime. For more information on these steps, see "Assembling and Preparing the SQL Statement".
18 EXEC SQL allocate descriptor 'desc';
19 chkerr("ALLOCATE DESCRIPTOR desc");
20 EXEC SQL describe sid using sql descriptor 'desc';
21 chkerr("DESCRIBE sid");
22 EXEC SQL get descriptor 'desc' :cnt = COUNT;
23 chkerr("GET DESCRIPTOR desc");
24 for (i = 1; i <= cnt; i++)
25 prsysdesc(i);
Lines 18 and 19
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.
Line 20 and 21
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.
Lines 22 and 23
The GET DESCRIPTOR statement obtains the number of columns (COUNT field) found by the DESCRIBE. This number is stored in the cnt host variable.
Lines 24 and 25
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.
26 loc1.loc_loctype = loc2.loc_loctype = LOCFNAME;
27 loc1.loc_fname = loc2.loc_fname = "desc_ins.txt";
28 loc1.loc_size = loc2.loc_size = -1;
29 loc1.loc_oflags = LOC_RONLY;
30 i = CLOCATORTYPE;
31 EXEC SQL set descriptor 'desc' VALUE 1
32 TYPE = :i, DATA = :loc1;
33 chkerr("SET DESCRIPTOR 1");
34 EXEC SQL set descriptor 'desc' VALUE 2
35 TYPE = :i, DATA = :loc2;
36 chkerr("SET DESCRIPTOR 2");
37 EXEC SQL execute sid using sql descriptor 'desc';
38 chkerr("EXECUTE sid");
Lines 26 to 29
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".
Lines 30 to 36
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.
Lines 37 and 38
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.
39 loc1.loc_loctype = loc2.loc_loctype = LOCFNAME;
40 loc1.loc_fname = "txt_out1";
41 loc2.loc_fname = "txt_out2";
42 loc1.loc_oflags = loc2.loc_oflags = LOC_WONLY;
43 EXEC SQL select * into :loc1, :loc2 from a;
44 chkerr("SELECT");
45 EXEC SQL free sid;
46 chkerr("FREE sid");
47 EXEC SQL deallocate descriptor 'desc';
48 chkerr("DEALLOCATE DESCRIPTOR desc");
49 EXEC SQL close database;
50 chkerr("CLOSE DATABASE txt_test");
51 EXEC SQL drop database txt_test;
52 chkerr("DROP DATABASE txt_test");
53 EXEC SQL disconnect current;
54 }
55 chkerr(s)
56 char *s;
57 {
58 if (SQLCODE)
59 printf("%s error %ld\n", s, SQLCODE);
60 }
Lines 39 to 44
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.
Lines 45 to 48
The FREE statement (line 45) releases the resources allocated for the sid prepared statement. Once a prepared statement has been 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".
Lines 55 to 60
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."
Executing an INSERT That Is Associated with a Cursor
Your ESQL/C program must still use the DESCRIBE and SET DESCRIPTOR statements (page 16-29) 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:
1. Prepare the INSERT statement and associate it with an insert cursor with the DECLARE statement. All multirow INSERT statements must have a declared insert cursor.
2. Create the cursor for the INSERT statement with the OPEN statement.
3. Insert the first set of column values into the insert buffer with a PUT statement and its USING SQL DESCRIPTOR clause. After this PUT statement, the column values stored in the specified system-descriptor area are stored in the insert buffer. Repeat the PUT statement within a loop until there are no more rows to insert.
4. After all the rows are inserted, exit the loop and flush the insert buffer with the FLUSH statement.
5. Close the insert cursor with the CLOSE statement.
You handle the insert cursor in much the same way as you handle the cursor associated with a SELECT statement (page 16-13). For more information on how to use an insert cursor, refer to the PUT statement in the Informix Guide to SQL: Syntax.
| 
