1 /******************************************************************************
2 *
3 * Project: OGR ODBC Driver
4 * Purpose: Declarations for ODBC Access Cover API.
5 * Author: Frank Warmerdam, warmerdam@pobox.com
6 *
7 ******************************************************************************
8 * Copyright (c) 2003, Frank Warmerdam
9 * Copyright (c) 2008, Even Rouault <even dot rouault at spatialys.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 <wchar.h>
31
32 #include "cpl_odbc.h"
33 #include "cpl_vsi.h"
34 #include "cpl_string.h"
35 #include "cpl_error.h"
36
37 CPL_CVSID("$Id: cpl_odbc.cpp 02d450524dbd32158b8cbe30261d4ea618404c79 2021-07-03 18:42:13 +0200 Even Rouault $")
38
39 #ifndef SQLColumns_TABLE_CAT
40 #define SQLColumns_TABLE_CAT 1
41 #define SQLColumns_TABLE_SCHEM 2
42 #define SQLColumns_TABLE_NAME 3
43 #define SQLColumns_COLUMN_NAME 4
44 #define SQLColumns_DATA_TYPE 5
45 #define SQLColumns_TYPE_NAME 6
46 #define SQLColumns_COLUMN_SIZE 7
47 #define SQLColumns_BUFFER_LENGTH 8
48 #define SQLColumns_DECIMAL_DIGITS 9
49 #define SQLColumns_NUM_PREC_RADIX 10
50 #define SQLColumns_NULLABLE 11
51 #define SQLColumns_REMARKS 12
52 #define SQLColumns_COLUMN_DEF 13
53 #define SQLColumns_SQL_DATA_TYPE 14
54 #define SQLColumns_SQL_DATETIME_SUB 15
55 #define SQLColumns_CHAR_OCTET_LENGTH 16
56 #define SQLColumns_ORDINAL_POSITION 17
57 #define SQLColumns_IS_NULLABLE 18
58 #endif // ndef SQLColumns_TABLE_CAT
59
60 /************************************************************************/
61 /* CPLODBCDriverInstaller() */
62 /************************************************************************/
63
CPLODBCDriverInstaller()64 CPLODBCDriverInstaller::CPLODBCDriverInstaller() :
65 m_nErrorCode(0),
66 m_nUsageCount(0)
67 {
68 memset( m_szPathOut, '\0', ODBC_FILENAME_MAX );
69 memset( m_szError, '\0', SQL_MAX_MESSAGE_LENGTH );
70 }
71
72 /************************************************************************/
73 /* InstallDriver() */
74 /************************************************************************/
75
InstallDriver(const char * pszDriver,CPL_UNUSED const char * pszPathIn,WORD fRequest)76 int CPLODBCDriverInstaller::InstallDriver( const char* pszDriver,
77 CPL_UNUSED const char* pszPathIn,
78 WORD fRequest )
79 {
80 CPLAssert( nullptr != pszDriver );
81
82 // Try to install driver to system-wide location.
83 if( FALSE == SQLInstallDriverEx( pszDriver, nullptr, m_szPathOut,
84 ODBC_FILENAME_MAX, nullptr, fRequest,
85 &m_nUsageCount ) )
86 {
87 const WORD nErrorNum = 1; // TODO - a function param?
88
89 // Failure is likely related to no write permissions to
90 // system-wide default location, so try to install to HOME.
91
92 static char* pszEnvIni = nullptr;
93
94 // Read HOME location.
95 const char* pszEnvHome = getenv("HOME");
96 CPLAssert( nullptr != pszEnvHome );
97 CPLDebug( "ODBC", "HOME=%s", pszEnvHome );
98
99 const char* pszEnvOdbcSysIni = nullptr;
100 if( pszEnvIni == nullptr )
101 {
102 // record previous value, so we can rollback on failure
103 pszEnvOdbcSysIni = getenv("ODBCSYSINI");
104
105 // Set ODBCSYSINI variable pointing to HOME location.
106 const size_t nLen = strlen(pszEnvHome) + 12;
107 pszEnvIni = static_cast<char *>(CPLMalloc(nLen));
108
109 snprintf( pszEnvIni, nLen, "ODBCSYSINI=%s", pszEnvHome );
110 // A 'man putenv' shows that we cannot free pszEnvIni
111 // because the pointer is used directly by putenv in old glibc.
112 // coverity[tainted_string]
113 putenv( pszEnvIni );
114
115 CPLDebug( "ODBC", "%s", pszEnvIni );
116 }
117
118 // Try to install ODBC driver in new location.
119 if( FALSE == SQLInstallDriverEx(pszDriver, pszEnvHome, m_szPathOut,
120 ODBC_FILENAME_MAX, nullptr, fRequest,
121 &m_nUsageCount) )
122 {
123 // if installing the driver fails, we need to roll back the changes to ODBCSYSINI environment
124 // variable or all subsequent use of ODBC calls will fail
125 char * pszEnvRollback = nullptr;
126 if ( pszEnvOdbcSysIni )
127 {
128 const size_t nLen = strlen( pszEnvOdbcSysIni ) + 12;
129 pszEnvRollback = static_cast<char *>(CPLMalloc(nLen));
130 snprintf( pszEnvRollback, nLen, "ODBCSYSINI=%s", pszEnvOdbcSysIni );
131 }
132 else
133 {
134 // ODBCSYSINI not previously set, so remove
135 #ifdef _MSC_VER
136 // for MSVC an environment variable is removed by setting to empty string
137 // https://docs.microsoft.com/en-us/cpp/c-runtime-library/reference/putenv-wputenv?view=vs-2019
138 pszEnvRollback = CPLStrdup("ODBCSYSINI=");
139 #else
140 // for gnuc an environment variable is removed by not including the equal sign
141 // https://man7.org/linux/man-pages/man3/putenv.3.html
142 pszEnvRollback = CPLStrdup("ODBCSYSINI");
143 #endif
144 }
145
146 // A 'man putenv' shows that we cannot free pszEnvRollback
147 // because the pointer is used directly by putenv in old glibc.
148 // coverity[tainted_string]
149 putenv( pszEnvRollback );
150
151 CPL_UNUSED RETCODE cRet = SQLInstallerError( nErrorNum, &m_nErrorCode,
152 m_szError, SQL_MAX_MESSAGE_LENGTH, nullptr );
153 (void)cRet;
154 CPLAssert( SQL_SUCCESS == cRet || SQL_SUCCESS_WITH_INFO == cRet );
155
156 // FAIL
157 return FALSE;
158 }
159 }
160
161 // SUCCESS
162 return TRUE;
163 }
164
165 /************************************************************************/
166 /* RemoveDriver() */
167 /************************************************************************/
168
RemoveDriver(const char * pszDriverName,int fRemoveDSN)169 int CPLODBCDriverInstaller::RemoveDriver( const char* pszDriverName,
170 int fRemoveDSN )
171 {
172 CPLAssert( nullptr != pszDriverName );
173
174 if( FALSE == SQLRemoveDriver( pszDriverName, fRemoveDSN, &m_nUsageCount ) )
175 {
176 const WORD nErrorNum = 1; // TODO - a function param?
177
178 // Retrieve error code and message.
179 SQLInstallerError( nErrorNum, &m_nErrorCode,
180 m_szError, SQL_MAX_MESSAGE_LENGTH, nullptr );
181
182 return FALSE;
183 }
184
185 // SUCCESS
186 return TRUE;
187 }
188
189 /************************************************************************/
190 /* CPLODBCSession() */
191 /************************************************************************/
192
193 /** Constructor */
CPLODBCSession()194 CPLODBCSession::CPLODBCSession()
195 {
196 }
197
198 /************************************************************************/
199 /* ~CPLODBCSession() */
200 /************************************************************************/
201
202 /** Destructor */
~CPLODBCSession()203 CPLODBCSession::~CPLODBCSession()
204
205 {
206 CloseSession();
207 }
208
209 /************************************************************************/
210 /* CloseSession() */
211 /************************************************************************/
212
213 /** Close session */
CloseSession()214 int CPLODBCSession::CloseSession()
215
216 {
217 if( m_hDBC!=nullptr )
218 {
219 if( IsInTransaction() )
220 CPLError( CE_Warning, CPLE_AppDefined,
221 "Closing session with active transactions." );
222 CPLDebug( "ODBC", "SQLDisconnect()" );
223 SQLDisconnect( m_hDBC );
224 SQLFreeConnect( m_hDBC );
225 m_hDBC = nullptr;
226 }
227
228 if( m_hEnv!=nullptr )
229 {
230 SQLFreeEnv( m_hEnv );
231 m_hEnv = nullptr;
232 }
233
234 return TRUE;
235 }
236
237 /************************************************************************/
238 /* ClearTransaction() */
239 /************************************************************************/
240
241 /** Clear transaction */
ClearTransaction()242 int CPLODBCSession::ClearTransaction()
243
244 {
245 #if (ODBCVER >= 0x0300)
246
247 if( m_bAutoCommit )
248 return TRUE;
249
250 SQLUINTEGER bAutoCommit;
251 // See if we already in manual commit mode.
252 if( Failed( SQLGetConnectAttr( m_hDBC, SQL_ATTR_AUTOCOMMIT, &bAutoCommit,
253 sizeof(SQLUINTEGER), nullptr) ) )
254 return FALSE;
255
256 if( bAutoCommit == SQL_AUTOCOMMIT_OFF )
257 {
258 // Switch the connection to auto commit mode (default).
259 if( Failed( SQLSetConnectAttr( m_hDBC, SQL_ATTR_AUTOCOMMIT,
260 reinterpret_cast<SQLPOINTER>(SQL_AUTOCOMMIT_ON), 0 ) ) )
261 return FALSE;
262 }
263
264 m_bInTransaction = FALSE;
265 m_bAutoCommit = TRUE;
266
267 #endif
268 return TRUE;
269 }
270
271 /************************************************************************/
272 /* CommitTransaction() */
273 /************************************************************************/
274
275 /** Begin transaction */
BeginTransaction()276 int CPLODBCSession::BeginTransaction()
277
278 {
279 #if (ODBCVER >= 0x0300)
280
281 SQLUINTEGER bAutoCommit;
282 // See if we already in manual commit mode.
283 if( Failed( SQLGetConnectAttr( m_hDBC, SQL_ATTR_AUTOCOMMIT, &bAutoCommit,
284 sizeof(SQLUINTEGER), nullptr) ) )
285 return FALSE;
286
287 if( bAutoCommit == SQL_AUTOCOMMIT_ON )
288 {
289 // Switch the connection to manual commit mode.
290 #ifdef HAVE_GCC_WARNING_ZERO_AS_NULL_POINTER_CONSTANT
291 #pragma GCC diagnostic push
292 #pragma GCC diagnostic ignored "-Wzero-as-null-pointer-constant"
293 #endif
294 if( Failed( SQLSetConnectAttr( m_hDBC, SQL_ATTR_AUTOCOMMIT,
295 reinterpret_cast<SQLPOINTER>(SQL_AUTOCOMMIT_OFF), 0 ) ) )
296 return FALSE;
297 #ifdef HAVE_GCC_WARNING_ZERO_AS_NULL_POINTER_CONSTANT
298 #pragma GCC diagnostic pop
299 #endif
300 }
301
302 m_bInTransaction = TRUE;
303 m_bAutoCommit = FALSE;
304
305 #endif
306 return TRUE;
307 }
308
309 /************************************************************************/
310 /* CommitTransaction() */
311 /************************************************************************/
312
313 /** Commit transaction */
CommitTransaction()314 int CPLODBCSession::CommitTransaction()
315
316 {
317 #if (ODBCVER >= 0x0300)
318
319 if( m_bInTransaction )
320 {
321 if( Failed( SQLEndTran( SQL_HANDLE_DBC, m_hDBC, SQL_COMMIT ) ) )
322 {
323 return FALSE;
324 }
325 m_bInTransaction = FALSE;
326 }
327
328 #endif
329 return TRUE;
330 }
331
332 /************************************************************************/
333 /* RollbackTransaction() */
334 /************************************************************************/
335
336 /** Rollback transaction */
RollbackTransaction()337 int CPLODBCSession::RollbackTransaction()
338
339 {
340 #if (ODBCVER >= 0x0300)
341
342 if( m_bInTransaction )
343 {
344 // Rollback should not hide the previous error so Failed() is not
345 // called.
346 int nRetCode = SQLEndTran( SQL_HANDLE_DBC, m_hDBC, SQL_ROLLBACK );
347 m_bInTransaction = FALSE;
348
349 return (nRetCode == SQL_SUCCESS || nRetCode == SQL_SUCCESS_WITH_INFO );
350 }
351
352 #endif
353 return TRUE;
354 }
355
356 /************************************************************************/
357 /* Failed() */
358 /************************************************************************/
359
360 /** Test if a return code indicates failure, return TRUE if that
361 * is the case. Also update error text.
362 *
363 * ODBC error messages are reported in the following format:
364 * [SQLState]ErrorMessage(NativeErrorCode)
365 *
366 * Multiple error messages are delimited by ",".
367 */
Failed(int nRetCode,HSTMT hStmt)368 int CPLODBCSession::Failed( int nRetCode, HSTMT hStmt )
369
370 {
371 m_osLastError.clear();
372
373 if( nRetCode == SQL_SUCCESS || nRetCode == SQL_SUCCESS_WITH_INFO )
374 return FALSE;
375
376 SQLRETURN nDiagRetCode = SQL_SUCCESS;
377 for(SQLSMALLINT nRecNum = 1; nDiagRetCode == SQL_SUCCESS; ++nRecNum)
378 {
379 SQLCHAR achSQLState[5 + 1] = {};
380 SQLCHAR* pachCurErrMsg = static_cast<SQLCHAR *>(CPLMalloc((SQL_MAX_MESSAGE_LENGTH + 1) * sizeof(SQLCHAR)));
381 SQLSMALLINT nTextLength = 0;
382 SQLINTEGER nNativeError = 0;
383
384 nDiagRetCode = SQLGetDiagRec( SQL_HANDLE_STMT, hStmt, nRecNum,
385 achSQLState, &nNativeError,
386 reinterpret_cast<SQLCHAR *>(pachCurErrMsg),
387 SQL_MAX_MESSAGE_LENGTH, &nTextLength );
388 if (nDiagRetCode == SQL_SUCCESS ||
389 nDiagRetCode == SQL_SUCCESS_WITH_INFO)
390 {
391 if (nTextLength >= SQL_MAX_MESSAGE_LENGTH)
392 {
393 // the buffer wasn't enough, retry
394 SQLSMALLINT nTextLength2 = 0;
395 pachCurErrMsg = static_cast<SQLCHAR *>(CPLRealloc(pachCurErrMsg, (nTextLength + 1) * sizeof(SQLCHAR)));
396 nDiagRetCode = SQLGetDiagRec(SQL_HANDLE_STMT, hStmt, nRecNum,
397 achSQLState, &nNativeError,
398 reinterpret_cast<SQLCHAR *>(pachCurErrMsg),
399 nTextLength, &nTextLength2);
400 }
401 pachCurErrMsg[nTextLength] = '\0';
402 m_osLastError += CPLString().Printf("%s[%5s]%s(" CPL_FRMT_GIB ")",
403 (m_osLastError.empty() ? "" : ", "), achSQLState,
404 pachCurErrMsg, static_cast<GIntBig>(nNativeError));
405 }
406 CPLFree(pachCurErrMsg);
407 }
408
409 if( nRetCode == SQL_ERROR && m_bInTransaction )
410 RollbackTransaction();
411
412 return TRUE;
413 }
414
415 /************************************************************************/
416 /* ConnectToMsAccess() */
417 /************************************************************************/
418
419 /**
420 * Connects to a Microsoft Access database.
421 *
422 * @param pszName The file name of the Access database to connect to. This is not
423 * optional.
424 *
425 * @param pszDSNStringTemplate optional DSN string template for Microsoft Access
426 * ODBC Driver. If not specified, then a set of known driver templates will
427 * be used automatically as a fallback. If specified, it is the caller's responsibility
428 * to ensure that the template is correctly formatted.
429 *
430 * @return TRUE on success or FALSE on failure. Errors will automatically be reported
431 * via CPLError.
432 *
433 * @since GDAL 3.2
434 */
ConnectToMsAccess(const char * pszName,const char * pszDSNStringTemplate)435 bool CPLODBCSession::ConnectToMsAccess(const char *pszName, const char *pszDSNStringTemplate)
436 {
437 const auto Connect = [this, &pszName](const char* l_pszDSNStringTemplate, bool bVerboseError)
438 {
439 char* pszDSN = static_cast< char * >( CPLMalloc(strlen(pszName)+strlen(l_pszDSNStringTemplate)+100) );
440 /* coverity[tainted_string] */
441 snprintf( pszDSN,
442 strlen(pszName)+strlen(l_pszDSNStringTemplate)+100,
443 l_pszDSNStringTemplate, pszName );
444 CPLDebug( "ODBC", "EstablishSession(%s)", pszDSN );
445 int bError = !EstablishSession( pszDSN, nullptr, nullptr );
446 if ( bError )
447 {
448 if( bVerboseError )
449 {
450 CPLError( CE_Failure, CPLE_AppDefined,
451 "Unable to initialize ODBC connection to DSN for %s,\n"
452 "%s", pszDSN, GetLastError() );
453 }
454 CPLFree( pszDSN );
455 return false;
456 }
457
458 CPLFree( pszDSN );
459 return true;
460 };
461
462 if( pszDSNStringTemplate )
463 {
464 return Connect(pszDSNStringTemplate, true);
465 }
466
467 for( const char* l_pszDSNStringTemplate:
468 { "DRIVER=Microsoft Access Driver (*.mdb, *.accdb);DBQ=%s",
469 "DRIVER=Microsoft Access Driver (*.mdb, *.accdb);DBQ=\"%s\"",
470 "DRIVER=Microsoft Access Driver (*.mdb);DBQ=%s",
471 "DRIVER=Microsoft Access Driver (*.mdb);DBQ=\"%s\"" } )
472 {
473 if( Connect(l_pszDSNStringTemplate, false) )
474 return true;
475 }
476
477 CPLError( CE_Failure, CPLE_AppDefined,
478 "Unable to initialize ODBC connection to DSN for %s,\n"
479 "%s", pszName, GetLastError() );
480 return false;
481 }
482
483 /************************************************************************/
484 /* EstablishSession() */
485 /************************************************************************/
486
487 /**
488 * Connect to database and logon.
489 *
490 * @param pszDSN The name of the DSN being used to connect. This is not
491 * optional.
492 *
493 * @param pszUserid the userid to logon as, may be NULL if not not required,
494 * or provided by the DSN.
495 *
496 * @param pszPassword the password to logon with. May be NULL if not required
497 * or provided by the DSN.
498 *
499 * @return TRUE on success or FALSE on failure. Call GetLastError() to get
500 * details on failure.
501 */
502
EstablishSession(const char * pszDSN,const char * pszUserid,const char * pszPassword)503 int CPLODBCSession::EstablishSession( const char *pszDSN,
504 const char *pszUserid,
505 const char *pszPassword )
506
507 {
508 CloseSession();
509
510 if( Failed( SQLAllocEnv( &m_hEnv ) ) )
511 return FALSE;
512
513 if( Failed( SQLAllocConnect( m_hEnv, &m_hDBC ) ) )
514 {
515 CloseSession();
516 return FALSE;
517 }
518
519 #ifdef _MSC_VER
520 #pragma warning( push )
521 // warning C4996: 'SQLSetConnectOption': ODBC API: SQLSetConnectOption is
522 // deprecated. Please use SQLSetConnectAttr instead
523 #pragma warning( disable : 4996 )
524 #endif
525 SQLSetConnectOption( m_hDBC, SQL_LOGIN_TIMEOUT, 30 );
526 #ifdef _MSC_VER
527 #pragma warning( pop )
528 #endif
529
530 if( pszUserid == nullptr )
531 pszUserid = "";
532 if( pszPassword == nullptr )
533 pszPassword = "";
534
535 bool bFailed = false;
536 if( strstr(pszDSN, "=") != nullptr )
537 {
538 CPLDebug( "ODBC", "SQLDriverConnect(%s)", pszDSN );
539 SQLCHAR szOutConnString[1024] = {};
540 SQLSMALLINT nOutConnStringLen = 0;
541
542 bFailed = CPL_TO_BOOL(Failed(
543 SQLDriverConnect( m_hDBC, nullptr,
544 reinterpret_cast<SQLCHAR*>(const_cast<char*>(pszDSN)),
545 static_cast<SQLSMALLINT>(strlen(pszDSN)),
546 szOutConnString, sizeof(szOutConnString),
547 &nOutConnStringLen, SQL_DRIVER_NOPROMPT ) ));
548 }
549 else
550 {
551 CPLDebug( "ODBC", "SQLConnect(%s)", pszDSN );
552 bFailed = CPL_TO_BOOL(Failed(
553 SQLConnect( m_hDBC, reinterpret_cast<SQLCHAR*>(const_cast<char*>(pszDSN)), SQL_NTS,
554 reinterpret_cast<SQLCHAR*>(const_cast<char*>(pszUserid)), SQL_NTS,
555 reinterpret_cast<SQLCHAR*>(const_cast<char*>(pszPassword)), SQL_NTS ) ));
556 }
557
558 if( bFailed )
559 {
560 CPLDebug( "ODBC", "... failed: %s", GetLastError() );
561 CloseSession();
562 return FALSE;
563 }
564
565 return TRUE;
566 }
567
568 /************************************************************************/
569 /* GetLastError() */
570 /************************************************************************/
571
572 /**
573 * Returns the last ODBC error message.
574 *
575 * @return pointer to an internal buffer with the error message in it.
576 * Do not free or alter. Will be an empty (but not NULL) string if there is
577 * no pending error info.
578 */
579
GetLastError()580 const char *CPLODBCSession::GetLastError()
581
582 {
583 return m_osLastError.c_str();
584 }
585
586 /************************************************************************/
587 /* ==================================================================== */
588 /* CPLODBCStatement */
589 /* ==================================================================== */
590 /************************************************************************/
591
592 /************************************************************************/
593 /* CPLODBCStatement() */
594 /************************************************************************/
595
596 /** Constructor */
CPLODBCStatement(CPLODBCSession * poSession)597 CPLODBCStatement::CPLODBCStatement( CPLODBCSession *poSession ) :
598 m_poSession(poSession)
599 {
600
601 if( Failed(SQLAllocStmt(poSession->GetConnection(), &m_hStmt)) )
602 {
603 m_hStmt = nullptr;
604 }
605 }
606
607 /************************************************************************/
608 /* ~CPLODBCStatement() */
609 /************************************************************************/
610
611 /** Destructor */
~CPLODBCStatement()612 CPLODBCStatement::~CPLODBCStatement()
613
614 {
615 Clear();
616
617 if( m_hStmt != nullptr )
618 SQLFreeStmt( m_hStmt, SQL_DROP );
619 }
620
621 /************************************************************************/
622 /* ExecuteSQL() */
623 /************************************************************************/
624
625 /**
626 * Execute an SQL statement.
627 *
628 * This method will execute the passed (or stored) SQL statement,
629 * and initialize information about the resultset if there is one.
630 * If a NULL statement is passed, the internal stored statement that
631 * has been previously set via Append() or Appendf() calls will be used.
632 *
633 * @param pszStatement the SQL statement to execute, or NULL if the
634 * internally saved one should be used.
635 *
636 * @return TRUE on success or FALSE if there is an error. Error details
637 * can be fetched with OGRODBCSession::GetLastError().
638 */
639
ExecuteSQL(const char * pszStatement)640 int CPLODBCStatement::ExecuteSQL( const char *pszStatement )
641
642 {
643 if( m_poSession == nullptr || m_hStmt == nullptr )
644 {
645 // We should post an error.
646 return FALSE;
647 }
648
649 if( pszStatement != nullptr )
650 {
651 Clear();
652 Append( pszStatement );
653 }
654
655 #if( ODBCVER >= 0x0300 )
656
657 if( !m_poSession->IsInTransaction() )
658 {
659 // Commit pending transactions and set to autocommit mode.
660 m_poSession->ClearTransaction();
661 }
662
663 #endif
664
665 // SQL_NTS=-3 is a valid value for SQLExecDirect.
666 // coverity[negative_returns]
667 if( Failed(
668 SQLExecDirect( m_hStmt, reinterpret_cast<SQLCHAR *>(m_pszStatement), SQL_NTS ) ) )
669 return FALSE;
670
671 return CollectResultsInfo();
672 }
673
674 /************************************************************************/
675 /* CollectResultsInfo() */
676 /************************************************************************/
677
678 /** CollectResultsInfo */
CollectResultsInfo()679 int CPLODBCStatement::CollectResultsInfo()
680
681 {
682 if( m_poSession == nullptr || m_hStmt == nullptr )
683 {
684 // We should post an error.
685 return FALSE;
686 }
687
688 if( Failed( SQLNumResultCols(m_hStmt, &m_nColCount) ) )
689 return FALSE;
690
691 /* -------------------------------------------------------------------- */
692 /* Allocate per column information. */
693 /* -------------------------------------------------------------------- */
694 m_papszColNames =
695 static_cast<char **>(CPLCalloc(sizeof(char *), m_nColCount + 1));
696 m_papszColValues =
697 static_cast<char **>(CPLCalloc(sizeof(char *), m_nColCount + 1));
698 m_panColValueLengths = static_cast<CPL_SQLLEN *>(
699 CPLCalloc(sizeof(CPL_SQLLEN), m_nColCount + 1));
700
701 m_panColType =
702 static_cast<SQLSMALLINT *>(CPLCalloc(sizeof(SQLSMALLINT), m_nColCount));
703 m_papszColTypeNames =
704 static_cast<char **>(CPLCalloc(sizeof(char *), m_nColCount + 1));
705 m_panColSize =
706 static_cast<CPL_SQLULEN *>(CPLCalloc(sizeof(CPL_SQLULEN), m_nColCount));
707 m_panColPrecision =
708 static_cast<SQLSMALLINT *>(CPLCalloc(sizeof(SQLSMALLINT), m_nColCount));
709 m_panColNullable =
710 static_cast<SQLSMALLINT *>(CPLCalloc(sizeof(SQLSMALLINT), m_nColCount));
711 m_papszColColumnDef =
712 static_cast<char **>(CPLCalloc(sizeof(char *), m_nColCount + 1));
713
714 /* -------------------------------------------------------------------- */
715 /* Fetch column descriptions. */
716 /* -------------------------------------------------------------------- */
717 for( SQLUSMALLINT iCol = 0; iCol < m_nColCount; iCol++ )
718 {
719 SQLCHAR szName[256] = {};
720 SQLSMALLINT nNameLength = 0;
721
722 if( Failed( SQLDescribeCol(m_hStmt, iCol+1,
723 szName, sizeof(szName), &nNameLength,
724 m_panColType + iCol,
725 m_panColSize + iCol,
726 m_panColPrecision + iCol,
727 m_panColNullable + iCol) ) )
728 return FALSE;
729
730 szName[nNameLength] = '\0'; // Paranoid; the string should be
731 // null-terminated by the driver.
732 m_papszColNames[iCol] = CPLStrdup(reinterpret_cast<char *>(szName));
733
734 // SQLDescribeCol() fetches just a subset of column attributes.
735 // In addition to above data we need data type name.
736 if( Failed( SQLColAttribute(m_hStmt, iCol + 1, SQL_DESC_TYPE_NAME,
737 szName, sizeof(szName),
738 &nNameLength, nullptr) ) )
739 return FALSE;
740
741 szName[nNameLength] = '\0'; // Paranoid.
742 m_papszColTypeNames[iCol] = CPLStrdup(reinterpret_cast<char *>(szName));
743
744 #if DEBUG_VERBOSE
745 CPLDebug( "ODBC", "%s %s %d", m_papszColNames[iCol],
746 szName, m_panColType[iCol] );
747 #endif
748 }
749
750 return TRUE;
751 }
752
753 /************************************************************************/
754 /* GetRowCountAffected() */
755 /************************************************************************/
756
757 /** GetRowCountAffected */
GetRowCountAffected()758 int CPLODBCStatement::GetRowCountAffected()
759 {
760 SQLLEN nResultCount = 0;
761 SQLRowCount( m_hStmt, &nResultCount );
762
763 return static_cast<int>(nResultCount);
764 }
765
766 /************************************************************************/
767 /* GetColCount() */
768 /************************************************************************/
769
770 /**
771 * Fetch the resultset column count.
772 *
773 * @return the column count, or zero if there is no resultset.
774 */
775
GetColCount()776 int CPLODBCStatement::GetColCount()
777
778 {
779 return m_nColCount;
780 }
781
782 /************************************************************************/
783 /* GetColName() */
784 /************************************************************************/
785
786 /**
787 * Fetch a column name.
788 *
789 * @param iCol the zero based column index.
790 *
791 * @return NULL on failure (out of bounds column), or a pointer to an
792 * internal copy of the column name.
793 */
794
GetColName(int iCol)795 const char *CPLODBCStatement::GetColName( int iCol )
796
797 {
798 if( iCol < 0 || iCol >= m_nColCount )
799 return nullptr;
800
801 return m_papszColNames[iCol];
802 }
803
804 /************************************************************************/
805 /* GetColType() */
806 /************************************************************************/
807
808 /**
809 * Fetch a column data type.
810 *
811 * The return type code is a an ODBC SQL_ code, one of SQL_UNKNOWN_TYPE,
812 * SQL_CHAR, SQL_NUMERIC, SQL_DECIMAL, SQL_INTEGER, SQL_SMALLINT, SQL_FLOAT,
813 * SQL_REAL, SQL_DOUBLE, SQL_DATETIME, SQL_VARCHAR, SQL_TYPE_DATE,
814 * SQL_TYPE_TIME, SQL_TYPE_TIMESTAMPT.
815 *
816 * @param iCol the zero based column index.
817 *
818 * @return type code or -1 if the column is illegal.
819 */
820
GetColType(int iCol)821 short CPLODBCStatement::GetColType( int iCol )
822
823 {
824 if( iCol < 0 || iCol >= m_nColCount )
825 return -1;
826
827 return m_panColType[iCol];
828 }
829
830 /************************************************************************/
831 /* GetColTypeName() */
832 /************************************************************************/
833
834 /**
835 * Fetch a column data type name.
836 *
837 * Returns data source-dependent data type name; for example, "CHAR",
838 * "VARCHAR", "MONEY", "LONG VARBINAR", or "CHAR ( ) FOR BIT DATA".
839 *
840 * @param iCol the zero based column index.
841 *
842 * @return NULL on failure (out of bounds column), or a pointer to an
843 * internal copy of the column dat type name.
844 */
845
GetColTypeName(int iCol)846 const char *CPLODBCStatement::GetColTypeName( int iCol )
847
848 {
849 if( iCol < 0 || iCol >= m_nColCount )
850 return nullptr;
851
852 return m_papszColTypeNames[iCol];
853 }
854
855 /************************************************************************/
856 /* GetColSize() */
857 /************************************************************************/
858
859 /**
860 * Fetch the column width.
861 *
862 * @param iCol the zero based column index.
863 *
864 * @return column width, zero for unknown width columns.
865 */
866
GetColSize(int iCol)867 short CPLODBCStatement::GetColSize( int iCol )
868
869 {
870 if( iCol < 0 || iCol >= m_nColCount )
871 return -1;
872
873 return static_cast<short>(m_panColSize[iCol]);
874 }
875
876 /************************************************************************/
877 /* GetColPrecision() */
878 /************************************************************************/
879
880 /**
881 * Fetch the column precision.
882 *
883 * @param iCol the zero based column index.
884 *
885 * @return column precision, may be zero or the same as column size for
886 * columns to which it does not apply.
887 */
888
GetColPrecision(int iCol)889 short CPLODBCStatement::GetColPrecision( int iCol )
890
891 {
892 if( iCol < 0 || iCol >= m_nColCount )
893 return -1;
894
895 return m_panColPrecision[iCol];
896 }
897
898 /************************************************************************/
899 /* GetColNullable() */
900 /************************************************************************/
901
902 /**
903 * Fetch the column nullability.
904 *
905 * @param iCol the zero based column index.
906 *
907 * @return TRUE if the column may contains or FALSE otherwise.
908 */
909
GetColNullable(int iCol)910 short CPLODBCStatement::GetColNullable( int iCol )
911
912 {
913 if( iCol < 0 || iCol >= m_nColCount )
914 return -1;
915
916 return m_panColNullable[iCol];
917 }
918
919 /************************************************************************/
920 /* GetColColumnDef() */
921 /************************************************************************/
922
923 /**
924 * Fetch a column default value.
925 *
926 * Returns the default value of a column.
927 *
928 * @param iCol the zero based column index.
929 *
930 * @return NULL if the default value is not dpecified
931 * or the internal copy of the default value.
932 */
933
GetColColumnDef(int iCol)934 const char *CPLODBCStatement::GetColColumnDef( int iCol )
935
936 {
937 if( iCol < 0 || iCol >= m_nColCount )
938 return nullptr;
939
940 return m_papszColColumnDef[iCol];
941 }
942
943 /************************************************************************/
944 /* Fetch() */
945 /************************************************************************/
946
947 /**
948 * Fetch a new record.
949 *
950 * Requests the next row in the current resultset using the SQLFetchScroll()
951 * call. Note that many ODBC drivers only support the default forward
952 * fetching one record at a time. Only SQL_FETCH_NEXT (the default) should
953 * be considered reliable on all drivers.
954 *
955 * Currently it isn't clear how to determine whether an error or a normal
956 * out of data condition has occurred if Fetch() fails.
957 *
958 * @param nOrientation One of SQL_FETCH_NEXT, SQL_FETCH_LAST, SQL_FETCH_PRIOR,
959 * SQL_FETCH_ABSOLUTE, or SQL_FETCH_RELATIVE (default is SQL_FETCH_NEXT).
960 *
961 * @param nOffset the offset (number of records), ignored for some
962 * orientations.
963 *
964 * @return TRUE if a new row is successfully fetched, or FALSE if not.
965 */
966
Fetch(int nOrientation,int nOffset)967 int CPLODBCStatement::Fetch( int nOrientation, int nOffset )
968
969 {
970 ClearColumnData();
971
972 if( m_hStmt == nullptr || m_nColCount < 1 )
973 return FALSE;
974
975 /* -------------------------------------------------------------------- */
976 /* Fetch a new row. Note that some brain dead drives (such as */
977 /* the unixodbc text file driver) don't implement */
978 /* SQLScrollFetch(), so we try to stick to SQLFetch() if we */
979 /* can). */
980 /* -------------------------------------------------------------------- */
981 SQLRETURN nRetCode;
982
983 if( nOrientation == SQL_FETCH_NEXT && nOffset == 0 )
984 {
985 nRetCode = SQLFetch( m_hStmt );
986 if( Failed(nRetCode) )
987 {
988 if( nRetCode != SQL_NO_DATA )
989 {
990 CPLError( CE_Failure, CPLE_AppDefined, "%s",
991 m_poSession->GetLastError() );
992 }
993 return FALSE;
994 }
995 }
996 else
997 {
998 nRetCode = SQLFetchScroll(m_hStmt,
999 static_cast<SQLSMALLINT>(nOrientation),
1000 nOffset);
1001 if( Failed(nRetCode) )
1002 {
1003 if( nRetCode == SQL_NO_DATA )
1004 {
1005 CPLError( CE_Failure, CPLE_AppDefined, "%s",
1006 m_poSession->GetLastError() );
1007 }
1008 return FALSE;
1009 }
1010 }
1011
1012 /* -------------------------------------------------------------------- */
1013 /* Pull out all the column values. */
1014 /* -------------------------------------------------------------------- */
1015 for( SQLSMALLINT iCol = 0; iCol < m_nColCount; iCol++ )
1016 {
1017 CPL_SQLLEN cbDataLen = 0;
1018 SQLSMALLINT nFetchType = GetTypeMapping( m_panColType[iCol] );
1019
1020 // Handle values other than WCHAR and BINARY as CHAR.
1021 if( nFetchType != SQL_C_BINARY && nFetchType != SQL_C_WCHAR )
1022 nFetchType = SQL_C_CHAR;
1023
1024 char szWrkData[513] = {};
1025
1026 nRetCode = SQLGetData( m_hStmt, iCol + 1, nFetchType,
1027 szWrkData, sizeof(szWrkData) - 1,
1028 &cbDataLen );
1029
1030 // SQLGetData() is giving garbage values in the first 4 bytes of
1031 // cbDataLen in some architectures. Converting it to int discards the
1032 // unnecessary bytes. This should not be a problem unless the buffer
1033 // size reaches 2GB. (#3385)
1034 cbDataLen = static_cast<int>(cbDataLen);
1035
1036 // a return code of SQL_NO_DATA is not indicative of an error - see
1037 // https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/getting-long-data?view=sql-server-ver15
1038 // "When there is no more data to return, SQLGetData returns SQL_NO_DATA"
1039 // and the example from that page which uses:
1040 // while ((rc = SQLGetData(hstmt, 2, SQL_C_BINARY, BinaryPtr, sizeof(BinaryPtr), &BinaryLenOrInd)) != SQL_NO_DATA) { ... }
1041 if( nRetCode != SQL_NO_DATA && Failed( nRetCode ) )
1042 {
1043 CPLError( CE_Failure, CPLE_AppDefined, "%s",
1044 m_poSession->GetLastError() );
1045 return FALSE;
1046 }
1047
1048 // if first call to SQLGetData resulted in SQL_NO_DATA return code, then the data is empty (NULL)
1049 if( cbDataLen == SQL_NULL_DATA || nRetCode == SQL_NO_DATA )
1050 {
1051 m_papszColValues[iCol] = nullptr;
1052 m_panColValueLengths[iCol] = 0;
1053 }
1054
1055 // Assume big result: should check for state=SQLSATE 01004.
1056 else if( nRetCode == SQL_SUCCESS_WITH_INFO )
1057 {
1058 if( cbDataLen >= static_cast<CPL_SQLLEN>(sizeof(szWrkData)-1) )
1059 {
1060 cbDataLen = static_cast<CPL_SQLLEN>(sizeof(szWrkData)-1);
1061 if( nFetchType == SQL_C_CHAR )
1062 while( (cbDataLen > 1) && (szWrkData[cbDataLen - 1] == 0) )
1063 --cbDataLen; // Trimming the extra terminators: bug 990
1064 else if( nFetchType == SQL_C_WCHAR )
1065 while( (cbDataLen > 1) && (szWrkData[cbDataLen - 1] == 0 )
1066 && (szWrkData[cbDataLen - 2] == 0))
1067 cbDataLen -= 2; // Trimming the extra terminators.
1068 }
1069
1070 m_papszColValues[iCol] =
1071 static_cast<char *>(CPLMalloc(cbDataLen + 2));
1072 memcpy( m_papszColValues[iCol], szWrkData, cbDataLen );
1073 m_papszColValues[iCol][cbDataLen] = '\0';
1074 m_papszColValues[iCol][cbDataLen+1] = '\0';
1075 m_panColValueLengths[iCol] = cbDataLen;
1076
1077 while( true )
1078 {
1079 nRetCode = SQLGetData( m_hStmt,
1080 static_cast<SQLUSMALLINT>(iCol) + 1,
1081 nFetchType,
1082 szWrkData, sizeof(szWrkData)-1,
1083 &cbDataLen );
1084 if( nRetCode == SQL_NO_DATA )
1085 break;
1086
1087 if( Failed( nRetCode ) )
1088 {
1089 CPLError( CE_Failure, CPLE_AppDefined, "%s",
1090 m_poSession->GetLastError() );
1091 return FALSE;
1092 }
1093
1094 CPL_SQLLEN nChunkLen;
1095 if( cbDataLen >= static_cast<int>(sizeof(szWrkData) - 1)
1096 || cbDataLen == SQL_NO_TOTAL )
1097 {
1098 nChunkLen = sizeof(szWrkData)-1;
1099 if( nFetchType == SQL_C_CHAR )
1100 while( (nChunkLen > 1)
1101 && (szWrkData[nChunkLen - 1] == 0) )
1102 --nChunkLen; // Trimming the extra terminators.
1103 else if( nFetchType == SQL_C_WCHAR )
1104 while( (nChunkLen > 1)
1105 && (szWrkData[nChunkLen - 1] == 0)
1106 && (szWrkData[nChunkLen - 2] == 0) )
1107 nChunkLen -= 2; // Trimming the extra terminators.
1108 }
1109 else
1110 {
1111 nChunkLen = cbDataLen;
1112 }
1113 szWrkData[nChunkLen] = '\0';
1114
1115 m_papszColValues[iCol] = static_cast<char *>(
1116 CPLRealloc( m_papszColValues[iCol],
1117 m_panColValueLengths[iCol] + nChunkLen + 2 ));
1118 memcpy( m_papszColValues[iCol] + m_panColValueLengths[iCol],
1119 szWrkData, nChunkLen );
1120 m_panColValueLengths[iCol] += nChunkLen;
1121 m_papszColValues[iCol][m_panColValueLengths[iCol]] = '\0';
1122 m_papszColValues[iCol][m_panColValueLengths[iCol]+1] = '\0';
1123 }
1124 }
1125 else
1126 {
1127 m_panColValueLengths[iCol] = cbDataLen;
1128 m_papszColValues[iCol] =
1129 static_cast<char *>(CPLMalloc(cbDataLen + 2));
1130 memcpy( m_papszColValues[iCol], szWrkData, cbDataLen );
1131 m_papszColValues[iCol][cbDataLen] = '\0';
1132 m_papszColValues[iCol][cbDataLen+1] = '\0';
1133 }
1134
1135 // Convert WCHAR to UTF-8, assuming the WCHAR is UCS-2.
1136 if( nFetchType == SQL_C_WCHAR && m_papszColValues[iCol] != nullptr
1137 && m_panColValueLengths[iCol] > 0 )
1138 {
1139 #if WCHAR_MAX == 0xFFFFu
1140 wchar_t *pwszSrc = reinterpret_cast<wchar_t*>(m_papszColValues[iCol]);
1141 #else
1142 unsigned int i = 0;
1143 GUInt16 *panColValue =
1144 reinterpret_cast<GUInt16 *>(m_papszColValues[iCol]);
1145 wchar_t *pwszSrc =
1146 static_cast<wchar_t *>(CPLMalloc((m_panColValueLengths[iCol] / 2 + 1)
1147 * sizeof(wchar_t)));
1148
1149 while( panColValue[i] != 0 ) {
1150 pwszSrc[i] = static_cast<wchar_t>(panColValue[i]);
1151 i += 1;
1152 }
1153 pwszSrc[i] = L'\0';
1154
1155 CPLFree( panColValue );
1156 #endif
1157
1158 m_papszColValues[iCol] =
1159 CPLRecodeFromWChar( pwszSrc, CPL_ENC_UCS2, CPL_ENC_UTF8 );
1160 m_panColValueLengths[iCol] = strlen(m_papszColValues[iCol]);
1161
1162 CPLFree( pwszSrc );
1163 }
1164 }
1165
1166 return TRUE;
1167 }
1168
1169 /************************************************************************/
1170 /* GetColData() */
1171 /************************************************************************/
1172
1173 /**
1174 * Fetch column data.
1175 *
1176 * Fetches the data contents of the requested column for the currently loaded
1177 * row. The result is returned as a string regardless of the column type.
1178 * NULL is returned if an illegal column is given, or if the actual column
1179 * is "NULL".
1180 *
1181 * @param iCol the zero based column to fetch.
1182 *
1183 * @param pszDefault the value to return if the column does not exist, or is
1184 * NULL. Defaults to NULL.
1185 *
1186 * @return pointer to internal column data or NULL on failure.
1187 */
1188
GetColData(int iCol,const char * pszDefault)1189 const char *CPLODBCStatement::GetColData( int iCol, const char *pszDefault )
1190
1191 {
1192 if( iCol < 0 || iCol >= m_nColCount )
1193 return pszDefault;
1194 else if( m_papszColValues[iCol] != nullptr )
1195 return m_papszColValues[iCol];
1196 else
1197 return pszDefault;
1198 }
1199
1200 /************************************************************************/
1201 /* GetColData() */
1202 /************************************************************************/
1203
1204 /**
1205 * Fetch column data.
1206 *
1207 * Fetches the data contents of the requested column for the currently loaded
1208 * row. The result is returned as a string regardless of the column type.
1209 * NULL is returned if an illegal column is given, or if the actual column
1210 * is "NULL".
1211 *
1212 * @param pszColName the name of the column requested.
1213 *
1214 * @param pszDefault the value to return if the column does not exist, or is
1215 * NULL. Defaults to NULL.
1216 *
1217 * @return pointer to internal column data or NULL on failure.
1218 */
1219
GetColData(const char * pszColName,const char * pszDefault)1220 const char *CPLODBCStatement::GetColData( const char *pszColName,
1221 const char *pszDefault )
1222
1223 {
1224 const int iCol = GetColId( pszColName );
1225
1226 if( iCol == -1 )
1227 return pszDefault;
1228 else
1229 return GetColData( iCol, pszDefault );
1230 }
1231
1232 /************************************************************************/
1233 /* GetColDataLength() */
1234 /************************************************************************/
1235
1236 /** GetColDataLength */
GetColDataLength(int iCol)1237 int CPLODBCStatement::GetColDataLength( int iCol )
1238
1239 {
1240 if( iCol < 0 || iCol >= m_nColCount )
1241 return 0;
1242 else if( m_papszColValues[iCol] != nullptr )
1243 return static_cast<int>(m_panColValueLengths[iCol]);
1244 else
1245 return 0;
1246 }
1247
1248 /************************************************************************/
1249 /* GetColId() */
1250 /************************************************************************/
1251
1252 /**
1253 * Fetch column index.
1254 *
1255 * Gets the column index corresponding with the passed name. The
1256 * name comparisons are case insensitive.
1257 *
1258 * @param pszColName the name to search for.
1259 *
1260 * @return the column index, or -1 if not found.
1261 */
1262
GetColId(const char * pszColName)1263 int CPLODBCStatement::GetColId( const char *pszColName )
1264
1265 {
1266 for( SQLSMALLINT iCol = 0; iCol < m_nColCount; iCol++ )
1267 if( EQUAL(pszColName, m_papszColNames[iCol]) )
1268 return iCol;
1269
1270 return -1;
1271 }
1272
1273 /************************************************************************/
1274 /* ClearColumnData() */
1275 /************************************************************************/
1276
1277 /** ClearColumnData */
ClearColumnData()1278 void CPLODBCStatement::ClearColumnData()
1279
1280 {
1281 if( m_nColCount > 0 )
1282 {
1283 for( int iCol = 0; iCol < m_nColCount; iCol++ )
1284 {
1285 if( m_papszColValues[iCol] != nullptr )
1286 {
1287 CPLFree( m_papszColValues[iCol] );
1288 m_papszColValues[iCol] = nullptr;
1289 }
1290 }
1291 }
1292 }
1293
1294 /************************************************************************/
1295 /* Failed() */
1296 /************************************************************************/
1297
1298 /** Failed */
Failed(int nResultCode)1299 int CPLODBCStatement::Failed( int nResultCode )
1300
1301 {
1302 if( m_poSession != nullptr )
1303 return m_poSession->Failed( nResultCode, m_hStmt );
1304
1305 return TRUE;
1306 }
1307
1308 /************************************************************************/
1309 /* Append(const char *) */
1310 /************************************************************************/
1311
1312 /**
1313 * Append text to internal command.
1314 *
1315 * The passed text is appended to the internal SQL command text.
1316 *
1317 * @param pszText text to append.
1318 */
1319
Append(const char * pszText)1320 void CPLODBCStatement::Append( const char *pszText )
1321
1322 {
1323 const size_t nTextLen = strlen(pszText);
1324
1325 if( m_nStatementMax < m_nStatementLen + nTextLen + 1 )
1326 {
1327 m_nStatementMax = (m_nStatementLen + nTextLen) * 2 + 100;
1328 if( m_pszStatement == nullptr )
1329 {
1330 m_pszStatement = static_cast<char *>(VSIMalloc(m_nStatementMax));
1331 m_pszStatement[0] = '\0';
1332 }
1333 else
1334 {
1335 m_pszStatement = static_cast<char *>(
1336 CPLRealloc(m_pszStatement, m_nStatementMax));
1337 }
1338 }
1339
1340 strcpy( m_pszStatement + m_nStatementLen, pszText );
1341 m_nStatementLen += nTextLen;
1342 }
1343
1344 /************************************************************************/
1345 /* AppendEscaped(const char *) */
1346 /************************************************************************/
1347
1348 /**
1349 * Append text to internal command.
1350 *
1351 * The passed text is appended to the internal SQL command text after
1352 * escaping any special characters so it can be used as a character string
1353 * in an SQL statement.
1354 *
1355 * @param pszText text to append.
1356 */
1357
AppendEscaped(const char * pszText)1358 void CPLODBCStatement::AppendEscaped( const char *pszText )
1359
1360 {
1361 const size_t nTextLen = strlen(pszText);
1362 char *pszEscapedText = static_cast<char *>(VSIMalloc(nTextLen*2 + 1));
1363
1364 size_t iOut = 0; // Used after for.
1365 for( size_t iIn = 0; iIn < nTextLen; iIn++ )
1366 {
1367 switch( pszText[iIn] )
1368 {
1369 case '\'':
1370 case '\\':
1371 pszEscapedText[iOut++] = '\\';
1372 pszEscapedText[iOut++] = pszText[iIn];
1373 break;
1374
1375 default:
1376 pszEscapedText[iOut++] = pszText[iIn];
1377 break;
1378 }
1379 }
1380
1381 pszEscapedText[iOut] = '\0';
1382
1383 Append( pszEscapedText );
1384 CPLFree( pszEscapedText );
1385 }
1386
1387 /************************************************************************/
1388 /* Append(int) */
1389 /************************************************************************/
1390
1391 /**
1392 * Append to internal command.
1393 *
1394 * The passed value is formatted and appended to the internal SQL command text.
1395 *
1396 * @param nValue value to append to the command.
1397 */
1398
Append(int nValue)1399 void CPLODBCStatement::Append( int nValue )
1400
1401 {
1402 char szFormattedValue[32] = {};
1403
1404 snprintf( szFormattedValue, sizeof(szFormattedValue), "%d", nValue );
1405 Append( szFormattedValue );
1406 }
1407
1408 /************************************************************************/
1409 /* Append(double) */
1410 /************************************************************************/
1411
1412 /**
1413 * Append to internal command.
1414 *
1415 * The passed value is formatted and appended to the internal SQL command text.
1416 *
1417 * @param dfValue value to append to the command.
1418 */
1419
Append(double dfValue)1420 void CPLODBCStatement::Append( double dfValue )
1421
1422 {
1423 char szFormattedValue[100] = {};
1424
1425 snprintf( szFormattedValue, sizeof(szFormattedValue), "%24g", dfValue );
1426 Append( szFormattedValue );
1427 }
1428
1429 /************************************************************************/
1430 /* Appendf() */
1431 /************************************************************************/
1432
1433 /**
1434 * Append to internal command.
1435 *
1436 * The passed format is used to format other arguments and the result is
1437 * appended to the internal command text. Long results may not be formatted
1438 * properly, and should be appended with the direct Append() methods.
1439 *
1440 * @param pszFormat printf() style format string.
1441 *
1442 * @return FALSE if formatting fails due to result being too large.
1443 */
1444
Appendf(CPL_FORMAT_STRING (const char * pszFormat),...)1445 int CPLODBCStatement::Appendf( CPL_FORMAT_STRING(const char *pszFormat), ... )
1446
1447 {
1448 va_list args;
1449
1450 va_start( args, pszFormat );
1451
1452 char szFormattedText[8000] = {}; // TODO: Move this off the stack.
1453 szFormattedText[0] = '\0';
1454
1455 #if defined(HAVE_VSNPRINTF)
1456 const bool bSuccess =
1457 vsnprintf( szFormattedText, sizeof(szFormattedText)-1,
1458 pszFormat, args )
1459 < static_cast<int>( sizeof(szFormattedText) - 1 );
1460 #else
1461 vsprintf( szFormattedText, pszFormat, args );
1462 const bool bSuccess = true;
1463 #endif
1464 va_end( args );
1465
1466 if( bSuccess )
1467 Append( szFormattedText );
1468
1469 return bSuccess;
1470 }
1471
1472 /************************************************************************/
1473 /* Clear() */
1474 /************************************************************************/
1475
1476 /**
1477 * Clear internal command text and result set definitions.
1478 */
1479
Clear()1480 void CPLODBCStatement::Clear()
1481
1482 {
1483 /* Closing the cursor if opened */
1484 if( m_hStmt != nullptr )
1485 SQLFreeStmt( m_hStmt, SQL_CLOSE );
1486
1487 ClearColumnData();
1488
1489 if( m_pszStatement != nullptr )
1490 {
1491 CPLFree( m_pszStatement );
1492 m_pszStatement = nullptr;
1493 }
1494
1495 m_nStatementLen = 0;
1496 m_nStatementMax = 0;
1497
1498 m_nColCount = 0;
1499
1500 if( m_papszColNames )
1501 {
1502 CPLFree( m_panColType );
1503 m_panColType = nullptr;
1504
1505 CSLDestroy( m_papszColTypeNames );
1506 m_papszColTypeNames = nullptr;
1507
1508 CPLFree( m_panColSize );
1509 m_panColSize = nullptr;
1510
1511 CPLFree( m_panColPrecision );
1512 m_panColPrecision = nullptr;
1513
1514 CPLFree( m_panColNullable );
1515 m_panColNullable = nullptr;
1516
1517 CSLDestroy( m_papszColColumnDef );
1518 m_papszColColumnDef = nullptr;
1519
1520 CSLDestroy( m_papszColNames );
1521 m_papszColNames = nullptr;
1522
1523 CPLFree( m_papszColValues );
1524 m_papszColValues = nullptr;
1525
1526 CPLFree( m_panColValueLengths );
1527 m_panColValueLengths = nullptr;
1528 }
1529 }
1530
1531 /************************************************************************/
1532 /* GetColumns() */
1533 /************************************************************************/
1534
1535 /**
1536 * Fetch column definitions for a table.
1537 *
1538 * The SQLColumn() method is used to fetch the definitions for the columns
1539 * of a table (or other queryable object such as a view). The column
1540 * definitions are digested and used to populate the CPLODBCStatement
1541 * column definitions essentially as if a "SELECT * FROM tablename" had
1542 * been done; however, no resultset will be available.
1543 *
1544 * @param pszTable the name of the table to query information on. This
1545 * should not be empty.
1546 *
1547 * @param pszCatalog the catalog to find the table in, use NULL (the
1548 * default) if no catalog is available.
1549 *
1550 * @param pszSchema the schema to find the table in, use NULL (the
1551 * default) if no schema is available.
1552 *
1553 * @return TRUE on success or FALSE on failure.
1554 */
1555
GetColumns(const char * pszTable,const char * pszCatalog,const char * pszSchema)1556 int CPLODBCStatement::GetColumns( const char *pszTable,
1557 const char *pszCatalog,
1558 const char *pszSchema )
1559
1560 {
1561 #ifdef notdef
1562 if( pszCatalog == nullptr )
1563 pszCatalog = "";
1564 if( pszSchema == nullptr )
1565 pszSchema = "";
1566 #endif
1567
1568 #if (ODBCVER >= 0x0300)
1569
1570 if( !m_poSession->IsInTransaction() )
1571 {
1572 /* commit pending transactions and set to autocommit mode*/
1573 m_poSession->ClearTransaction();
1574 }
1575
1576 #endif
1577 /* -------------------------------------------------------------------- */
1578 /* Fetch columns resultset for this table. */
1579 /* -------------------------------------------------------------------- */
1580 if( Failed( SQLColumns( m_hStmt,
1581 reinterpret_cast<SQLCHAR *>(const_cast<char*>(pszCatalog)), SQL_NTS,
1582 reinterpret_cast<SQLCHAR *>(const_cast<char*>(pszSchema)), SQL_NTS,
1583 reinterpret_cast<SQLCHAR *>(const_cast<char*>(pszTable)), SQL_NTS,
1584 nullptr /* "" */, SQL_NTS ) ) )
1585 return FALSE;
1586
1587 /* -------------------------------------------------------------------- */
1588 /* Allocate per column information. */
1589 /* -------------------------------------------------------------------- */
1590 #ifdef notdef
1591 // SQLRowCount() is too unreliable (with unixodbc on AIX for instance)
1592 // so we now avoid it.
1593 SQLINTEGER nResultCount = 0;
1594
1595 if( Failed(SQLRowCount( m_hStmt, &nResultCount ) ) )
1596 nResultCount = 0;
1597
1598 if( nResultCount < 1 )
1599 m_nColCount = 500; // Hopefully lots.
1600 else
1601 m_nColCount = nResultCount;
1602 #endif
1603
1604 m_nColCount = 500;
1605
1606 m_papszColNames =
1607 static_cast<char **>(CPLCalloc(sizeof(char *), m_nColCount + 1));
1608 m_papszColValues =
1609 static_cast<char **>(CPLCalloc(sizeof(char *), m_nColCount + 1));
1610
1611 m_panColType =
1612 static_cast<SQLSMALLINT *>(CPLCalloc(sizeof(SQLSMALLINT), m_nColCount));
1613 m_papszColTypeNames =
1614 static_cast<char **>(CPLCalloc(sizeof(char *), m_nColCount + 1));
1615 m_panColSize =
1616 static_cast<CPL_SQLULEN *>(CPLCalloc(sizeof(CPL_SQLULEN), m_nColCount));
1617 m_panColPrecision =
1618 static_cast<SQLSMALLINT *>(CPLCalloc(sizeof(SQLSMALLINT), m_nColCount));
1619 m_panColNullable =
1620 static_cast<SQLSMALLINT *>(CPLCalloc(sizeof(SQLSMALLINT), m_nColCount));
1621 m_papszColColumnDef =
1622 static_cast<char **>(CPLCalloc(sizeof(char *), m_nColCount + 1));
1623
1624 /* -------------------------------------------------------------------- */
1625 /* Establish columns to use for key information. */
1626 /* -------------------------------------------------------------------- */
1627 for( SQLUSMALLINT iCol = 0; iCol < m_nColCount; iCol++ )
1628 {
1629 if( Failed( SQLFetch( m_hStmt ) ) )
1630 {
1631 m_nColCount = iCol;
1632 break;
1633 }
1634
1635 char szWrkData[8193] = {};
1636 CPL_SQLLEN cbDataLen = 0;
1637
1638 SQLGetData( m_hStmt, SQLColumns_COLUMN_NAME, SQL_C_CHAR,
1639 szWrkData, sizeof(szWrkData)-1, &cbDataLen );
1640 m_papszColNames[iCol] = CPLStrdup(szWrkData);
1641
1642 SQLGetData( m_hStmt, SQLColumns_DATA_TYPE, SQL_C_CHAR,
1643 szWrkData, sizeof(szWrkData)-1, &cbDataLen );
1644 m_panColType[iCol] = static_cast<short>(atoi(szWrkData));
1645
1646 SQLGetData( m_hStmt, SQLColumns_TYPE_NAME, SQL_C_CHAR,
1647 szWrkData, sizeof(szWrkData)-1, &cbDataLen );
1648 m_papszColTypeNames[iCol] = CPLStrdup(szWrkData);
1649
1650 SQLGetData( m_hStmt, SQLColumns_COLUMN_SIZE, SQL_C_CHAR,
1651 szWrkData, sizeof(szWrkData)-1, &cbDataLen );
1652 m_panColSize[iCol] = atoi(szWrkData);
1653
1654 SQLGetData( m_hStmt, SQLColumns_DECIMAL_DIGITS, SQL_C_CHAR,
1655 szWrkData, sizeof(szWrkData)-1, &cbDataLen );
1656 m_panColPrecision[iCol] = static_cast<short>(atoi(szWrkData));
1657
1658 SQLGetData( m_hStmt, SQLColumns_NULLABLE, SQL_C_CHAR,
1659 szWrkData, sizeof(szWrkData)-1, &cbDataLen );
1660 m_panColNullable[iCol] = atoi(szWrkData) == SQL_NULLABLE;
1661 #if (ODBCVER >= 0x0300)
1662 SQLGetData( m_hStmt, SQLColumns_COLUMN_DEF, SQL_C_CHAR,
1663 szWrkData, sizeof(szWrkData)-1, &cbDataLen );
1664 if( cbDataLen > 0 )
1665 m_papszColColumnDef[iCol] = CPLStrdup(szWrkData);
1666 #endif
1667 }
1668
1669 return TRUE;
1670 }
1671
1672 /************************************************************************/
1673 /* GetPrimaryKeys() */
1674 /************************************************************************/
1675
1676 /**
1677 * Fetch primary keys for a table.
1678 *
1679 * The SQLPrimaryKeys() function is used to fetch a list of fields
1680 * forming the primary key. The result is returned as a result set matching
1681 * the SQLPrimaryKeys() function result set. The 4th column in the result
1682 * set is the column name of the key, and if the result set contains only
1683 * one record then that single field will be the complete primary key.
1684 *
1685 * @param pszTable the name of the table to query information on. This
1686 * should not be empty.
1687 *
1688 * @param pszCatalog the catalog to find the table in, use NULL (the
1689 * default) if no catalog is available.
1690 *
1691 * @param pszSchema the schema to find the table in, use NULL (the
1692 * default) if no schema is available.
1693 *
1694 * @return TRUE on success or FALSE on failure.
1695 */
1696
GetPrimaryKeys(const char * pszTable,const char * pszCatalog,const char * pszSchema)1697 int CPLODBCStatement::GetPrimaryKeys( const char *pszTable,
1698 const char *pszCatalog,
1699 const char *pszSchema )
1700
1701 {
1702 if( pszCatalog == nullptr )
1703 pszCatalog = "";
1704 if( pszSchema == nullptr )
1705 pszSchema = "";
1706
1707 #if (ODBCVER >= 0x0300)
1708
1709 if( !m_poSession->IsInTransaction() )
1710 {
1711 /* commit pending transactions and set to autocommit mode*/
1712 m_poSession->ClearTransaction();
1713 }
1714
1715 #endif
1716
1717 /* -------------------------------------------------------------------- */
1718 /* Fetch columns resultset for this table. */
1719 /* -------------------------------------------------------------------- */
1720 if( Failed( SQLPrimaryKeys( m_hStmt,
1721 reinterpret_cast<SQLCHAR *>(const_cast<char*>(pszCatalog)), SQL_NTS,
1722 reinterpret_cast<SQLCHAR *>(const_cast<char*>(pszSchema)), SQL_NTS,
1723 reinterpret_cast<SQLCHAR *>(const_cast<char*>(pszTable)), SQL_NTS ) ) )
1724 return FALSE;
1725
1726 return CollectResultsInfo();
1727 }
1728
1729 /************************************************************************/
1730 /* GetTables() */
1731 /************************************************************************/
1732
1733 /**
1734 * Fetch tables in database.
1735 *
1736 * The SQLTables() function is used to fetch a list tables in the
1737 * database. The result is returned as a result set matching
1738 * the SQLTables() function result set. The 3rd column in the result
1739 * set is the table name. Only tables of type "TABLE" are returned.
1740 *
1741 * @param pszCatalog the catalog to find the table in, use NULL (the
1742 * default) if no catalog is available.
1743 *
1744 * @param pszSchema the schema to find the table in, use NULL (the
1745 * default) if no schema is available.
1746 *
1747 * @return TRUE on success or FALSE on failure.
1748 */
1749
GetTables(const char * pszCatalog,const char * pszSchema)1750 int CPLODBCStatement::GetTables( const char *pszCatalog,
1751 const char *pszSchema )
1752
1753 {
1754 CPLDebug( "ODBC", "CatalogNameL: %s\nSchema name: %s",
1755 pszCatalog ? pszCatalog : "(null)",
1756 pszSchema ? pszSchema : "(null)" );
1757
1758 #if (ODBCVER >= 0x0300)
1759
1760 if( !m_poSession->IsInTransaction() )
1761 {
1762 // Commit pending transactions and set to autocommit mode.
1763 m_poSession->ClearTransaction();
1764 }
1765
1766 #endif
1767
1768 /* -------------------------------------------------------------------- */
1769 /* Fetch columns resultset for this table. */
1770 /* -------------------------------------------------------------------- */
1771 if( Failed( SQLTables( m_hStmt,
1772 reinterpret_cast<SQLCHAR *>(const_cast<char*>(pszCatalog)), SQL_NTS,
1773 reinterpret_cast<SQLCHAR *>(const_cast<char*>(pszSchema)), SQL_NTS,
1774 nullptr, SQL_NTS,
1775 reinterpret_cast<SQLCHAR *>(const_cast<char*>("'TABLE','VIEW'")), SQL_NTS ) ) )
1776 return FALSE;
1777
1778 return CollectResultsInfo();
1779 }
1780
1781 /************************************************************************/
1782 /* DumpResult() */
1783 /************************************************************************/
1784
1785 /**
1786 * Dump resultset to file.
1787 *
1788 * The contents of the current resultset are dumped in a simply formatted
1789 * form to the provided file. If requested, the schema definition will
1790 * be written first.
1791 *
1792 * @param fp the file to write to. stdout or stderr are acceptable.
1793 *
1794 * @param bShowSchema TRUE to force writing schema information for the rowset
1795 * before the rowset data itself. Default is FALSE.
1796 */
1797
DumpResult(FILE * fp,int bShowSchema)1798 void CPLODBCStatement::DumpResult( FILE *fp, int bShowSchema )
1799
1800 {
1801 /* -------------------------------------------------------------------- */
1802 /* Display schema */
1803 /* -------------------------------------------------------------------- */
1804 if( bShowSchema )
1805 {
1806 fprintf( fp, "Column Definitions:\n" );
1807 for( int iCol = 0; iCol < GetColCount(); iCol++ )
1808 {
1809 fprintf( fp, " %2d: %-24s ", iCol, GetColName(iCol) );
1810 if( GetColPrecision(iCol) > 0
1811 && GetColPrecision(iCol) != GetColSize(iCol) )
1812 fprintf( fp, " Size:%3d.%d",
1813 GetColSize(iCol), GetColPrecision(iCol) );
1814 else
1815 fprintf( fp, " Size:%5d", GetColSize(iCol) );
1816
1817 CPLString osType = GetTypeName( GetColType(iCol) );
1818 fprintf( fp, " Type:%s", osType.c_str() );
1819 if( GetColNullable(iCol) )
1820 fprintf( fp, " NULLABLE" );
1821 fprintf( fp, "\n" );
1822 }
1823 fprintf( fp, "\n" );
1824 }
1825
1826 /* -------------------------------------------------------------------- */
1827 /* Display results */
1828 /* -------------------------------------------------------------------- */
1829 int iRecord = 0;
1830 while( Fetch() )
1831 {
1832 fprintf( fp, "Record %d\n", iRecord++ );
1833
1834 for( int iCol = 0; iCol < GetColCount(); iCol++ )
1835 {
1836 fprintf( fp, " %s: %s\n", GetColName(iCol), GetColData(iCol) );
1837 }
1838 }
1839 }
1840
1841 /************************************************************************/
1842 /* GetTypeName() */
1843 /************************************************************************/
1844
1845 /**
1846 * Get name for SQL column type.
1847 *
1848 * Returns a string name for the indicated type code (as returned
1849 * from CPLODBCStatement::GetColType()).
1850 *
1851 * @param nTypeCode the SQL_ code, such as SQL_CHAR.
1852 *
1853 * @return internal string, "UNKNOWN" if code not recognised.
1854 */
1855
GetTypeName(int nTypeCode)1856 CPLString CPLODBCStatement::GetTypeName( int nTypeCode )
1857
1858 {
1859 switch( nTypeCode )
1860 {
1861 case SQL_CHAR:
1862 return "CHAR";
1863
1864 case SQL_NUMERIC:
1865 return "NUMERIC";
1866
1867 case SQL_DECIMAL:
1868 return "DECIMAL";
1869
1870 case SQL_INTEGER:
1871 return "INTEGER";
1872
1873 case SQL_SMALLINT:
1874 return "SMALLINT";
1875
1876 case SQL_FLOAT:
1877 return "FLOAT";
1878
1879 case SQL_REAL:
1880 return "REAL";
1881
1882 case SQL_DOUBLE:
1883 return "DOUBLE";
1884
1885 case SQL_DATETIME:
1886 return "DATETIME";
1887
1888 case SQL_VARCHAR:
1889 return "VARCHAR";
1890
1891 case SQL_TYPE_DATE:
1892 return "DATE";
1893
1894 case SQL_TYPE_TIME:
1895 return "TIME";
1896
1897 case SQL_TYPE_TIMESTAMP:
1898 return "TIMESTAMP";
1899
1900 default:
1901 CPLString osResult;
1902 osResult.Printf( "UNKNOWN:%d", nTypeCode );
1903 return osResult;
1904 }
1905 }
1906
1907 /************************************************************************/
1908 /* GetTypeMapping() */
1909 /************************************************************************/
1910
1911 /**
1912 * Get appropriate C data type for SQL column type.
1913 *
1914 * Returns a C data type code, corresponding to the indicated SQL data
1915 * type code (as returned from CPLODBCStatement::GetColType()).
1916 *
1917 * @param nTypeCode the SQL_ code, such as SQL_CHAR.
1918 *
1919 * @return data type code. The valid code is always returned. If SQL
1920 * code is not recognised, SQL_C_BINARY will be returned.
1921 */
1922
GetTypeMapping(SQLSMALLINT nTypeCode)1923 SQLSMALLINT CPLODBCStatement::GetTypeMapping( SQLSMALLINT nTypeCode )
1924
1925 {
1926 switch( nTypeCode )
1927 {
1928 case SQL_CHAR:
1929 case SQL_VARCHAR:
1930 case SQL_LONGVARCHAR:
1931 return SQL_C_CHAR;
1932
1933 case SQL_WCHAR:
1934 case SQL_WVARCHAR:
1935 case SQL_WLONGVARCHAR:
1936 return SQL_C_WCHAR;
1937
1938 case SQL_DECIMAL:
1939 case SQL_NUMERIC:
1940 return SQL_C_NUMERIC;
1941
1942 case SQL_SMALLINT:
1943 return SQL_C_SSHORT;
1944
1945 case SQL_INTEGER:
1946 return SQL_C_SLONG;
1947
1948 case SQL_REAL:
1949 return SQL_C_FLOAT;
1950
1951 case SQL_FLOAT:
1952 case SQL_DOUBLE:
1953 return SQL_C_DOUBLE;
1954
1955 case SQL_BIGINT:
1956 return SQL_C_SBIGINT;
1957
1958 case SQL_BIT:
1959 case SQL_TINYINT:
1960 // case SQL_TYPE_UTCDATETIME:
1961 // case SQL_TYPE_UTCTIME:
1962 case SQL_INTERVAL_MONTH:
1963 case SQL_INTERVAL_YEAR:
1964 case SQL_INTERVAL_YEAR_TO_MONTH:
1965 case SQL_INTERVAL_DAY:
1966 case SQL_INTERVAL_HOUR:
1967 case SQL_INTERVAL_MINUTE:
1968 case SQL_INTERVAL_SECOND:
1969 case SQL_INTERVAL_DAY_TO_HOUR:
1970 case SQL_INTERVAL_DAY_TO_MINUTE:
1971 case SQL_INTERVAL_DAY_TO_SECOND:
1972 case SQL_INTERVAL_HOUR_TO_MINUTE:
1973 case SQL_INTERVAL_HOUR_TO_SECOND:
1974 case SQL_INTERVAL_MINUTE_TO_SECOND:
1975 return SQL_C_CHAR;
1976
1977 case SQL_GUID:
1978 return SQL_C_GUID;
1979
1980 case SQL_DATE:
1981 case SQL_TYPE_DATE:
1982 return SQL_C_DATE;
1983
1984 case SQL_TIME:
1985 case SQL_TYPE_TIME:
1986 return SQL_C_TIME;
1987
1988 case SQL_TIMESTAMP:
1989 case SQL_TYPE_TIMESTAMP:
1990 return SQL_C_TIMESTAMP;
1991
1992 case SQL_BINARY:
1993 case SQL_VARBINARY:
1994 case SQL_LONGVARBINARY:
1995 case -151: // SQL_SS_UDT
1996 return SQL_C_BINARY;
1997
1998 default:
1999 return SQL_C_CHAR;
2000 }
2001 }
2002