1 /******************************************************************************
2 *
3 * Project: Oracle Spatial Driver
4 * Purpose: Implementation of OGROCISession, which encapsulates much of the
5 * direct access to OCI.
6 * Author: Frank Warmerdam, warmerdam@pobox.com
7 *
8 ******************************************************************************
9 * Copyright (c) 2002, Frank Warmerdam <warmerdam@pobox.com>
10 *
11 * Permission is hereby granted, free of charge, to any person obtaining a
12 * copy of this software and associated documentation files (the "Software"),
13 * to deal in the Software without restriction, including without limitation
14 * the rights to use, copy, modify, merge, publish, distribute, sublicense,
15 * and/or sell copies of the Software, and to permit persons to whom the
16 * Software is furnished to do so, subject to the following conditions:
17 *
18 * The above copyright notice and this permission notice shall be included
19 * in all copies or substantial portions of the Software.
20 *
21 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
22 * OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
23 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
24 * THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
25 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
26 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
27 * DEALINGS IN THE SOFTWARE.
28 ****************************************************************************/
29
30 #include "ogr_oci.h"
31 #include "cpl_conv.h"
32
33 CPL_CVSID("$Id: ogrocisession.cpp bf3a27bbd1f495a7e83d7373545798549a9f813e 2021-05-04 14:06:34 -0400 fechen123 $")
34
35 /************************************************************************/
36 /* OGRGetOCISession() */
37 /************************************************************************/
38
OGRGetOCISession(const char * pszUserid,const char * pszPassword,const char * pszDatabase)39 OGROCISession * OGRGetOCISession( const char *pszUserid,
40 const char *pszPassword,
41 const char *pszDatabase )
42
43 {
44 OGROCISession *poSession;
45
46 poSession = new OGROCISession();
47 if( poSession->EstablishSession( pszUserid, pszPassword, pszDatabase ) )
48 return poSession;
49 else
50 {
51 delete poSession;
52 return nullptr;
53 }
54 }
55
56 /************************************************************************/
57 /* OGROCISession() */
58 /************************************************************************/
59
OGROCISession()60 OGROCISession::OGROCISession()
61
62 {
63 hEnv = nullptr;
64 hError = nullptr;
65 hSvcCtx = nullptr;
66 hServer = nullptr;
67 hSession = nullptr;
68 hDescribe = nullptr;
69 hGeometryTDO = nullptr;
70 hOrdinatesTDO = nullptr;
71 hElemInfoTDO = nullptr;
72 pszUserid = nullptr;
73 pszPassword = nullptr;
74 pszDatabase = nullptr;
75 nServerVersion = 10;
76 nServerRelease = 1;
77 nMaxNameLength = 30;
78 }
79
80 /************************************************************************/
81 /* ~OGROCISession() */
82 /************************************************************************/
83
~OGROCISession()84 OGROCISession::~OGROCISession()
85
86 {
87 if( hDescribe != nullptr )
88 OCIHandleFree((dvoid *)hDescribe, (ub4)OCI_HTYPE_DESCRIBE);
89
90 if( hSvcCtx != nullptr )
91 {
92 OCISessionEnd(hSvcCtx, hError, hSession, (ub4) 0);
93
94 if( hSvcCtx && hError)
95 OCIServerDetach(hServer, hError, (ub4) OCI_DEFAULT);
96
97 if( hServer )
98 OCIHandleFree((dvoid *) hServer, (ub4) OCI_HTYPE_SERVER);
99
100 if( hSvcCtx )
101 OCIHandleFree((dvoid *) hSvcCtx, (ub4) OCI_HTYPE_SVCCTX);
102
103 if( hError )
104 OCIHandleFree((dvoid *) hError, (ub4) OCI_HTYPE_ERROR);
105
106 if( hSession )
107 OCIHandleFree((dvoid *) hSession, (ub4) OCI_HTYPE_SESSION);
108
109 if( hEnv )
110 OCIHandleFree((dvoid *) hEnv, (ub4) OCI_HTYPE_ENV);
111 }
112
113 CPLFree( pszUserid );
114 CPLFree( pszPassword );
115 CPLFree( pszDatabase );
116 }
117
118 /************************************************************************/
119 /* EstablishSession() */
120 /************************************************************************/
121
EstablishSession(const char * pszUseridIn,const char * pszPasswordIn,const char * pszDatabaseIn)122 int OGROCISession::EstablishSession( const char *pszUseridIn,
123 const char *pszPasswordIn,
124 const char *pszDatabaseIn )
125
126 {
127 /* -------------------------------------------------------------------- */
128 /* Operational Systems's authentication option */
129 /* -------------------------------------------------------------------- */
130
131 //Setting to "/" for backward compatibility
132 const char* pszUser = "/";
133
134 ub4 eCred = OCI_CRED_RDBMS;
135
136 if( EQUAL(pszPasswordIn, "") &&
137 EQUAL(pszUseridIn, "") )
138 {
139 //user and password are ignored in this credential type.
140 eCred = OCI_CRED_EXT;
141 }
142 else
143 {
144 pszUser = pszUseridIn;
145 }
146
147 /* -------------------------------------------------------------------- */
148 /* Initialize Environment handler */
149 /* -------------------------------------------------------------------- */
150
151 if( Failed( OCIEnvCreate( (OCIEnv **) &hEnv, OCI_THREADED | OCI_OBJECT,
152 nullptr,
153 nullptr,
154 nullptr,
155 nullptr,
156 0,
157 nullptr ) ) )
158 {
159 return FALSE;
160 }
161
162 if( Failed( OCIHandleAlloc( (dvoid *) hEnv, (dvoid **) &hError,
163 OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) nullptr) ) )
164 {
165 return FALSE;
166 }
167
168 /* -------------------------------------------------------------------- */
169 /* Initialize Server Context */
170 /* -------------------------------------------------------------------- */
171
172 if( Failed( OCIHandleAlloc( (dvoid *) hEnv, (dvoid **) &hServer,
173 OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) nullptr) ) )
174 {
175 return FALSE;
176 }
177
178 if( Failed( OCIHandleAlloc( (dvoid *) hEnv, (dvoid **) &hSvcCtx,
179 OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) nullptr) ) )
180 {
181 return FALSE;
182 }
183
184 if( Failed( OCIServerAttach( hServer, hError, (text*) pszDatabaseIn,
185 static_cast<int>(strlen((char*) pszDatabaseIn)), 0) ) )
186 {
187 return FALSE;
188 }
189
190 /* -------------------------------------------------------------------- */
191 /* Initialize Service Context */
192 /* -------------------------------------------------------------------- */
193
194 if( Failed( OCIAttrSet( (dvoid *) hSvcCtx, OCI_HTYPE_SVCCTX, (dvoid *)hServer,
195 (ub4) 0, OCI_ATTR_SERVER, (OCIError *) hError) ) )
196 {
197 return FALSE;
198 }
199
200 if( Failed( OCIHandleAlloc((dvoid *) hEnv, (dvoid **)&hSession,
201 (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) nullptr) ) )
202 {
203 return FALSE;
204 }
205
206 if( Failed( OCIAttrSet((dvoid *) hSession, (ub4) OCI_HTYPE_SESSION,
207 (dvoid *) pszUser, (ub4) strlen((char *) pszUser),
208 (ub4) OCI_ATTR_USERNAME, hError) ) )
209 {
210 return FALSE;
211 }
212
213 if( Failed( OCIAttrSet((dvoid *) hSession, (ub4) OCI_HTYPE_SESSION,
214 (dvoid *) pszPasswordIn, (ub4) strlen((char *) pszPasswordIn),
215 (ub4) OCI_ATTR_PASSWORD, hError) ) )
216 {
217 return FALSE;
218 }
219
220 /* -------------------------------------------------------------------- */
221 /* Initialize Session */
222 /* -------------------------------------------------------------------- */
223
224 if( Failed( OCISessionBegin(hSvcCtx, hError, hSession, eCred,
225 (ub4) OCI_DEFAULT) ) )
226 {
227 CPLDebug("OCI", "OCISessionBegin() failed to initialize session");
228 return FALSE;
229 }
230
231 /* -------------------------------------------------------------------- */
232 /* Initialize Service */
233 /* -------------------------------------------------------------------- */
234
235 if( Failed( OCIAttrSet((dvoid *) hSvcCtx, (ub4) OCI_HTYPE_SVCCTX,
236 (dvoid *) hSession, (ub4) 0,
237 (ub4) OCI_ATTR_SESSION, hError) ) )
238 {
239 return FALSE;
240 }
241
242 /* -------------------------------------------------------------------- */
243 /* Create a describe handle. */
244 /* -------------------------------------------------------------------- */
245
246 if( Failed(
247 OCIHandleAlloc( hEnv, (dvoid **) &hDescribe, (ub4)OCI_HTYPE_DESCRIBE,
248 (size_t)0, (dvoid **)nullptr ),
249 "OCIHandleAlloc(Describe)" ) )
250 return FALSE;
251
252 /* -------------------------------------------------------------------- */
253 /* Try to get the MDSYS.SDO_GEOMETRY type object. */
254 /* -------------------------------------------------------------------- */
255 /* If we have no MDSYS.SDO_GEOMETRY then we consider we are
256 working along with the VRT driver and access non spatial tables.
257 See #2202 for more details (Tamas Szekeres)*/
258 if (OCIDescribeAny(hSvcCtx, hError,
259 (text *) SDO_GEOMETRY, (ub4) strlen(SDO_GEOMETRY),
260 OCI_OTYPE_NAME, (ub1) OCI_DEFAULT, (ub1)OCI_PTYPE_TYPE,
261 hDescribe ) != OCI_ERROR)
262 {
263 hGeometryTDO = PinTDO( SDO_GEOMETRY );
264 if( hGeometryTDO == nullptr )
265 return FALSE;
266
267 /* -------------------------------------------------------------------- */
268 /* Try to get the MDSYS.SDO_ORDINATE_ARRAY type object. */
269 /* -------------------------------------------------------------------- */
270 hOrdinatesTDO = PinTDO( "MDSYS.SDO_ORDINATE_ARRAY" );
271 if( hOrdinatesTDO == nullptr )
272 return FALSE;
273
274 /* -------------------------------------------------------------------- */
275 /* Try to get the MDSYS.SDO_ELEM_INFO_ARRAY type object. */
276 /* -------------------------------------------------------------------- */
277 hElemInfoTDO = PinTDO( "MDSYS.SDO_ELEM_INFO_ARRAY" );
278 if( hElemInfoTDO == nullptr )
279 return FALSE;
280 }
281 /* -------------------------------------------------------------------- */
282 /* Record information about the session. */
283 /* -------------------------------------------------------------------- */
284 pszUserid = CPLStrdup(pszUseridIn);
285 pszPassword = CPLStrdup(pszPasswordIn);
286 pszDatabase = CPLStrdup(pszDatabaseIn);
287
288 /* -------------------------------------------------------------------- */
289 /* Get server version information */
290 /* -------------------------------------------------------------------- */
291
292 char szVersionTxt[256];
293
294 OCIServerVersion( hSvcCtx, hError, (text*) szVersionTxt,
295 (ub4) sizeof(szVersionTxt), (ub1) OCI_HTYPE_SVCCTX );
296
297 char** papszNameValue = CSLTokenizeString2( szVersionTxt, " .",
298 CSLT_STRIPLEADSPACES );
299
300 int count = CSLCount( papszNameValue);
301
302 for( int i = 0; i < count; i++)
303 {
304 if( EQUAL(papszNameValue[i], "Release") )
305 {
306 if( i + 1 < count )
307 {
308 nServerVersion = atoi(papszNameValue[i + 1]);
309 }
310 if( i + 2 < count )
311 {
312 nServerRelease = atoi(papszNameValue[i + 2]);
313 }
314 break;
315 }
316 }
317
318 CPLDebug("OCI", "From '%s' :", szVersionTxt);
319 CPLDebug("OCI", "Version:%d", nServerVersion);
320 CPLDebug("OCI", "Release:%d", nServerRelease);
321
322 /* -------------------------------------------------------------------- */
323 /* Set maximum name length (before 12.2 ? 30 : 128) */
324 /* -------------------------------------------------------------------- */
325
326 if( nServerVersion > 12 || (nServerVersion == 12 && nServerRelease >= 2) )
327 {
328 nMaxNameLength = 128;
329 }
330
331 CSLDestroy( papszNameValue );
332
333 /* -------------------------------------------------------------------- */
334 /* Setting up the OGR compatible time formatting rules. */
335 /* -------------------------------------------------------------------- */
336 OGROCIStatement oSetNLSTimeFormat( this );
337 if( oSetNLSTimeFormat.Execute( "ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD' \
338 NLS_TIME_FORMAT='HH24:MI:SS' NLS_TIME_TZ_FORMAT='HH24:MI:SS TZHTZM' \
339 NLS_TIMESTAMP_FORMAT='YYYY/MM/DD HH24:MI:SS' \
340 NLS_TIMESTAMP_TZ_FORMAT='YYYY/MM/DD HH24:MI:SS TZHTZM' \
341 NLS_NUMERIC_CHARACTERS = '. '" ) != CE_None )
342 return OGRERR_FAILURE;
343
344 return TRUE;
345 }
346
347 /************************************************************************/
348 /* Failed() */
349 /************************************************************************/
350
Failed(sword nStatus,const char * pszFunction)351 int OGROCISession::Failed( sword nStatus, const char *pszFunction )
352
353 {
354 if( pszFunction == nullptr )
355 pszFunction = "<unnamed>";
356 if( nStatus == OCI_ERROR )
357 {
358 sb4 nErrCode = 0;
359 char szErrorMsg[10000];
360
361 szErrorMsg[0] = '\0';
362 if( hError != nullptr )
363 {
364 OCIErrorGet( (dvoid *) hError, (ub4) 1, nullptr, &nErrCode,
365 (text *) szErrorMsg, (ub4) sizeof(szErrorMsg),
366 OCI_HTYPE_ERROR );
367 }
368 szErrorMsg[sizeof(szErrorMsg)-1] = '\0';
369
370 CPLError( CE_Failure, CPLE_AppDefined,
371 "%s in %s", szErrorMsg, pszFunction );
372 return TRUE;
373 }
374 else if( nStatus == OCI_NEED_DATA )
375 {
376 CPLError( CE_Failure, CPLE_AppDefined,
377 "OCI_NEED_DATA" );
378 return TRUE;
379 }
380 else if( nStatus == OCI_INVALID_HANDLE )
381 {
382 CPLError( CE_Failure, CPLE_AppDefined,
383 "OCI_INVALID_HANDLE in %s", pszFunction );
384 return TRUE;
385 }
386 else if( nStatus == OCI_STILL_EXECUTING )
387 {
388 CPLError( CE_Failure, CPLE_AppDefined,
389 "OCI_STILL_EXECUTING in %s", pszFunction );
390 return TRUE;
391 }
392 else if( nStatus == OCI_CONTINUE )
393 {
394 CPLError( CE_Failure, CPLE_AppDefined,
395 "OCI_CONTINUE in %s", pszFunction );
396 return TRUE;
397 }
398 else
399 return FALSE;
400 }
401
402 /************************************************************************/
403 /* GetParamInfo() */
404 /************************************************************************/
405
406 CPLErr
GetParamInfo(OCIParam * hParamDesc,OGRFieldDefn * poOGRDefn,ub2 * pnOCIType,ub4 * pnOCILen)407 OGROCISession::GetParamInfo( OCIParam *hParamDesc, OGRFieldDefn *poOGRDefn,
408 ub2 *pnOCIType, ub4 *pnOCILen )
409
410 {
411 ub2 nOCIType, nOCILen;
412 ub4 nColLen;
413 ub1 bOCINull;
414 char *pszColName;
415 char szTermColName[128];
416
417 /* -------------------------------------------------------------------- */
418 /* Get basic parameter details. */
419 /* -------------------------------------------------------------------- */
420 if( Failed(
421 OCIAttrGet( hParamDesc, OCI_DTYPE_PARAM,
422 &nOCIType, nullptr, OCI_ATTR_DATA_TYPE, hError ),
423 "OCIAttrGet(Type)" ) )
424 return CE_Failure;
425
426 if( Failed(
427 OCIAttrGet( hParamDesc, OCI_DTYPE_PARAM,
428 &nOCILen, nullptr, OCI_ATTR_DATA_SIZE, hError ),
429 "OCIAttrGet(Size)" ) )
430 return CE_Failure;
431
432 if( Failed(
433 OCIAttrGet( hParamDesc, OCI_DTYPE_PARAM, &pszColName,
434 &nColLen, OCI_ATTR_NAME, hError ),
435 "OCIAttrGet(Name)") )
436 return CE_Failure;
437
438 if( Failed(
439 OCIAttrGet( hParamDesc, OCI_DTYPE_PARAM, &bOCINull,
440 nullptr, OCI_ATTR_IS_NULL, hError ),
441 "OCIAttrGet(Null)") )
442 return CE_Failure;
443
444 if( nColLen >= sizeof(szTermColName) )
445 {
446 CPLError( CE_Failure, CPLE_AppDefined,
447 "Column length (%d) longer than column name buffer (%d) in\n"
448 "OGROCISession::GetParamInfo()",
449 nColLen, (int) sizeof(szTermColName) );
450 return CE_Failure;
451 }
452
453 strncpy( szTermColName, pszColName, nColLen );
454 szTermColName[nColLen] = '\0';
455
456 poOGRDefn->SetName( szTermColName );
457 poOGRDefn->SetNullable( bOCINull );
458
459 /* -------------------------------------------------------------------- */
460 /* Attempt to classify as an OGRType. */
461 /* -------------------------------------------------------------------- */
462 switch( nOCIType )
463 {
464 case SQLT_CHR:
465 case SQLT_AFC: /* CHAR(), NCHAR() */
466 poOGRDefn->SetType( OFTString );
467 if( nOCILen <= 4000 )
468 poOGRDefn->SetWidth( nOCILen );
469 break;
470
471 case SQLT_NUM:
472 {
473 // NOTE: OCI docs say this should be ub1 type, but we have
474 // determined that oracle is actually returning a short so we
475 // use that type and try to compensate for possible problems by
476 // initializing, and dividing by 256 if it is large.
477 unsigned short byPrecision = 0;
478 sb1 nScale;
479
480 if( Failed(
481 OCIAttrGet( hParamDesc, OCI_DTYPE_PARAM, &byPrecision,
482 nullptr, OCI_ATTR_PRECISION, hError ),
483 "OCIAttrGet(Precision)" ) )
484 return CE_Failure;
485 if( Failed(
486 OCIAttrGet( hParamDesc, OCI_DTYPE_PARAM, &nScale,
487 nullptr, OCI_ATTR_SCALE, hError ),
488 "OCIAttrGet(Scale)") )
489 return CE_Failure;
490 #ifdef notdef
491 CPLDebug( "OCI", "%s: Scale=%d, Precision=%d",
492 szTermColName, nScale, byPrecision );
493 #endif
494 if( byPrecision > 255 )
495 byPrecision = byPrecision / 256;
496
497 if( nScale < 0 )
498 poOGRDefn->SetType( OFTReal );
499 else if( nScale > 0 )
500 {
501 poOGRDefn->SetType( OFTReal );
502 poOGRDefn->SetWidth( byPrecision );
503 poOGRDefn->SetPrecision( nScale );
504 }
505 else if( byPrecision < 38 )
506 {
507 poOGRDefn->SetType( (byPrecision < 10) ? OFTInteger : OFTInteger64 );
508 poOGRDefn->SetWidth( byPrecision );
509 }
510 else
511 {
512 poOGRDefn->SetType( OFTInteger64 );
513 }
514 }
515 break;
516
517 case SQLT_DAT:
518 case SQLT_DATE:
519 poOGRDefn->SetType( OFTDate );
520 break;
521 case SQLT_TIMESTAMP:
522 case SQLT_TIMESTAMP_TZ:
523 case SQLT_TIMESTAMP_LTZ:
524 case SQLT_TIME:
525 case SQLT_TIME_TZ:
526 poOGRDefn->SetType( OFTDateTime );
527 break;
528
529 case SQLT_RID:
530 case SQLT_BIN:
531 case SQLT_LBI:
532 case 111: /* REF */
533 case SQLT_CLOB:
534 case SQLT_BLOB:
535 case SQLT_FILE:
536 case 208: /* UROWID */
537 poOGRDefn->SetType( OFTBinary );
538 break;
539
540 default:
541 poOGRDefn->SetType( OFTBinary );
542 break;
543 }
544
545 if( pnOCIType != nullptr )
546 *pnOCIType = nOCIType;
547
548 if( pnOCILen != nullptr )
549 *pnOCILen = nOCILen;
550
551 return CE_None;
552 }
553
554 /************************************************************************/
555 /* CleanName() */
556 /* */
557 /* Modify a name in-place to be a well formed Oracle name. */
558 /************************************************************************/
559
CleanName(char * pszName)560 void OGROCISession::CleanName( char * pszName )
561
562 {
563 int i;
564
565 if( strlen(pszName) > nMaxNameLength )
566 pszName[nMaxNameLength] = '\0';
567
568 for( i = 0; pszName[i] != '\0'; i++ )
569 {
570 pszName[i] = static_cast<char>(toupper(pszName[i]));
571
572 if( (pszName[i] < '0' || pszName[i] > '9')
573 && (pszName[i] < 'A' || pszName[i] > 'Z')
574 && pszName[i] != '_' )
575 pszName[i] = '_';
576 }
577 }
578
579 /************************************************************************/
580 /* PinTDO() */
581 /* */
582 /* Fetch a Type Description Object for the named type. */
583 /************************************************************************/
584
PinTDO(const char * pszType)585 OCIType *OGROCISession::PinTDO( const char *pszType )
586
587 {
588 OCIParam *hGeomParam = nullptr;
589 OCIRef *hGeomTypeRef = nullptr;
590 OCIType *hPinnedTDO = nullptr;
591
592 if( Failed(
593 OCIDescribeAny(hSvcCtx, hError,
594 (text *) pszType, (ub4) strlen(pszType),
595 OCI_OTYPE_NAME, (ub1)1, (ub1)OCI_PTYPE_TYPE,
596 hDescribe ),
597 "GetTDO()->OCIDescribeAny()" ) )
598 return nullptr;
599
600 if( Failed(
601 OCIAttrGet((dvoid *)hDescribe, (ub4)OCI_HTYPE_DESCRIBE,
602 (dvoid *)&hGeomParam, (ub4 *)nullptr, (ub4)OCI_ATTR_PARAM,
603 hError), "GetTDO()->OCIGetAttr(ATTR_PARAM)") )
604 return nullptr;
605
606 if( Failed(
607 OCIAttrGet((dvoid *)hGeomParam, (ub4)OCI_DTYPE_PARAM,
608 (dvoid *)&hGeomTypeRef, (ub4 *)nullptr, (ub4)OCI_ATTR_REF_TDO,
609 hError), "GetTDO()->OCIAttrGet(ATTR_REF_TDO)" ) )
610 return nullptr;
611
612 if( Failed(
613 OCIObjectPin(hEnv, hError, hGeomTypeRef, (OCIComplexObject *)nullptr,
614 OCI_PIN_ANY, OCI_DURATION_SESSION,
615 OCI_LOCK_NONE, (dvoid **)&hPinnedTDO ),
616 "GetTDO()->OCIObjectPin()" ) )
617 return nullptr;
618
619 return hPinnedTDO;
620 }
621