Client 3 - Simple Processing - PQexec() and PQprint()
Client 3 ”Simple Processing ”PQexec() and PQprint()Now let's turn our attention to the task of processing a query. I'll start by showing a simple example ”you'll connect to a database, execute a hard-wired query, process the results, clean up, and exit. 1 /* 2 ** File: client3.c 3 */ 4 5 #include <stdlib.h> 6 #include <libpq-fe.h> 7 8 void process_query( PGconn * connection, const char * query_text ) 9 { 10 PGresult * result; 11 PQprintOpt options = {0}; 12 13 if(( result = PQexec( connection, query_text )) == NULL ) 14 { 15 printf( "%s\n", PQerrorMessage( connection )); 16 return; 17 } 18 19 options.header = 1; /* Ask for column headers */ 20 options.align = 1; /* Pad short columns for alignment */ 21 options.fieldSep = ""; /* Use a pipe as the field separator */ 22 23 PQprint( stdout, result, &options ); 24 25 PQclear( result ); 26 } 27 28 int main( int argc, char * argv[] ) 29 { 30 PGconn * connection; 31 32 if( argc != 2 ) 33 { 34 printf( "usage : %s \"connection-string\"\n", argv[0] ); 35 printf( "example: %s \"user=myname password=cows\"\n", argv[0]); 36 exit( 1 ); 37 } 38 39 if(( connection = PQconnectdb( argv[1] )) == NULL ) 40 { 41 printf( "Fatal error - unable to allocate connection\n" ); 42 exit( 1 ); 43 } 44 45 if( PQstatus( connection ) != CONNECTION_OK ) 46 printf( "%s\n", PQerrorMessage( connection )); 47 else 48 { 49 process_query( connection, "SELECT * FROM rentals" ); 50 } 51 52 PQfinish( connection ); 53 54 exit( 0 ); 55 } The interesting part of this program is the process_query() function (lines 8 “26). You start by calling PQexec() . This function is used to synchronously execute a query. (Like the connection API, there are two methods to execute a query: synchronous and asynchronous. I'll show you the asynchronous query functions later.) When you call PQexec() , you provide a connection object(a PGconn pointer) and a commandstring. PQexec() returns a pointer to a PGresult object. A PGresult is similar to a PGconn ”it is an opaque handle and you can query the object for different pieces of information (such as "Did my query succeed or fail?"). A PGresult object represents the results of a command. When you execute a query (as opposed to an INSERT command), the entire result set (including meta-data) of the query is accessible through the object. A PGresult object also provides access to any error messages that may result from executing a command. I'm going to cheat here. Older versions of libpq provided a handy function called PQprint() that does all the dirty work required to print the results of a query. PQprint() is still included in libpq (at least as of version 7.2.1), but the online documentation says that the function is obsolete and is not supported. It's likely that PQprint() will not be removed from libpq, but you won't see too many new features added to is as new PostgreSQL releases appear. I'll use PQprint() here because it is such a simple way to print a result set. Later, I'll show you how to produce much of the same functionality yourself. Before you can call PQprint() , you must construct a PQprintOpt object. At line 11, you initialize the PQprintOpt object and then set the three members that you care about ( header , align , and fieldSep ) at lines 19 “21. PQprint() requires three arguments: a FILE pointer (in this case, specify stdout ), a PGresult pointer (returned from PQexec() ), and a pointer to a PGprintOpt object. PQprint() formats the results of the query and prints them to the file that you specified. If the query fails, PQprint() will print an appropriate error message. Remember that PQexec() returned a pointer to a PGresult object ”you need to free that object because PQclear() will destroy a PGresult object. When you are finished processing the result set, free the PGresult resources using PQclear() (see line 25). It's important to PQclear() all PGresult objects when you are done with them. When libpq executes a query on your behalf , the entire result set of the query is accessible through a PGresult object. That means that if you execute a query that returns 100,000 rows, the PGresult object will consume enough memory to hold all 100,000 rows. Results Returned by PQexec()Many client applications need to do more than just print column values. After executing a command, you can obtain a lot of information about the results of the command through the PGresult object returned by PQexec() . The most obvious piece of information that you can obtain from a PGresult pointer is whether your command succeeded or failed. If your command succeeded, PQresultStatus() will return either PGRES_COMMAND_OK or PGRES_TUPLES_OK . PGRES_TUPLES_OK means that you successfully executed a query and there are zero or more rows available for processing. PGRES_COMMAND_OK means that you successfully executed some command other than SELECT ; an INSERT command for example. If your query causes an error, you will get back a result of PGRES_FATAL_ERROR or PGRES_NONFATAL_ERROR . (There are other values that PQresultStatus() can return; see the PostgreSQL Programmer's Guide for more information.) It's possible that PQexec() will return a NULL PGresult pointer if libpq runs out of memory ”you should treat that as a PGRES_FATAL_ERROR . If your command fails, you can use PQresultErrorMessage() to find the reason for failure. To call PQresultErrorMessage() , you pass the PGresult pointer that was returned by PQexec() . PQresultErrorMessage() returns a pointer to the null- terminated string containing the reason for failure (if you call PQresultErrorMessage() for a successful query, you'll get back a pointer to an empty string). I'll modify the process_query() function from the previous example (client3.c) to show how to use PQresultStatus() and PQresultErrorMessage() : 1 /* 2 ** File: client3b.c 3 */ 4 5 #include <stdlib.h> 6 #include <libpq-fe.h> 7 8 void process_query( PGconn * connection, const char * query_text ) 9 { 10 PGresult * result; 11 12 if(( result = PQexec( connection, query_text )) == NULL ) 13 { 14 printf( "%s\n", PQerrorMessage( connection )); 15 return; 16 } 17 18 if(( PQresultStatus( result ) == PGRES_COMMAND_OK ) 19 ( PQresultStatus( result ) == PGRES_TUPLES_OK )) 20 { 21 PQprintOpt options = {0}; 22 23 options.header = 1; /* Ask for column headers */ 24 options.align = 1; /* Pad short columns for alignment */ 25 options.fieldSep = ""; /* Use a pipe as the field separator*/ 26 27 PQprint( stdout, result, &options ); 28 29 } 30 else 31 { 32 printf( "%s\n", PQresStatus( PQresultStatus( result ))); 33 printf( "%s\n", PQresultErrorMessage( result )); 34 35 } 36 37 PQclear( result ); 38 } 39 40 int main( int argc, char * argv[] ) 41 { 42 PGconn * connection; 43 44 if( argc != 2 ) 45 { 46 printf( "usage : %s \"connection-string\"\n", argv[0] ); 47 printf( "example: %s \"user=myname password=cows\"\n", argv[0]); 48 exit( 1 ); 49 } 50 51 if(( connection = PQconnectdb( argv[1] )) == NULL ) 52 { 53 printf( "Fatal error - unable to allocate connection\n" ); 54 exit( 1 ); 55 } 56 57 if( PQstatus( connection ) != CONNECTION_OK ) 58 printf( "%s\n", PQerrorMessage( connection )); 59 else 60 { 61 process_query( connection, "SELECT * FROM rentals" ); 62 } 63 64 PQfinish( connection ); 65 66 exit( 0 ); 67 } At lines 18 and 19, check to see whether the command succeeded. If so, use PQprint() to print the result set just like you did in client3.c If the command failed, tell the user what went wrong. Look closely at line 32. You are calling the PQresultStatus() function again, but this time around you call PQresStatus() with the return value. PQresultStatus() returns the command status in the form of an integer [1] . The PQresStatus() function translates a value returned by PQresultStatus() into a human-readable string.
At line 33, you call PQresultErrorMessage() to retrieve the text of the error message. After you have successfully executed a query (that is, PQresultStatus() has returned either PGRES_COMMAND_OK or PGRES_TUPLES_OK ), you are ready to process the actual results. There are three types of information that you can access through a PGresult object. You've already seen the first type of information: success or failure and an error message. The second type of information is metadata, or data about your data. We'll look at meta-data next . Finally, you can access the values returned by the command itself ”the rows returned by a query or the OID of an affected row in the case of an INSERT or UPDATE . First, I'll show you how to find the metadata for your query. libpq provides a number of functions that let you find information about the kind of data returned by your query. For example, the PQntuples() function tells you how many rows (or tuples) will be returned from your query. The following function prints (most of) the metadata returned for a command: 1 void print_meta_data( PGresult * result ) 2 { 3 int col; 4 5 printf( "Status: %s\n", PQresStatus( PQresultStatus( result ))); 6 printf( "Returned %d rows ", PQntuples( result )); 7 printf( "with %d columns\n\n", PQnfields( result )); 8 9 printf( "Column Type TypeMod Size Name \n" ); 10 printf( "------ ---- ------- ---- -----------\n" ); 11 12 for( col = 0; col < PQnfields( result ); col++ ) 13 { 14 printf( "%3d %4d %7d %4d %s\n", 15 col, 16 PQftype( result, col ), 17 PQfmod( result, col ), 18 PQfsize( result, col ), 19 PQfname( result, col )); 20 } 21 } If you want to try this function, it is included in client3c.c in the sample code for this book. I won't show the complete application here because it is largely the same as client3b.c . At line 5, you print the success/failure status from the given PQresult object. It uses the same PQresStatus() and PQresultStatus() functions described earlier, but I've included them in this example because they really do return metadata information. At line 6, you use the PQntuples() function to retrieve the number of rows returned by the command. PQntuples() returns zero if the command was not a query. PQntuples() also returns zero if the command was a query, but the query happened to return zero rows in the result set. libpq does not consider it an error for a query to return zero rows. In fact, the PQresult object contains all the usual metadata even when a query does not return any rows. The PQnfields() function (line 7) returns the number of columns in the result set. Line PQntuples() , PQnfields() returns zero for commands other than SELECT . The naming convention for the metadata functions is a little confusing at first. PQntuples() returns the number of rows in the result set. PQnfields() returns the number of columns in the result set. A tuple is the same thing as a row. A field is the same thing as a column [2] .
At line 16, you call PQftype() to find the data type for a given column. The PQftype() , PQfmod() , and PQfsize() functions work together to tell you about the format of the data in a given column. PQftype() returns a value of type OID . The value returned by PQftype() corresponds to the object-id ( OID ) of a row in the pg_type system table. (In Chapter 6, "Extending PostgreSQL," you learned that data type descriptions are stored in pg_type .) You can find the OIDs for predefined data types in the catalog/pg_type.h PostgreSQL header file. PQfmod() returns a value that, in theory, gives you more detailed information about a data type. The values returned by PQfmod() are type-specific and are not documented. You can use the format_type() [3] function to convert values returned by PQftype() and PQfmod() into a human-readable string. PQfsize() returns the number of bytes required to hold a value on the server. For variable-length data types, PQfsize() returns “1.
It turns out that the information returned by PQftype() , PQfmod() , and PQfsize() is not all that useful in most applications. In most cases, the field values returned to your application will be null-terminated strings. For example, if you SELECT a date column, the date values will be converted into string form before it gets to your application. The same is true for numeric values. It is possible to request raw data values (that is, values that have not been converted into string form). I'll show you how to do that a little later. The last two metadata functions are PQfname() and PQfnumber() . PQfname() returns the name of the given column in the result set. PQfnumber() returns the column number of the named column. Now that you know how to retrieve the metadata for a query, let's see how to actually retrieve the data. In this example, you'll replace the earlier calls to PQprint() with your own function. 1 /* 2 ** File: client3d.c 3 */ 4 5 #include <stdlib.h> 6 #include <string.h> 7 #include <libpq-fe.h> 8 9 #define MAX_PRINT_LEN 40 10 11 static char separator[MAX_PRINT_LEN+1]; 12 13 void print_result_set( PGresult * result ) 14 { 15 int col; 16 int row; 17 int * sizes; 18 19 /* 20 ** Compute the size for each column 21 */ 22 sizes = (int *)calloc( PQnfields( result ), sizeof( int )); 23 24 for( col = 0; col < PQnfields( result ); col++ ) 25 { 26 int len = 0; 27 28 for( row = 0; row < PQntuples( result ); row++ ) 29 { 30 if( PQgetisnull( result, row, col )) 31 len = 0; 32 else 33 len = PQgetlength( result, row, col ); 34 35 if( len > sizes[col] ) 36 sizes[col] = len; 37 } 38 39 if(( len = strlen( PQfname( result, col ))) > sizes[col] ) 40 sizes[col] = len; 41 42 if( sizes[col] > MAX_PRINT_LEN ) 43 sizes[col] = MAX_PRINT_LEN; 44 } 45 46 /* 47 ** Print the field names. 48 */ 49 for( col = 0; col < PQnfields( result ); col++ ) 50 { 51 printf( "%-*s ", sizes[col], PQfname( result, col )); 52 } 53 54 printf( "\n" ); 55 56 /* 57 ** Print the separator line 58 */ 59 memset( separator, '-', MAX_PRINT_LEN ); 60 61 for( col = 0; col < PQnfields( result ); col++ ) 62 { 63 printf( "%*.*s ", sizes[col], sizes[col], separator ); 64 } 65 66 printf( "\n" ); 67 68 /* 69 ** Now loop through each of the tuples returned by 70 ** our query and print the results. 71 */ 72 for( row = 0; row < PQntuples( result ); row++ ) 73 { 74 for( col = 0; col < PQnfields( result ); col++ ) 75 { 76 if( PQgetisnull( result, row, col )) 77 printf( "%*s", sizes[col], "" ); 78 else 79 printf( "%*s ", sizes[col], PQgetvalue( result, row, col )); 80 } 81 82 printf( "\n" ); 83 84 } 85 printf( "(%d rows)\n\n", PQntuples( result )); 86 free( sizes ); 87 } This function ( print_result_set() ) replaces your earlier use of PQprint() . The real work in this function is finding the width of each column. For each column in the result set, you have to search through all rows, finding the widest value. At line 22, you allocate an array ( sizes[] ) of integers to hold the column widths. At lines 24 through 44, you fill in the sizes[] array. The PQgetisnull() function tells you whether a given column is NULL in the current row. If you find a NULL field, consider it to have a length of 0. Use the PQgetlength() function to find the length of each value. Notice that we ensure that each column is wide enough to hold the column name. The limit to each column is MAX_PRINT_LEN characters . This is a rather arbitrary decision that you can certainly change. After computing the column widths, you print the name of each column followed by a line of separator characters (lines 46 “66). At lines 68 through 84, you loop through every row in the result set and print each column value. The PQgetvalue() function returns a pointer to the value for a given row and column. Because you have not requested a BINARY cursor (I'll talk about those soon), each data value comes to you in the form of a null-terminated string. Finally, at line 86, you free up the resource that you allocated ( sizes[] ) and return. 89 void process_query( PGconn * connection, const char * query_text ) 90 { 91 PGresult * result; 92 93 if(( result = PQexec( connection, query_text )) == NULL ) 94 { 95 printf( "%s\n", PQerrorMessage( connection )); 96 return; 97 } 98 99 if( PQresultStatus( result ) == PGRES_TUPLES_OK ) 100 { 101 print_result_set( result ); 102 } 103 else if( PQresultStatus( result ) == PGRES_COMMAND_OK ) 104 { 105 printf( "%s", PQcmdStatus( result )); 106 107 if( strlen( PQcmdTuples( result ))) 108 printf( " - %s rows\n\n", PQcmdTuples( result )); 109 else 110 printf( "\n\n" ); 111 } 112 else 113 { 114 printf( "%s\n\n", PQresultErrorMessage( result )); 115 } 116 117 PQclear( result ); 118 } This function ( process_query() ) is not very complex. You execute the given command and print the results. If an error occurs, you use PQerrorMessage() or PQresultErrorMessage() to display an error message to the user. You call PQerrorMessage() if PQexec() fails to return a PQresult pointer, otherwise you call PQresultErrorMessage() . If the command is successful, you need to decide whether it was a SELECT or some other type of command. If PQresultStatus() returns PGRES_TUPLES_OK , you know that the command was a query and you call print_result_set() to do the grunt work. If PQresultStatus() returns PGRES_COMMAND_OK , you know that some other command was executed. PQcmdStatus() tells you the name of the command that you just executed. You've probably noticed that when you execute a command (other than SELECT ) in psql , the name of the command is echoed if the command succeeded ”that's what PQcmdStatus() gives us. PQcmdTuples() tells us how many rows were affected by the command. PQcmdTuples() is meaningful for the INSERT , UPDATE , or DELETE command. For any other command, PQcmdTuples() returns a string of zero length. Finish process_query() by freeing up the PGresult object and all the resources (that is, memory) managed by that object. The main() function for client3d.c is the same as for client3.c : 117 int main( int argc, char * argv[] ) 118 { 119 PGconn * connection; 120 121 if( argc != 2 ) 122 { 123 printf( "usage : %s \"connection-string\"\n", argv[0] ); 124 printf( "example: %s \"user=myname password=cows\"\n", argv[0]); 125 exit( 1 ); 126 } 127 128 if(( connection = PQconnectdb( argv[1] )) == NULL ) 129 { 130 printf( "Fatal error - unable to allocate connection\n" ); 131 exit( 1 ); 132 } 133 134 if( PQstatus( connection ) != CONNECTION_OK ) 135 printf( "%s\n", PQerrorMessage( connection )); 136 else 137 process_query( connection, "SELECT * FROM rentals" ); 138 139 PQfinish( connection ); 140 141 exit( 0 ); 142 } Now let's compile this client and run it: $ make client3d cc -g -I/usr/local/pg721/include -c -o client3d.o client3d.c cc -g client1.o -L/usr/local/pgsql/lib -lpq -o client3 $ ./client3d "dbname=movies" tape_id rental_date customer_id -------- ----------- ----------- AB-12345 2002-07-01 1 AB-67472 2002-07-01 3 OW-41221 2002-07-01 1 (3 rows) Let's compare that with the output from client3: $ ./client3 "dbname=movies" tape_id rental_datecustomer_id --------+-----------+----------- AB-12345 2002-07-01 1 AB-67472 2002-07-01 3 OW-41221 2002-07-01 1 (3 rows) Pretty similar ” the only differences are in the vertical separator characters. Remember, client3 uses the PQprint() function (from the libpq library) to format the result set. In client3d , you did all of the hard work yourself. Binary CursorsNow let's look at another option for processing query results. So far, every data value that was retrieved from the server has come to you in the form of a null-terminated string. When you store data in a PostgreSQL table, it is rarely, if ever, stored in the form of a null-terminated string. In Chapter 6, "Extending PostgreSQL," you explored the difference between the external form of a data value and the internal form. In short, the external form is meant to be "human-readable" and the internal form is meant to be "computer-friendly." The external form of an INTEGER value, " 521 " for example, is a series of numeric characters expressed in the encoding of the client application (in other words, you see the ASCII characters ' 5 ', ' 2 ', and ' 1 ' if you are using an ASCII client). The internal form of the same numeric value is a four-byte, binary-encoded integer. On an Intel-based system, this value is represented by the bits ' 1000001001 ' (leading zeroes suppressed). CPUs know how to deal with these binary-encoded values, but most people don't find that form very convenient . When you retrieve SELECT values using libpq, you get the results in external form (and the external form is contained in a null-terminated string). The disadvantage to external form is that PostgreSQL must convert every value that it sends to you. That can be an expensive operation, especially if your application converts the external form back into internal form. Instead of retrieving values from a SELECT command, you can utilize a BINARY CURSOR . A binary cursor is a cursor that does not convert the raw data to external form. When you call PQgetvalue() to retrieve values from a binary cursor, you get back a pointer to the internal form of the data. A binary cursor is a strange beast . In all the other RDBMS systems that I have used (Oracle, Sybase, SQL Server, and so on), I tell the API which format I want the data to appear in on a column-by-column basis. The SQL commands that I send to the server are the same, regardless of the data format that I choose. Data conversion is an API issue, not a SQL issue. PostgreSQL takes a different approach. If I want raw (unconverted) data in PostgreSQL, I have to send a different set of commands to the server. All columns retrieved from a binary cursor are internal form ”I can't pick and choose. Let's see how you might convert a normal SELECT command into a binary cursor. You have been using the following command in most of the examples in this chapter: SELECT * FROM rentals; If you want to retrieve this data in internal form, you must execute the following commands: BEGIN TRANSACTION; DECLARE mycursor BINARY CURSOR FOR SELECT * FROM rentals; FETCH ALL FROM mycursor; END TRANSACTION; The only command that returns any data values here is FETCH ALL FROM mycursor . When you fetch from mycursor , you will get three columns of data and each column will be in internal format. Now you're probably wondering exactly what the internal form for each column will be. Table 8.3 shows the relationships between SQL data types and corresponding C data types. Table 8.3. Equivalent C Types for Built-In PostgreSQL Types
Here is the definition of the rentals table: movies=# \d rentals Table "rentals" Column Type Modifiers -------------+--------------+----------- tape_id character(8) rental_date date customer_id integer Given the mappings shown in Table 8.2, you would expect to find the tape_id column represented as a pointer to a char[8] array, the rental_date column as a pointer to a DateADT , and the customer_id column as a pointer to an int32 ; and in fact, that's what you get. Most of the data type mappings are easy to understand. For example, the internal form for a POINT value is POINT structure. If you look in the utils/geo_decls.h header file, you will see that a POINT structure looks like this: typedef struct { double x; double y; } Point; That pretty much matches what you would expect. A few of the internal data types, particularly the date/time types, are more complex. A DATE value, for example, is represented by the DateADT type. The utils/date.h header file shows this definition for DateADT : typedef int32 DateADT; This tells you that a DATE value is stored as a 32-bit integer, but you don't know how to interpret the internal-form values. A bit of sleuthing through the PostgreSQL documentation, combined with some experimentation, shows that a DateADT value represents the number of days since 01-JAN-2000. A good way to deal with internal date/time values is to not deal with internal date/time values. For example, rather than selecting the rental_date column from the rentals table, you could SELECT DATE_PART( ' EPOCH ', rental_date ) . The DATE_PART( 'EPOCH', ...) function returns the number of seconds since the Midnight of 01-JAN-1970. The return value will be of type DOUBLE PRECISION (internal form = float8 ). This way, you can avoid the DateADT type completely. You also have the added benefit that the value returned by DATE_PART( 'EPOCH', ...) just happens to match the standard Unix epoch, so you can use epoch-based date values with Unix library functions. There is one other gotcha to watch out for when using binary cursors. Different CPUs use different byte orderings. For example, on an Intel CPU, the number 0x12345678 would be stored in memory as 78 65 43 21 whereas on a SPARC CPU, this number would be stored as 12 34 56 78 (SPARC format is called big-endian and Intel format is call little-endian .) libpq will not convert between byte-orderings. If your data is hosted on a SPARC-based computer but you are reading internal values from within an Intel-hosted client, you must take care of the byte-ordering conversion yourself. |
EAN: 2147483647
Pages: 220