use c language to connect postgreSQL


There is a small example copied from postgreSQL.org

[codesyntax lang=”c” tab_width=”4” strict=”yes”]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
/*
* libpq sample program
*/
#include <stdio.h>
#include <stdlib.h>
#include "libpq-fe.h" /* libpq header file */
int main()
{
char state_code[3]; /* holds user state code */
char query_string[256]; /* holds constructed SQL query */
PGconn *conn; /* holds database connection */
PGresult *res; /* holds query result */
int i;
conn = PQconnectdb("dbname=test"); /* connect to the database */
if (PQstatus(conn) == CONNECTION_BAD) /* did the connection fail? */
{
fprintf(stderr, "Connection to database failed.\n");
fprintf(stderr, "%s", PQerrorMessage(conn));
exit(1);
}
printf("Enter a state code: "); /* prompt user for a state code */
scanf("%2s", state_code);
sprintf(query_string, /* create an SQL query string */
"SELECT name \
FROM statename \
WHERE code = '%s'", state_code);
res = PQexec(conn, query_string); /* send the query */
if (PQresultStatus(res) != PGRES_TUPLES_OK) /* did the query fail? */
{
fprintf(stderr, "SELECT query failed.\n");
PQclear(res);
PQfinish(conn);
exit(1);
}
for (i = 0; i < PQntuples(res); i++) /* loop through all rows returned */
printf("%s\n", PQgetvalue(res, i, 0)); /* print the value returned */
PQclear(res); /* free result */
PQfinish(conn); /* disconnect from the database */
return 0;
}

above is how to connect localhost database. If you want to connect with remote postgreSQL server. You will following those rules(copied from vivek):

Step # 1: Login over ssh if server is outside your IDC

1
$ ssh user@remote.pgsql.server.com

Step # 2: Enable client authentication

Once connected, you need edit the PostgreSQL configuration file, edit the PostgreSQL configuration file /var/lib/pgsql/data/pg_hba.conf (or /etc/postgresql/8.2/main/pg_hba.conf for latest 8.2 version) using a text editor such as vi.

Login as postgres user using su / sudo command, enter:

1
$ su - postgres

Edit the file:
1
$ vi /var/lib/pgsql/data/pg_hba.conf

OR
1
$ vi /etc/postgresql/8.2/main/pg_hba.conf

Append the following configuration lines to give access to 10.10.29.0/24 network:
1
host all all 10.10.29.0/24 trust

Save and close the file. Make sure you replace 10.10.29.0/24 with actual network IP address range of the clients system in your own network.

Step # 2: Enable networking for PostgreSQL

You need to enable TCP / IP networking. Use either step #3 or #3a as per your PostgreSQL database server version.

Step # 3: Allow TCP/IP socket

If you are using PostgreSQL version 8.x or newer use the following instructions or skip to Step # 3a for older version (7.x or older).

You need to open PostgreSQL configuration file /var/lib/pgsql/data/postgresql.conf or /etc/postgresql/8.2/main/postgresql.conf.

1
# vi /etc/postgresql/8.2/main/postgresql.conf

OR
1
# vi /var/lib/pgsql/data/postgresql.conf

Find configuration line that read as follows:
1
listen_addresses='localhost'

Next set IP address(es) to listen on; you can use comma-separated list of addresses; defaults to ‘localhost’, and ‘*’ is all ip address:
1
listen_addresses='*'

Or just bind to 202.54.1.2 and 202.54.1.3 IP address
1
listen_addresses='202.54.1.2 202.54.1.3'

Save and close the file. Skip to step # 4.

Step #3a - Information for old version 7.x or older

Following configuration only required for PostgreSQL version 7.x or older. Open config file, enter:

1
# vi /var/lib/pgsql/data/postgresql.conf

Bind and open TCP/IP port by setting tcpip_socket to true. Set / modify tcpip_socket to true:
1
tcpip_socket = true

Save and close the file.

Step # 4: Restart PostgreSQL Server

Type the following command:

1
# /etc/init.d/postgresql restart

Step # 5: Iptables firewall rules

Make sure iptables is not blocking communication, open port 5432 (append rules to your iptables scripts or file /etc/sysconfig/iptables):

1
2
3
4
iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d 10.10.29.50 --dport 5432\
 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s 10.10.29.50 --sport 5432 -d 0/0 --dport \
1024:65535 -m state --state ESTABLISHED -j ACCEPT

Restart firewall:
1
# /etc/init.d/iptables restart

Step # 6: Test your setup

Use psql command from client system. Connect to remote server using IP address 10.10.29.50 and login using vivek username and sales database, enter:

1
$ psql -h 10.10.29.50 -U vivek -d sales

Step #7: Change your code

1
2
const char * connstr = "host='10.10.29.50' dbname='my_database' user='postgres' password='secret'"
conn = PQconnectdb(connstr); /* connect to the database */