repos_util/libpq_test/libpq_test/libpq_test.cpp

// libpq_test.cpp : 定义控制台应用程序的入口点。
//

#include "stdafx.h"
#include "libpq_test.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#endif


// 唯一的应用程序对象

CWinApp theApp;

using namespace std;


static void exit_nicely(PGconn *conn)
{
	PQfinish(conn);
	exit(EXIT_SUCCESS);
}

/************************************************************************/
/*  函数：[1/10/2018 Jeff];
/*  描述：;
/*  参数：;
/*  	[IN] pHost：数据库主机地址;
/*  	[IN] pPort：数据库端口号;
/*  	[IN] pUser：数据库用户名;
/*  	[IN] pPsw：数据库密码;
/*  	[IN] pDbName：数据库名;
/*  	[OUT] ：;
/*  	[IN/OUT] ：;
/*  返回：void;
/*  注意：;
/*  示例：;
/*
/*  修改：;
/*  日期：;
/*  内容：;
/************************************************************************/
static PGconn* connection_psql(const char *pHost, const char *pPort, const char* pUser, const char* pPsw, const char* pDbName)
{
	if ( pHost == NULL || pPort == NULL || pUser == NULL || pPsw == NULL ||pDbName == NULL )
		return NULL;

	//PGconn *conn = NULL;
	char conninfo[1024] = {0};
	// postgresql://[user[:password]@][netlocation][:port][/dbname][?param1=value1&...] // 如果密码包含@则连接不通;
	// _stprintf(conninfo, _T("postgresql://postgres:%s@%s:%s/%s"),pUser, pPsw, pHost, pPort, pDbName);
	sprintf_s(conninfo, "dbname=%s user=%s password=%s host=%s port=%s", pDbName, pUser, pPsw, pHost, pPort);

	return PQconnectdb(conninfo);
}

static PGconn* connection_psql2(const char *pHost, const char *pPort, const char* pUser, const char* pPsw, const char* pDbName)
{
	if ( pHost == NULL || pPort == NULL || pUser == NULL || pPsw == NULL ||pDbName == NULL )
		return NULL;

	// 必须以NULL结束;
	const char *keywords[] = {"dbname","user","password","host","port", NULL};
	const char *values[] = {pDbName,pUser,pPsw,pHost,pPort,NULL};

	return PQconnectdbParams(keywords, values, 0);
}

static PGconn* connection_psql3(const char *pHost, const char *pPort, const char* pUser, const char* pPsw, const char* pDbName)
{
	if ( pHost == NULL || pPort == NULL || pUser == NULL || pPsw == NULL ||pDbName == NULL )
		return NULL;

	// 可以连接;
	return PQsetdbLogin(pHost,pPort,"","",pDbName,pUser,pPsw);
}

