【转】PostgreSQL 数据库C语言代码实现【数据库连接，创建表，以及增删改查】

首先YY下， postgresql 数据库是一开源数据库，使用的人貌似不多，差了好半天的资料也没找到合适的，最后别人给了个链接，

是英文版的，写的很详细！文章如下：

In this article,I’ll show you an example on how to integrate the Postgresql C++ library into your C++ project solution. The Postgresql version that i am using for this example is Postgresql for Windows version 8.4.4. In case you don’t have Postgresql installed on your machine,you can download it atpostgresql-8.4.4-1-windows.exe. You may need to provide some details first before start to download. Make sure that you have installed Postgresql successfully and please REMEMBER your password to login to Postgresql through its command console windows in later. Let’s start to create a database for this example now. Run thepsql in the command windows and create a new database named “testdb” as the following screen shot.

Once the database has been created,we are now ready for the coding part. Let’s create a Win32 console application now (I am using visual studio 2005 for this example). The first task we need to do right now is to include the Postgresql C++ interface header file and lib to our project. You can do that by right click your project solution and click properties.Note 1: You might need to change the following file path accordingly. [C/C++ -> General -> Additional Include Directories]C:\Program Files\Postgresql\8.4\include[Linker -> General -> Additional Library Directories]C:\Program Files\Postgresql\8.4\lib[Linker -> Input -> Additional Dependencies]libpq.libNote 2:At the end of this example,you will need to copy your project output (EXE) to ..\Postgresql\8.4\bin. Alternately,you can copy all DLLs located at ..\Postgresql\8.4\bin\ to you project output folder.

PS: 我在项目里面添加的库文件只有一个，一个是 libqp.lib,另外一个是对应的 libpq.dll

We will start the coding part in our cpp file now. Make sure that you include the following header accordingly. libpq-fe.h must be included.

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

We will start the coding part in our cpp file now. Make sure that you include the following header accordingly. libpq-fe.h must be included.

/* Close connection to database */
 void CloseConn(PGconn *conn)
 {
     PQfinish(conn);
   getchar();
     exit(1);
 }

Next,we create a function named ConnectDB(). This function will establish a connection to Postgresql server. Please be sure that you provide the correct parameters in Postgresql(). Prior to this example,i have setup a database called testdb with user password of test123 during the installation. You might need to modify it accordingly in order to make sure the compilation success.

/* Establish connection to database */
 PGconn *ConnectDB()
 {
   PGconn *conn = NULL;
 
  // Make a connection to the database
   conn = PQconnectdb("user=postgres password=test123 dbname=testdb hostaddr=127.0.0.1 port=5432");
 
  // Check to see that the backend connection was successfully made 
    if (PQstatus(conn) != CONNECTION_OK)
     {
         printf("Connection to database Failed");
         CloseConn(conn);
     }
 
  printf("Connection to database - OK\n");
 
  return conn;
 }

Next,we create a function named CreateEmployeeTable(). This function will create an employee table in ourtestdb database.

/* Create employee table */
 void CreateEmployeeTable(PGconn *conn)
 {
   // Execute with sql statement
     PGresult *res = PQexec(conn,"CREATE TABLE employee (Fname char(30),Lname char(30))");
     
  if (PQresultStatus(res) != PGRES_COMMAND_OK)
     {
         printf("Create employee table Failed");
         PQclear(res);
         CloseConn(conn);
     }
 
  printf("Create employee table - OK\n");
 
  // Clear result
   PQclear(res);
 }

这里我想补充一下，创建表也可以在 sql shell (psql) 里面创建：

首先连接数据库，然后执行 create table 的 sql 语句，注意各个字段的数据类型，注意语句最后用 “ ; ” 分号结尾。

Next,we create a function named InsertEmployeeRec(). This function will take 2 parameters,fname and lname in char pointer type,to form a sql statement. It then will be executed in order to store the record into the employee table.

/* Append sql statement and insert record into employee table */
 void InsertEmployeeRec(PGconn *conn,char * fname,char * lname)
 {
   // Append the sql statment
   std::string ssql;
   ssql.append("INSERT INTO employee VALUES ('");
   ssql.append(fname);
   ssql.append("','");
   ssql.append(lname);
   ssql.append("')");
   
  // Execute with sql statement
   PGresult *res = PQexec(conn,ssql.c_str());
 
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
     {
         printf("Insert employee record Failed");
         PQclear(res);
         CloseConn(conn);
     }
 
  printf("Insert employee record - OK\n");
 
  // Clear result
   PQclear(res);
 }

Next,we create a function named FetchEmployeeRec(). This function will fetch all the record in employee table and display it on the console windows.

