postgresql libpq C语言网络库接口操作数据库例子

Postgre SQL 前端之家
前端之家收集整理的这篇文章主要介绍了postgresql libpq C语言网络库接口操作数据库例子，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

关于libpq各个函数接口的说明，参考：http://www.postgres.cn/docs/9.3/libpq.html

例子1：

  1. /*
  2.  * testlibpq.c
  3.  *
  4.  *      Test the C version of libpq,the Postgresql frontend library.
  5.  */
  6. #include <stdio.h>
  7. #include <stdlib.h>
  8. #include <libpq-fe.h>
  9.  
  10. static void
  11. exit_nicely(PGconn *conn)
  12. {
  13.     PQfinish(conn);
  14.     exit(1);
  15. }
  16.  
  17. int
  18. main(int argc,char **argv)
  19. {
  20.     const char *conninfo;
  21.     PGconn     *conn;
  22.     PGresult   *res;
  23.     int         nFields;
  24.     int         i,j;
  25.  
  26.     /*
  27.      * If the user supplies a parameter on the command line,use it as the
  28.      * conninfo string; otherwise default to setting dbname=postgres and using
  29.      * environment variables or defaults for all other connection parameters.
  30.      */
  31.     if (argc > 1)
  32.         conninfo = argv[1];
  33.     else
  34.         conninfo = "dbname = postgres";
  35.  
  36.     /* Make a connection to the database */
  37.     conn = PQconnectdb(conninfo);
  38.  
  39.     /* Check to see that the backend connection was successfully made */
  40.     if (PQstatus(conn) != CONNECTION_OK)
  41.     {
  42.         fprintf(stderr,"Connection to database Failed: %s",PQerrorMessage(conn));
  43.         exit_nicely(conn);
  44.     }
  45.  
  46.     /*
  47.      * Our test case here involves using a cursor,for which we must be inside
  48.      * a transaction block.  We could do the whole thing with a single
  49.      * PQexec() of "select * from pg_database",but that's too trivial to make
  50.      * a good example.
  51.      */
  52.  
  53.     /* Start a transaction block */
  54.     res = PQexec(conn,"BEGIN");
  55.     if (PQresultStatus(res) != PGRES_COMMAND_OK)
  56.     {
  57.         fprintf(stderr,"BEGIN command Failed: %s",PQerrorMessage(conn));
  58.         PQclear(res);
  59.         exit_nicely(conn);
  60.     }
  61.  
  62.     /*
  63.      * Should PQclear PGresult whenever it is no longer needed to avoid memory
  64.      * leaks
  65.      */
  66.     PQclear(res);
  67.  
  68.     /*
  69.      * Fetch rows from pg_database,the system catalog of databases
  70.      */
  71.     res = PQexec(conn,"DECLARE myportal CURSOR FOR select * from pg_database");
  72.     if (PQresultStatus(res) != PGRES_COMMAND_OK)
  73.     {
  74.         fprintf(stderr,"DECLARE CURSOR Failed: %s",PQerrorMessage(conn));
  75.         PQclear(res);
  76.         exit_nicely(conn);
  77.     }
  78.     PQclear(res);
  79.  
  80.     res = PQexec(conn,"FETCH ALL in myportal");
  81.     if (PQresultStatus(res) != PGRES_TUPLES_OK)
  82.     {
  83.         fprintf(stderr,"FETCH ALL Failed: %s",PQerrorMessage(conn));
  84.         PQclear(res);
  85.         exit_nicely(conn);
  86.     }
  87.  
  88.     /* first,print out the attribute names */
  89.     nFields = PQnfields(res);
  90.     for (i = 0; i < nFields; i++)
  91.         printf("%-15s",PQfname(res,i));
  92.     printf("\n\n");
  93.  
  94.     /* next,print out the rows */
  95.     for (i = 0; i < PQntuples(res); i++)
  96.     {
  97.         for (j = 0; j < nFields; j++)
  98.             printf("%-15s",PQgetvalue(res,i,j));
  99.         printf("\n");
  100.     }
  101.  
  102.     PQclear(res);
  103.  
  104.     /* close the portal ... we don't bother to check for errors ... */
  105.     res = PQexec(conn,"CLOSE myportal");
  106.     PQclear(res);
  107.  
  108.     /* end the transaction */
  109.     res = PQexec(conn,"END");
  110.     PQclear(res);
  111.  
  112.     /* close the connection to the database and cleanup */
  113.     PQfinish(conn);
  114.  
  115.     return 0;
  116. }

