POSTGRESQL is a network-capable database. That is, the database server and user application can be run on different computers. Because character strings have the same representation on all computers, they are used for communication between the user program and database server. Queries are submitted as character strings, and results are passed back as character strings. This approach provides reliable communication even when the two computers involved are quite different.
The sample programs in this chapter perform SELECTs on a CHAR(30) column. Because query results are returned as character strings, returned values can be assigned directly to program variables. In contrast, noncharacter string columns, like INTEGER and FLOAT , cannot be assigned directly to integer or floating-point variables. A conversion might be required instead.
For example, when you are using LIBPQ or LIBPQ ++, a SELECT on an INTEGER column does not return an integer from the database, but rather a character string that must be converted to an integer by the application. An INTEGER is returned as the string '983' rather than the integer value 983. To assign this value to an integer variable, you use the C library function atoi() --for example, var = atoi(colval).
One exception involves BINARY cursors, which return binary representations of column values. You can assign results from BINARY cursors directly to program variables. However, because they return column values in binary format, both the application and the database server must be running on the same computer or at least on computers with the same CPU architecture. See the DECLARE manual page for more information on BINARY cursors.
LIBPGEASY uses fetch() to return values directly into program variables. This function should place results into character string variables or use BINARY cursors if possible.
ECPG automatically converts data returned by POSTGRESQL to the proper format before assignment to program variables.
The interpreted languages covered later in this chapter have type-less variables, so they do not have this problem.