使用OCI向Oracle插入Geometry数据

    使用C/C++操作Oracle数据库，使用OCI可谓是最强大，当然也是最难的方式。Oracle是一个功能复杂而强大的数据库，它可以很好的支持空间数据（Oracle spatial）。如何使用OCI向Oracle数据库中插入空间数据（SDO_GEOMETRY字段）呢，本文将给出代码。

    首先，给出OCI连接数据库的代码：（ph开头的变量均是OCI句柄）

//以指定的模式创建环境句柄
     OCIEnvCreate(&phEnv,connMode,(dvoid *)0,0,0,0,(size_t)0,(dvoid **)0);
     //分配服务器句柄,注意不要少了取值符号&
     OCIHandleAlloc((dvoid *)phEnv,(dvoid **)&phServer,OCI_HTYPE_SERVER,0,(dvoid **)0);
     //分配错误句柄
     OCIHandleAlloc((dvoid *)phEnv,(dvoid **)&phErr,OCI_HTYPE_ERROR,0,(dvoid **)0);
     //创建服务器上下文句柄,c_str()函数将C++类型的string字符串转化为C串 char*
     if(OCIServerAttach(phServer,phErr,(text *)DBname.c_str(),strlen(DBname.c_str()),OCI_DEFAULT) ==OCI_SUCCESS)
         cout<<"DB "+DBname+" is connected successfully!"<<endl;
     else
     {
          cout<<"DB "+DBname+" is fail to connect，Please check whether you type a right DB name!"<<endl;
     }
     //分配上下文句柄
      OCIHandleAlloc((dvoid *)phEnv,(dvoid **)&phSvcCtx,OCI_HTYPE_SVCCTX,0,(dvoid **)0);
      //设置服务器上下文句柄的服务器句柄属性
      OCIAttrSet((dvoid *)phSvcCtx,OCI_HTYPE_SVCCTX,(dvoid *)phServer,(ub4)0,OCI_ATTR_SERVER,phErr);
      //分配用户会话句柄
       OCIHandleAlloc((dvoid *)phEnv,(dvoid **)&phSession,OCI_HTYPE_SESSION,0,(dvoid **)0);
       //为用户会话句柄设置用户名和密码属性
       OCIAttrSet((dvoid *)phSession,OCI_HTYPE_SESSION,(dvoid *)username.c_str(),(ub4)strlen(username.c_str()),OCI_ATTR_USERNAME,phErr);
       OCIAttrSet((dvoid *)phSession,OCI_HTYPE_SESSION,(dvoid *)password.c_str(),(ub4)strlen(password.c_str()),OCI_ATTR_PASSWORD,phErr);
       //申请描述句柄
       OCIHandleAlloc((dvoid*)phEnv,(dvoid**)&phdsc,OCI_HTYPE_DESCRIBE,0,(void**)0);
       if (OCISessionBegin(phSvcCtx,phErr,phSession,OCI_CRED_RDBMS,OCI_DEFAULT)==OCI_SUCCESS)
       {
           cout<<"user session is created successfully!"<<endl;
       } 
       else
       {
           cout<<"user session is fail to create! "<<endl;
       }
       //在服务器上下文环境中设置用户会话属性
       OCIAttrSet((dvoid *)phSvcCtx, OCI_HTYPE_SVCCTX,(dvoid *)phSession,(ub4) 0,OCI_ATTR_SESSION,phErr);

接下来进行数据插入：

//HYARRAYT型指针
 OCIType * ordinates_tdo =NULL;
//HYARRAYT的类型映射OCIARRAY
OCIArray * hyarray_Point =NULL;
//HYARRAYT的类型映射OCIARRAY
OCIArray * hyarray_MBR =NULL;
OCINumber oci_number;
//声明绑定
OCIBind* bndhp1 = NULL;
OCIBind* bndhp2 = NULL;
//创建用于描述SDO_ORDINATE_ARRAY类型的描述符
ordinates_tdo = get_tdo(SDO_ORDINATE_ARRAY);
//分配语句句柄
CheckErr(phErr,OCIHandleAlloc(phEnv,(void **)&phStmt,OCI_HTYPE_STMT,0,0));
string sql_str ="";
sql_str = "INSERT INTO "+table+" VALUES(:1,SDO_GEOMETRY(2002,NULL,NULL,SDO_ELEM_INFO_ARRAY( 1,2,1),:ordinates))"
text * insert = (text*)sql_str.c_str();
//准备SQL语句
    CheckErr(phErr,OCIStmtPrepare(phStmt,phErr,insert,strlen((char *)insert),OCI_NTV_SYNTAX,OCI_DEFAULT));

//变长数组类型的初始化，初始化SDO_ORDINATE_ARRAY
CheckErr(phErr,OCIObjectNew(phEnv,phErr,phSvcCtx,OCI_TYPECODE_VARRAY,ordinates_tdo,(dvoid*)NULL,OCI_DURATION_SESSION,FALSE,(dvoid**)&hyarray_Point));
vector<trackpoint> pointSet = track.getPointSet();
//输入坐标值 (linestring)
for (int i=0;i<pointSet.size();i++)
    {
        CheckErr(phErr,OCINumberFromReal(phErr,(dvoid*)&pointSet[i].lon,(uword)sizeof(double),&oci_number));
        CheckErr(phErr,OCICollAppend(phEnv,phErr,(dvoid*)&oci_number,(dvoid*)0,(OCIColl*)hyarray_Point));
        CheckErr(phErr,OCINumberFromReal(phErr,(dvoid*)&pointSet[i].lat,(uword)sizeof(double),&oci_number));
        CheckErr(phErr,OCICollAppend(phEnv,phErr,(dvoid*)&oci_number,(dvoid*)0,(OCIColl*)hyarray_Point));
    }
//绑定变量
CheckErr(phErr,OCIBindByPos(phStmt,&bndhp1,phErr,1,&nid,sizeof(int),SQLT_FLT,0,0,0,0,0,OCI_DEFAULT));
//变量绑定,此处为坐标
CheckErr(phErr,OCIBindByName(phStmt,&bndhp1,phErr,(text*)":ordinates",(sb4)-1,(dvoid*)0,(sb4)0,SQLT_NTY,(dvoid*)0,(ub2*)0,(ub2*)0,(ub4)0,(ub4*)0,(ub4)OCI_DEFAULT));
CheckErr(phErr,OCIBindObject(bndhp1,phErr,ordinates_tdo,(dvoid**)&hyarray_Point,(ub4*)0,(dvoid**)0,(ub4*)0));
//执行SQL语句
CheckErr(phErr,OCIStmtExecute(phSvcCtx,phStmt,phErr,1,0,NULL,NULL,OCI_DEFAULT));
OCITransCommit(phSvcCtx,phErr,(ub4)0);

应该注意的是Oracle spatial的SDO_GEOMETRY字段，它的构建方式为：

SDO_GEOMETRY(

      2002  --表示集合类型，此处为线串

      NULL  --表示坐标系

      NULL  -- 表示点的类型

      SDO_ELEM_INFO_ARRAY（1,2,1） --表示几何的属性

      SDO_ORDINATE_ARRAY   --表示几何类型的坐标

      (

            x1,x2,

            x3,x4,

            .....

            xn,xn+1

      )

)

以上具体可查看Oracle官方文档。