处理数据类型

大对象类型

插入记录

OCIStmt *stmthp;

text *sqlstmt = (text *) “insert into T1(C10) value (‘vastbase-G100’)”;

if(OCIHandleAlloc ((dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR,
             (size_t) 0, (dvoid **) 0))
{
    printf("FAILED: OCIHandleAlloc()\n");         
return OCI_ERROR
}

if(OCIStmtPrepare(stmthp, errhp, sqlstmt, strlen((char *)sqlstmt), OCI_NTV_SYNTAX, 0))
{
printf(“FAILED: OCIStmtPrepare()\n”);
return OCI_ERROR;
}

if(OCIStmtExecute(svchp, stmthp, errhp, 0, 0, 0, 0, 0))
{
    printf(“FAILED: OCIStmtExecute()\n”);
}

读取LOB大对象定位器

调用OCIDescriptorAlloc申请大对象描述符句柄，通过OCIDefineByPos定义绑定结果，将定位器绑定到大对象描述符上以便后续调用大对象接口。也可以再申请完大对象描述符句柄后调用OCILobCreateTemporary创建临时LOB对象。

OCIEnv *envhp;
    OCIServer *srvhp;
    OCIError *errhp;
    OCISvcCtx *svchp;
    OCIStmt *stmthp;

   /*可进行不同的sql测试，单个绑定和复合绑定可以选择下边的sqlstmt*/
   text *sqlstmt=(text *)"select  c10 from T2 where c1 = 998999";
	int i;
	int flag;

	int     *c1 = (int*) malloc (sizeof(int) * GET_ROWS);

	OCIDefine *bndhp[2];
	ub4     stmrow, stmrowEx;
	OCIParam* colhd = NULL;
	i = '+';
	memset(c1, 0, sizeof(int) * GET_ROWS);

    if(OCIEnvCreate ((OCIEnv **)&envhp, (ub4)OCI_DEFAULT, (dvoid *)0,
                (dvoid * (*)(dvoid *, size_t))0,
                (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                (void (*)(dvoid *, dvoid *))0, (size_t)0, (dvoid **)0))
	{
		printf("FAILED: OCIEnvCreate()\n");
		return OCI_ERROR;
	}

    if(OCIHandleAlloc ((dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR,
                  (size_t) 0, (dvoid **) 0))
	{
		printf("FAILED: OCIHandleAlloc()\n");
		return OCI_ERROR;
	}

	if(OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp,
           OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0))
	{
		printf("FAILED: OCIHandleAlloc()\n");
		return OCI_ERROR;
	}

    int total =10;
    OCILobLocator **lobLocator = (OCILobLocator **)malloc(sizeof(OCILobLocator *)*total);

    OCILobLocator **lobLocator_dst = (OCILobLocator **)malloc(sizeof(OCILobLocator *)*total);

    for(i =0; i<total; ++i)
    {
        if (OCIDescriptorAlloc(envhp, (dvoid **)&lobLocator[i], OCI_DTYPE_LOB, 0, 0))
        {
            printf("FAILED: lob_locatorOCIDescriptorAlloc()\n");
            report_error(errhp);
        }
        if (OCIDescriptorAlloc(envhp, (dvoid **)&lobLocator_dst[i], OCI_DTYPE_LOB, 0, 0))
        {
            printf("FAILED: lob_locatorOCIDescriptorAlloc()\n");
            report_error(errhp);
        }
    }

    /*
     * Connect and create an user session
	*/
    
	if (OCILogon(envhp, errhp, &svchp, username, strlen (username),
			   password, strlen (password), dsn, strlen(dsn)))
	{
		printf("FAILED: log_on()\n");
		return OCI_ERROR;
	}

	//测试准备一条SQL
	if(OCIStmtPrepare(stmthp, errhp, sqlstmt, strlen((char *)sqlstmt),
	 OCI_NTV_SYNTAX, 0))
        {
                printf("FAILED: OCIStmtPrepare()\n");
                report_error(errhp);
                return OCI_ERROR;
        }

	if (OCIDefineByPos(stmthp, &bndhp[0], errhp, 1,
		(dvoid *)lobLocator, sizeof(OCILobLocator *), (ub2)SQLT_CLOB,
		(dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT))
	{
		printf("FAILED: OCIDefineByPos2()\n");
		report_error(errhp);
		return OCI_ERROR;
	}

	if (OCIDefineArrayOfStruct(bndhp[0], errhp, sizeof(OCILobLocator *),
		0, 0, 0))
	{
		printf("FAILED: OCIDefineArrayOfStruct2()\n");
		report_error(errhp);
		return OCI_ERROR;
	}

	//测试SQL执行
	if(OCIStmtExecute(svchp, stmthp, errhp, 0, 0, 0, 0, 0))
        {
            report_error(errhp);
            printf("FAILED: OCIStmtExecute()\n");
        }

    //测试获取 SQL 生成的结果集中的行集
    if(OCIStmtFetch(stmthp, errhp, total, OCI_FETCH_NEXT, 0))
    {
        printf("FAILED: OCIStmtFetch()\n");
        report_error(errhp);

    }

操作大对象类型

再获取了LOB对象定位器之后，可以调用OCI大对象接口操作大对象，包括OCILobAssign，OCILobGetLength，OCILobIsEqual，OCILobRead，OCILobWrite，OCILobIsEqual。

    OCIAttrGet(stmthp, OCI_HTYPE_STMT, &stmrow, 0, OCI_ATTR_ROW_COUNT, errhp);

    for(i=0; i<stmrow; ++i)
    {
        int len = 0;
        int len_dst = 0;
        boolean flag = 0;
        boolean flag1 = 0;
        boolean isEqual = 0;

        if(OCILobLocatorIsInit(envhp, errhp, lobLocator[i],&flag)){
            printf("FAILED: OCILobLocatorIsInit()\n");
            report_error(errhp);
            return OCI_ERROR;
        }
        printf("OCILobLocatorIsInit is %d\n", flag);

        if(OCILobIsOpen(svchp,errhp, lobLocator[i], &flag1)){
            printf("FAILED: OCILobIsOpen()\n");
            report_error(errhp);
            return OCI_ERROR;
        }
        printf("OCILobIsOpen -1 is %d\n", flag1);

        if(OCILobOpen(svchp, errhp, lobLocator[i],OCI_LOB_READWRITE)){
           printf("FAILED: OCILobOpen()\n");
		report_error(errhp);
		return OCI_ERROR;
        }

        if(OCILobIsOpen(svchp,errhp, lobLocator[i], &flag1)){
            printf("FAILED: OCILobIsOpen()\n");
            report_error(errhp);
            return OCI_ERROR;
        }
        printf("OCILobIsOpen - 2 is %d\n", flag1);

        if(OCILobAssign(envhp, errhp, lobLocator[i], &lobLocator_dst[i]))
        {
            printf("FAILED: OCILobAssign()\n");
            report_error(errhp);
            return OCI_ERROR;
        }

        if(OCILobIsEqual(envhp, lobLocator[i], lobLocator_dst[i], &isEqual))
        {
            printf("FAILED: OCILobAssign()\n");
            return OCI_ERROR;
        }
        printf("OCILobIsEqual = %d\n", isEqual);

        if(OCILobGetLength(svchp, errhp, lobLocator[i], &len)){
            printf("FAILED: OCILobGetLength()\n");
		report_error(errhp);
		return OCI_ERROR;
        }
        printf("len = %d\n", len);

        if(OCILobGetLength(svchp, errhp, lobLocator_dst[i], &len_dst)){
            printf("FAILED: OCILobGetLength()\n");
		report_error(errhp);
		return OCI_ERROR;
        }
        printf("len_dst = %d\n", len_dst);

        ub4 amount = 2;
        char readStr[10000]={0};
        if(OCILobRead(svchp, errhp, lobLocator[i], &amount, 1, (void *)readStr, 2, NULL, NULL, 0, 0)){
            printf("FAILED: OCILobRead()\n");
            report_error(errhp);
            return OCI_ERROR;
        }
        printf("readStr=%s\n", readStr);

        ub4 amount1 = 3;
        char *writeStr = "vastbase";
        if(OCILobWrite(svchp, errhp, lobLocator[i], &amount1, 2, (void *)writeStr, 3, 0, NULL, NULL, 0, 0)){
            printf("FAILED: OCILobWrite()\n");
            report_error(errhp);
            return OCI_ERROR;
        }

        if(OCILobTrim(svchp, errhp, lobLocator[i], 6)){
            printf("FAILED: OCILobTrim()\n");
            report_error(errhp);
            return OCI_ERROR;
        }

        if(OCILobClose(svchp, errhp, lobLocator[i])){
            printf("FAILED: OCILobClose()\n");
		report_error(errhp);
		return OCI_ERROR;
        }
        if(OCILobClose(svchp, errhp, lobLocator_dst[i])){
            printf("FAILED: OCILobClose()\n");
		report_error(errhp);
		return OCI_ERROR;
       }
    }