chap16_eg1.cpp

《Oracle Spatial与OCI高级编程》相关配套源码。介绍了Oracle数据库中的两项关键开发技术Oracle Spatial以及OCI(Oracle Call Interface)。

/*
* Copyright (c) OCI高级编程
* @File name: chap16_eg1.cpp
* @Author   : He Xiong
* @Content  : 写含有对象类型的记录行数据

****/
#include "..\hytype.h"
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oci.h>

#if defined(WIN32)
#pragma comment(lib, "oci.lib")
#endif

#define HYARRAYT	"HYARRAYT"
#define HYTYPE		"HYTYPE"

//获取错误诊断信息
//@param   : pErr,错误句柄
//           lStatus, 状态码信息
//@return  : 0: 失败，出错
//			 1: 成功返回
int CheckErr(OCIError *pErr, sword lStatus);

//获取一个类型名的OCI类型指针
OCIType *get_tdo(char *szname);

//数据库服务名
static text* dbname = (text*) "orcl";

//用户及密码
static text* username = (text*) "ocitest";
static text* password = (text*) "ocitest";

//表名
static text* tablename = (text*) "hytab";

//HYARRAYT型指针
static OCIType  *hyarrayt_tdo = NULL;
//HYARRAYT的类型映射OCIARRAY
static OCIArray *hyarray = NULL;

//INSERT语句
static text* insert = (text*)"INSERT INTO hytab VALUES(:1, HYTYPE(:2, :3, :hyarray))";

