CC
C/C++Today, C and C++ are very widespread. A lot of software has been written in C or C++, especially for Unix. PostgreSQL is written in C as well, so it seems obvious that PostgreSQL provides a powerful C/C++ interface. In this section, you will take a closer look at this interface. An Overview of C/C++Before we get to the C programming interface for PostgreSQL, we will have a closer look at the history and the development of C. C was developed at Bell Laboratories by Dennis Ritchie in 1972 and is today's most widespread programming language. C compilers are available on almost any platform, so C code is very portable as long as developers stick closely to the ANSI C standard. When Dennis Ritchie developed C, he took many of the principles and ideas from the earlier language, B, and B's earlier ancestors BCPL and CPL. Combined Programming Language (CPL) was a language that was designed to be a high-level programming language that also allows the programmer to influence the behavior of the hardware itself. Platform independency has also always played a major part in C development. One major disadvantage of CPL was that it was too large for use in many applications at that time. In 1967, Basic CPL (BCPL) was created as a scaled-down version of CPL while still retaining the basic features of CPL. In 1970, Ken Thompson (Bell Labs) developed B ”a scaled-down language of BCPL that was especially optimized for system programming. In 1972, Dennis Richie brought back some generality to the programming language and the result is now known as the C programming language. Up to that time, the UNIX operating system was still mostly written in Assembler. When the advantages of C became apparent, the major parts of the operating system were rewritten in C. From that time on, the C programming language has always been strongly related to UNIX, and C is still the language of UNIX hackers. Soon, C was used by many organizations all around the globe. In 1983, an ANSI standard was created to make C applications compatible because many slightly different accents of the language already existed. C is a procedural language. People soon recognized that some sort of object-orientation would be useful for the language. In October 1970, a first version of "C with classes" was used. In December 1983, "C with classes" finally became known as C++. Along with C, C++ was rapidly developed and became very widespread around the globe. Many compilers, libraries, and documentation were written for C/C++ to make it the language it is today. Despite the development of many other languages, C/C++ is still the number one and loved by many people. One of the main advantages of the language is its speed. C programs perform extremely well but are, in contrast to Assembler, still readable and, which is very important, portable. Like most of the software packages used on a UNIX system, PostgreSQL is written in C. Therefore, the C/C++ interface is highly developed and used as the base for building future programming interfaces. In the next few sections, we will take a closer look at the C/C++ interface of PostgreSQL and how this interface can be used efficiently in some examples. Major parts of the sample code shown in this section have been provided and developed by Erich Fruehstueck, our long time UNIX teacher and C guru. Erich Fruehstueck is the developer and maintainer of the EFEU (EFEU is short for E rich F ruehstueck E ntwicklungs u mgebung) package; a package containing a very powerful C/C++-like UNIX interpreter that can easily be used for working with multidimensional data structures. ConnectingThe first thing we will deal with in this section is connecting to the database. The C programming interface to PostgreSQL is called libpq . libpq is the base for other programming interfaces, such as libpq++ (C++), libpgtcl (Tcl), Perl, ECPG, and many others. The most important function when establishing a connection is PQconnectdb that returns a PGconn object representing one open connection. In general, PQconnectdb and PQsetdbLogin return no null pointer as long as there is enough memory available on the system to create the PGconn object. To check whether a connection has successfully been established, the PQstatus function has to be used. We will have a closer look at that function later in this section. To expound how a connection can be established using the libpq interface, we have included two examples. The main target is to write a program that establishes a connection to the database and supports some command-line parameters, as is shown in the following code line. We will also have a look at exception handling. conn1 db user=hs host=localhost port=5432 The file containing the main function will be called main.c and contains the code as follows : // main1.c #include <stdlib.h> #include "example.h" #define ARGS "db [host=name] [port=num] [user=name] [password=auth]" int main (int argc, char **argv) { char *cinfo; PGconn *conn; int i; /* Build connection info string from command args. */ if (argc < 2) { fprintf(stderr, "usage: %s %s\n", argv[0], ARGS); exit(EXIT_FAILURE); } cinfo = paste("dbname", "=", argv[1]); for (i = 2; i < argc; i++) { char *save = cinfo; cinfo = paste(save, " ", argv[i]); free(save); } /* Make a new connection to the database server */ conn = PQconnectdb(cinfo); if (conn == NULL) { fputs("not enough memory to create PGconn data.\n", stderr); exit(EXIT_FAILURE); } /* Check the status of the connection */ if (PQstatus(conn) == CONNECTION_BAD) { char *msg = PQerrorMessage(conn); fputs(msg ? msg : "PQconnectdb() failed.\n", stderr); info(stderr, conn); PQfinish(conn); exit(EXIT_FAILURE); } /* Frontend commands */ info(stdout, conn); /* Finish connection */ PQfinish(conn); free(cinfo); exit(EXIT_SUCCESS); } First, we include the required C libraries in the program and add a preprocessor command to set the variable ARGS that contains the definition of the command-line parameters accepted by the program. In main.c , we include only stdlib.h and example.h ”the library we need for interacting with the database is included in example.h : /* header for connection examples - example.h */ #ifndef _example_h #define _example_h 1 #include <pgsql/libpq-fe.h> extern char *paste (const char *s1, const char *delim, const char *s2); extern void info (FILE *file, PGconn *conn); #endif /* example.h */ As you can see, example.h includes libpq-fe.h , which is the library for interacting with PostgreSQL. Depending on your system, the location of this library may vary. Users of Debian (the program has been tested on Debian 2.2r2) can simply use #include <libpq-fe.h> instead of #include <pgsql/libpq-fe.h> , as has to be done on RedHat 7.1 for example (if you use the standard RPM packages of PostgreSQL). To find out where the required library can be found on the system, simply use the following: find /usr/ -name libpq*h -print 2> /dev/null example.h also contains the header information we use for the self-defined function we will use in the main.c file. In the main function, we define three variables we will use later in the program: *paste (const char *s1, const char *delim, const char *s2) will be used to make one string out of the command line flags. info(FILE *file, PGconn *conn) will be used to retrieve information about the authentification process. Let's get back to main.c. After defining the variables, we start building the connect string we need to establish the connection to the database. The first parameter passed to the function is the name of the database; if it is not defined, an error will be displayed and fprintf is used to display the error: fprintf(stderr, "usage: %s %s\n", argv[0], ARGS); The error message itself is defined in the preprocessor command in line 4 in the main.c file. Let's see what happens when we execute the script without parameters: [erich@duron ex1]$ ./conn1 usage: ./conn1 db [host=name] [port=num] [user=name] [password=auth] In the next step, the connect string is compiled using paste ”the complete connect string is assigned cinfo : for (i = 2; i < argc; i++) { char *save = cinfo; cinfo = paste(save, " ", argv[i]); free(save); } The paste function used here is defined in paste.c : // paste.c #include <stdlib.h> #include "example.h" /* paste strings */ char *paste (const char *s1, const char *delim, const char *s2) { size_t n1, nx, n2; char *s; n1 =strlen(s1); nx = strlen(delim); n2 = strlen(s2); s = malloc(n1 + nx + n2 + 1); if (s == NULL) { fprintf(stderr, "malloc(%d) failed.\n", n1 + nx + n2); exit(EXIT_FAILURE); } strcpy(s, s1); strcpy(s + n1, delim); strcpy(s + n1 + nx, s2); return s; } After we have computed the connect string, we can easily establish the connection to the database by using: conn = PQconnectdb(cinfo); If PQconnectdb returns a NULL pointer, the connect process has failed due to a lack of memory. Checking the NULL pointer is the first thing that should be done when establishing the connection. The second thing is to check the result of the PQstatus function. PQstatus has to be called with the result of the PQconnectdb function. If PQstatus returns CONNECTION_BAD , the connection has not been established successfully. We extract the error using PQerrorMessage and display it by using the following: fputs(msg ? msg : "PQconnectdb() failed.\n", stderr); info(stderr, conn); info is a function defined in info.c : // info.c /* Connection Info */ #include <stdlib.h> #include "example.h" static void pval (FILE *file, const char *name, const char *val) { if (val) { fprintf(file, "\t%s = \"%s\"\n", name, val); } else fprintf(file, "\t%s = NULL\n", name); } void info (FILE *file, PGconn *conn) { fprintf(file, "PGconn = { \n"); pval(file, "status", (PQstatus(conn) == CONNECTION_OK) ? "ok" : "bad"); pval(file, "dbname", PQdb(conn)); pval(file, "user", PQuser(conn)); pval(file, "pass", PQpass(conn)); pval(file, "host", PQhost(conn)); pval(file, "port", PQport(conn)); pval(file, "tty", PQtty(conn)); pval(file, "options", PQoptions(conn)); fprintf(file, "} \n"); } After compiling the program, we can easily execute it by using a command such as follows: [erich@duron ex1]$ conn1 db user=bad_user Some people do not like this kind of syntax. To show you that options can be extracted from the command line differently, we have included a version of the program that uses getopt instead of paste and PQsetdbLogin instead of PQconnectdb to establish the connection to the database. Both versions of the program perform exactly the same task but use different functions for connecting to the database: // main2.c #include <stdlib.h> #include <unistd.h> #include "example.h" #define OPTIONS "?h:p:u:a:" #define ARGS "[-h host] [-p port] [-u user] [-a auth] db" /* main program */ int main (int argc, char **argv) { char *pghost, *pgport, *login, *passwd, *dbname; PGconn *conn; int opt; /* Parse command line options */ pghost = NULL; pgport = NULL; login = NULL; passwd = NULL; dbname = NULL; while ((opt = getopt(argc, argv, OPTIONS)) != -1) { switch (opt) { case '?': fprintf(stderr, "usage: %s %s\n", argv[0], ARGS); exit(EXIT_SUCCESS); case 'h': pghost = optarg; break; case 'p': pgport = optarg; break; case 'u': login = optarg; break; case 'a': passwd = optarg; break; default: exit(EXIT_FAILURE); } } if (optind == argc - 1) { dbname = argv[optind]; } else { fprintf(stderr, "usage: %s %s\n", argv[0], ARGS); exit(EXIT_FAILURE); } /* Make a new connection to the database server */ conn = PQsetdbLogin(pghost, pgport, NULL, NULL, dbname, login, passwd); if (conn == NULL) { fputs("not enough memory to create PGconn data.\n", stderr); exit(EXIT_FAILURE); } /* Check the status of the connection */ if (PQstatus(conn) == CONNECTION_BAD) { char *msg = PQerrorMessage(conn); fputs(msg ? msg : "PQsetdbLogin() failed.\n", stderr); info(stderr, conn); PQfinish(conn); exit(EXIT_FAILURE); } /* Frontend commands */ info(stdout, conn); /* Finish connection */ PQfinish(conn); exit(EXIT_SUCCESS); } The program accepts the same parameters as our first example. This time, the syntax is different: [erich@duron ex1]$ ./conn2 -? usage: ./conn2 [-h host] [-p port] [-u user] [-a auth] db The name of the database does not have to be the first parameter anymore because all flags are extracted using getopt . After we have presented and explained all components of our two examples, we can write a Makefile to compile the programs. Using Makefiles (or software that generates Makefiles) for compiling your software is very useful because it saves a lot of time when compiling huge applications. If the Makefile has been written properly, it is even possible to install and remove a compiled application correctly. We have included a Makefile that compiles the previous code. We have tried to write a "beautiful" Makefile so that C newbies and Windows hackers can see how a Makefile should look. In general, we have tried to show you short Makefiles in the book that do the basic job. I guess this section about C is a good place to show a "full" Makefile containing a little bit more than just the plain compiler commands. # Makefile for running example # Configuration parameters DB= db # Name of database PSQL= psql -q # PostgreSQL - frontend PQINC= -I/usr/include/postgresql PQLIB= -lpq # Compiling rules CC= gcc CPPFLAGS= $(PQINC) CFLAGS= -O2 LDFLAGS= $(PQLIB) # Show usage of make for current example usage:: @echo "usage: make setup run clean" # Rules for commands main1.o: example.h main1.c $(CC) $(CFLAGS) $(CPPFLAGS) -c main1.c -o $@ main2.o: example.h main2.c $(CC) $(CFLAGS) $(CPPFLAGS) -c main2.c -o $@ paste.o: example.h paste.c $(CC) $(CFLAGS) $(CPPFLAGS) -c paste.c -o $@ info.o: example.h info.c $(CC) $(CFLAGS) $(CPPFLAGS) -c info.c -o $@ clean:: rm -f main1.o main2.o paste.o info.o setup:: conn1 clean:: rm -f conn1 conn1: main1.o paste.o info.o $(CC) $(CFLAGS) main1.o paste.o info.o -o $@ $(LDFLAGS) setup:: conn2 clean:: rm -f conn2 conn2: main2.o info.o $(CC) $(CFLAGS) main2.o info.o -o $@ $(LDFLAGS) # Rules for example database setup:: stamp_db clean:: -dropdb $(DB) rm -f stamp_db stamp_db: if test -f $@; then dropdb $(DB); fi createdb $(DB) touch $@ # Run the example run:: setup conn1 conn2 @echo "Running example" -conn1 $(DB) -conn1 $(DB) user=postgres -conn1 $(DB) user=bad_user -conn1 bad_database -conn2 $(DB) -conn2 -u postgres $(DB) -conn2 -u bad_user $(DB) -conn2 bad_database Let's test the Makefile: [erich@duron ex1]$ make usage: make setup run clean make tells us that three major targets are defined in the Makefile. We have not set up a database yet, because generating and removing the database is also included in the Makefile. Let's have a look at what has to be done when setting up the database and compiling the code: [erich@duron ex1]$ make setup -n gcc -O2 -I/usr/include/postgresql -c main1.c -o main1.o gcc -O2 -I/usr/include/postgresql -c paste.c -o paste.o gcc -O2 -I/usr/include/postgresql -c info.c -o info.o gcc -O2 main1.o paste.o info.o -o conn1 -lpq gcc -O2 -I/usr/include/postgresql -c main2.c -o main2.o gcc -O2 main2.o info.o -o conn2 -lpq if test -f stamp_db; then dropdb db ; fi createdb db touch stamp_db First, we compile our source files with the help of Richard M. Stallman's (in my opinion one of the greatest hackers in history and the father of free software and the GPL) C compiler (GNU C compiler). If make setup has been used before, a file called stamp_db exists. If this file does not exist, the database has to be recreated. make run is used to execute the software. We can use make run without running make setup first. Our Makefile runs make setup implicitly if the sources have not been compiled and if the database has not been initialized yet. make run executes the sample code with various settings. Some of these settings will not work. [erich@duron ex1]$ make run -n echo "Running example" conn1 db conn1 db user=postgres conn1 db user=bad_user conn1 bad_database conn2 db conn2 -u postgres db conn2 -u bad_user db conn2 bad_database Our sample program tells us which connect string is used to establish the connection to the database and whether the connection has been established successfully. Look at what happens when we execute the software: Note We execute the programs as user erich . You have to make sure that this user has enough rights to run the batch job. [erich@duron ex1]$ make run Running example conn1 db PGconn = { status = "ok" dbname = "db" user = "erich" pass = "" host = NULL port = "5432" tty = "" options = "" } conn1 db user=postgres FATAL 1: user "postgres" does not exist PGconn = { status = "bad" dbname = "db" user = "postgres" pass = "" host = NULL port = "5432" tty = "" options = "" } make: [run] Error code 1 (ignored) conn1 db user=bad_user FATAL 1: SetUserId: user 'bad_user' is not in 'pg_shadow' PGconn = { status = "bad" dbname = "db" user = "bad_user" pass = "" host = NULL port = "5432" tty = "" options = "" } make: [run] Error code 1 (ignored) conn1 bad_database FATAL 1: Database "bad_database" does not exist in the system catalog. PGconn = { status = "bad" dbname = "bad_database" user = "erich" pass = "" host = NULL port = "5432" tty = "" options = "" } make: [run] Error code 1 (ignored) conn2 db PGconn = { status = "ok" dbname = "db" user = "erich" pass = "" host = NULL port = "5432" tty = "" options = "" } conn2 -u postgres db FATAL 1: user "postgres" does not exist PGconn = { status = "bad" dbname = "db" user = "postgres" pass = "" host = NULL port = "5432" tty = "" options = "" } make: [run] Error code 1 (ignored) conn2 -u bad_user db FATAL 1: user "bad_user" does not exist PGconn = { status = "bad" dbname = "db" user = "bad_user" pass = "" host = NULL port = "5432" tty = "" options = "" } make: [run] Error code 1 (ignored) conn2 bad_database FATAL 1: Database "bad_database" does not exist in the system catalog. PGconn = { status = "bad" dbname = "bad_database" user = "erich" pass = "" host = NULL port = "5432" tty = "" options = "" } make: [run] Error code 1 (ignored) Our Makefile not only produces binaries and creates a database, it also makes sure that the stuff created can be removed from the system safely. Therefore, we have included make clean . In the following code, you can see what make clean is good for: [erich@duron ex1]$ make clean -n rm -f main1.o main2.o paste.o info.o rm -f conn1 rm -f conn2 dropdb db rm -f stamp_db Finally, it can be said that connecting and disconnecting with the help of the libpq library is an easy task. In the next section, we will see how other things, such as selecting data, can be done. Simple Selects ”An ExampleAfter dealing with the authentification process and database connections, we will see how data can be retrieved from the database by using simple SELECT statements. We have compiled a small sample database that we will use in this section to show you how things work: -- Seting up example tables -- CREATE TABLE "persons" ( "name" text, "birth" date, "gender" character(1) ); CREATE UNIQUE INDEX persons_name_key on persons ("name"); CREATE TABLE "income" ( "name" text, "year" integer, "income" integer ); CREATE INDEX income_name_key on income ("name"); COPY "persons" FROM stdin; Albert 1970-01-01 m John 1973-04-04 m Carla 1963-10-21 f Jenny 1982-09-21 f Hans 1978-08-09 m Epi 1976-06-12 m Etschi 1960-02-20 f Bill 1980-07-12 m \. COPY "income" FROM stdin; Albert 1997 32000 Albert 1998 28000 Albert 1999 30000 Albert 2000 35000 Jon 1998 20000 Jon 1999 40000 Jon 2000 50000 Carla 1998 30000 Carla 1999 32000 Jenny 1999 45000 Jenny 2000 45000 Hans 1999 47000 Hans 2000 59000 Epi 1999 25000 Epi 2000 21000 Etschi 1999 30000 Etschi 2000 34000 Bill 1999 41000 Bill 2000 43000 \. CREATE VIEW pincome AS SELECT p.name, p.gender, i.year, i.income FROM persons p, income i WHERE p.name = i.name; The following example shows how data can be retrieved from a table. Like our first example, the program supports some command-line parameters: // main1.c #include <stdlib.h> #include "example.h" #define ARGS "db [host=name] [port=num] [user=name] [password=auth]" int main (int argc, char **argv) { char *cinfo; PGconn *conn; int i; /* Build connection info string from command args. */ if (argc < 2) { fprintf(stderr, "usage: %s %s\n", argv[0], ARGS); exit(EXIT_FAILURE); } cinfo = paste("dbname", "=", argv[1]); for (i = 2; i < argc; i++) { char *save = cinfo; cinfo = paste(save, " ", argv[i]); free(save); } /* Make a new connection to the database server */ conn = PQconnectdb(cinfo); if (conn == NULL) { fputs("not enough memory to create PGconn data.\n", stderr); exit(EXIT_FAILURE); } /* Check the status of the connection */ if (PQstatus(conn) == CONNECTION_BAD) { char *msg = PQerrorMessage(conn); fputs(msg ? msg : "PQconnectdb() failed.\n", stderr); info(stderr, conn); PQfinish(conn); exit(EXIT_FAILURE); } /* Frontend commands */ example_select(conn); /* Finish connection */ PQfinish(conn); free(cinfo); exit(EXIT_SUCCESS); } The first thing that has to be done is to include the header files, such as example.h : // example.h /* header for connection examples */ #ifndef _example_h #define _example_h 1 #include <pgsql/libpq-fe.h> extern char *paste (const char *s1, const char *delim, const char *s2); extern void info (FILE *file, PGconn *conn); extern PGresult *query (PGconn *conn, const char *cmd); extern void example_select (PGconn *conn); #endif /* example.h */ As in previous examples, we connect to the database and display an error if the connect process fails. To execute the query, use example_select(conn); The function is defined in select.h . We simply have to pass the database handle to the function. The code of select.c is as follows: // select.c /* Selection example */ #include <stdlib.h> #include "example.h" #define QUERY "SELECT * FROM pincome ORDER BY name, year" void example_select (PGconn *conn) { PGresult *res; int i, j, n, k; printf("query: %s\n", QUERY); res = query(conn, QUERY); if (PQresultStatus(res) != PGRES_TUPLES_OK) { fprintf(stderr, "Command didn't return tuples properly\n"); PQclear(res); return; } n = PQntuples(res); k = PQnfields(res); for (j = 0; j < k; j++) { if (j) printf(" "); printf("%10s", PQfname(res, j)); } printf("\n"); for (i = 0; i < n; i++) { for (j = 0; j < k; j++) { if (j) printf(" "); printf("%10s", PQgetvalue(res, i, j)); } printf("\n"); } PQclear(res); } After including all required header files, we define the query string using a preprocessor command. In example_select , we define several variables: res is a pointer to the result of the query. The integer values defined next are used in the loops that display the header and the result of the query. To execute the query, call the query function defined in query.c : // query.c /* Connection Info */ #include <stdlib.h> #include "example.h" PGresult *query (PGconn *conn, const char *cmd) { PGresult *result; char *msg; result = PQexec(conn, cmd); if (result == NULL) { fputs("not enough memory to create PGresult data.\n", stderr); PQfinish(conn); exit(EXIT_FAILURE); } switch (PQresultStatus(result)) { case PGRES_BAD_RESPONSE: case PGRES_NONFATAL_ERROR: case PGRES_FATAL_ERROR: msg = PQresultErrorMessage(result); fputs(msg ? msg : "PQexec() failed.\n", stderr); PQclear(result); PQfinish(conn); exit(EXIT_FAILURE); default: break; } return result; } In query.c , we call PQexec to select data from the table. If the function returns NULL , the system has run out of memory and we terminate the program. Otherwise , we use PQresultStatus to check the return value of the query. Depending on the return value, we decide whether we have to quit the application. If no error has occurred, we return a pointer to the result to the calling function that is, in our case, example_select . Back in example_select , we use the PQresultStatus function to check for errors again and compute the number of tuples and fields. We will need this information to display the result correctly. The first thing we have to display is the header of the result. Consequently, we use the PQfname function to find out which label is assigned to a certain column. After displaying the header, we extract and display the data. PQgetvalue extracts one field from the result, and we can display the result by using a simple printf command. In the previous example, we want the result to be displayed as a string that is 10 bytes long. Finally, we clear the result of the query. To compile the sources, we use a Makefile again. The following is the code of a very simple Makefile: setup : main1.c paste.c info.c query.c select.c example.h gcc -g -I/usr/include/postgresql -c main1.c -o main1.o gcc -g -I/usr/include/postgresql -c paste.c -o paste.o gcc -g -I/usr/include/postgresql -c info.c -o info.o gcc -g -I/usr/include/postgresql -c query.c -o query.o gcc -g -I/usr/include/postgresql -c select.c -o select.o gcc -g main1.o paste.o info.o query.o select.o -o sel1 -lpq if test -f stamp_db; then dropdb db ; fi createdb db touch stamp_db psql -q db < setup.sql clean : rm -f main1.o paste.o info.o query.o select.o rm -f sel1 dropdb db rm -f stamp_db run : ./sel1 db To set up the database and compile the sources, we use make again: [erich@duron ex2]$ make setup gcc -g -I/usr/include/postgresql -c main1.c -o main1.o gcc -g -I/usr/include/postgresql -c paste.c -o paste.o gcc -g -I/usr/include/postgresql -c info.c -o info.o gcc -g -I/usr/include/postgresql -c query.c -o query.o gcc -g -I/usr/include/postgresql -c select.c -o select.o gcc -g main1.o paste.o info.o query.o select.o -o sel1 -lpq if test -f stamp_db; then dropdb db ; fi createdb db CREATE DATABASE touch stamp_db psql -q db < setup.sql The paste.c and info.c files are the same as in the previous examples, so we have not included the code again. To execute the program, we use make run : [erich@duron ex2]$ make run Running example sel1 db query: SELECT * FROM pincome ORDER BY name, year name gender year income Albert m 1997 32000 Albert m 1998 28000 Albert m 1999 30000 Albert m 2000 35000 Bill m 1999 41000 Bill m 2000 43000 Carla f 1998 30000 Carla f 1999 32000 Epi m 1999 25000 Epi m 2000 21000 Etschi f 1999 30000 Etschi f 2000 34000 Hans m 1999 47000 Hans m 2000 59000 Jenny f 1999 45000 Jenny f 2000 45000 All records in pincome are returned and displayed onscreen. Of course, we have also defined make clean to safely remove the binaries and the database created by make setup : [erich@duron ex2]$ make clean -n rm -f main1.o paste.o info.o query.o select.o rm -f sel1 dropdb db rm -f stamp_db Binary Cursors and COPYIn this section, we will see how data can be sent to PostgreSQL using some sort of COPY and how data can be retrieved with the help of binary cursors. In some cases, binary cursors have a significant advantage over "ordinary" cursors because many C programs work with binary data internally. If the database has to convert the binary data to ASCII, the application has to convert the result back to a binary format. If the data is retrieved in binary format, the whole transformation process can be skipped , and your applications will run faster. The program we will present next is already quite long, and we recommend that you spend some time to understand every line completely: // main.c #include <stdlib.h> #include <unistd.h> #include "example.h" #define OPTIONS "?h:p:u:a:dcn:" #define ARGS "[-h host] [-p port] [-u user] [-a auth] \ [-d] [-c] [-n anz] db tab" /* main program */ int main (int argc, char **argv) { char *pghost, *pgport, *login, *passwd; char *dbname, *tabname; int debug, create, ngrp; PGconn *conn; int opt; /* Parse command line options */ pghost = NULL; pgport = NULL; login = NULL; passwd = NULL; dbname = NULL; tabname = NULL; debug = 0; create = 0; ngrp = 2; while ((opt = getopt(argc, argv, OPTIONS)) != -1) { switch (opt) { case '?': fprintf(stderr, "usage: %s %s\n", argv[0], ARGS); exit(EXIT_SUCCESS); case 'h': pghost = optarg; break; case 'p': pgport = optarg; break; case 'u': login = optarg; break; case 'a': passwd = optarg; break; case 'd': debug = 1; break; case 'c': create = 1; break; case 'n': ngrp = strtol(optarg, NULL, 0); break; default: exit(EXIT_FAILURE); } } if (optind == argc - 2) { dbname = argv[optind]; tabname = argv[optind + 1]; } else { fprintf(stderr, "usage: %s %s\n", argv[0], ARGS); exit(EXIT_FAILURE); } /* Make a new connection to the database server */ conn = PQsetdbLogin(pghost, pgport, NULL, NULL, dbname, login, passwd); if (conn == NULL) { fputs("not enough memory to create PGconn data.\n", stderr); exit(EXIT_FAILURE); } /* Check the status of the connection */ if (PQstatus(conn) == CONNECTION_BAD) { char *msg = PQerrorMessage(conn); fputs(msg ? msg : "PQsetdbLogin() failed.\n", stderr); info(stderr, conn); PQfinish(conn); exit(EXIT_FAILURE); } if (debug) PQtrace(conn, stderr); /* Frontend commands */ if (create) sample_data(conn, tabname, ngrp); else cursor_example(conn, tabname, ngrp); /* Finish connection */ PQfinish(conn); exit(EXIT_SUCCESS); } First, we include the required libraries and define some variables. In the next step, some variables are initialized. One of these values is debug . If debug is set to a value different than zero, the application will display debugging information during the execution of the program. This can be very useful if you are looking for tricky bugs . We use PQtrace for generating the debugging information. PQtrace enables the frontend and backend messages. Note Using PQtrace is only useful if you know the basics of PostgreSQL's internal protocols. To turn off tracing, PQuntrace has to be used. Because we want the program to understand command-line options, we use getopt to extract the flags from the input. If you want to import a huge amount of data into the database, it is not recommended to use a set of INSERT statements because of speed. INSERT statements are ghastly slow compared to COPY , so the performance of your application will suffer when the amount of data increases . In this example, we will show how COPY can be used. Let's have a closer look at sample.c : //sample.c * Selection example */ #include <stdlib.h> #include "example.h" #define SQL_CREATE "CREATE TABLE %s (idx int4, val real)" #define SQL_COPY "COPY %s from stdin" void sample_data (PGconn *conn, const char *name, int rows) { PGresult *res; char buf[256]; int i; vquery(conn, sql(SQL_CREATE, name), PGRES_COMMAND_OK); res = xquery(conn, sql(SQL_COPY, name), PGRES_COPY_IN); srand48(0); for (i = 1; i <= rows; i++) { sprintf(buf, "%d\t%.5f\n", i, drand48()); if (PQputline(conn, buf) != 0) fprintf(stderr, "PQputline() failed\n"); } if (PQputline(conn, "\.\n") != 0) fprintf(stderr, "PQputline() failed\n"); if (PQendcopy(conn) != 0) fprintf(stderr, "PQendcopy() failed\n"); PQclear(res); } The function generates random records and stores them in a table. sample_data needs two additional files. Let's have a look at sql.c first: // sql.c /* Function to insert parameters in a sql-command: In real applications there should be checks to avoid buffer overflow. Using a fixed size buffer is not a good idea. */ #include <stdlib.h> #include <stdarg.h> #include "example.h" static char sql_buf[1024]; char *sql (const char *fmt, ...) { va_list args; va_start(args, fmt); vsprintf(sql_buf, fmt, args); va_end(args); return sql_buf; } The second file needed is query.c : // query.c /* Connection Info */ #include <stdlib.h> #include "example.h" PGresult *query (PGconn *conn, const char *cmd) { PGresult *result; const char *msg; result = PQexec(conn, cmd); if (result == NULL) { fputs("not enough memory to create PGresult data.\n", stderr); PQfinish(conn); exit(EXIT_FAILURE); } switch (PQresultStatus(result)) { case PGRES_BAD_RESPONSE: case PGRES_NONFATAL_ERROR: case PGRES_FATAL_ERROR: fprintf(stderr, "PQexec() failed.\n"); fprintf(stderr, "Command was: %s.\n", cmd); if ((msg = PQresultErrorMessage(result)) != NULL) fputs(msg, stderr); PQclear(result); PQfinish(conn); exit(EXIT_FAILURE); default: break; } return result; } PGresult *xquery (PGconn *conn, const char *cmd, ExecStatusType type) { PGresult *result = query(conn, cmd); int stat = PQresultStatus(result); if (stat != type) { fprintf(stderr, "PQexec() returns %s where %s is expected.\n", PQresStatus(stat), PQresStatus(type)); fprintf(stderr, "Command was: %s.\n", cmd); PQclear(result); PQfinish(conn); exit(EXIT_FAILURE); } return result; } void vquery (PGconn *conn, const char *cmd, ExecStatusType type) { PQclear(xquery(conn, cmd, type)); } The code for working with the database cursor is included in cursor.c : // cursor.c /* Selection example */ #include <stdlib.h> #include <stdarg.h> #include "example.h" #include "pgsql/postgres.h" /* SQL-Commands are defined as Macro */ #define SQL_BEGIN "BEGIN" #define SQL_DECL "DECLARE tmpcursor BINARY CURSOR FOR select * from %s" #define SQL_FETCH "FETCH %d in tmpcursor" #define SQL_CLOSE "CLOSE tmpcursor" #define SQL_COMMIT "COMMIT" /* Function to get index of column with diagnostic-messages */ static int getidx (PGresult *res, const char *name) { int i = PQfnumber(res, name); if (i < 0) fprintf(stderr, "Column %s not defined.\n", name); return i; } /* Binary cursor example */ void cursor_example (PGconn *conn, const char *name, int rows) { PGresult *res; int i, n, k; int i_idx, i_val; int *p_idx; double *p_val; /* start a transaction block */ vquery(conn, SQL_BEGIN, PGRES_COMMAND_OK); vquery(conn, sql(SQL_DECL, name), PGRES_COMMAND_OK); /* get the column index after first request. */ res = xquery(conn, sql(SQL_FETCH, rows), PGRES_TUPLES_OK); n = PQntuples(res); i_idx = getidx(res, "idx"); i_val = getidx(res, "val"); /* if i_idx or i_val ist not defined, set n to 0 to omit data evaluation. */ if (i_idx < 0 i_val < 0) n = 0; /* get data as long as FETCH returns tuples */ for (k = 1; n > 0; k++) { printf("# group %d: %d rows\n", k, n); for (i = 0; i < n; i++) { p_idx = (int *) PQgetvalue(res, i, 0); p_val = (double *) PQgetvalue(res, i, 1); printf("%d\t%g\n", *p_idx, *p_val); } PQclear(res); res = xquery(conn, sql(SQL_FETCH, rows), PGRES_TUPLES_OK); n = PQntuples(res); } PQclear(res); /* close transaction */ vquery(conn, SQL_CLOSE, PGRES_COMMAND_OK); vquery(conn, SQL_COMMIT, PGRES_COMMAND_OK); } First, we include the header files and declare some preprocessor variables. getidx is used to get the index of a column. cursor_example shows how binary cursors can be used. To compile the sources and generate binaries out of it, we use a Makefile: [erich@duron ex3]$ make setup gcc -g -O2 -I/usr/include/postgresql -c main.c -o main.o gcc -g -O2 -I/usr/include/postgresql -c paste.c -o paste.o gcc -g -O2 -I/usr/include/postgresql -c info.c -o info.o gcc -g -O2 -I/usr/include/postgresql -c query.c -o query.o gcc -g -O2 -I/usr/include/postgresql -c sql.c -o sql.o gcc -g -O2 -I/usr/include/postgresql -c cursor.c -o cursor.o gcc -g -O2 -I/usr/include/postgresql -c sample.c -o sample.o gcc -g -O2 main.o paste.o info.o query.o sql.o cursor.o sample.o -o tprog -lpq if test -f stamp_db; then dropdb db ; fi createdb db CREATE DATABASE touch stamp_db Now we want to execute the program. We can use make run to start tprog : [erich@duron ex3]$ make run Running example tprog -c -n 8 db data tprog -n 5 db data # group 1: 5 rows 1 5.00302e-270 2 5.24281e-315 3 5.12022e-315 4 5.25281e-315 5 5.22851e-315 # group 2: 3 rows 6 5.00302e-270 7 5.23803e-315 8 5.20034e-315 echo "DROP TABLE data;" psql -q db We execute the script twice. First we create eight random records. The second time the script is executed, we want the result to be displayed. Environment VariablesLike many other programming interfaces, libpq can access environment variables. For example, PQconnectdb or PQsetdbLogin can access the following variables. Usually, the value of the environment variables is taken. If required, the value is not defined by the user:
Because libpq is the base for many other programming interfaces, the environment variables just listed are also supported by most other programming interfaces. A Reference of the C InterfaceAfter showing you some examples of how libpq can be used and after dealing with environment variables, we have included a complete reference of libpq . Database Connection FunctionsPostgreSQL provides some functions that are essential for connecting to a database. In this section, you will see how these functions can be used efficiently.
Query Execution FunctionsNow that you have seen how you can connect to the database, you will take a closer look at the functions needed for executing queries.
Asynchronous Query ProcessingQueries sent to the server using PQexec have some significant restrictions. In general, the application has to wait until the query has been processed successfully. In the meantime, the application could have done something useful, such as refreshing the user interface. Another problem is that the entire program is controlled by Pqexec , and there is no way to quit an ongoing query. To solve some of these restrictions, PostgreSQL supports some additional functions:
|