例子2： 
  1. /*
  2.  * testlibpq2.c
  3.  *      Test of the asynchronous notification interface
  4.  *
  5.  * Start this program,then from psql in another window do
  6.  *   NOTIFY TBL2;
  7.  * Repeat four times to get this program to exit.
  8.  *
  9.  * Or,if you want to get fancy,try this:
  10.  * populate a database with the following commands
  11.  * (provided in src/test/examples/testlibpq2.sql):
  12.  *
  13.  *   CREATE TABLE TBL1 (i int4);
  14.  *
  15.  *   CREATE TABLE TBL2 (i int4);
  16.  *
  17.  *   CREATE RULE r1 AS ON INSERT TO TBL1 DO
  18.  *     (INSERT INTO TBL2 VALUES (new.i); NOTIFY TBL2);
  19.  *
  20.  * and do this four times:
  21.  *
  22.  *   INSERT INTO TBL1 VALUES (10);
  23.  */
  24. #include <stdio.h>
  25. #include <stdlib.h>
  26. #include <string.h>
  27. #include <errno.h>
  28. #include <sys/time.h>
  29. #include <libpq-fe.h>
  30.  
  31. static void
  32. exit_nicely(PGconn *conn)
  33. {
  34.     PQfinish(conn);
  35.     exit(1);
  36. }
  37.  
  38. int
  39. main(int argc,char **argv)
  40. {
  41.     const char *conninfo;
  42.     PGconn     *conn;
  43.     PGresult   *res;
  44.     PGnotify   *notify;
  45.     int         nnotifies;
  46.  
  47.     /*
  48.      * If the user supplies a parameter on the command line,PQerrorMessage(conn));
  49.         exit_nicely(conn);
  50.     }
  51.  
  52.     /*
  53.      * Issue LISTEN command to enable notifications from the rule's NOTIFY.
  54.      */
  55.     res = PQexec(conn,"LISTEN TBL2");
  56.     if (PQresultStatus(res) != PGRES_COMMAND_OK)
  57.     {
  58.         fprintf(stderr,"LISTEN command Failed: %s",PQerrorMessage(conn));
  59.         PQclear(res);
  60.         exit_nicely(conn);
  61.     }
  62.  
  63.     /*
  64.      * should PQclear PGresult whenever it is no longer needed to avoid memory
  65.      * leaks
  66.      */
  67.     PQclear(res);
  68.  
  69.     /* Quit after four notifies are received. */
  70.     nnotifies = 0;
  71.     while (nnotifies < 4)
  72.     {
  73.         /*
  74.          * Sleep until something happens on the connection.  We use select(2)
  75.          * to wait for input,but you could also use poll() or similar
  76.          * facilities.
  77.          */
  78.         int         sock;
  79.         fd_set      input_mask;
  80.  
  81.         sock = PQsocket(conn);
  82.  
  83.         if (sock < 0)
  84.             break;              /* shouldn't happen */
  85.  
  86.         FD_ZERO(&input_mask);
  87.         FD_SET(sock,&input_mask);
  88.  
  89.         if (select(sock + 1,&input_mask,NULL,NULL) < 0)
  90.         {
  91.             fprintf(stderr,"select() Failed: %s\n",strerror(errno));
  92.             exit_nicely(conn);
  93.         }
  94.  
  95.         /* Now check for input */
  96.         PQconsumeInput(conn);
  97.         while ((notify = PQnotifies(conn)) != NULL)
  98.         {
  99.             fprintf(stderr,"ASYNC NOTIFY of '%s' received from backend PID %d\n",notify->relname,notify->be_pid);
  100.             PQfreemem(notify);
  101.             nnotifies++;
  102.         }
  103.     }
  104.  
  105.     fprintf(stderr,"Done.\n");
  106.  
  107.     /* close the connection to the database and cleanup */
  108.     PQfinish(conn);
  109.  
  110.     return 0;
  111. }


