postgresql 异步请求示例,:查询和写入空间数据

 本文是参照PostgreSQL 9.6的中文手册编写的示例代码,  示例依赖libpq 库,可自行取下或者安装postgresql获取, 

服务端环境 postgresql +postGIS 插件, 

在线手册 http://www.postgres.cn/docs/9.6/index.html

主要的异步请求代码如下: 最大的坑是异步请求后的释放问题,  发出请求后必须使用PQGetResult 获取完所有数据才能执行下一条sql命令,  这对于一个链接来说不就是同步吗?  如果我不去取结果集? 所以想用异步请求 , 己得加上以下代码:

    res = PQgetResult(conn);

    while (true)
    {
        if (!res)
            break;
        res = PQgetResult(conn);
    }

连接数据库,创建表,插入数据, 完整例子下载:https://download.csdn.net/download/chijingjing/11388040

int InsertValues()
{
	//WSAStartup();
	WORD version = MAKEWORD(2, 2);
	WSADATA data;

	//int nRet = WSAStartup(version, &data);
	const char *conninfo;
	PGconn     *conn;
	PGresult   *res;
	const char *result;
	const char *paramValues[2];
	int     nFields;
	int        i, j;
	ExecStatusType tyty;

	conninfo = "host=localhost dbname=dg user=postgres password=chijing port=5432 connect_timeout = 10 client_encoding=utf-8";
	conn = PQconnectStart(conninfo);
	ConnStatusType  ConnType = PQstatus(conn);
	if (CONNECTION_BAD == ConnType)
		return 0;
	int ab = PQsocket(conn);
	PostgresPollingStatusType polltype = PGRES_POLLING_FAILED;
	while (true)
	{
		polltype = PQconnectPoll(conn);
		if (polltype == PGRES_POLLING_FAILED)
			return errorMSG(conn);
		if (polltype == PGRES_POLLING_OK)
			break;
	}

	res = PQexec(conn, "drop TABLE \"TEST1\"");
	PQclear(res);
	//创建数据表
	res = PQexec(conn, "CREATE TABLE \"TEST1\" ( \
			\"OID\" bigserial NOT NULL,\
			\"GEOMETRY\" geometry,\
			\"TESTCOL\" text,\
			PRIMARY KEY(\"OID\")\
		)\
			WITH(OIDS = FALSE)\
			; ");

	ConnType = PQstatus(conn);
	tyty = PQresultStatus(res);
	//创建空间索引
	res = PQexec(conn, "CREATE INDEX \"TEST1_GIST\" ON \"TEST1\" USING gist(\"GEOMETRY\");");
	errorMSG(conn);
	ConnType = PQstatus(conn);
	tyty = PQresultStatus(res);

	std::string strSQL = "INSERT INTO \"public\".\"TEST1\"(\"GEOMETRY\",\"TESTCOL\") VALUES (ST_GeomFromEWKB($1),$2)";

	Oid paramTypes[1] = { 5 };
	int ascres = PQsendPrepare(conn, "INSERT_TEST1", strSQL.c_str(), 2, NULL);
	ExecStatusType v = PQresultStatus(res);
	if (v != PGRES_COMMAND_OK)
	{
		errorMSG(conn);
		printf("The Function Error./n");
		PQclear(res);
	}
	if (ascres != 1)//成功
	{
		return 1;
	}
	else//失败
	{

	}
	res = PQgetResult(conn);
	while (true)
	{
		if (!res)
			break;
		res = PQgetResult(conn);
	}

	GsGrowByteBuffer buff;
	GsWKBOGCWriter pWKB(&buff);
	GsEnvelopePtr penv = new GsEnvelope();
	GsStringStream m_ss;
	GsPointPtr point = new GsPoint(1, 1);
	for (int i = 1; i < 10000; i++) {

		const char* const* a = 0;
		const char *tparamValues[2];
		point->Set(i, i);
		char id[12] = {};
		itoa(i, id, 10);
		//tparamValues[0] = id;
		pWKB.Reset();
		pWKB.Write(point);
		GsGrowByteBuffer hexBuff(pWKB.WKB()->BufferHead(), pWKB.WKB()->BufferSize());
		//GsGeometryFactory::ConvertByteOrderToStorageBlob(hexBuff.BufferHead(), hexBuff.BufferSize());
		//GsGeometryFactory::ConvertByteOrderToStorageBlob(point->GeometryBlobPtr()->BufferHead(), point->GeometryBlobPtr()->BufferSize());
		tparamValues[0] = (char*)hexBuff.BufferHead();

		int binaryIntVal = htonl((uint32_t)2);

		int paramFormat[2] = { 1,0 };

		tparamValues[1] = "dfgdf";
		int paramLengths[2] = { /*sizeof(i),*/hexBuff.BufferSize(), 5 };
		ascres = PQsendQueryPrepared(conn, "INSERT_TEST1", 2, tparamValues, paramLengths, paramFormat, 1);

		if (ascres != 1)//成功
		{
			return 1;
		}
		else//失败
		{

		}
		res = PQgetResult(conn);
		while (true)
		{
			if (!res)
				break;
			res = PQgetResult(conn);
		}
		errorMSG(conn);
		ConnType = PQstatus(conn);
		tyty = PQresultStatus(res);
		
	}
}

