Access PostgreSQL: C Language Interface

C Language Interface

C is a common language for connecting to databases. Many databases are written in C. This language is efficient and flexible, so if you want to write a client interface, whether it only contains the console or GUI, and do not want to use a browser, C language may be the best choice.

The name of the database used by the C language and ipvs is libpq, which is bound with the PostgreSQL source code tree. If the database system is installed using a binary file instead of the source code, libpq can be installed independently, but remember to use the option-dev package (or-devel, depends on the version of the Linux system ). On Debian and its distribution version, run the command # aptitude install libpq-Dev to install libpq. In a RedHat-based system, such as centos, you can find libpq in the postgresqlxx-libs package, where XX is the major and minor version number. Therefore, connect to PostgreSQL
9.1 database, if you use fedora16, you need to install the postgresql91-lib or postgresql-devel. Most RHEL/centos users use the Postgres library, which may lead to different program names. In addition, fedora users can use more latest packages, therefore, a third-party library is required only when an old version is used. In short, any system that supports PostgreSQL has its libpq available.

In addition to the C language, libpq is also an API engine for C ++, Perl, and TCL. It provides basic functions to connect, query, and modify databases. Many common functions contain the prefix "PQ", such as pqconnectdb or pqerrormessage. For more examples, see the PostgreSQL documentation or SRC/test/examples. In the C program file, include the libpq-fe.h header file and add the corresponding link tag-lpq at compilation.

After installation and setup, the first thing to do is to connect to the database. The pqconnectdb () function contains a char * conninfo format parameter, such as dbname = [database_name]. Of course, it can also be other content, as long as the format is correct, the most common keywords are host, hostaddr (numeric format to avoid useless DNS queries), port, user, password, and sslmode. If no parameter is used, the default option is used. Assume that the server address is 192.168.0.101, the username is ipvs, and the database name is testdb1. The code for trying to connect to the database is as follows:

#include <stdio.h>#include <libpq-fe.h>

Int main (INT argc, char * argv [])
{
Const char * conninfo;
Pgconn * conn;
If (argc> 1)
Conninfo = argcv [1];
Else
{
Printf ("not enough arguments, exiting ...");
Return 1;
}
Conn = pqconnectdb (conninfo );
/* Check to see how I did */
If (pqstatus (conn) = connection_ OK)
Printf ("connection succeeded. \ n ");
Else
{
Printf ("connection failed. \ n ");
}
}

How to compile PostgreSQL C program in Linux

In Linux, the compilation problem occurs when the C program uses PostgreSQL 8.4 to connect to the database.

1.

Problem: In function 'main ':

'Pgconn' undeclared (first use in this function)

'Pgresresult' undeclared (first use in this function)

'Connection _ bad' undeclared (first use in this function)

'Pgres _ command_ OK 'undeclared (first use in this function)

'Pgres _ tuples_ OK 'undeclared (first use in this function)

This problem occurs for missing # include <libpq-fe.h>

2.

Problem: libpq-fe.h: no such file or directory
Solution: The system does not find the libpq-fe.h and you need to specify its Directory
Method 1: gcc-c-I/usr/local/pgsql/include test. c
Method 2: cppflags + =-I/usr/local/pgsql/include

3. Compile. C to. O, but the following problem occurs during the link.

In function 'main ':

Undefined reference to 'pqsetdblogin'

Undefined reference to 'pqstatus'

Undefined reference to 'pqerrormessage'

Solution: this is because the library file required for the link is missing. You need to manually specify its Directory

 

gcc -o test test1.o test2.o -L/usr/local/pgsql/lib -lpq

 

 

Compiled successfully.

Save the code as testlibpq and compile it. The compiled program usage is as follows:

$ Testlibpg "hostaddr = 192.168.0.101 user = s dbname = testdb1"

If no error occurs, the "connection succeeded." prompt will be displayed on the screen, indicating that the database is successfully connected, but the above is of no practical use. So what is the actual use? query the data, but here we will first describe how to disconnect, that is, call pqfinish. This function has only one parameter pgconn * conn and returns void.

The pqexec function executes the query. The parameters are pgconn * conn and const char * command, and a pgresult type object is returned. In the following example, declare a pgresult variable and send a command to the server. You can write code to check whether a connection is established or not and handle errors.

Pgresult * res;


Res = pqexec (Conn, "select * From mydatabase ");


Pqclear (RES );

Obviously, this Code cannot be compiled, just to show readers the functions provided by the libpq library, rather than directly providing available code. However, in this Code, Res contains the query results, which can be parsed by the reader at will. Pqresultstatus can be used to query the command status. This function returns pgres_command_ OK or pgres_fatal_error .. You can find a comprehensive list of exec functions on the PostgreSQL project page.

The following describes some useful functions. For example, the pqntuples function uses the given res as a parameter and returns the number of columns in the table as an integer. If the query status is pgres_tuples_ OK, it takes the pgresult object as the parameter and returns an integer value. The pqnfields function provides the number of columns in each row. The p;name function returns the name of the column associated with a number, while the p;number function returns the opposite. To obtain the value of a unit, you must pass the pgresult and the column number and row number of the Unit to the pqgetvalue function.

It can be seen that these functions are relatively simple, but pqexec cannot process multiple SQL commands at the same time, because this function can only return one structure. If there are multiple commands, only the results of the last command can be returned. Another disadvantage is that when executing a command, pqexec waits until the command is returned. Therefore, the reader should be careful when using this command in case of blocking execution. If these deficiencies affect users' usage, you can use other functions, such as pqsendquery and pqgetresult. You can use these functions for asynchronous query processing.