例子3：

  1. /*
  2.  * testlibpq3.c
  3.  *      Test out-of-line parameters and binary I/O.
  4.  *
  5.  * Before running this,populate a database with the following commands
  6.  * (provided in src/test/examples/testlibpq3.sql):
  7.  *
  8.  * CREATE TABLE test1 (i int4,t text,b bytea);
  9.  *
  10.  * INSERT INTO test1 values (1,'joe''s place','\\000\\001\\002\\003\\004');
  11.  * INSERT INTO test1 values (2,'ho there','\\004\\003\\002\\001\\000');
  12.  *
  13.  * The expected output is:
  14.  *
  15.  * tuple 0: got
  16.  *  i = (4 bytes) 1
  17.  *  t = (11 bytes) 'joe's place'
  18.  *  b = (5 bytes) \000\001\002\003\004
  19.  *
  20.  * tuple 0: got
  21.  *  i = (4 bytes) 2
  22.  *  t = (8 bytes) 'ho there'
  23.  *  b = (5 bytes) \004\003\002\001\000
  24.  */
  25. #include <stdio.h>
  26. #include <stdlib.h>
  27. #include <string.h>
  28. #include <sys/types.h>
  29. #include <libpq-fe.h>
  30.  
  31. /* for ntohl/htonl */
  32. #include <netinet/in.h>
  33. #include <arpa/inet.h>
  34.  
  35.  
  36. static void
  37. exit_nicely(PGconn *conn)
  38. {
  39.     PQfinish(conn);
  40.     exit(1);
  41. }
  42.  
  43. /*
  44.  * This function prints a query result that is a binary-format fetch from
  45.  * a table defined as in the comment above.  We split it out because the
  46.  * main() function uses it twice.
  47.  */
  48. static void
  49. show_binary_results(PGresult *res)
  50. {
  51.     int         i,j;
  52.     int         i_fnum,t_fnum,b_fnum;
  53.  
  54.     /* Use PQfnumber to avoid assumptions about field order in result */
  55.     i_fnum = PQfnumber(res,"i");
  56.     t_fnum = PQfnumber(res,"t");
  57.     b_fnum = PQfnumber(res,"b");
  58.  
  59.     for (i = 0; i < PQntuples(res); i++)
  60.     {
  61.         char       *iptr;
  62.         char       *tptr;
  63.         char       *bptr;
  64.         int         blen;
  65.         int         ival;
  66.  
  67.         /* Get the field values (we ignore possibility they are null!) */
  68.         iptr = PQgetvalue(res,i_fnum);
  69.         tptr = PQgetvalue(res,t_fnum);
  70.         bptr = PQgetvalue(res,b_fnum);
  71.  
  72.         /*
  73.          * The binary representation of INT4 is in network byte order,which
  74.          * we'd better coerce to the local byte order.
  75.          */
  76.         ival = ntohl(*((uint32_t *) iptr));
  77.  
  78.         /*
  79.          * The binary representation of TEXT is,well,text,and since libpq
  80.          * was nice enough to append a zero byte to it,it'll work just fine
  81.          * as a C string.
  82.          *
  83.          * The binary representation of BYTEA is a bunch of bytes,which could
  84.          * include embedded nulls so we have to pay attention to field length.
  85.          */
  86.         blen = PQgetlength(res,b_fnum);
  87.  
  88.         printf("tuple %d: got\n",i);
  89.         printf(" i = (%d bytes) %d\n",PQgetlength(res,i_fnum),ival);
  90.         printf(" t = (%d bytes) '%s'\n",t_fnum),tptr);
  91.         printf(" b = (%d bytes) ",blen);
  92.         for (j = 0; j < blen; j++)
  93.             printf("\\%03o",bptr[j]);
  94.         printf("\n\n");
  95.     }
  96. }
  97.  
  98. int
  99. main(int argc,char **argv)
  100. {
  101.     const char *conninfo;
  102.     PGconn     *conn;
  103.     PGresult   *res;
  104.     const char *paramValues[1];
  105.     int         paramLengths[1];
  106.     int         paramFormats[1];
  107.     uint32_t    binaryIntVal;
  108.  
  109.     /*
  110.      * If the user supplies a parameter on the command line,PQerrorMessage(conn));
  111.         exit_nicely(conn);
  112.     }
  113.  
  114.     /*
  115.      * The point of this program is to illustrate use of PQexecParams() with
  116.      * out-of-line parameters,as well as binary transmission of data.
  117.      *
  118.      * This first example transmits the parameters as text,but receives the
  119.      * results in binary format.  By using out-of-line parameters we can
  120.      * avoid a lot of tedious mucking about with quoting and escaping,even
  121.      * though the data is text.  Notice how we don't have to do anything
  122.      * special with the quote mark in the parameter value.
  123.      */
  124.  
  125.     /* Here is our out-of-line parameter value */
  126.     paramValues[0] = "joe's place";
  127.  
  128.     res = PQexecParams(conn,"SELECT * FROM test1 WHERE t = $1",1,/* one param */
  129.                        NULL,/* let the backend deduce param type */
  130.                        paramValues,/* don't need param lengths since text */
  131.                        NULL,/* default to all text params */
  132.                        1);      /* ask for binary results */
  133.  
  134.     if (PQresultStatus(res) != PGRES_TUPLES_OK)
  135.     {
  136.         fprintf(stderr,"SELECT Failed: %s",PQerrorMessage(conn));
  137.         PQclear(res);
  138.         exit_nicely(conn);
  139.     }
  140.  
  141.     show_binary_results(res);
  142.  
  143.     PQclear(res);
  144.  
  145.     /*
  146.      * In this second example we transmit an integer parameter in binary
  147.      * form,and again retrieve the results in binary form.
  148.      *
  149.      * Although we tell PQexecParams we are letting the backend deduce
  150.      * parameter type,we really force the decision by casting the parameter
  151.      * symbol in the query text.  This is a good safety measure when sending
  152.      * binary parameters.
  153.      */
  154.  
  155.     /* Convert integer value "2" to network byte order */
  156.     binaryIntVal = htonl((uint32_t) 2);
  157.  
  158.     /* Set up parameter arrays for PQexecParams */
  159.     paramValues[0] = (char *) &binaryIntVal;
  160.     paramLengths[0] = sizeof(binaryIntVal);
  161.     paramFormats[0] = 1;        /* binary */
  162.  
  163.     res = PQexecParams(conn,"SELECT * FROM test1 WHERE i = $1::int4",paramLengths,paramFormats,1);      /* ask for binary results */
  164.  
  165.     if (PQresultStatus(res) != PGRES_TUPLES_OK)
  166.     {
  167.         fprintf(stderr,PQerrorMessage(conn));
  168.         PQclear(res);
  169.         exit_nicely(conn);
  170.     }
  171.  
  172.     show_binary_results(res);
  173.  
  174.     PQclear(res);
  175.  
  176.     /* close the connection to the database and cleanup */
  177.     PQfinish(conn);
  178.  
  179.     return 0;
  180. }

