Client 1 - Connecting to the Server

Client 1 ”Connecting to the Server

Our first client is very simple ”it connects to a server, disconnects, and then exits.

There are two sets of functions that you can use to connect to a PostgreSQL server: the simple form uses the PQconnectdb() function, whereas the more complex form uses PQconnectStart() and PQconnectPoll() . PQconnectdb() is easier to use because it is a synchronous function; when you call PQconnectdb() , your program will not continue until the connection attempt succeeds or fails. The PQconnectStart() and PQconnectPoll() functions give your application a way to connect to a server asynchronously . A call to PQconnectStart() returns immediately ”it won't wait for the connection attempt to complete. The PQconnectPoll() function can be used to monitor the progress of a connection attempt started by PQconnectStart() . I use the synchronous form in this chapter:

 /* ** File: client1.c */ #include "libpq-fe.h" int main( void ) {     PGconn * connection;     connection = PQconnectdb( "" );     PQfinish( connection);     return( 0 ); } 

client1.c starts by including a single header file: libpq-fe.h . The libpq-fe.h file defines the data types that we need to communicate with libpq. libpq-fe.h also contains function prototypes for the libpq API functions.

Connecting to a PostgreSQL database from libpq can be very simple. The PQconnectdb() function returns a handle to a connection object. PQconnectdb() is synchronous ”it will not return to the caller until the connection attempt succeeds or fails. Here is the prototype for PQconnectdb() :

 extern PGconn *PQconnectdb(const char *conninfo); 

PQconnectdb() takes a single argument ” a pointer to a null- terminated connection string. A connection string is a list of zero or more connection attributes. For example, the connection string "dbname=accounting user =korry" specifies that we want to connect to a database named " accounting " as user " korry ". Each option is of the form keyword=value . Multiple attributes are separated by whitespace.

Notice that I specified an empty connection string in this example. When PQconnectdb() finds an empty connection string, it connects to the default database using a default set of attributes. An empty string is not the same as a NULL pointer. Don't pass a NULL pointer to PQconnectdb() unless you want to see libpq (and your application) die a fiery death.

I'll describe connection attributes and their default values in more detail a bit later. When you call PQconnectdb() , you get back a pointer to a PGconn . PGconn is considered a handle. A handle is an opaque data type, meaning that there is something behind a PGconn pointer, but you can't see it. The information behind a handle is for "internal use only". The libpq library has access to the implementation details, but API users do not. A PGconn object represents a database connection within your application. You will use this object when you call other libpq functions.

Compiling the Client

Now let's compile client1.c and try to run it. You will use a simple makefile to drive the C compiler and linker. Here is the makefile you will use throughout this chapter ”as you add new clients , you will just add new targets to the makefile :

 ## File: Makefile ## ##       Rules to create libpq sample applications CPPFLAGS += -I/usr/local/pgsql/include CFLAGS   += -g LDFLAGS  += -g LDLIBS   += -L/usr/local/pgsql/lib -lpq client1:  client1.o 

If you have installed PostgreSQL into a directory other than /usr/local/pgsql , you should substitute your directory names in the makefile .

To build client1 with this makefile , you can use the following command:

 $ make client1 cc -g -I/usr/local/pg721/include  -c -o client1.o client1.c cc -g  client1.o -L/usr/local/pgsql/lib -lpq -o client1 $ 

The client1 application doesn't expect any command-line parameters so you can run it like this:

 $ ./client1 

Using GNU make to Build libpq Applications

The make utility is used to perform the operations required to turn a source file (such as client1.c ) into an application. make does two (extremely useful) things for you. First, make determines the minimum set of operations required to build an application. Second, make invokes the various preprocessors, compilers, and linkers to actually carry out minimum required operations.

The make utility learns how to build an application by consulting two sources of information. make has a huge collection of built-in rules that describe how to convert one type of file into another type of file. For example, make knows how convert a " .c " file into an executable. First, make converts a source file into a " .o " (object) module by asking the C compiler to compile the source file. Then, make converts the " .o " into an executable by invoking the linker.