static	OCIEnv *envhp;			// 环境句柄
static	OCIServer *srvhp;		//服务器句柄
static  OCIError *errhp;		//错误句柄
static	OCISession *usrhp;		//用户会话句柄
static 	OCISvcCtx *svchp;		//服务上下文句柄
static	OCIDescribe* dschp;		//描述句柄
int main()
{

	OCIParam	*parmhp = NULL;	//参数句柄
    OCIParam	*collsthp = NULL;	//列列句柄
	OCIParam	*colhp = NULL;		//列句柄
	OCIStmt		*stmthp = NULL; //语句句柄
	OCIBind* bndhp1 = NULL;
	OCIBind* bndhp2 = NULL;
	OCIBind* bndhp3 = NULL;
	OCIBind* bndhp4 = NULL;

	int nID = 1;
	char szName[32] = "hexiong";
	int i = 0;
	const ub4 seed = 10000;
	int nV = 0;
	OCINumber oci_number;


	//使用对象模式来创建环境句柄
	OCIEnvCreate(&envhp, OCI_OBJECT, (dvoid *)0,
		0, 0, 0, (size_t) 0, (dvoid **)0);
	//分配服务器句柄
	OCIHandleAlloc ((dvoid *)envhp, (dvoid **)&srvhp,
		OCI_HTYPE_SERVER, 0, (dvoid **) 0);
	//分配错误句柄
	OCIHandleAlloc ((dvoid *)envhp, (dvoid **)&errhp,
		OCI_HTYPE_ERROR, 0, (dvoid **) 0);

	//创建服务器上下文句柄,"orcl"为建立连接的数据库名
	if (OCIServerAttach (srvhp, errhp, (text *)dbname,
		strlen ((char*)dbname), OCI_DEFAULT) == OCI_SUCCESS)
		printf("\n已经成功连上数据库orcl\n");
	else //终止程序
	{
		printf("\n数据库名字不对，连接数据库失败!\n");
		return -1;
	}

	//分配服务器上下文句柄
	OCIHandleAlloc ((dvoid *)envhp, (dvoid **)&svchp,
		OCI_HTYPE_SVCCTX, 0, (dvoid **) 0);
	//设置服务器上下文句柄的服务器句柄属性
	OCIAttrSet ((dvoid *)svchp, OCI_HTYPE_SVCCTX,
		(dvoid *)srvhp, (ub4) 0, OCI_ATTR_SERVER, errhp);
	//分配用户会话句柄
	OCIHandleAlloc ((dvoid *)envhp, (dvoid **)&usrhp,
		OCI_HTYPE_SESSION, 0, (dvoid **) 0);
	//为用户会话句柄设置用户名和密码属性
	OCIAttrSet ((dvoid *)usrhp, OCI_HTYPE_SESSION,
		(dvoid *)username, (ub4)strlen((char*)username),
		OCI_ATTR_USERNAME, errhp);
	OCIAttrSet ((dvoid *)usrhp, OCI_HTYPE_SESSION,
		(dvoid *)password, (ub4)strlen((char*)password),
		OCI_ATTR_PASSWORD, errhp);

	//申请描述句柄
	OCIHandleAlloc((dvoid*)envhp, (dvoid**)&dschp, OCI_HTYPE_DESCRIBE, 0, (void**)0);

	if (OCISessionBegin ( svchp, errhp, usrhp,
		OCI_CRED_RDBMS, OCI_DEFAULT) == OCI_SUCCESS)
	{
		printf("成功建立用户会话!\n");
	}
	else
	{
		printf("建立用户会话失败!\n");
		return -1;
	}
	//在服务器上下文环境中设置用户会话属性
	OCIAttrSet ( (dvoid *)svchp, OCI_HTYPE_SVCCTX,
		(dvoid *)usrhp, (ub4) 0, OCI_ATTR_SESSION, errhp);
	
	
	//获取类型映射信息
	hyarrayt_tdo = get_tdo(HYARRAYT);
	
	//HYARRAYT型对象初始化
	CheckErr(errhp, OCIObjectNew(envhp, errhp, svchp, OCI_TYPECODE_VARRAY,
	    hyarrayt_tdo, (dvoid *)NULL, 
	    OCI_DURATION_SESSION,
	    FALSE, (dvoid **)&hyarray));

	printf("对象初始化成功!\n");
	
	//分配语句句柄
	CheckErr(errhp, OCIHandleAlloc(envhp, (void**)&stmthp, OCI_HTYPE_STMT, 0, 0));
	//准备SQL语句
	CheckErr(errhp, OCIStmtPrepare(stmthp, errhp, insert, strlen((char*)insert),
		OCI_NTV_SYNTAX, OCI_DEFAULT));

	srand(seed);
	
	//向变长数组hyarray里输入一些随机的值
	for (i=0; i<15; i++)
	{
		nV = rand();
		CheckErr(errhp, OCINumberFromInt(errhp, (dvoid *)&nV, 
		    (uword)sizeof(int),OCI_NUMBER_UNSIGNED, &oci_number));

		CheckErr(errhp, OCICollAppend(envhp, errhp, 
		    (dvoid *) &oci_number,
		    (dvoid *)0, (OCIColl *)hyarray));
	}
	
	//输入变量绑定
	CheckErr(errhp, OCIBindByPos(stmthp, &bndhp1, errhp, 1, &nID, 
		sizeof(nID), SQLT_INT, 0, 0, 0, 0, 0, OCI_DEFAULT));
	CheckErr(errhp, OCIBindByPos(stmthp, &bndhp2, errhp, 2, &szName, 
		sizeof(szName), SQLT_STR, 0, 0, 0, 0, 0, OCI_DEFAULT));
	CheckErr(errhp, OCIBindByPos(stmthp, &bndhp3, errhp, 3, &nID, 
		sizeof(nID), SQLT_INT, 0, 0, 0, 0, 0, OCI_DEFAULT));
	CheckErr(errhp, OCIBindByName(stmthp, &bndhp4, errhp, 
	    (text *)":hyarray", (sb4)-1, (dvoid *)0, 
	    (sb4)0, SQLT_NTY, (dvoid *)0, (ub2 *)0, 
	    (ub2 *)0, (ub4)0, (ub4 *)0, 
	    (ub4)OCI_DEFAULT));
	CheckErr(errhp, OCIBindObject(bndhp4, errhp, hyarrayt_tdo, 
	    (dvoid **)&hyarray, (ub4 *)0, 
	    (dvoid **)0, (ub4 *)0));

	CheckErr(errhp, OCIStmtExecute(svchp, stmthp, errhp, 1, 0, NULL, NULL,
		OCI_DEFAULT));
	printf("插入记录成功!\n");
	printf("结束会话和数据库连接!\n");

	//释放对象类型句柄
	CheckErr(errhp, OCIObjectFree(envhp, errhp, (dvoid *)hyarray, 
	    (ub2)OCI_OBJECTFREE_FORCE));
	//结束会话
	OCISessionEnd(svchp, errhp, usrhp, OCI_DEFAULT);
	//断开连接
	OCIServerDetach(srvhp, errhp, OCI_DEFAULT);
	//释放环境句柄
	OCIHandleFree((void*)envhp, OCI_HTYPE_ENV);
	int nTemp;
	scanf("%d", &nTemp);
	return 0;
}

