在我的项目中,连接oracle数据库并执行各种增删改查操作,主要是通过oracle的存储过程,这比直接执行SQL语句要简单并灵活多变。因为项目需要,要迁移到PostgreSQL下,因为考虑到各个平台的兼容性,采用libpq库来达到目的,在开发的过程中碰到了一些问题,在这里记录一下。
业务需求:pg中有个照片表,需要将照片信息及数据插入到该表中。并可能伴随增删改查动作。本节只处理插入操作。
照片表创建:
1 CREATE TABLE ist_image 2 ( 3 isc_imgid bigint NOT NULL, 4 isc_imgname character varying(64), 5 isc_updatetime timestamp without time zone, 6 isc_id bigint, -- 插入顺序编号(由序列生成) 7 isc_imgdata bytea, 8 CONSTRAINT ist_image_pkey PRIMARY KEY (isc_imgid) 9 )
自定义函数创建:
1 create or replace function func_insertimage(in nImgID bigint, in szImgName varchar, in pImgData bytea) 2 returns integer --必须有返回值 3 as 4 $$ 5 begin 6 insert into ist_image(isc_imgid, isc_imgname, isc_updatetime, isc_id, isc_imgdata) 7 values(nImgID, szImgName, now()::timestamp(0), nextval('iss_seq_isc_id'), pImgData); 8 return 0; 9 end; 10 $$ 11 language plpgsql;
照片数据结构:
1 class ImageInfo { 2 public: 3 __int64 nImgNumber; 4 char szImgName[64]; 5 int nImgDataLen; 6 unsigned char* pImgData; 7 }
插入函数:
1 //注意该函数是有BUG的 2 unsigned long long htonll(unsigned long long val) { 3 return (((unsigned long long )htonl((int)((val << 32) >> 32))) << 32) | (unsigned int)htonl((int)(val >> 32)); 4 } 5 6 int GP_TestInsert(deque<ImageInfo>& deqImages, char* szConnStr) { 7 int nParamNum = 3; //参数个数 8 int paramLens[3] = {0}; //参数长度 9 int paramFormats[3] = {1, 1, 1}; //参数是二进制格式(1表示二进制格式 0表示文本格式) 10 int nReturnForm = 0; //返回值是文本格式 11 unsigned long long ullHID = 0, ullNID = 0; 12 13 //函数调用语句 14 TCHAR szSQL[1024] = {0}; 15 _stprintf_s(szSQL, 1024, _T("select func_insertimage($1::bigint, $2::character varying, $3::bytea)")); 16 17 //连接GP数据库 18 PGConn* pConn = PQconnectdb(szConnStr); 19 if (CONNECTION_OK != PQstatus(pConn)) { 20 printf("GP_TestInsert Connect failed. ErrMsg: %s", PQerrorMessage(pConn)); 21 return -1; 22 } 23 24 //开始批量插入事务(显式BEGIN会开始一个事务) 25 PGresult* pRes = PQexec(pConn, "BEGIN"); 26 if (PQresultStatus(pRes) != PGRES_COMMAND_OK) { 27 printf("GP_TestInsert Exec BEGIN command failed. ErrMsg: %s", PQerrorMessage(pConn)); 28 PQclear(pRes); 29 return -1; 30 } 31 PQclear(pRes); //任何时候不再需要 PGresult 时,应该PQclear它来避免内存泄露 32 33 //循环插入数据 34 for (deque<ImageInfo>::iterator it = deqImages.begin(); it != deqImages.end(); ++it) 35 { 36 ullHID = (unsigned long long)(it->nImgNumber); 37 ullNID = htonll(ullHID); 38 39 //value 40 const char* const pszParamValue[3] = { (char*)&ullNID, 41 it->szImgName, 42 (char*)it->pImgData }; 43 44 //Length 45 paramLens[0] = sizeof(__int64); 46 paramLens[1] = (int)strlen(it->szImgName); //字符串类型长度要注意 47 paramLens[2] = it->nImgDataLen; 48 49 //execse 50 pRes = PQexecParams(pConn, szSQL, 3, NULL, (const char**)pszParamValue, paramLens, paramFormats, nReturnForm); 51 ExecStatusType resState = PQresultStatus(pRes); 52 if (PGRES_TUPLES_OK != resState || strcmp(PQgetvalue(pRes, 0, 0), "0") != 0) { 53 printf("GP_TestInsert Exec function failed. ErrCode: %d ErrInfo: %s ErrDesc: %s"), 54 resState, PQresStatus(resState), PQresultErrorMessage(pRes)); 55 return -1; 56 } 57 58 PQclear(pRes); 59 } 60 61 //结束事务(做完此步才会进行commit) 62 pRes = PQexec(pConn, "END"); 63 PQclear(pRes); 64 65 return 0; 66 }
这里面有几个要注意的地方:
1.每次连接、执行SQL语句要注意检查状态,判断是否执行成功;
2.对于整形数据的插入,要进行字节序转换,并根据整形结构的长度区别使用不同的字节序转换函数;
3.PostgreSQL数据库好像是默认开启AutoCommit的,若不想其自动commit,可以采用以下两种办法:
1)关闭数据库的自动commit属性,不过这种方式会导致管理时,出现一些烦人的提示;
2)每次显式调用BEGIN命令,一个事务(很多次条语句执行过后)结束后,执行END命令来commit,这种方式更灵活,个人更喜欢这种方式;
4.任何时候不再需要PGresult时,应该调用PQclear来清理它,避免内存泄露,具体请参见libpq说明文档;
当然,使用insert方式插入数据,对于PostgreSQL来说,并不是高效的方式,这里只是用这个例子来简单说明使用libpq库调用自定义函数的方式,后续再补充使用更高效方式来插入数据的例子,对于PostgreSQL来说,我也仅仅是初学者,若文中有错误烦请指正。