int _tmain(int argc, TCHAR* argv[], TCHAR* envp[])
{
	int nRetCode = 0;

	// 初始化 MFC 并在失败时显示错误
	if (!AfxWinInit(::GetModuleHandle(NULL), NULL, ::GetCommandLine(), 0))
	{
		// TODO: 更改错误代码以符合您的需要
		_tprintf(_T("错误: MFC 初始化失败\n"));
		nRetCode = 1;
	}
	else
	{
		// TODO: 在此处为应用程序的行为编写代码。
		char    *pghost = NULL, *pgport = NULL, *pgoptions = NULL, *pgtty = NULL;
		char    *dbName = NULL;

		PGconn    *conn = NULL;
		PGresult   *res = NULL;

		int   nFields;
		int   i = 0,j = 0;

#ifdef DEBUG
		FILE    *debug;
#endif  /* DEBUG */


		/*
		* Begin by setting the parameters for a backend connection.
		* If the parameters are NULL, the system tries to use 
		* reasonable defaults by looking up environment variables 
		* or, failing that, using hardwired constants.
		*/

		conn = connection_psql2("itcivilian.vicp.io", "5432", "postgres", "postgres@851208", "test");

		// ## 连接数据,有三个函数可以使用:PQconnectdb,PQconnectdbParams,PQsetdbLogin
#if 0	// 密码带@字符的问题未解决;
		// URI 的一般形式是： 
		// postgresql://[user[:password]@][netlocation][:port][/dbname][?param1=value1&...]
		//const char *conninfo="postgresql://postgres:postgres@851208@itcivilian.vicp.io:5432/test";
		const char *conninfo="postgresql://dbname=test&host=itcivilian.vicp.io&port=5432&user=postgres&password='postgres@851208'";

		/* make a connection to the database */
		//conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);
		conn = PQconnectdb(conninfo);
#endif

#if 0
		// 可以连接;
		const char *conninfo="dbname=test user=postgres password=postgres@851208 host=itcivilian.vicp.io port=5432";

		/* make a connection to the database */
		//conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);
		conn = PQconnectdb(conninfo);
#endif

#if 0
		// 可以连接;
		const char *keywords[] = {"dbname","user","password","host","port", NULL};
		const char *values[] = {"test","postgres","postgres@851208","itcivilian.vicp.io","5432",NULL};

		conn = PQconnectdbParams(keywords, values, 0);
#endif

#if 0
		// 可以连接;
		conn = PQsetdbLogin("itcivilian.vicp.io",
			"5432",
			"",
			"",
			"test",
			"postgres",
			"postgres@851208");

		// 设置客户端的编码格式 ;
		PQsetClientEncoding(conn,"GBK");
#endif

		// 返回数据库名称;
		char *pDbName = PQdb(conn);
		printf("数据库名:%s\n", pDbName);

		// 返回的用户名;
		char *pUser = PQuser(conn);
		printf("用户名:%s\n", pUser);

		// 返回的密码;
		char *pPsw = PQpass(conn);
		printf("密码:%s\n", pPsw);

		// 服务器主机名;
		char *pHost = PQhost(conn);
		printf("服务器主机名:%s\n", pHost);

		// 端口号;
		char *pPort = PQport(conn);
		printf("端口号:%s\n", pPort);

		// 查看当前服务器的参数设置;
		printf("服务端版本号=%s,\n服务端编码格式=%s,\n客户端编码格式=%s,\n%s,\n%s,\n%s,\n%s,\n%s",
			PQparameterStatus(conn,"server_version"),
			PQparameterStatus(conn,"server_encoding"),
			PQparameterStatus(conn,"client_encoding"),
			PQparameterStatus(conn,"session_authorization"),
			PQparameterStatus(conn,"DateStyle"),
			PQparameterStatus(conn,"TimeZone"),
			PQparameterStatus(conn,"integer_datetimes"),
			PQparameterStatus(conn,"standard_conforming_strings")
			);

		// 判断conn的连接状态是否有效;
		/* check to see that the backend connection was successfully made */
		if (PQstatus(conn) == CONNECTION_BAD)
		{
			fprintf(stderr, "Connection to database '%s' failed.\n", dbName);
			fprintf(stderr, "%s", PQerrorMessage(conn));
			system("pause");
			exit_nicely(conn);
		}

#ifdef DEBUG
		debug = fopen("/tmp/trace.out", "w");
		PQtrace(conn, debug);
#endif  /* DEBUG */

		// ###开启事件;
		/* start a transaction block */
		res = PQexec(conn, "BEGIN");
		if (PQresultStatus(res) != PGRES_COMMAND_OK)
		{
			fprintf(stderr, "BEGIN command failed\n");
			PQclear(res);
			system("pause");
			exit_nicely(conn);
		}

		/*
		* should PQclear PGresult whenever it is no longer needed 
		* so as to avoid memory leaks
		*/
		PQclear(res);

		/*
		* fetch instances from the pg_database, the system catalog of
		* databases
		*/
		res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");
		if (PQresultStatus(res) != PGRES_COMMAND_OK)
		{
			fprintf(stderr, "DECLARE CURSOR command failed\n");
			PQclear(res);
			system("pause");
			exit_nicely(conn);
		}
		PQclear(res);

		res = PQexec(conn, "FETCH ALL in myportal");
		if (PQresultStatus(res) != PGRES_TUPLES_OK)
		{
			fprintf(stderr, "FETCH ALL command didn't return tuples properly\n");
			PQclear(res);
			system("pause");
			exit_nicely(conn);
		}

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

		printf("\n\n");

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

		PQclear(res);

		/* close the portal */
		res = PQexec(conn, "CLOSE myportal");
		PQclear(res);

		// ###结束事务;
		/* end the transaction */
		res = PQexec(conn, "END");
		PQclear(res);

		/* close the connection to the database and cleanup */
		PQfinish(conn);

#ifdef DEBUG
		if (debug)
			fclose(debug);
#endif  /* DEBUG */

		system("pause");

	}

	return nRetCode;
}