windows vs2022 c/c++连接操作postgresql数据库 - 增删改查

最新推荐文章于 2024-09-03 14:33:08 发布

qiuzen

最新推荐文章于 2024-09-03 14:33:08 发布

阅读量1k

点赞数

分类专栏： C语言 windows visual studio 文章标签： postgresql c语言数据库

本文链接：https://blog.csdn.net/qiuzen/article/details/127590248

版权

C语言同时被 3 个专栏收录

10 篇文章 0 订阅

订阅专栏

visual studio

7 篇文章 0 订阅

订阅专栏

windows

6 篇文章 0 订阅

订阅专栏

关于环境的配置请参考上一篇文章，本文仅包含示例代码

namespace idealand
{
	typedef int (*PGResultHandler)(PGresult*);

  class DB
  {
  public:
		const char* conn_string = "user=** password=** host=** port=** dbname=** connect_timeout=5";
		const char* set_secure = "SELECT pg_catalog.set_config('search_path', 'public', false)";
		PGconn* conn=0;
		PGresult* res=0;


		void close()
		{
			if(conn) PQfinish(conn); 
		}

		void ouput()
		{
			int nFields, i, j;

			// first, print out the attribute names 
			nFields = PQnfields(res);	for (i = 0; i < nFields; i++) 	printf("%-15s", PQfname(res, i));
			printf("\n");

			// next, print out the rows
                                                      int rows=PQntuples(res);
			for (i = 0; i < rows; i++)
			{
				for (j = 0; j < nFields; j++) printf("%-15s", PQgetvalue(res, i, j));
				printf("\n");
			}
		}

		int exe_select(const char* sql, PGResultHandler handler)
		{
			int r = 0;
			char* cmd = string("DECLARE myportal CURSOR FOR %s", sql);

			conn = PQconnectdb(conn_string); if (PQstatus(conn) != CONNECTION_OK) goto fail;
			/* Set always-secure search path, so malicious users can't take control. */
			res = PQexec(conn, set_secure); if (PQresultStatus(res) != PGRES_TUPLES_OK) { goto fail; } PQclear(res);
			/* Start a transaction block */
			res = PQexec(conn, "BEGIN"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { goto fail; } PQclear(res); 

			res = PQexec(conn, cmd); if (PQresultStatus(res) != PGRES_COMMAND_OK) { goto fail; } PQclear(res);


			res = PQexec(conn, "FETCH ALL in myportal"); if (PQresultStatus(res) != PGRES_TUPLES_OK) { goto fail; }

			if (handler)
			{
				handler(res);
			}
			else
			{
				ouput();
			}

			PQclear(res);

			res = PQexec(conn, "END"); PQclear(res); goto ok;
		  
		fail: IdealandF(cmd); log_show("Postgres failed: %s", PQerrorMessage(conn)); if (res) PQclear(res); close(); return -2;
		ok:	IdealandF(cmd); close(); return 0;
		}

		int exe_insert(const char* sql, PGResultHandler handler)
		{
			int r = 0;
			char* cmd = string("%s RETURNING id;", sql);
			
			conn = PQconnectdb(conn_string); if (PQstatus(conn) != CONNECTION_OK) goto fail;
			/* Set always-secure search path, so malicious users can't take control. */
			res = PQexec(conn, set_secure); if (PQresultStatus(res) != PGRES_TUPLES_OK) { goto fail; } PQclear(res);
			/* Start a transaction block */
			res = PQexec(conn, "BEGIN"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { goto fail; } PQclear(res);

			res = PQexec(conn, cmd); if (PQresultStatus(res) != PGRES_TUPLES_OK) { goto fail; }
			if (handler)
			{
				handler(res);
			}
			else
			{
				r = atoi(PQgetvalue(res, 0, 0));
			}

			PQclear(res);

			res = PQexec(conn, "END"); PQclear(res); goto ok;

		fail:IdealandF(cmd); log_show("Postgres failed: %s", PQerrorMessage(conn)); if (res) PQclear(res); close(); return -2;
		ok:IdealandF(cmd); close(); return r;
		}

		int exe_update_delete(const char* sql, PGResultHandler handler)
		{
			int r = 0;

			conn = PQconnectdb(conn_string); if (PQstatus(conn) != CONNECTION_OK) goto fail;
			/* Set always-secure search path, so malicious users can't take control. */
			res = PQexec(conn, set_secure); if (PQresultStatus(res) != PGRES_TUPLES_OK) { goto fail; } PQclear(res);
			/* Start a transaction block */
			res = PQexec(conn, "BEGIN"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { goto fail; } PQclear(res);

			res = PQexec(conn, sql); if (PQresultStatus(res) != PGRES_COMMAND_OK) { goto fail; }
			if (handler)
			{
				handler(res);
			}
			else
			{
				ouput();
			}

			PQclear(res);

			res = PQexec(conn, "END"); PQclear(res); goto ok;

		fail:log_show("Postgres failed: %s", PQerrorMessage(conn)); if (res) PQclear(res); close(); return -2;
		ok:close(); return r;
		}


  };

}