达梦6数据库 OCI编程接口示例

为了使达梦6数据库兼容OCI接口应用程序，DM6中提供了一组OCI接口。一个OCI应用程序的基本结构包括：

1. 初始化OCI环境
2. 分配必要的句柄
3. 建立数据库连接，开始会话
4. 准备SQL，执行SQL，数据处理等
5. 结束用户会话，断开数据库连接
6. 释放分配的句柄

/*
person表定义：
CREATE TABLE "SYSTEM"."SYSDBA"."PERSON"(
"PERSONID" VARCHAR(16),
"NAME" VARCHAR(128),
"PHONE" VARCHAR(32),
"SEX" CHAR(1),
"EMAIL" VARCHAR(200)
);
数据示例：
begin 
for i in 1..10000 loop
insert into person values(i,'test','123456789123','M','[email protected]');
end loop;
commit;
end;
*/

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include "oci.h"

#pragma comment(lib,"dmoci_dll.lib")

typedef struct person_info_struct person_info_t;
struct person_info_struct
{
	char szpersonid[16];       /* 存储 personid 列*/
	char szname[128];          /* 存储 name 列*/
	char szphone[32];          /* 存储 phone 列*/
	unsigned short indp;
	unsigned short rlenp;
};

/*批量取数绑定的行数*/
#define BIND_ROWS   5000  