int CheckErr(OCIError *pErr, sword lStatus)
{
    
    sb4		m_s_nErrCode = 0;
	char	m_s_szErr[512];
    
    switch (lStatus)
    {
    case OCI_SUCCESS:
        strcpy(m_s_szErr,"OCI_SUCCESS");    
        break;
    case OCI_SUCCESS_WITH_INFO:
        strcpy(m_s_szErr, "OCI_SUCCESS_WITH_INFO");
		printf("OCI Error: %s\n", m_s_szErr);
        break;
    case OCI_ERROR:
        OCIErrorGet((dvoid *)pErr, (ub4)1, (text *)NULL, &m_s_nErrCode, 
            (unsigned char*)m_s_szErr, (ub4)sizeof(m_s_szErr), OCI_HTYPE_ERROR);
		printf("OCI Error: %s\n", m_s_szErr);
        break;
    case OCI_NEED_DATA:
        strcpy(m_s_szErr, "OCI_NEED_DATA");
		printf("OCI Error: %s\n", m_s_szErr);
        break;
    case OCI_NO_DATA:
        strcpy(m_s_szErr, "OCI_NO_DATA");
		printf("OCI Error: %s\n", m_s_szErr);
        break;
    case OCI_INVALID_HANDLE:
        strcpy(m_s_szErr, "OCI_INVALID_HANDLE");
		printf("OCI Error: %s\n", m_s_szErr);
        break;
    case OCI_STILL_EXECUTING:
        strcpy(m_s_szErr, "OCI_STILL_EXECUTING");
		printf("OCI Error: %s\n", m_s_szErr);
        break;
    case OCI_CONTINUE:
        strcpy(m_s_szErr, "OCI_CONTINUE");
		printf("OCI Error: %s\n", m_s_szErr);
        break;
    default:
        break;
    }
    if (lStatus != OCI_SUCCESS && lStatus != OCI_SUCCESS_WITH_INFO)
    {
        //return 0;  //确实有错误
		exit(-1);
    } 
    else 
    {
        return 1;  //没有检查到错误
    }
}

OCIType *get_tdo(char *szname)
{
	OCIParam *paramp = NULL;
	OCIRef *type_ref = NULL;
	OCIType *tdo = NULL;

	CheckErr(errhp, OCIDescribeAny(svchp, errhp, (text *)szname, 
	    (ub4)strlen((char *)szname), 
	    OCI_OTYPE_NAME, (ub1)1, 
	    (ub1)OCI_PTYPE_TYPE, dschp));
	CheckErr(errhp, OCIAttrGet((dvoid *)dschp, (ub4)OCI_HTYPE_DESCRIBE,
	    (dvoid *)&paramp, (ub4 *)0, 
	    (ub4)OCI_ATTR_PARAM, errhp));
	CheckErr(errhp, OCIAttrGet((dvoid *)paramp, (ub4)OCI_DTYPE_PARAM,
	    (dvoid *)&type_ref, (ub4 *)0, 
	    (ub4)OCI_ATTR_REF_TDO, errhp));
	CheckErr(errhp, OCIObjectPin(envhp, errhp, type_ref, (OCIComplexObject *)0, 
	    OCI_PIN_ANY, OCI_DURATION_SESSION, 
	    OCI_LOCK_NONE, (dvoid **)&tdo));
	if (!tdo)
	{
		fprintf(stderr, "Null tdo returned for type %s.\n", szname);
		exit(-1);
	}

	return tdo;
}