A sample program that uses an sqlda structure
The program illustrates how to use an sqlda structure to handle both input parameters of a WHERE clause and the columns in the select list.
=======================================================================
1. #include <stdio.h>
2. EXEC SQL include sqlda;
3. EXEC SQL include sqltypes;
4. #define FNAME 15
5. #define LNAME 15
6. #define PHONE 18
=======================================================================Line 2
The program must include the Informix® ESQL/C sqlda.h header file to use an sqlda structure.
=======================================================================
7. main()
8. {
9. char fname[ FNAME + 1 ];
10. char lname[ LNAME + 1 ];
11. char phone[ PHONE + 1 ];
12. int count, customer_num, i;
13. struct sqlvar_struct *pos;
14. struct sqlda *sqlda_ptr;
15. printf("Sample ESQL program running.\n\n");
16. EXEC SQL connect to 'stores7';
17. stcopy("Carole", fname);
18. stcopy("Sadler", lname);
19. EXEC SQL prepare sql_id from
20. 'select * from customer where fname=? and lname=?';
21. EXEC SQL declare slct_cursor cursor for sql_id;
=======================================================================Lines 9 - 14
Lines 9 - 11 declare variables to hold the data that is obtained from the user. The sqlda_ptr variable (line 14) is the pointer to an sqlda structure. The pos variable (line 13) points to an sqlvar_struct structure so that the code can proceed through each of the sqlvar_struct structures in the variable-length portion of sqlda. Neither of these variables is defined as the Informix ESQL/C host variable.
Lines 17 - 20
These lines assemble the character string for the SELECT statement and prepare the SELECT string. This program assumes the number and data types of the input parameters. Therefore, no C code needs to determine this information at run time. The question mark (?) indicates the input parameters in the WHERE clause. For more information about these steps, see Assemble and prepare the SQL statement.
Line 21
This line declares the slct_cursor cursor for the prepared statement identifier, sql_id.
=======================================================================
22. count=2;
23. whereClauseMem(&sqlda_ptr, count, fname, lname);
24. EXEC SQL open slct_cursor using descriptor sqlda_ptr;
25. free(sqlda_ptr->sqlvar);
26. free(sqlda_ptr);
=======================================================================Lines 22 and 23
These lines initialize the sqlda structure with the input parameter information. The program assumes two input parameters (line 22). If the number of input parameters is unknown, the program needs to parse the SELECT character string (not the prepared version) and count the number of characters that it contains.
The program then calls the whereClauseMem() function to allocate and initialize the sqlda structure. For more information, see lines 69 - 77.
Line 24
The database server executes the SELECT statement when it opens the cursor. You must include the USING DESCRIPTOR clause of OPEN to specify the sqlda structure as the location of the input parameter values.
Lines 25 and 26
Once the OPEN...USING DESCRIPTOR statement has executed, these input parameter values have been used. Deallocate this sqlda structure because it is no longer needed and so that it does not conflict with the sqlda that contains the retrieved values. Keep in mind that this second sqlda must have memory allocated before it can be used.
=======================================================================
27. EXEC SQL describe sql_id into sqlda_ptr;
28. selectListMem(sqlda_ptr);
29. while(1)
30. {
31. EXEC SQL fetch slct_cursor using descriptor sqlda_ptr;
32. if(SQLCODE != 0)
33. {
34. printf("fetch SQLCODE %d\n", SQLCODE);
35. break;
36. }
=======================================================================Line 27
For demonstration purposes, this program assumes that the number and data types of the select-list columns are also unknown at compile time. It uses the DESCRIBE...INTO statement (line 27) to allocate an sqlda structure, and puts information about the select-list columns into the structure to which sqlda_ptr points.
Lines 28
The selectListMem() function handles the allocation of memory for column values. For more information about selectListMem(), see Lines 85 - 102.
Lines 29 - 31
The while loop executes for each row fetched from the database. The FETCH statement (line 31) includes the USING DESCRIPTOR clause to specify an sqlda structure as the location for the returned column values. For more information about how to handle unknown select-list columns, see Handling an unknown select list.
Lines 32 - 36
These lines test the value
of the SQLCODE variable to determine if the FETCH was successful.
If SQLCODE contains a nonzero value, then the FETCH generates the
NOT FOUND condition (100) or an error (< 0).
In any of these cases, line 34 prints out the SQLCODE value. To determine
if the FETCH statement generated warnings, you need to examine the sqlca.sqlwarn structure.
=======================================================================
37. for(i=0; i<sqlda_ptr->sqld; i++)
38. {
39. printf("\ni=%d\n", i);
40. prsqlda(sqlda_ptr->sqlvar[i]);
41. switch (i)
42. {
43. case 0:
44. customer_num = *(int *)(sqlda_ptr->sqlvar[i].sqldata);
45. break;
46. case 1:
47. stcopy(sqlda_ptr->sqlvar[i].sqldata, fname);
48. break;
49. case 2:
50. stcopy(sqlda_ptr->sqlvar[i].sqldata, lname);
51. break;
52. case 9:
53. stcopy(sqlda_ptr->sqlvar[i].sqldata, phone);
54. break;
55. }
56. }
57. printf("%d ==> |%s|, |%s|, |%s|\n",
58. customer_num, fname, lname, phone);
59. }
60. EXEC SQL close slct_cursor;
61. EXEC SQL free slct_cursor;
62. EXEC SQL free sql_id;
=======================================================================Lines 37 - 59
These lines access the fields of the sqlvar_struct structure for each column in the select list. The prsqlda() function (see lines 75 - 81) displays the column name (from sqlvar_struct.sqlname) and its value (from the sqlvar_struct.sqldata field). The switch (lines 41 - 55) transfers the column values from the sqlda structure into host variables of the appropriate lengths and data types.
Lines 60 - 62
These lines free resources after all the rows are fetched. Line 60 closes the slct_cursor cursor and line 61 frees it. Line 62 frees the sql_id statement ID.
=======================================================================
63. free(sqlda_ptr->sqlvar);
64. free(sqlda_ptr);
65. EXEC SQL close database;
66. EXEC SQL disconnect current;
67. printf("\nProgram Over.\n");
68. }
69. whereClauseMem(descp, count, fname, lname)
70. struct sqlda **descp;
71. int count;
72. char *fname, *lname;
73. {
74. (*descp)=(struct sqlda *) malloc(sizeof(struct sqlda));
75. (*descp)->sqld=count;
76. (*descp)->sqlvar=(struct sqlvar_struct *)
77. calloc(count, sizeof(struct sqlvar_struct));
=======================================================================Lines 63 and 64
These free() system calls release the memory that is associated with the sqlda structure. Line 63 releases the memory allocated to the sqlvar_struct structures. Line 64 releases the memory allocated for the sqlda structure. The program does not need to deallocate memory associated with the sqldata fields because these fields have used space that is in a data buffer. For more information, see Free memory allocated to an sqlda structure.
Lines 69 - 77
The whereClauseMem() function initializes the sqlda structure with the input-parameter definitions. Line 74 allocates memory for an sqlda structure to hold the input parameters in the WHERE clause. Use of a DESCRIBE...INTO statement to allocate an sqlda results in an sqlda that holds information about the select-list columns of the SELECT. Because you want to describe the input parameters in the WHERE clause, do not use DESCRIBE here.
Line 75 sets the sqld field of the sqlda structure
to the value of count (2) to indicate the
number of parameters that are in the WHERE clause. Lines 76 and 77
use the calloc() system function to allocate the
memory so that each input parameter in the WHERE clause has an sqlvar_struct structure.
These lines then set the sqlvar field of the sqlda structure
so that it points to this sqlvar_struct memory.
=======================================================================
78. (*descp)->sqlvar[0].sqltype = CCHARTYPE;
79. (*descp)->sqlvar[0].sqllen = FNAME + 1;
80. (*descp)->sqlvar[0].sqldata = fname;
81. (*descp)->sqlvar[1].sqltype = CCHARTYPE;
82. (*descp)->sqlvar[1].sqllen = LNAME + 1;
83. (*descp)->sqlvar[1].sqldata = lname;
84. }
85. selectListMem(descp)
86. struct sqlda *descp;
87. {
88. struct sqlvar_struct *col_ptr;
89. static char buf[1024];
90. int pos, cnt, size;
91. printf("\nWITHIN selectListMem: \n");
92. printf("number of parms: %d\n", descp->sqld);
93. for(col_ptr=descp->sqlvar, cnt=pos=0; cnt < descp->sqld;
94. cnt++, col_ptr++)
95. {
96. prsqlda(col_ptr);
97. pos = rtypalign(pos, col_ptr->sqltype);
98. col_ptr->sqldata = &buf[pos];
99. size = rtypmsize(col_ptr->sqltype, col_ptr->sqllen);
100. pos += size;
101. }
102. }
=======================================================================Lines 78 - 84
Lines 78 - 80 set the sqltype, sqllen, and sqldata fields of the sqlvar_struct structure to describe the first input parameter: a character (CCHARTYPE) host variable of length 16 (FNAME + 1) whose data is stored in the fname buffer. The fname buffer is a character buffer declared in the main() program and passed as an argument to whereClauseMem().
Lines 81 - 83 set the sqltype, sqllen, and sqldata fields of the sqlvar_struct structure to describe the second input parameter. This parameter is for the lname column. It is defined in the same way as the fname column (lines 78 - 80) but it receives its data from the lname buffer [also passed from main() to whereClauseMem()].
Lines 85 - 102
The selectListMem() function allocates the memory and initializes the sqlda structure for the unknown select-list columns of the parameterized SELECT statement. For more information about how to use an sqlda structure for select-list columns, see Handling a parameterized SELECT statement.