1 /******************************************************************************
2 *
3 * Project: Oracle Spatial Driver
4 * Purpose: Implementation of OGROCIStatement, which encapsulates the
5 * preparation, execution and fetching from an SQL statement.
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: ogrocistatement.cpp fa752ad6eabafaf630a704e1892a9d837d683cb3 2021-03-06 17:04:38 +0100 Even Rouault $")
34
35 /************************************************************************/
36 /* OGROCIStatement() */
37 /************************************************************************/
38
OGROCIStatement(OGROCISession * poSessionIn)39 OGROCIStatement::OGROCIStatement( OGROCISession *poSessionIn )
40
41 {
42 poSession = poSessionIn;
43 hStatement = nullptr;
44 poDefn = nullptr;
45
46 nRawColumnCount = 0;
47 papszCurColumn = nullptr;
48 papszCurImage = nullptr;
49 panCurColumnInd = nullptr;
50 panFieldMap = nullptr;
51
52 pszCommandText = nullptr;
53 nAffectedRows = 0;
54 }
55
56 /************************************************************************/
57 /* ~OGROCIStatement() */
58 /************************************************************************/
59
~OGROCIStatement()60 OGROCIStatement::~OGROCIStatement()
61
62 {
63 Clean();
64 }
65
66 /************************************************************************/
67 /* Clean() */
68 /************************************************************************/
69
Clean()70 void OGROCIStatement::Clean()
71
72 {
73 int i;
74
75 CPLFree( pszCommandText );
76 pszCommandText = nullptr;
77
78 if( papszCurColumn != nullptr )
79 {
80 for( i = 0; papszCurColumn[i] != nullptr; i++ )
81 CPLFree( papszCurColumn[i] );
82 }
83 CPLFree( papszCurColumn );
84 papszCurColumn = nullptr;
85
86 CPLFree( papszCurImage );
87 papszCurImage = nullptr;
88
89 CPLFree( panCurColumnInd );
90 panCurColumnInd = nullptr;
91
92 CPLFree( panFieldMap );
93 panFieldMap = nullptr;
94
95 if( poDefn != nullptr && poDefn->Dereference() <= 0 )
96 {
97 delete poDefn;
98 poDefn = nullptr;
99 }
100
101 if( hStatement != nullptr )
102 {
103 OCIHandleFree((dvoid *)hStatement, (ub4)OCI_HTYPE_STMT);
104 hStatement = nullptr;
105 }
106 }
107
108 /************************************************************************/
109 /* Prepare() */
110 /************************************************************************/
111
Prepare(const char * pszSQLStatement)112 CPLErr OGROCIStatement::Prepare( const char *pszSQLStatement )
113
114 {
115 Clean();
116
117 CPLDebug( "OCI", "Prepare(%s)", pszSQLStatement );
118
119 pszCommandText = CPLStrdup(pszSQLStatement);
120
121 if( hStatement != nullptr )
122 {
123 CPLError( CE_Failure, CPLE_AppDefined,
124 "Statement already executed once on this OGROCIStatement." );
125 return CE_Failure;
126 }
127
128 /* -------------------------------------------------------------------- */
129 /* Allocate a statement handle. */
130 /* -------------------------------------------------------------------- */
131 if( poSession->Failed(
132 OCIHandleAlloc( poSession->hEnv, (dvoid **) &hStatement,
133 (ub4)OCI_HTYPE_STMT,(size_t)0, (dvoid **)nullptr ),
134 "OCIHandleAlloc(Statement)" ) )
135 return CE_Failure;
136
137 /* -------------------------------------------------------------------- */
138 /* Prepare the statement. */
139 /* -------------------------------------------------------------------- */
140 if( poSession->Failed(
141 OCIStmtPrepare( hStatement, poSession->hError,
142 (text *) pszSQLStatement, static_cast<unsigned int>(strlen(pszSQLStatement)),
143 (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT ),
144 "OCIStmtPrepare" ) )
145 return CE_Failure;
146
147 return CE_None;
148 }
149
150 /************************************************************************/
151 /* BindObject() */
152 /************************************************************************/
153
BindObject(const char * pszPlaceName,void * pahObjects,OCIType * hTDO,void ** papIndicators)154 CPLErr OGROCIStatement::BindObject( const char *pszPlaceName,
155 void *pahObjects, OCIType *hTDO,
156 void **papIndicators )
157
158 {
159 OCIBind *hBindOrd = nullptr;
160
161 if( poSession->Failed(
162 OCIBindByName( hStatement, &hBindOrd, poSession->hError,
163 (text *) pszPlaceName, (sb4) strlen(pszPlaceName),
164 (dvoid *) nullptr, (sb4) 0, SQLT_NTY, (dvoid *)nullptr,
165 (ub2 *)nullptr, (ub2 *)nullptr, (ub4)0, (ub4 *)nullptr,
166 (ub4)OCI_DEFAULT),
167 "OCIBindByName()") )
168 return CE_Failure;
169
170 if( poSession->Failed(
171 OCIBindObject( hBindOrd, poSession->hError, hTDO,
172 (dvoid **) pahObjects, (ub4 *)nullptr,
173 (dvoid **)papIndicators, (ub4 *)nullptr),
174 "OCIBindObject()" ) )
175 return CE_Failure;
176
177 return CE_None;
178 }
179
180 /************************************************************************/
181 /* BindScalar() */
182 /************************************************************************/
183
BindScalar(const char * pszPlaceName,void * pData,int nDataLen,int nSQLType,sb2 * paeInd)184 CPLErr OGROCIStatement::BindScalar( const char *pszPlaceName,
185 void *pData, int nDataLen,
186 int nSQLType, sb2 *paeInd )
187
188 {
189 OCIBind *hBindOrd = nullptr;
190
191 if( poSession->Failed(
192 OCIBindByName( hStatement, &hBindOrd, poSession->hError,
193 (text *) pszPlaceName, (sb4) strlen(pszPlaceName),
194 (dvoid *) pData, (sb4) nDataLen,
195 (ub2) nSQLType, (dvoid *)paeInd, (ub2 *)nullptr,
196 (ub2 *)nullptr, (ub4)0, (ub4 *)nullptr,
197 (ub4)OCI_DEFAULT),
198 "OCIBindByName()") )
199 return CE_Failure;
200 else
201 return CE_None;
202 }
203
204 /************************************************************************/
205 /* BindString() */
206 /************************************************************************/
207
BindString(const char * pszPlaceName,const char * pszData,sb2 * paeInd)208 CPLErr OGROCIStatement::BindString( const char *pszPlaceName,
209 const char *pszData, sb2 *paeInd )
210
211 {
212 return BindScalar(
213 pszPlaceName, const_cast<void*>(reinterpret_cast<const void*>(pszData)),
214 static_cast<int>(strlen(pszData)) + 1, SQLT_STR , paeInd);
215 }
216
217 /************************************************************************/
218 /* Execute() */
219 /************************************************************************/
220
Execute(const char * pszSQLStatement,int nMode)221 CPLErr OGROCIStatement::Execute( const char *pszSQLStatement,
222 int nMode )
223
224 {
225 /* -------------------------------------------------------------------- */
226 /* Prepare the statement if it is being passed in. */
227 /* -------------------------------------------------------------------- */
228 if( pszSQLStatement != nullptr )
229 {
230 CPLErr eErr = Prepare( pszSQLStatement );
231 if( eErr != CE_None )
232 return eErr;
233 }
234
235 if( hStatement == nullptr )
236 {
237 CPLError( CE_Failure, CPLE_AppDefined,
238 "No prepared statement in call to OGROCIStatement::Execute(NULL)" );
239 return CE_Failure;
240 }
241
242 /* -------------------------------------------------------------------- */
243 /* Determine if this is a SELECT statement. */
244 /* -------------------------------------------------------------------- */
245 ub2 nStmtType;
246
247 if( poSession->Failed(
248 OCIAttrGet( hStatement, OCI_HTYPE_STMT,
249 &nStmtType, nullptr, OCI_ATTR_STMT_TYPE, poSession->hError ),
250 "OCIAttrGet(ATTR_STMT_TYPE)") )
251 return CE_Failure;
252
253 int bSelect = (nStmtType == OCI_STMT_SELECT);
254
255 /* -------------------------------------------------------------------- */
256 /* Work out some details about execution mode. */
257 /* -------------------------------------------------------------------- */
258 if( nMode == -1 )
259 {
260 if( bSelect )
261 nMode = OCI_DEFAULT;
262 else
263 nMode = OCI_COMMIT_ON_SUCCESS;
264 }
265
266 /* -------------------------------------------------------------------- */
267 /* Execute the statement. */
268 /* -------------------------------------------------------------------- */
269 if( poSession->Failed(
270 OCIStmtExecute( poSession->hSvcCtx, hStatement,
271 poSession->hError, (ub4)bSelect ? 0 : 1, (ub4)0,
272 (OCISnapshot *)nullptr, (OCISnapshot *)nullptr, nMode ),
273 pszCommandText ) )
274 return CE_Failure;
275
276 if( !bSelect )
277 {
278 ub4 row_count;
279 if( poSession->Failed(
280 OCIAttrGet( hStatement, OCI_HTYPE_STMT,
281 &row_count, nullptr, OCI_ATTR_ROW_COUNT, poSession->hError ),
282 "OCIAttrGet(OCI_ATTR_ROW_COUNT)") )
283 return CE_Failure;
284 nAffectedRows = row_count;
285
286 return CE_None;
287 }
288
289 /* -------------------------------------------------------------------- */
290 /* Count the columns. */
291 /* -------------------------------------------------------------------- */
292 for( nRawColumnCount = 0; true; nRawColumnCount++ )
293 {
294 OCIParam *hParamDesc;
295
296 if( OCIParamGet( hStatement, OCI_HTYPE_STMT, poSession->hError,
297 (dvoid**)&hParamDesc,
298 (ub4) nRawColumnCount+1 ) != OCI_SUCCESS )
299 break;
300 }
301
302 panFieldMap = (int *) CPLCalloc(sizeof(int),nRawColumnCount);
303
304 papszCurColumn = (char **) CPLCalloc(sizeof(char*),nRawColumnCount+1);
305 panCurColumnInd = (sb2 *) CPLCalloc(sizeof(sb2),nRawColumnCount+1);
306
307 /* ==================================================================== */
308 /* Establish result column definitions, and setup parameter */
309 /* defines. */
310 /* ==================================================================== */
311 poDefn = new OGRFeatureDefn( pszCommandText );
312 poDefn->SetGeomType(wkbNone);
313 poDefn->Reference();
314
315 for( int iParam = 0; iParam < nRawColumnCount; iParam++ )
316 {
317 OGRFieldDefn oField( "", OFTString );
318 OCIParam *hParamDesc;
319 ub2 nOCIType;
320 ub4 nOCILen;
321
322 /* -------------------------------------------------------------------- */
323 /* Get parameter definition. */
324 /* -------------------------------------------------------------------- */
325 if( poSession->Failed(
326 OCIParamGet( hStatement, OCI_HTYPE_STMT, poSession->hError,
327 (dvoid**)&hParamDesc, (ub4) iParam+1 ),
328 "OCIParamGet") )
329 return CE_Failure;
330
331 if( poSession->GetParamInfo( hParamDesc, &oField, &nOCIType, &nOCILen )
332 != CE_None )
333 return CE_Failure;
334
335 if( oField.GetType() == OFTBinary )
336 {
337 /* We could probably generalize that, but at least it works in that */
338 /* use case */
339 if( EQUAL(oField.GetNameRef(), "DATA_DEFAULT") && nOCIType == SQLT_LNG )
340 {
341 oField.SetType(OFTString);
342 }
343 else
344 {
345 panFieldMap[iParam] = -1;
346 continue;
347 }
348 }
349
350 poDefn->AddFieldDefn( &oField );
351 panFieldMap[iParam] = poDefn->GetFieldCount() - 1;
352
353 /* -------------------------------------------------------------------- */
354 /* Prepare a binding. */
355 /* -------------------------------------------------------------------- */
356 int nBufWidth = 256, nOGRField = panFieldMap[iParam];
357 OCIDefine *hDefn = nullptr;
358
359 if( oField.GetWidth() > 0 )
360 /* extra space needed for the decimal separator the string
361 terminator and the negative sign (Tamas Szekeres)*/
362 nBufWidth = oField.GetWidth() + 3;
363 else if( oField.GetType() == OFTInteger )
364 nBufWidth = 22;
365 else if( oField.GetType() == OFTReal )
366 nBufWidth = 36;
367 else if ( oField.GetType() == OFTDateTime )
368 nBufWidth = 40;
369 else if ( oField.GetType() == OFTDate )
370 nBufWidth = 20;
371
372 papszCurColumn[nOGRField] = (char *) CPLMalloc(nBufWidth+2);
373 CPLAssert( ((long) papszCurColumn[nOGRField]) % 2 == 0 );
374
375 if( poSession->Failed(
376 OCIDefineByPos( hStatement, &hDefn, poSession->hError,
377 iParam+1,
378 (ub1 *) papszCurColumn[nOGRField], nBufWidth,
379 SQLT_STR, panCurColumnInd + nOGRField,
380 nullptr, nullptr, OCI_DEFAULT ),
381 "OCIDefineByPos" ) )
382 return CE_Failure;
383 }
384
385 return CE_None;
386 }
387
388 /************************************************************************/
389 /* SimpleFetchRow() */
390 /************************************************************************/
391
SimpleFetchRow()392 char **OGROCIStatement::SimpleFetchRow()
393
394 {
395 int nStatus, i;
396
397 if( papszCurImage == nullptr )
398 {
399 papszCurImage = (char **)
400 CPLCalloc(sizeof(char *), nRawColumnCount+1 );
401 }
402
403 nStatus = OCIStmtFetch( hStatement, poSession->hError, 1,
404 OCI_FETCH_NEXT, OCI_DEFAULT );
405
406 if( nStatus == OCI_NO_DATA )
407 return nullptr;
408 else if( poSession->Failed( nStatus, "OCIStmtFetch" ) )
409 return nullptr;
410
411 for( i = 0; papszCurColumn[i] != nullptr; i++ )
412 {
413 if( panCurColumnInd[i] == OCI_IND_NULL )
414 papszCurImage[i] = nullptr;
415 else
416 papszCurImage[i] = papszCurColumn[i];
417 }
418
419 return papszCurImage;
420 }
421