chap16_eg2.cpp

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

/*
* Copyright (c) OCI高级编程
* @File name: chap16_eg2.cpp
* @Author   : He Xiong
* @Content  : 从表中读取含有对象类型的记录行数据
* @Date		: 2003-10
****/
#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";

static OCIType  *hytype_tdo = NULL;
//
static hytype   *g_hytype = NULL;
static hytype_ind *g_hytype_ind = NULL;

//SELECT语句
static text* select = (text*)"SELECT val FROM hytab";

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; //语句句柄
	
	OCIDefine* defn1p = NULL;
	//是否存在
	boolean exists;

	int nID = 1;
	char* szName;
	int i = 0;
	const ub4 seed = 10000;
	int nV = 0;
	OCINumber* oci_number;
	int count = 0;
	int elem = 0;


	//使用对象模式来创建环境句柄
	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);
	
	
	//获取类型映射信息
	hytype_tdo = get_tdo(HYTYPE);
	

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

	//定义输出变量
	CheckErr(errhp, OCIDefineByPos(stmthp, &defn1p, errhp, (ub4)1, 
	    (dvoid *)0, (sb4)0, SQLT_NTY, (dvoid *)0,
	    (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT));

	CheckErr(errhp, OCIDefineObject(defn1p, errhp, hytype_tdo, 
	    (dvoid **)&g_hytype, (ub4 *)0, 
	    (dvoid **)&g_hytype_ind, (ub4 *)0));

	//执行查询并提取记录
	CheckErr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4)0, (ub4)0, 
	    (OCISnapshot *)NULL, (OCISnapshot *)NULL, 
	    (ub4)OCI_DEFAULT));
	
	printf("HYTAB表中val字段内容如下: \n");
	while( OCI_NO_DATA != OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT))
	{
		++i;
		printf("第%d行: ", i);
		if (g_hytype_ind->_atomic == OCI_IND_NOTNULL)
		{
			if (g_hytype_ind->hystr == OCI_IND_NOTNULL)
			{
				szName = (char*)OCIStringPtr(envhp, g_hytype->hystr);
				printf("%-32s, ", szName);
			}
			
			if (g_hytype_ind->hynum == OCI_IND_NOTNULL)
			{
				CheckErr(errhp, OCINumberToInt(errhp, &g_hytype->hynum, 
				 (uword)sizeof(ub4), OCI_NUMBER_UNSIGNED,
				 &nV));
				printf("%-8d, ", nV);
			}
			if (g_hytype_ind->hyarray == OCI_IND_NOTNULL)
			{
				CheckErr(errhp, OCICollSize(envhp, errhp, 
					(OCIColl *)(g_hytype->hyarray), 
					&(count)));
				for (int j=0; j<count; j++)
				{
					CheckErr(errhp, OCICollGetElem(envhp, errhp, 
			           (OCIColl *)(g_hytype->hyarray), 
			           (sb4)(j), (boolean *)&exists,
			           (dvoid **)&oci_number, (dvoid **)0));
					CheckErr(errhp, OCINumberToInt(errhp, oci_number, 
						(uword)sizeof(ub4), OCI_NUMBER_SIGNED,
						&elem));
					printf("%-8d ", elem);
				}
			}
		}
		else
		{
			printf("值为空");
		}
		printf("\n");
	}

	//
	printf("获取记录成功!\n");
	printf("结束会话和数据库连接!\n");


	//结束会话
	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;
}