Client 1 - Connecting to the Server

Client 1 ”Connecting to the Server

If you have read the previous three chapters, you know that there are two schemes for managing PostgreSQL connections.

In libpq and ODBC, you ask the API to create a connection object (a handle) and then your application keeps track of the connection. When you need to interact with the database, you call an API function and pass the connection object to the API. When you are finished interacting with the database, you ask the API to tear down the connection and destroy the connection object. When you use libpgeasy, the API keeps track of the connection object for you. You still have to ask the API to create a connection and, when you are finished, you must ask the API to tear down the connection, but libpgeasy stores the connection object itself and you never need to worry about it.

The ecpg interface gives you a mixture of these two schemes. Most ecpg applications use a single database connection. If you only need one connection, ecpg will keep track of it for you. If your application needs to work with multiple connections, you can switch between them.

In the libpq and ODBC APIs, a database connection is represented by a handle of some type. In an ecpg application, a database connection is simply a name ^[1] .

^[1] Later in this chapter, I'll show you how to use C variables (called host variables in ecpg) within EXEC SQL statements. If you use a host variable to specify a connection name, the variable should be a pointer to a null- terminated string.

Let's start by building a simple client application that connects to a database and then disconnects:

 /* client1a.pgc */ int main( ) {     EXEC SQL CONNECT TO movies AS myconnection;     EXEC SQL DISCONNECT myconnection;     return( 0 ); } 

In client1a , you create a database connection named myconnection . Assuming that the connection attempt is successful, myconnection can be used to access the movies database. You will notice that you did not have to declare any C variables to keep track of the connection; the ecpg API does that for you ”all you have to do is remember the name of the connection. Just like normal C statements, EXEC SQL statements are terminated with a semicolon.

If your application doesn't need more than one database connection, you can omit the AS database clause when you create the connection. You can also omit the name in the DISCONNECT statement:

 /* client1b.pgc */ int main(  ) {     EXEC SQL CONNECT TO movies;     EXEC SQL DISCONNECT;     return( 0 ); } 

client1a.pgc and client2a.pgc are functionally equivalent applications.

You can associate a SQL statement with a named connection using an extended form of the EXEC SQL prefix:

 EXEC SQL AT  connection_name sql_statement  ; 

If you don't specify an AT connection_name clause, ecpg will execute statements using the current connection. When you create a new connection, that connection becomes the current one. You can change the current connection using the SET CONNECTION TO command:

 SET CONNECTION TO  connection_name  ; 

When you close a connection, you can specify any of the statements shown in Table 11.1.

Table 11.1. Various Approaches to DISCONNECT

Statement	Explanation
EXEC SQL DISCONNECT connection-name ;	Closes the named connection
EXEC SQL DISCONNECT;	Closes the current connection
EXEC SQL DISCONNECT CURRENT;	Closes the current connection
EXEC SQL DISCONNECT ALL;	Closes all connections

The ecpg Preprocessor

The C compiler obviously won't understand the EXEC SQL statements that you must include in an ecpg application. To fix this problem, you have to run the source code for your applications through a preprocessor named ecpg .

You can view the syntax expected by the ecpg preprocessor using the --help option:

 $ ecpg --help ecpg - the postgresql preprocessor, version: 2.8.0 Usage: ecpg:  [-v] [-t]               [-I include path]               [ -o output file name]               [-D define name]              file1 [file2] ... 

Let's take a quick peek under the hood to see what the ecpg preprocessor is doing with our source code. I'll run the client1b.pgc program through ecpg:

 $ ecpg client1b.pgc $ cat client1b.c /* Processed by ecpg (2.8.0) */ /* These three include files are added by the preprocessor */ #include <ecpgtype.h> #include <ecpglib.h> #include <ecpgerrno.h> #line 1 "client1b.pgc" /* client1b.pgc */ int main( ) {     { ECPGconnect(__LINE__, "movies" , NULL,NULL , NULL, 0); } #line 5 "client1b.pgc"     { ECPGdisconnect(__LINE__, "CURRENT");} #line 7 "client1b.pgc"     return( 0 ); } 

