帐前卒专栏

Without software, we are nothing.

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 */