The second information source that make uses is known as a makefile (probably because the file is usually named " makefile " ”clever huh?). A makefile is a set of rules that define how to build your specific application (or applications). makefile s are usually written in terms of targets and prerequisites. A target is something that you want to build. A prerequisite is a file that the target depends on. In this case, you want to build an application named client1 ”that's your target. The prerequisite for your target is client1.c . The makefile rule that describes this relationship is " client1: client1.c" . This line is read as " client1 depends on client1.c ". When make sees this rule, it looks through its database of built-in rules to find a way to convert client1.c into client1 . It finds the rule (or actually, rules) to perform this conversion, invokes the C compiler to produce client1.o from client1.c , and then invokes the linker to convert client1.o into the client executable.

The makefile that you will use for the examples in this chapter is a little more complex than the single rule that I just described.

The built-in rule that produces an object module ( .o ) from a C source file ( .c ) looks like this:

 $(CC) -c $(CPPFLAGS) $(CFLAGS) 

This command invokes the C compiler, passing it the command-line flags -c , $(CPPFLAGS) , and $(CFLAGS) . $(CPPFLAGS) and $(CFLAGS) are variables that you can modify within the makefile . To build a libpq application, you have to tell the C compiler how to find the PostgreSQL header files. You can do that by modifying the $(CPPFLAGS) variable:

 CPPFLAGS += -I/usr/local/pgsql/include 

If you want the C compiler to produce debuggable code, you can modify the $(CFLAGS) variable to include the -g flag:

 CFLAGS += -g 

Now when make invokes the C compiler to compile client1.c , the command will look like this:

 cc -c -I/usr/local/pgsql/include -g -o client1.o client1.c 

If the compiler does not find any serious errors in client1.c , you will end up with an object module named client1.o . Your target is not client1.o , but client1 : client1.o is just an intermediate target. To build client1 from client1.o , make will invoke the linker using the following built-in rule:

 $(CC) $(LDFLAGS)  prerequisite.o  $(LOADLIBES) $(LDLIBS) 

You want to link client1.o with the libpq library to produce client1 . The libpq library is found in /usr/local/pgsql/lib on my system, so I'll tell make to include libpq by modifying $(LDLIBS) . I want debugging symbols in my executable, so I also will add the -g flag to $(LDFLAGS) :

 LDLIBS += -L/usr/local/pgsql/lib -lpq LDFLAGS += -g 

The final command produced by make is

 cc -g client1.o -L/usr/local/pgsql/lib -lpq -o client1 

The complete makefile looks like this:

 CPPFLAGS += -I/usr/local/pgsql/include CFLAGS   += -g LDFLAGS  += -g LDLIBS   += -L/usr/local/pgsql/lib -lpq client1:  client1.o 

Identifying the Server

If you provide an empty connection string to PQconnectdb() , how does it find a database server? libpq uses a hierarchy of default values to decide which server to try to connect to.

The libpq library uses three different sources when trying to find each connection attribute.

First, the connection string (given to PQconnectedb() ) can contain a set of keyword=value pairs.

Next, libpq looks for a set of specifically named environment variables. Each environment variable corresponds to one of the keyword=value pairs that you can use in the connection string.

Finally, libpq uses a set of values that are hard-wired into the library at build-time.

Table 8.2 shows how the keywords and environment variables correspond to each other.

Table 8.2. Connection Attributes

Connect-String Keyword	Environment Variable	Example
user	PGUSER	user=korry
password	PGPASSWORD	password=cows
dbname	PGDATABASE	dbname=accounting
host	PGHOST	host=jersey
hostaddr	PGHOSTADDR	hostaddr=127.0.0.1
port	PGPORT	port=5432

