Working with Oracle spatial types ...
Few days ago, I saw thread in OTN Oracle Call Interface forum about how to handle Oracle Spacial SDO_GEOMETRY in OCI.
I decided to show how it could be done with OCILIB :)
Here is the writegeom sample app from Oracle rewritten with OCILIB :
-
number of lines reduced by factor 4-5
-
much easier code to read
-
the whole sdo_object is binded to the statement
Here is the code :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
#include "ocilib.h"
#define NB_ELEM 500
void error(OCI_Error *err)
{
printf("msg : %s\n", OCI_ErrorGetString(err));
exit(EXIT_FAILURE);
}
int main(int argc, char **argv)
{
OCI_Connection *cn;
OCI_Statement *st;
OCI_Object *obj_sdo;
OCI_Coll *coll_inf, *coll_ord;
OCI_Elem *elem_inf, *elem_ord;
OCI_TypeInfo *tif_inf, *tif_ord, *tif_sdo;
int i;
/* check command line */
if (argc < 3)
{
fprintf(stderr, "Usage: %s user password\n", argv[0]);
return EXIT_FAILURE;
}
/* init OCILIB */
if (OCI_Initialize(error, NULL, OCI_ENV_DEFAULT))
{
/* connect to oracle */
if (cn = OCI_ConnectionCreate(NULL, argv[1], argv[2], OCI_SESSION_DEFAULT))
{
printf ("\nConnected to Oracle.\n");
/* retreive type info */
tif_sdo = OCI_TypeInfoGet(cn, "MDSYS.SDO_GEOMETRY", OCI_TIF_TYPE);
tif_inf = OCI_TypeInfoGet(cn, "MDSYS.SDO_ELEM_INFO_ARRAY", OCI_TIF_TYPE);
tif_ord = OCI_TypeInfoGet(cn, "MDSYS.SDO_ORDINATE_ARRAY", OCI_TIF_TYPE);
/* create sdo object */
obj_sdo = OCI_ObjectCreate(cn, tif_sdo);
/* create sub arrays */
coll_inf = OCI_CollCreate(tif_inf);
coll_ord = OCI_CollCreate(tif_ord);
/* create sub array element accessors */
elem_inf = OCI_ElemCreate(tif_inf);
elem_ord = OCI_ElemCreate(tif_ord);
/* build ordinates collection with test values */
for (i = 0; i < NB_ELEM; i++)
{
OCI_ElemSetDouble(elem_ord, (double) i);
OCI_CollAppend(coll_ord, elem_ord);
OCI_CollAppend(coll_ord, elem_ord);
}
/* setup information collection attribute 'starting_offset' */
OCI_ElemSetUnsignedInt(elem_inf, 1);
OCI_CollAppend(coll_inf, elem_inf);
/* setup information collection attribute 'element_type' */
OCI_ElemSetUnsignedInt(elem_inf, 1);
OCI_CollAppend(coll_inf, elem_inf);
/* setup information collection attribute 'interpretation' */
OCI_ElemSetUnsignedInt(elem_inf, 1);
OCI_CollAppend(coll_inf, elem_inf);
/* set sdo object member attributes */
OCI_ObjectSetInt(obj_sdo, "SDO_GTYPE", 4);
OCI_ObjectSetNull(obj_sdo, "SDO_SRID");
OCI_ObjectSetNull(obj_sdo, "SDO_POINT");
OCI_ObjectSetColl(obj_sdo, "SDO_ELEM_INFO", coll_inf);
OCI_ObjectSetColl(obj_sdo, "SDO_ORDINATES", coll_ord);
/*create statement object */
st = OCI_StatementCreate(cn);
/* prepare, bind and excute statement then commit*/
OCI_Prepare(st, "INSERT INTO test_insert (gid, geometry) VALUES (1, :sdo)");
OCI_BindObject(st, "sdo", obj_sdo);
OCI_Execute(st);
OCI_Commit(cn);
/* free local objects */
OCI_ObjectFree(obj_sdo);
OCI_CollFree(coll_inf);
OCI_CollFree(coll_ord);
OCI_ElemFree(elem_inf);
OCI_ElemFree(elem_ord);
}
}
/* disconnect from oracle and cleanup OCILIB */
OCI_Cleanup();
printf ("\nDisconnected from Oracle.\n");
return EXIT_SUCCESS;
}