int main(int argc,char *argv[])
{
	OCIEnv *envhp;						/* 环境句柄 */
	OCISvcCtx *svchp;					/* 服务环境句柄 */
	OCIServer *srvhp;					/* 服务器句柄 */
	OCISession *authp;					/* 会话句柄 */
	OCIStmt *stmthp;					/* 语句句柄 */
	OCIDescribe *dschp;					/* 描述句柄 */
	OCIError *errhp;					/* 错误句柄 */
	OCIDefine *defhp[3];				/* 定义句柄 */

	int i;
	char sql[256];						 /* 存储执行的 sql 语句*/

	char strServerName[50];
	char strUserName[50];
	char strPassword[50];

	person_info_t*  pdata;
	person_info_t*  pdata2;
	/* 设置服务器，用户名和密码 */
	strcpy(strServerName,"localhost");
	strcpy(strUserName,"SYSDBA");
	strcpy(strPassword,"SYSDBA");

	pdata = (person_info_t*)malloc(BIND_ROWS * sizeof(person_info_t));
	if (pdata == NULL)
	{
		printf("分配内存失败！\n");
		return -1;
	}
	pdata2 = pdata;
	/* 初始化OCI 应用环境*/
	OCIInitialize(OCI_DEFAULT, NULL, NULL, NULL, NULL);
	/* 初始化环境句柄*/
	OCIEnvInit(&envhp, OCI_DEFAULT,0, 0);

	/* 分配句柄 */
	OCIHandleAlloc(envhp, (dvoid**)&svchp, OCI_HTYPE_SVCCTX, 0, 0);			 /* 服务器环境句柄*/																
	OCIHandleAlloc(envhp, (dvoid**)&srvhp, OCI_HTYPE_SERVER, 0, 0);			 /* 服务器句柄 */
	OCIHandleAlloc(envhp, (dvoid**)&authp, OCI_HTYPE_SESSION, 0, 0);		 /* 会话句柄 */
	OCIHandleAlloc(envhp, (dvoid**)&errhp, OCI_HTYPE_ERROR, 0, 0);			 /* 错误句柄 */
	OCIHandleAlloc(envhp, (dvoid**)&dschp, OCI_HTYPE_DESCRIBE,0,0);			 /* 描述符句柄*/

	/* 连接服务器 */
	OCIServerAttach(srvhp, errhp,(oratext*)strServerName,(sb4)strlen(strServerName),OCI_DEFAULT);

	/* 设置用户名和密码 */
	OCIAttrSet(authp,OCI_HTYPE_SESSION,(oratext*)strUserName,(ub4)strlen(strUserName),OCI_ATTR_USERNAME,errhp);
	OCIAttrSet(authp,OCI_HTYPE_SESSION,(oratext*)strPassword,(ub4)strlen(strPassword), OCI_ATTR_PASSWORD,errhp);


	/* 设置服务器环境句柄属性 */
	OCIAttrSet((dvoid*)svchp, (ub4)OCI_HTYPE_SVCCTX,(dvoid*)srvhp, (ub4)0,OCI_ATTR_SERVER, errhp);
	OCIAttrSet((dvoid*)svchp, (ub4)OCI_HTYPE_SVCCTX,(dvoid*)authp, (ub4)0, OCI_ATTR_SESSION, errhp);

	/* 创建并开始一个用户会话 */
	OCISessionBegin (svchp, errhp, authp,OCI_CRED_RDBMS,OCI_DEFAULT);

	OCIHandleAlloc(envhp, (dvoid**)&stmthp,OCI_HTYPE_STMT, 0, 0); /* 语句句柄 */

	/************************************************************************/
	/* 查询person 表 */
	/************************************************************************/
	strcpy(sql, "select personid, name, phone from system.sysdba.person;");

	/* 准备 SQL 语句*/
	OCIStmtPrepare(stmthp, errhp,(oratext *)sql, strlen(sql),OCI_NTV_SYNTAX, OCI_DEFAULT);

	/* 绑定输出列(批量行绑定) */
    OCIDefineByPos(stmthp,&defhp[0],errhp,1,pdata->szpersonid,16,SQLT_STR,(void *)(&pdata->indp),(ub2 *)(&pdata->rlenp),0,OCI_DEFAULT);
	OCIDefineArrayOfStruct(defhp[0], errhp, sizeof(person_info_t), sizeof(person_info_t), sizeof(person_info_t), 0);
	OCIDefineByPos(stmthp,&defhp[1],errhp,2,pdata->szname,128,SQLT_STR,(void *)(&pdata->indp),(ub2 *)(&pdata->rlenp),0,OCI_DEFAULT);
	OCIDefineArrayOfStruct(defhp[1], errhp, sizeof(person_info_t), sizeof(person_info_t), sizeof(person_info_t), 0);
	OCIDefineByPos(stmthp,&defhp[2],errhp,3,pdata->szphone,32,SQLT_STR,(void *)(&pdata->indp),(ub2 *)(&pdata->rlenp),0,OCI_DEFAULT);
    OCIDefineArrayOfStruct(defhp[2], errhp, sizeof(person_info_t), sizeof(person_info_t), sizeof(person_info_t), 0);

	/* 执行 SQL 语句*/
	OCIStmtExecute(svchp, stmthp,errhp, (ub4)0, 0, NULL, NULL, OCI_DEFAULT);

	printf("%-10s%-10s%-10s\n", "PERSONID", "NAME", "PHONE");
	if ((OCIStmtFetch2(stmthp, errhp,BIND_ROWS,OCI_FETCH_NEXT,0,OCI_DEFAULT)) != OCI_NO_DATA)
	{
         for (i =0 ;i<BIND_ROWS ;i++)
         {
			 printf("%-10s", pdata->szpersonid);
			 printf("%-10s", pdata->szname);
			 printf("%-10s\n",pdata->szphone);
			 pdata++;
         }
	}
	//结束会话
	OCIHandleFree((dvoid*)stmthp, OCI_HTYPE_STMT );
	OCISessionEnd(svchp, errhp, authp, (ub4) 0);

	//断开与数据库的连接
	OCIServerDetach(srvhp, errhp, OCI_DEFAULT);
	
	//释放OCI 句柄
	OCIHandleFree((dvoid*)srvhp, OCI_HTYPE_SERVER);
	OCIHandleFree((dvoid*)svchp, OCI_HTYPE_SVCCTX);
	OCIHandleFree((dvoid*)errhp, OCI_HTYPE_ERROR);
	OCIHandleFree((dvoid*)authp, OCI_HTYPE_SESSION );
	OCIHandleFree((dvoid*)dschp, OCI_HTYPE_DESCRIBE);

	free(pdata2);
	return 0;
}