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).
Item Type | Meaning |
---|---|
CARDINALITY | Number of rows in result set (usually one and therefore not particularly useful) |
DATA | Actual data value |
DATETIME_INTERVAL_CODE | SQL3_DDT_DATE, SQL3_DDT_TIME, SQL3_DDT_TIMESTAMP, SQL3_DDT_TIMESTAMP_WITH_TIME_ZONE, SQL3_DDT_TIME_WITH_TIME_ZONE |
DATETIME_INTERVAL_PRECISION | Not currently used |
INDICATOR | Indicator variable |
KEY_MEMBER | Always returns FALSE |
LENGTH | Length of data as stored in server |
NAME | Name of field |
NULLABLE | Always returns TRUE |
OCTET_LENGTH | Length of data as stored in server |
PRECISION | Precision (for numeric values) |
RETURNED_LENGTH | Length of actual data item |
RETURNED_OCTET_LENGTH | Synonym for RETURNED_LENGTH |
SCALE | Scale (for numeric values) |
TYPE | SQL3 data type or PostgreSQL data type |
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.