Client 4—An Interactive Query Processor

Following the pattern set in the previous few chapters, I'll wrap up the discussion of ecpg by developing an interactive query processor. Because of the complexity of using ecpg to handle dynamic queries, I'll take a few shortcuts in this client, and I'll try to point to them as I go.

Let's start by looking at the main() function for the final client application in this chapter:

 1 /* client4.pgc */
 2
 3 #include <stdio.h>
 4 #include <stdlib.h>
 5
 6 EXEC SQL INCLUDE sql3types;
 7 EXEC SQL INCLUDE sqlca;
 8
 9 EXEC SQL WHENEVER SQLERROR DO print_error();
10
11 static int  is_select_stmt( char * stmt );
12 static void process_other_stmt( char * stmt_text );
13 static void process_select_stmt( char * stmt_text );
14 static void print_column_headers( int col_count );
15 static void print_meta_data( char * desc_name );
16 static void print_error( void );
17 static int  usage( char * program );
18
19 char * sep = "----------------------------------------";
20 char * md1 = "col field                data              ret";
21 char * md2 = "num name                 type              len";
22 char * md3 = "--- -------------------- ----------------- ---";
23
24 int    dump_meta_data = 0;
25
26 int main( int argc, char * argv[] )
27 {
28   EXEC SQL BEGIN DECLARE SECTION;
29     char * db   = argv[1];
30     char * stmt = argv[2];
31   EXEC SQL END DECLARE SECTION;
32
33   FILE * log = fopen( "client4.log", "w" );
34
35   ECPGdebug( 1, log );
36
37   if( argc < 3 )
38     exit( usage( argv[0] ));
39   else if( argc > 3 )
40     dump_meta_data = 1;
41
42   EXEC SQL CONNECT TO :db;
43
44   if( is_select_stmt( stmt ))
45     process_select_stmt( stmt );
46   else
47     process_other_stmt( stmt );
48
49   exit( 0 );
50 }

You've already seen most of this code. I've included an extra EXEC SQL INCLUDE statement: sql3types provides symbolic names for the data types returned by a dynamic SQL statement. I'll show you where to use these a little later.

The only other new feature in main() is the call to ECPGdebug(). Debugging dynamic SQL can be pretty tricky, and it's always helpful to have a record of the sequence of events that your application follows. When you call ECPGdebug(), you provide an integer and a FILE *. The first argument turns logging on or off: A 0 disables logging and any other value tells the ecpg library to write trace information to the file indicated by the second argument.

Here is the first shortcut that I've taken (for clarity). Rather than prompting you for multiple commands, client4 expects you to provide a single command (on the command line). This client expects either two or three command-line arguments. The first argument should be the name of the database to which you want to connect. The second argument is an SQL command. The third argument is optional. If you provide a third command-line argument (it doesn't matter what you provide), client4 will print out meta-data for a SELECT command. A typical invocation of this application might look like this:

$ ./client4 movies "select * from tapes" true

Notice that at line 44, I am calling the is_select_stmt() function. The processing required to handle a SELECT statement is considerably different from that required to handle other command types, so let's defer it for a while and first look instead at the code required execute commands other than SELECT:

52 static void process_other_stmt( char * stmt_text )
53 {
54   EXEC SQL BEGIN DECLARE SECTION;
55     char  * stmt = stmt_text;
56   EXEC SQL END DECLARE SECTION;
57
58   EXEC SQL EXECUTE IMMEDIATE :stmt;
59
60   if( sqlca.sqlcode >= 0 )
61   {
62     printf( "ok
" );
63     EXEC SQL COMMIT;
64   }
65 }

The process_other_stmt() function is actually pretty simple. Line 55 defines a variable to hold the statement text (inside of a DECLARE SECTION so that you can use it as a host variable). At line 50, this function executes the command using the host variable. Using this form of the EXEC SQL EXECUTE command, you don't get back any result information other than what's found in the sqlca structure. In the next section, I'll show you how to get more result information.

If the command succeeds, process_other_stmt() executes a COMMIT command to commit any changes.

Now let's look at the process_select_stmt() function—it is much more complex.

 67 static void process_select_stmt( char * stmt_text )
 68 {
 69   EXEC SQL BEGIN DECLARE SECTION;
 70     char  * stmt = stmt_text;
 71   EXEC SQL END DECLARE SECTION;
 72     int     row;
 73
 74     EXEC SQL ALLOCATE DESCRIPTOR my_desc;
 75     EXEC SQL PREPARE query FROM :stmt;
 76
 77     EXEC SQL DECLARE my_cursor CURSOR FOR query;
 78     EXEC SQL OPEN my_cursor;
 79
 80     for( row = 0; ; row++ )
 81     {
 82       EXEC SQL BEGIN DECLARE SECTION;
 83         int     col_count;
 84         int     i;
 85       EXEC SQL END DECLARE SECTION;
 86
 87       EXEC SQL FETCH IN my_cursor INTO SQL DESCRIPTOR my_desc;
 88
 89       if( sqlca.sqlcode != 0 )
 90         break;
 91
 92       EXEC SQL GET DESCRIPTOR my_desc :col_count = count;
 93
 94       if( row == 0 )
 95       {
 96         print_meta_data( "my_desc" );
 97         print_column_headers( col_count );
 98       }
 99
100       for( i = 1; i <= col_count; i++ )
101       {
102         EXEC SQL BEGIN DECLARE SECTION;
103           short   ind;
104         EXEC SQL END DECLARE SECTION;
105
106         EXEC SQL GET DESCRIPTOR my_desc VALUE
107           :i :ind = INDICATOR;
108
109         if( ind == -1 )
110         {
111           printf( "null " );
112         }
113         else
114         {
115           EXEC SQL BEGIN DECLARE SECTION;
116             varchar val[40+1];
117             int     len;
118           EXEC SQL END DECLARE SECTION;
119
120           EXEC SQL GET DESCRIPTOR my_desc VALUE
121             :i :len = RETURNED_LENGTH;
122
123           EXEC SQL GET DESCRIPTOR my_desc VALUE :i :val = DATA;
124
125           if( len > 40 )
126             len = 40;
127
128           printf( "%-*s ", len, val.arr );
129         }
130       }
131
132       printf( "
" );
133
134     }
135
136     printf( "%d rows
", row );
137
138 }

If you've read the previous few chapters, you know that the most stubborn problem in ad-hoc query processing is that you don't know, at the time you write the program, what kind of data will be returned by any given query. The bulk of the code that you need to write involves discovering and interpreting the meta-data associated with a query.

When you use ecpg to process dynamic SQL commands, the meta-data comes back in the form of a descriptor (or, more precisely, a group of descriptors). A descriptor is a data structure, much like libpq's PGresult, that contains information about the data returned by a SQL command.

Before you can use a descriptor, you must tell the ecpg library to allocate one. The following statement will create a new descriptor named my_desc:

EXEC SQL ALLOCATE DESCRIPTOR my_desc;

At line 75, process_select_stmt() prepares the command for execution. When you prepare a command, you are giving ecpg a chance to peek at the command and do whatever bookkeeping it needs to execute it. After a command has been prepared, ecpg will remember it for you and you can refer to that statement by name (query, in this case).

After you have a prepared the statement, declare a cursor (named my_cursor) for the statement and then open the cursor. (You can execute singleton^[5] SELECTs without preparing them, but there is a no way to tell that a dynamic query is a singleton SELECT.)

^[5] A singleton SELECT is a SELECT command that returns either zero rows or one row, never more.

At line 80, process_select_stmt() enters a loop to process all the rows returned by the cursor.

Line 87 shows the magic that occurs in a dynamic SQL application. When you execute the EXEC SQL statement at line 87, you are fetching the next row from my_cursor and putting the results into the my_desc descriptor. The my_desc descriptor now contains all the meta-data for this SQL command (FETCH).

I mentioned earlier that a descriptor is a data structure. Although that is a true statement, you can't access the members of the data structure using the normal C structure reference syntax. Instead, you use the EXEC SQL GET DESCRIPTOR directive. The general form of the GET DESCRIPTOR directive is

EXEC SQL GET DESCRIPTOR
descriptor_name [column_number] host_variable = item;

The item specifies what kind of information you want to retrieve from the descriptor. The returned information is placed into the host_variable. The column_number is optional, but there is only one piece of information that you can retrieve without specifying a column_number—a count of the columns in the result set.

The EXEC SQL GET DESCRIPTOR directive at line 72 retrieves the column count from my_desc and places the result into the col_count host variable.

After you know how many columns are in the result set, you can (optionally) print the meta-data and the column headers. I'll show you those functions in a moment.

At line 100, process_select_stmt() enters a loop that processes each column from the most recently fetched row.

To display a column value, the first thing you need to know is whether that column value is NULL. Each column in the result set has an associated indicator variable, and you can retrieve the value of that indicator through the descriptor. Notice (at line 107) that you have to tell ecpg which column you are interested in: for any descriptor item other than COUNT, you must include a column number after the word VALUE.

If the column contains NULL, process_select_stmt() just prints the word “null”. This is another shortcut that I've taken in this client; to properly maintain the alignment of the columns when you print the result set, you have to know the maximum length of each value within a column and that information is not available using dynamic SQL and ecpg. So, instead of printing null and then padding it with spaces to the proper length, we'll just print “null”. This means that you lose vertical alignment of the columns if your data includes NULL values.

If a column contains a value other than NULL, process_select_stmt() prints the value (or at most the first 40 characters of the value).

At line 120, this function retrieves the length of the character form of the value from the RETURNED_LENGTH member of the my_desc descriptor. I say the “length of the character form” here because there are other length-related items that you can retrieve from a descriptor. I'll include a description of all the descriptor items a little later.

Finally, at line 123, process_select_stmt() retrieves the actual data value from the descriptor. When you ask for a DATA item, you have to provide a host variable where ecpg can return the value. If the data value that you retrieve is longer than the host variable, ecpg will truncate the value and set sqlca.sqlwarn[1] to tell you that truncation has occurred.

After processing all the columns for all rows, process_select_stmt() prints a message indicating how many rows were retrieved.

Now let's move on to the print_meta_data() function. The first thing I'll point out about this function is that it expects the descriptor name to be passed in as the one and only argument. This isn't really important to the structure of this particular application, but I wanted to point out that you can use a host variable to specify a descriptor.

140 static void print_meta_data( char * desc_name )
141 {
142   EXEC SQL BEGIN DECLARE SECTION;
143     char  * desc = desc_name;
144     int     col_count;
145     int     i;
146   EXEC SQL END DECLARE SECTION;
147
148   static char * types[] =
149   {
150     "unused           ",
151     "CHARACTER        ",
152     "NUMERIC          ",
153     "DECIMAL          ",
154     "INTEGER          ",
155     "SMALLINT         ",
156     "FLOAT            ",
157     "REAL             ",
158     "DOUBLE           ",
159     "DATE_TIME        ",
160     "INTERVAL         ",
161     "unused           ",
162     "CHARACTER_VARYING",
163     "ENUMERATED       ",
164     "BIT              ",
165     "BIT_VARYING      ",
166     "BOOLEAN          ",
167     "abstract         "
168   };
169
170   if( dump_meta_data == 0 )
171     return;
172
173   EXEC SQL GET DESCRIPTOR :desc :col_count = count;
174
175   printf( "%s
", md1 );
176   printf( "%s
", md2 );
177   printf( "%s
", md3 );
178
179   for( i = 1; i <= col_count; i++ )
180   {
181     EXEC SQL BEGIN DECLARE SECTION;
182       int     type;
183       int     ret_len;
184       varchar name[21];
185     EXEC SQL END DECLARE SECTION;
186     char *  type_name;
187
188     EXEC SQL GET DESCRIPTOR :desc VALUE
189       :i :name = NAME;
190
191     EXEC SQL GET DESCRIPTOR :desc VALUE
192       :i :type = TYPE;
193
194     EXEC SQL GET DESCRIPTOR :desc VALUE
195       :i :ret_len = RETURNED_OCTET_LENGTH;
196
197     if( type > 0 && type < SQL3_abstract )
198       type_name = types[type];
199     else
200       type_name = "unknown";
201
202     printf( "%02d: %-20s %-17s %04d
",
203       i, name.arr, type_name, ret_len );
204   }
205
206   printf( "
" );
207 }

This function pulls a few more meta-data items out of the descriptor. The first thing this function does is to check the dump_meta_data flag—if you don't want to see meta-data, this function will simply return without printing anything. The dump_meta_data flag will be set to TRUE if you include a third argument on the command line when you run this program.

At line 173, print_meta_data() retrieves a count of the number of columns in the descriptor. Lines 175 through 177 print column headers for the meta-data (md1, md2, and md3 are defined at the top of client4.pgc).

At line 179, this function enters a loop that prints the meta-data for each column. Lines 188 through 195 retrieve the NAME, (data) TYPE, and RETURNED_OCTET_LENGTH for each column.

The TYPE item returns an integer that may correspond to one of the data type names defined in the sql3types.h header file. Not all data types are defined in sql3types.h—there are many PostgreSQL data types that don't exactly map to a SQL3 data type. If you encounter an unknown data type, this function will just print unknown instead of a real type name.

This is probably a good place to show you all the descriptor items that you can retrieve using ecpg (see Table 11.3).

The rest of client4.pgc is pretty mundane; I'll include the remainder of the source code here and offer a few quick explanations:

209 static void print_column_headers( int col_count )
210 {
211   EXEC SQL BEGIN DECLARE SECTION;
212     char    name[40];
213     int     len;
214   EXEC SQL END DECLARE SECTION;
215   int     i;
216
217   for( i = 1; i <= col_count; i++ )
218   {
219     EXEC SQL GET DESCRIPTOR my_desc VALUE
220       :i :name = NAME;
221
222     EXEC SQL GET DESCRIPTOR my_desc VALUE
223       :i :len  = RETURNED_OCTET_LENGTH;
224
225     if( len > 40 )
226       len = 40;
227
228     printf( "%-*s ", len, name );
229   }
230
231   printf( "
" );
232
233   for( i = 1; i <= col_count; i++ )
234   {
235     EXEC SQL GET DESCRIPTOR my_desc VALUE
236       :i :len  = RETURNED_OCTET_LENGTH;
237
238     if( len > 40 )
239       len = 40;
240
241     printf( "%*.*s ", len, len, sep );
242   }
243
244   printf( "
" );
245 }

The print_column_headers() function does a half-hearted job of trying to print properly aligned column headers. This function can't do a perfect job because ecpg doesn't expose enough information. For example, to properly align column headers, you have to know the longest value in any given column. Because you process SELECT statements one record at a time, you would have to do a lot of work to be able to find this information. If you are not a purist, you can mix ecpg and libpq code in the same application.

247 static int is_select_stmt( char * stmt )
248 {
249   char * token;
250
251   for( token = stmt; *token; token++ )
252     if( *token != ' ' && *token != '	' )
253       break;
254
255   if( *token == '' )
256     return( 0 );
257
258   if( strncasecmp( token, "select", 6 ) == 0 )
259     return( 1 );
260   else
261     return( 0 );
262 }

The is_select_stmt() function represents another shortcut—you have to look at the first word of a SQL command to determine whether it is a SELECT statement or some other command. With other dynamic SQL packages (such as Oracle's Pro*C product), you can obtain this information from the descriptor, but not with PostgreSQL.

264 static void print_error()
265 {
266   printf( "#%ld:%s
", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc );
267 }
268
269 static int usage( char * program )
270 {
271   fprintf( stderr, "usage: %s <database> <query>
", program );
272   return( 1 );
273 }

The print_error() and usage() functions are simple utility functions. print_error() is called whenever a SQL error occurs. The usage() function is called by main() if there is an improper number of arguments on the command line.