You can use the PQconndefaults() function to find the default value for each connection attribute.

 1 /*  2 ** File: get_dflts.c  3 */  4  5 #include <stdio.h>  6 #include <libpq-fe.h>  7  8 int main( void )  9 { 10   PQconninfoOption * d; 11   PQconninfoOption * start; 12 /* 13 **  Get the default connection attributes 14 */ 15   start = d = PQconndefaults( ); 16 17   while( d->keyword != NULL ) 18   { 19     printf( "keyword  = %s\n", d->keyword  ? d->keyword  : "null" ); 20     printf( "envvar   = %s\n", d->envvar   ? d->envvar   : "null" ); 21     printf( "label    = %s\n", d->label    ? d->label    : "null" ); 22     printf( "compiled = %s\n", d->compiled ? d->compiled : "null" ); 23     printf( "val      = %s\n", d->val      ? d->val      : "null" ); 24     printf( "\n" ); 25 26     d++; 27   } 28 29 /* 30 **  Free up the memory that lipq allocated on our behalf 31 */ 32 33   PQconninfoFree( start ); 34 35   return( 0 ); 

When you call the PQconndefaults() function, you get back a pointer to the first member of an array of PQconninfoOption structures. Each structure contains (among other things) a keyword, the name of an environment variable, a hard-wired (or compiled-in) value, and a current value. If you iterate through the members of this array, you can recognize the end of the list by looking for a member where the keyword pointer is NULL .

You can compile this program by adding another entry to the makefile and then typing make get_dflts :

 $ cat makefile ## ##  File:  Makefile ## ##         Rules for building libpq sample applications ## CPPFLAGS += -I/usr/local/pgsql/include CFLAGS   += -g LDFLAGS  += -g LDLIBS   += -L/usr/local/pgsql/lib -lpq client1:    client1.o get_dflts:  get_dflts.o $ make get_dflts cc -g -I/usr/local/pg721/include -c -o get_dflts.o get_dflts.c cc -g  get_dflts.o -L/usr/local/pgsql/lib -lpq -o get_dflts 

Running the get_dflts program on my system results in the following:

 $ ./get_dflts keyword  = authtype envvar   = PGAUTHTYPE label    = Database-Authtype compiled = val      = keyword  = service envvar   = PGSERVICE label    = Database-Service compiled = (null) val      = (null) keyword  = user envvar   = PGUSER label    = Database-User compiled = (null) val      = Administrator keyword  = password envvar   = PGPASSWORD label    = Database-Password compiled = val      = keyword  = dbname envvar   = PGDATABASE label    = Database-Name compiled = (null) val      = Administrator keyword  = host envvar   = PGHOST label    = Database-Host compiled = (null) val      = (null) keyword  = hostaddr envvar   = PGHOSTADDR label    = Database-Host-IPv4-Address compiled = (null) val      = (null) keyword  = port envvar   = PGPORT label    = Database-Port compiled = 5432 val      = 5432 keyword  = tty envvar   = PGTTY label    = Backend-Debug-TTY compiled = val      = keyword  = options envvar   = PGOPTIONS label    = Backend-Debug-Options compiled = val      = 

You can see that each keyword member corresponds to a keyword accepted by the PQconnectdb() function. You may have noticed that PQconndefaults() returned more connection attributes than are shown in Table 8.2. Some of the connection attributes are obsolete but still supported for compatibility with older clients. Some attributes are reserved for future use and are not fully supported. Other attributes exist for debugging purposes and are not normally used. If you stick to the connection attributes listed in Table 8.2, you should be safe.

Each connection parameter is computed from a sequence of default values, in the absence of explicitly specified values in the connection string.

For example, if you omit the port keyword from your PQconnectdb() connection string, libpq will look for an environment variable named PGPORT . If you have defined the PGPORT environment variable, libpq will use the value of that variable for the port; if not, a hard-wired (or compiled-in) value is used. In this case, the hard-wired port number is 5432 . (Compiled-in values are defined when the libpq object-code library is built from source code.) The default hierarchy works like this:

If the keyword is found in the connection string, the value is taken from the connection string, else

If the associated environment variable is defined, the value is taken from the environment variable, else

The hard-wired value is used.

The user and dbname parameters are treated a little differently ”rather than using hard-wired values, the last default for the user parameter is your login name and the dbname parameter is copied from the user parameter. For example, if I am logged in (to my Linux operating system) as user korry , both user and dbname will default to korry . Of course, I can override the default user and dbname attributes using environment variables or explicit connect-string attributes.