A sample program that executes a dynamic INSERT statement
This sample program shows how to execute a dynamic INSERT statement. This INSERT statement is not associated with an insert cursor.
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.
=======================================================================
1. EXEC SQL include locator;
2. EXEC SQL include sqltypes;
3. main()
4. {
5. EXEC SQL BEGIN DECLARE SECTION;
6. int ;
7. int cnt;
8. ifx_loc_t loc1;
9. ifx_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 - 10
These lines declare host variables to hold the column values to insert (obtained from the user).
Lines 15 - 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 can be created at run time. For more information about these steps, see Assemble and prepare 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 The ifx_int8add() function.
=======================================================================
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 - 29
To insert a TEXT value, the program must first locate the value with the Informix® 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 Informix® ESQL/C locator.h header file (line 1) to define the ifx_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 Informix®
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 Insert a simple large object from a named file.
Lines 30 - 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 Informix® ESQL/C sqltypes.h header file) to indicate that the column value is stored in the Informix® 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 %d\n", s, SQLCODE);
60. }
=======================================================================Lines 39 - 44
The program uses the loc1 and loc2 locator structures to select the values 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 overwrites any existing data in these output files.
Lines 45 - 48
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 Free memory allocated to a system-descriptor area.
Lines 55 - 60
The chkerr() function is a 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 Exception handling.