Client 4 - An Interactive Query Processor

   

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 * : a 0 means to turn off logging and any other value means to turn on ecpg library logging.

Here is the first shortcut that I've taken (for clarity). Rather than prompting you for multiple commands, you provide a single command (on the command line) for this application. 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 a 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\n" ); 63     EXEC SQL COMMIT; 64   } 65 } 

The process_other_stmt() function is actually pretty simple. You define a variable to hold the statement text (inside of a DECLARE SECTION so that you can use it as a substitution variable). At line 50, you execute the command using the substitution 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, execute a COMMIT command to commit any changes.

ecpg and Autocommit

When you compile this program, you do not use the -t flag. The - t flag tells the ecpg preprocessor to arrange for each statement to be committed as soon as it completes (in other words, the -t flag enables autocommit ). Because you aren't using autocommit in this example, you must COMMIT or ROLLBACK your changes to complete the transaction. If you forget to COMMIT your changes (and you don't use the -t flag), your changes will automatically be rolled back when your application completes. If you invoke the ecpg preprocessor with the -t flag, each change will be committed as soon as it completes.

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( "\n" ); 133 134     } 135 136     printf( "%d rows\n", 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, you prepare your 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 do 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, you will declare a cursor (named my_cursor ) for the statement and then open the cursor. (You can execute singleton [5] SELECT s 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, you enter 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] substitution_variable = item; 

The item specifies what kind of information you want to retrieve from the descriptor. The returned information is placed into the substitution_variable . The column_number is optional, but there is only one item that you can specify if you omit the column_number ”a count of the columns in the result set.

To retrieve the column count, ask ecpg to place the COUNT into the col_count variable.

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

At line 100, you enter a loop that processes each column from the most recently fetched row.

The first thing you need to know is whether a given column 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 in which column you are interested: for any descriptor item other than count, you have to include a column number after the word VALUE .

If the column contains NULL , just print 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, 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 , you will print the value (or at most the first 40 characters of the value).

At line 120, you retrieve 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, I retrieve the actual data value from the descriptor. When I ask for a DATA item, I have to provide a substitution variable where ecpg can return the value. If the data value that I retrieve is longer than 40 bytes, ecpg will truncate the value and set sqlca.sqlwarn[1] to tell me that truncation has occurred.

After you have processed all the columns for all rows, you print 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 substitution 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\n", md1 ); 176   printf( "%s\n", md2 ); 177   printf( "%s\n", 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\n", 203       i, name.arr, type_name, ret_len ); 204   } 205 206   printf( "\n" ); 207 } 

In this function, you are pulling a few more meta-data items out of the descriptor. The first thing you do in this function 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, you (again) retrieve 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, you enter 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, 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.4).

Table 11.4. Descriptor Item Types

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( "\n" ); 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( "\n" ); 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 != '\t' ) 253       break; 254 255   if( *token == ' 
 247 static int is_select_stmt( char * stmt ) 248 { 249 char * token; 250 251 for( token = stmt; *token; token++ ) 252 if( *token != ' ' && *token != '\t' ) 253 break; 254 255 if( *token == '\0' ) 256 return( 0 ); 257 258 if( strncasecmp( token, "select", 6 ) == 0 ) 259 return( 1 ); 260 else 261 return( 0 ); 262 } 
' ) 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\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc ); 267 } 268 269 static int usage( char * program ) 270 { 271   fprintf( stderr, "usage: %s <database> <query>\n", 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.

   


PostgreSQL
PostgreSQL (2nd Edition)
ISBN: 0672327562
EAN: 2147483647
Year: 2005
Pages: 220
Authors: Korry Douglas

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net