查询数据:

int SearchRows()
{
	//WSAStartup();
	WORD version = MAKEWORD(2, 2);
	WSADATA data;

	//int nRet = WSAStartup(version, &data);
	const char *conninfo;
	PGconn     *conn;
	PGresult   *res;
	const char *result;
	const char *paramValues[2];
	int     nFields;
	int        i, j;


	conninfo = "host=localhost dbname=dg user=postgres password=chijing port=5432";// connect_timeout = 5";
	conn = PQconnectStart(conninfo);
	ConnStatusType  ConnType = PQstatus(conn);
	if (CONNECTION_BAD == ConnType)
		return 0;
	int ab = PQsocket(conn);
	PostgresPollingStatusType polltype = PGRES_POLLING_FAILED;
	while (true)
	{
		polltype = PQconnectPoll(conn);
		if (polltype == PGRES_POLLING_FAILED)
			return errorMSG(conn);
		if (polltype == PGRES_POLLING_OK)
			break;
	}
	//res = PQexecParams(conn, "select \"funSelectBranchConditionBySid \"($1,$2)", 2, NULL, paramValues, NULL, NULL, 0);
	Oid paramTypes[1] = { 5 };
	int ascres = PQsendPrepare(conn, "asd", "SELECT \"OID\",ST_AsEWKT(\"GEOMETRY\") as GEOMETRY_WKT, \"TESTCOL\"   FROM \"TEST1\" \
	where ST_Intersects(\"GEOMETRY\", ST_MakeBox2D(ST_Point($1, $2), ST_Point($3, $4)))", 4, NULL);
	
	if (ascres != 1)//成功
	{
		return 1;
	}
	else//失败
	{

	}
	res = PQgetResult(conn);
	while (true)
	{
		if (!res)
			break;
		res = PQgetResult(conn);
	}

	const char* const* a = 0;
	const char *tparamValues[4];
	tparamValues[0] = "5";
	tparamValues[1] = "5";
	tparamValues[2] = "10";
	tparamValues[3] = "10";
	int paramLengths[4] = { 2,2,2,2 };
	int paramFormats[4] = { 0,0,0,0 };
	ascres = PQsendQueryPrepared(conn, "asd", 4, tparamValues, paramLengths, paramFormats, 0);
	

	if (ascres != 1)//成功
	{
		return 1;
	}
	else//失败
	{

	}
	res = PQgetResult(conn);

	ExecStatusType v = PQresultStatus(res);
	errorMSG(conn);
	/* first, print out the attribute names */
	nFields = PQnfields(res);
	for (i = 0; i < nFields; i++)
		printf("%-15s", PQfname(res, i));
	printf("\n");
	while (true)
	{
		if (!res)
			break;
		for (i = 0; i < PQntuples(res); i++)
		{
			for (j = 0; j < nFields; j++)
				printf("%-15s", PQgetvalue(res, i, j));
			printf("\n");
		}
		res = PQgetResult(conn);
	}



	/*res = PQexec(conn, "END");*/
	PQclear(res);
	/* close the connection to the database and cleanup */
	PQfinish(conn);

	WSACleanup();
	return 0;
}