/* Fetch employee record and display it on screen */
 void FetchEmployeeRec(PGconn *conn)
 {
   // Will hold the number of field in employee table
   int nFields;
 
  // Start a transaction block
   PGresult *res  = PQexec(conn,"BEGIN");
 
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
     {
         printf("BEGIN command Failed");
         PQclear(res);
         CloseConn(conn);
     }
 
   // Clear result
     PQclear(res);
 
    // Fetch rows from employee table
     res = PQexec(conn,"DECLARE emprec CURSOR FOR select * from employee");
     if (PQresultStatus(res) != PGRES_COMMAND_OK)
     {
         printf("DECLARE CURSOR Failed");
         PQclear(res);
         CloseConn(conn);
     }
 
  // Clear result
     PQclear(res);
 
    res = PQexec(conn,"FETCH ALL in emprec");
 
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
     {
         printf("FETCH ALL Failed");
         PQclear(res);
         CloseConn(conn);
     }
 
    // Get the field name
     nFields = PQnfields(res);
 
  // Prepare the header with employee table field name
   printf("\nFetch employee record:");
   printf("\n********************************************************************\n");
     for (int i = 0; i < nFields; i++)
         printf("%-30s",PQfname(res,i));
     printf("\n********************************************************************\n");
 
    // Next,print out the employee record for each row
     for (int i = 0; i < PQntuples(res); i++)
     {
         for (int j = 0; j < nFields; j++)
             printf("%-30s",PQgetvalue(res,i,j));
         printf("\n");
     }
   
    PQclear(res);
 
    // Close the emprec
     res = PQexec(conn,"CLOSE emprec");
     PQclear(res);
 
    // End the transaction
     res = PQexec(conn,"END");
 
  // Clear result
     PQclear(res);
 }

Next,we create a function named RemoveAllEmployeeRec(). This function will remove all record in employee table.

/* Erase all record in employee table */
 void RemoveAllEmployeeRec(PGconn *conn)
 {
   // Execute with sql statement
     PGresult *res = PQexec(conn,"DELETE FROM employee");
 
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
     {
         printf("Delete employees record Failed.");
         PQclear(res);
         CloseConn(conn);
     }
 
  printf("\nDelete employees record - OK\n");
 
  // Clear result
   PQclear(res);
 }

Next,we create a function named DropEmployeeTable(). This function will drop or remove the employee from thetestdb database.

/* Drop employee table from the database*/
 void DropEmployeeTable(PGconn *conn)
 {
   // Execute with sql statement
     PGresult *res = PQexec(conn,"DROP TABLE employee");
 
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
     {
         printf("Drop employee table Failed.");
         PQclear(res);
         CloseConn(conn);
     }
 
  printf("Drop employee table - OK\n");
 
  // Clear result
   PQclear(res);
 }

Finally,we update the main entry point function so that it call all the functions that we have created to demonstrate what we intend to show in this example.Finally,we update the main entry point function so that it call all the functions that we have created to demonstrate what we intend to show in this example.Next,i have setup a database called testdb with user password of test123 during the installation. You might need to modify it accordingly in order to make sure the compilation success.Finally,we update the main entry point function so that it call all the functions that we have created to demonstrate what we intend to show in this example.

int _tmain(int argc,_TCHAR* argv[])
 {
   PGconn     *conn = NULL;
 
  conn = ConnectDB();
   CreateEmployeeTable(conn);
   InsertEmployeeRec(conn,"Mario","Hewardt");
   InsertEmployeeRec(conn,"Daniel","Pravat");
   FetchEmployeeRec(conn);
 
  printf("\nPress ENTER to remove all records & table.....\n");
   getchar();
 
  RemoveAllEmployeeRec(conn);
   DropEmployeeTable(conn);
 
  CloseConn(conn);
 
  return 0;
 }

then try to compile and run this application now,you should see following screen shot:

At this point,if you running SELECT statment from the Postgresql command console,you will see the same data being display on you C++ Win32 console Window.

创建表的时候，对于主键 primary key，往往有很多时候我们希望他做到自增，主键数据对于我们来说不是很关键的数据，但又不能少，比如昨天添加了100条数据，id 最大为100，那么今天重启电脑了后，继续插入，那么id自动从 101 开始，逐个增大。
记得在sqlite 里面好像用一个 autoincrement 来对主键进行说明，但是postgresql 里面没有这么了，而是用一个 serial 来实现了这个功能，

CREATE TABLE tablename (
colname SERIAL
);

等价于声明下面几句话：

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename(
colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL
);

因此，我们就创建了一个整数字段并且把它的缺省数值安排为从一个序列发生器取值。应用了一个 NOT NULL 约束以确保空值不会被明确地插入。在大多数情况下你可能还希望附加一个 UNIQUE 或者 PRIMARY KEY 约束避免意外地插入重复的数值，但这个不是自动发生的。

注意: 在 Postgresql 7.3 以前，serial隐含 UNIQUE。但现在不再如此。如果你希望一个序列字段有一个唯一约束或者一个主键，那么你现在必须声明，就像其它数据类型一样。

要使用 serial 字段插入序列的下一个数值到表中，主要是要注意 serial 应该赋予缺省值。我们可以通过在 INSERT 语句中把该字段排除在字段列表之外来实现，也可以通过使用 DEFAULT 关键字来实现。

比如：

>> create table pk( id serial primary key,lastname text,firstname text);

>> CREATE TABLE pk

>> insert into pk values( 'a','Hello','mary') ; // 错误， id 为序列不可以为字符，但是好像可以用小数吧。

>> insert into pk values( 1,'AAA',' BBB' ); // 插入成功

>> insert into pk values( default,'BBBB','BBBB' ); // 插入成功，default 关键字会让id 从 2 开始增加，同样的方法在插入一条，则 id 从3 开始。

>> insert into pk values( 2,' BBBBB‘,'NNNN’ ); id 为 2 的一行已经存在（上面那条插入 default 时的），违反了 not null 限制。

OK,thats all,thanks for masters' sharing ！

【转】PostgreSQL 数据库C语言代码实现【数据库连接，创建表， 以及增删改查】

相关文章

【转】PostgreSQL 数据库C语言代码实现【数据库连接，创建表，以及增删改查】