Pqgetvalue failed to return column value for non-text data in binary format
The performance benefits of "binary" over "text" formats are often less than you might think. In the real world, on-the-wire binary formats often need parsing, just like text formats, and if they don't match your internal binary format, still more conversion is required.
Where python is the limiting factor, it's typically the instantiation of objects representing row values that dominates, and that would be true whether a text or binary format was used. The remaining cases are where the processing is non-trivial, and in those cases, I would much rather be working in a high level language where bugs are easier to identify. It's rare indeed that I resort to coding it in C.
I can get by using the socket function, ntohl and ntohs for smallint and int4 for the conversion. But there is no such thing for the float4 and float8 as well as the date, timestamp you mentioned. I wish there is such function or macro available for the C interface.
Serialising floats and doubles is a common enough activity, and it defies belief that the standard C libraries don't provide a way to do it. Having every application that wants to pass floats over the net implement it's own scheme is just asking for bugs.
I would suggest looking at the PostgreSQL source for one example of how to do floats. Timestamps are actually composite types made up of other primitive binary types. The only gotcha with the time types is that they can use ints or floats, depending on how the server was built. 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. 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. 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?
PQprint requires three arguments: 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? When you are finished processing the result set, free the PGresult resources using PQclear see line 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 , rows, the PGresult object will consume enough memory to hold all , rows.
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 fails, you can use PQresultErrorMessage to find the reason for failure. 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.
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. If the command failed, tell the user what went wrong. Look closely at line PQresultStatus returns the command status in the form of an integer .
At line 33, you call PQresultErrorMessage to retrieve the text of the error message. There are three types of information that you can access through a PGresult object. You've already seen the first type of information: 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? First, I'll show you how to find the metadata for your query. For example, the PQntuples function tells you how many rows or tuples will be returned from your query.
If you want to try this function, it is included in client3c. I won't show the complete application here because it is largely the same as client3b. 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. 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. 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 .
In MVCC, the database can contain multiple versions of the same row. There is also a slight difference between a field and a column. A column is stored in a table. A field is the result of an expression. A column is a valid expression, so a column can be considered a field, but a field is not necessarily a column. 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. 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.
PQfsize returns the number of bytes required to hold a value on the server. For variable-length data types, PQfsize returns? 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. 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. 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. 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? At lines 68 through 84, you loop through every row in the result set and print each column value.