1 /******************************************************************************
2 * $Id: ogrocistatement.cpp 28809 2015-03-28 17:10:07Z rouault $
3 *
4 * Project: Oracle Spatial Driver
5 * Purpose: Implementation of OGROCIStatement, which encapsulates the
6 * preparation, executation and fetching from an SQL statement.
7 * Author: Frank Warmerdam, warmerdam@pobox.com
8 *
9 ******************************************************************************
10 * Copyright (c) 2002, Frank Warmerdam <warmerdam@pobox.com>
11 *
12 * Permission is hereby granted, free of charge, to any person obtaining a
13 * copy of this software and associated documentation files (the "Software"),
14 * to deal in the Software without restriction, including without limitation
15 * the rights to use, copy, modify, merge, publish, distribute, sublicense,
16 * and/or sell copies of the Software, and to permit persons to whom the
17 * Software is furnished to do so, subject to the following conditions:
18 *
19 * The above copyright notice and this permission notice shall be included
20 * in all copies or substantial portions of the Software.
21 *
22 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
23 * OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
24 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
25 * THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
26 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
27 * FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
28 * DEALINGS IN THE SOFTWARE.
29 ****************************************************************************/
30
31 #include "ogr_oci.h"
32 #include "cpl_conv.h"
33
34 CPL_CVSID("$Id: ogrocistatement.cpp 28809 2015-03-28 17:10:07Z rouault $");
35
36 /************************************************************************/
37 /* OGROCIStatement() */
38 /************************************************************************/
39
OGROCIStatement(OGROCISession * poSessionIn)40 OGROCIStatement::OGROCIStatement( OGROCISession *poSessionIn )
41
42 {
43 poSession = poSessionIn;
44 hStatement = NULL;
45 poDefn = NULL;
46
47 nRawColumnCount = 0;
48 papszCurColumn = NULL;
49 papszCurImage = NULL;
50 panCurColumnInd = NULL;
51 panFieldMap = NULL;
52
53 pszCommandText = NULL;
54 nAffectedRows = 0;
55 }
56
57 /************************************************************************/
58 /* ~OGROCIStatement() */
59 /************************************************************************/
60
~OGROCIStatement()61 OGROCIStatement::~OGROCIStatement()
62
63 {
64 Clean();
65 }
66
67 /************************************************************************/
68 /* Clean() */
69 /************************************************************************/
70
Clean()71 void OGROCIStatement::Clean()
72
73 {
74 int i;
75
76 CPLFree( pszCommandText );
77 pszCommandText = NULL;
78
79 if( papszCurColumn != NULL )
80 {
81 for( i = 0; papszCurColumn[i] != NULL; i++ )
82 CPLFree( papszCurColumn[i] );
83 }
84 CPLFree( papszCurColumn );
85 papszCurColumn = NULL;
86
87 CPLFree( papszCurImage );
88 papszCurImage = NULL;
89
90 CPLFree( panCurColumnInd );
91 panCurColumnInd = NULL;
92
93 CPLFree( panFieldMap );
94 panFieldMap = NULL;
95
96 if( poDefn != NULL && poDefn->Dereference() <= 0 )
97 {
98 delete poDefn;
99 poDefn = NULL;
100 }
101
102 if( hStatement != NULL )
103 {
104 OCIHandleFree((dvoid *)hStatement, (ub4)OCI_HTYPE_STMT);
105 hStatement = NULL;
106 }
107 }
108
109 /************************************************************************/
110 /* Prepare() */
111 /************************************************************************/
112
Prepare(const char * pszSQLStatement)113 CPLErr OGROCIStatement::Prepare( const char *pszSQLStatement )
114
115 {
116 Clean();
117
118 CPLDebug( "OCI", "Prepare(%s)", pszSQLStatement );
119
120 pszCommandText = CPLStrdup(pszSQLStatement);
121
122 if( hStatement != NULL )
123 {
124 CPLError( CE_Failure, CPLE_AppDefined,
125 "Statement already executed once on this OGROCIStatement." );
126 return CE_Failure;
127 }
128
129 /* -------------------------------------------------------------------- */
130 /* Allocate a statement handle. */
131 /* -------------------------------------------------------------------- */
132 if( poSession->Failed(
133 OCIHandleAlloc( poSession->hEnv, (dvoid **) &hStatement,
134 (ub4)OCI_HTYPE_STMT,(size_t)0, (dvoid **)0 ),
135 "OCIHandleAlloc(Statement)" ) )
136 return CE_Failure;
137
138 /* -------------------------------------------------------------------- */
139 /* Prepare the statement. */
140 /* -------------------------------------------------------------------- */
141 if( poSession->Failed(
142 OCIStmtPrepare( hStatement, poSession->hError,
143 (text *) pszSQLStatement, strlen(pszSQLStatement),
144 (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT ),
145 "OCIStmtPrepare" ) )
146 return CE_Failure;
147
148 return CE_None;
149 }
150
151 /************************************************************************/
152 /* BindObject() */
153 /************************************************************************/
154
BindObject(const char * pszPlaceName,void * pahObjects,OCIType * hTDO,void ** papIndicators)155 CPLErr OGROCIStatement::BindObject( const char *pszPlaceName,
156 void *pahObjects, OCIType *hTDO,
157 void **papIndicators )
158
159 {
160 OCIBind *hBindOrd = NULL;
161
162 if( poSession->Failed(
163 OCIBindByName( hStatement, &hBindOrd, poSession->hError,
164 (text *) pszPlaceName, (sb4) strlen(pszPlaceName),
165 (dvoid *) 0, (sb4) 0, SQLT_NTY, (dvoid *)0,
166 (ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0,
167 (ub4)OCI_DEFAULT),
168 "OCIBindByName()") )
169 return CE_Failure;
170
171 if( poSession->Failed(
172 OCIBindObject( hBindOrd, poSession->hError, hTDO,
173 (dvoid **) pahObjects, (ub4 *)0,
174 (dvoid **)papIndicators, (ub4 *)0),
175 "OCIBindObject()" ) )
176 return CE_Failure;
177
178 return CE_None;
179 }
180
181 /************************************************************************/
182 /* BindScalar() */
183 /************************************************************************/
184
BindScalar(const char * pszPlaceName,void * pData,int nDataLen,int nSQLType,sb2 * paeInd)185 CPLErr OGROCIStatement::BindScalar( const char *pszPlaceName,
186 void *pData, int nDataLen,
187 int nSQLType, sb2 *paeInd )
188
189 {
190 OCIBind *hBindOrd = NULL;
191
192 if( poSession->Failed(
193 OCIBindByName( hStatement, &hBindOrd, poSession->hError,
194 (text *) pszPlaceName, (sb4) strlen(pszPlaceName),
195 (dvoid *) pData, (sb4) nDataLen,
196 (ub2) nSQLType, (dvoid *)paeInd, (ub2 *)0,
197 (ub2 *)0, (ub4)0, (ub4 *)0,
198 (ub4)OCI_DEFAULT),
199 "OCIBindByName()") )
200 return CE_Failure;
201 else
202 return CE_None;
203 }
204
205 /************************************************************************/
206 /* Execute() */
207 /************************************************************************/
208
Execute(const char * pszSQLStatement,int nMode)209 CPLErr OGROCIStatement::Execute( const char *pszSQLStatement,
210 int nMode )
211
212 {
213 /* -------------------------------------------------------------------- */
214 /* Prepare the statement if it is being passed in. */
215 /* -------------------------------------------------------------------- */
216 if( pszSQLStatement != NULL )
217 {
218 CPLErr eErr = Prepare( pszSQLStatement );
219 if( eErr != CE_None )
220 return eErr;
221 }
222
223 if( hStatement == NULL )
224 {
225 CPLError( CE_Failure, CPLE_AppDefined,
226 "No prepared statement in call to OGROCIStatement::Execute(NULL)" );
227 return CE_Failure;
228 }
229
230 /* -------------------------------------------------------------------- */
231 /* Determine if this is a SELECT statement. */
232 /* -------------------------------------------------------------------- */
233 ub2 nStmtType;
234
235 if( poSession->Failed(
236 OCIAttrGet( hStatement, OCI_HTYPE_STMT,
237 &nStmtType, 0, OCI_ATTR_STMT_TYPE, poSession->hError ),
238 "OCIAttrGet(ATTR_STMT_TYPE)") )
239 return CE_Failure;
240
241 int bSelect = (nStmtType == OCI_STMT_SELECT);
242
243 /* -------------------------------------------------------------------- */
244 /* Work out some details about execution mode. */
245 /* -------------------------------------------------------------------- */
246 if( nMode == -1 )
247 {
248 if( bSelect )
249 nMode = OCI_DEFAULT;
250 else
251 nMode = OCI_COMMIT_ON_SUCCESS;
252 }
253
254 /* -------------------------------------------------------------------- */
255 /* Execute the statement. */
256 /* -------------------------------------------------------------------- */
257 if( poSession->Failed(
258 OCIStmtExecute( poSession->hSvcCtx, hStatement,
259 poSession->hError, (ub4)bSelect ? 0 : 1, (ub4)0,
260 (OCISnapshot *)NULL, (OCISnapshot *)NULL, nMode ),
261 pszCommandText ) )
262 return CE_Failure;
263
264 if( !bSelect )
265 {
266 ub4 row_count;
267 if( poSession->Failed(
268 OCIAttrGet( hStatement, OCI_HTYPE_STMT,
269 &row_count, 0, OCI_ATTR_ROW_COUNT, poSession->hError ),
270 "OCIAttrGet(OCI_ATTR_ROW_COUNT)") )
271 return CE_Failure;
272 nAffectedRows = row_count;
273
274 return CE_None;
275 }
276
277 /* -------------------------------------------------------------------- */
278 /* Count the columns. */
279 /* -------------------------------------------------------------------- */
280 for( nRawColumnCount = 0; TRUE; nRawColumnCount++ )
281 {
282 OCIParam *hParmDesc;
283
284 if( OCIParamGet( hStatement, OCI_HTYPE_STMT, poSession->hError,
285 (dvoid**)&hParmDesc,
286 (ub4) nRawColumnCount+1 ) != OCI_SUCCESS )
287 break;
288 }
289
290 panFieldMap = (int *) CPLCalloc(sizeof(int),nRawColumnCount);
291
292 papszCurColumn = (char **) CPLCalloc(sizeof(char*),nRawColumnCount+1);
293 panCurColumnInd = (sb2 *) CPLCalloc(sizeof(sb2),nRawColumnCount+1);
294
295 /* ==================================================================== */
296 /* Establish result column definitions, and setup parameter */
297 /* defines. */
298 /* ==================================================================== */
299 poDefn = new OGRFeatureDefn( pszCommandText );
300 poDefn->SetGeomType(wkbNone);
301 poDefn->Reference();
302
303 for( int iParm = 0; iParm < nRawColumnCount; iParm++ )
304 {
305 OGRFieldDefn oField( "", OFTString );
306 OCIParam *hParmDesc;
307 ub2 nOCIType;
308 ub4 nOCILen;
309
310 /* -------------------------------------------------------------------- */
311 /* Get parameter definition. */
312 /* -------------------------------------------------------------------- */
313 if( poSession->Failed(
314 OCIParamGet( hStatement, OCI_HTYPE_STMT, poSession->hError,
315 (dvoid**)&hParmDesc, (ub4) iParm+1 ),
316 "OCIParamGet") )
317 return CE_Failure;
318
319 if( poSession->GetParmInfo( hParmDesc, &oField, &nOCIType, &nOCILen )
320 != CE_None )
321 return CE_Failure;
322
323 if( oField.GetType() == OFTBinary )
324 {
325 /* We could probably generalize that, but at least it works in that */
326 /* use case */
327 if( EQUAL(oField.GetNameRef(), "DATA_DEFAULT") && nOCIType == SQLT_LNG )
328 {
329 oField.SetType(OFTString);
330 }
331 else
332 {
333 panFieldMap[iParm] = -1;
334 continue;
335 }
336 }
337
338 poDefn->AddFieldDefn( &oField );
339 panFieldMap[iParm] = poDefn->GetFieldCount() - 1;
340
341 /* -------------------------------------------------------------------- */
342 /* Prepare a binding. */
343 /* -------------------------------------------------------------------- */
344 int nBufWidth = 256, nOGRField = panFieldMap[iParm];
345 OCIDefine *hDefn = NULL;
346
347 if( oField.GetWidth() > 0 )
348 /* extra space needed for the decimal separator the string
349 terminator and the negative sign (Tamas Szekeres)*/
350 nBufWidth = oField.GetWidth() + 3;
351 else if( oField.GetType() == OFTInteger )
352 nBufWidth = 22;
353 else if( oField.GetType() == OFTReal )
354 nBufWidth = 36;
355 else if ( oField.GetType() == OFTDateTime )
356 nBufWidth = 40;
357 else if ( oField.GetType() == OFTDate )
358 nBufWidth = 20;
359
360 papszCurColumn[nOGRField] = (char *) CPLMalloc(nBufWidth+2);
361 CPLAssert( ((long) papszCurColumn[nOGRField]) % 2 == 0 );
362
363 if( poSession->Failed(
364 OCIDefineByPos( hStatement, &hDefn, poSession->hError,
365 iParm+1,
366 (ub1 *) papszCurColumn[nOGRField], nBufWidth,
367 SQLT_STR, panCurColumnInd + nOGRField,
368 NULL, NULL, OCI_DEFAULT ),
369 "OCIDefineByPos" ) )
370 return CE_Failure;
371 }
372
373 return CE_None;
374 }
375
376 /************************************************************************/
377 /* SimpleFetchRow() */
378 /************************************************************************/
379
SimpleFetchRow()380 char **OGROCIStatement::SimpleFetchRow()
381
382 {
383 int nStatus, i;
384
385 if( papszCurImage == NULL )
386 {
387 papszCurImage = (char **)
388 CPLCalloc(sizeof(char *), nRawColumnCount+1 );
389 }
390
391 nStatus = OCIStmtFetch( hStatement, poSession->hError, 1,
392 OCI_FETCH_NEXT, OCI_DEFAULT );
393
394 if( nStatus == OCI_NO_DATA )
395 return NULL;
396 else if( poSession->Failed( nStatus, "OCIStmtFetch" ) )
397 return NULL;
398
399 for( i = 0; papszCurColumn[i] != NULL; i++ )
400 {
401 if( panCurColumnInd[i] == OCI_IND_NULL )
402 papszCurImage[i] = NULL;
403 else
404 papszCurImage[i] = papszCurColumn[i];
405 }
406
407 return papszCurImage;
408 }
409
410