The ecpg preprocessor converts client1b.pgc into client1b.c . You can see that ecpg has inserted quite a bit of code into our application.

First, ecpg has inserted some comments and a few #include statements. You can usually ignore the #include files ”they declare the functions and data types that are required by the ecpg library.

Following the #include s, ecpg has inserted a C preprocessor directive that you might not have seen before. The #line directive tells the C compiler to pretend that it is compiling the given line (and source file) ”ecpg inserts these directives so that any error messages produced by the C compiler correspond to the correct line numbers in your original source file. For example, consider what would happen if you had a syntax error in your declaration of the main() function. In your original source file ( client1b.pgc ), main() is declared at line 4. In the post-processed file, main() is declared at line 10. Without the #line directives, the C compiler would tell you that an error occurred at line 10 of client1b.c . With the #line directives, the C compiler will report the error at line 4 of client1b.pgc .

Debugging ecpg Applications

Unfortunately, the #line directives inserted by the ecpg preprocessor can really confuse most source-level debuggers . If you find that you need to debug an ecpg application, you should run the ecpg preprocessor over your source code, strip the #line directives from the resulting .c file, and then compile the .c file into an executable. At that point, you will have a program in which the debug symbols correspond to the .c file and your debugger should behave properly.

The interesting part of client1b.c starts where the preprocessor translated

 EXEC SQL CONNECT TO movies; 

into

 { ECPGconnect(__LINE__, "movies" , NULL,NULL , NULL, 0); } 

You can see that ecpg parsed out the EXEC SQL CONNECT command into a simple function call. This is really what ecpg is all about ”translating EXEC SQL statements into function calls. The resulting code calls functions defined in the ecpg library.

Connection Strings

When you create a client application using libpq or libpgeasy, you specify a connection string as a series of keyword=value properties. Connecting to a database using ecpg is a bit different. When you connect to a database using ecpg, you can use any of three forms. The first form is considered obsolete but is still accepted by the most recent releases of PostgreSQL:

  database  [@  host  ][:  port  ][AS  conn-name  ][USER  username  ] 

In this form, you must specify the name of the database to which you want to connect. You can also specify the hostname (or network address), port number (as an integer value), connection name, and username. The username can be in any of the following formats:

 userid userid/password userid IDENTIFIED BY password  userid  USING  password  

Each of the next two forms is similar to a URL (Uniform Resource Locator):

 TCP:POSTGRESQL://  host  [:  port  ] /  database  [AS  conn-name  ] [USER  username  ] UNIX:POSTGRESQL://  host  [:  port  ] /  database  [AS  conn-name  ] [USER  username  ] 

In each of these forms, you specify the type of socket to which you want to connect (either TCP or Unix). If you specify a Unix socket type, the only valid value for the host component is localhost , or 127.0.0.1 .

The documentation distributed with PostgreSQL says that the /database component is optional. In releases 7.1 and 7.2, an apparent bug in the preprocessor makes the / database component mandatory. The 7.1 and 7.2 documentation also suggests that you can specify DEFAULT or USER after EXEC SQL CONNECT TO ; these features do not seem to be implemented.

Here are a few sample connection strings, first in the old (obsolete) format:

 EXEC SQL CONNECT TO movies; EXEC SQL CONNECT TO movies AS movie_conn; EXEC SQL CONNECT TO movies USER bruce/cows; EXEC SQL CONNECT TO movies@arturo:1234 AS remote_movies USER sheila; 

and now in the new (URL-based) format:

 EXEC SQL CONNECT TO UNIX:POSTGRESQL://localhost/movies; EXEC SQL CONNECT TO UNIX:POSTGRESQL://localhost/movies AS movie_conn; EXEC SQL CONNECT TO UNIX:POSTGRESQL://localhost/movies USER bruce/cows; EXEC SQL CONNECT TO TCP:POSTGRESQL://arturo:1234/movies              AS remote_movies USER sheila;