猜你在找的Postgre SQL相关文章

PosegreSQL基础回顾(第 4 章 SQL语法)
来源：http://www.postgres.cn/docs/11/ 4.1.1.&#160;标识符和关键词 SQL标识符和关键...
PosegreSQL基础回顾(第 8 章 数据类型)
来源：http://www.postgres.cn/docs/11/ 8.1.&#160;数字类型 数字类型由2、4或8字节的...
PosegreSQL基础回顾(第 5 章 数据定义)
来源：http://www.postgres.cn/docs/11/ 5.1.&#160;表基础 SQL并不保证表中行的顺序。...
PosegreSQL基础回顾(第 6 章 数据操纵、第 7 章 查询)
来源：http://www.postgres.cn/docs/11/ 6.4.&#160;从修改的行中返回数据 有时在修改行...
PosegreSQL基础回顾(第 13 章 并发控制)
来源：http://www.postgres.cn/docs/11/ 13.2.1.&#160;读已提交隔离级别 读已提交是Po...
PostgreSQL安装和使用
青岛OJ系统用的关系型数据库是PostgreSQL,为此对PostgreSQL大致了解下。 今天的主要话题围...
PostgreSQL整体架构
 
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
为什么选择PostgreSQL而不是MySQL
David Bolton是一名独立开发者，他使用PostgreSQL和MySQL都已有超过十年的时间。近日，他撰...
专访唐成：从小工到专家，PostgreSQL的修炼之道
唐成，拥有十几年数据库、操作系统、存储领域的工作经验。目前任沃趣科技首席数据库架构师...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问