irods  4.2.8
About: iRODS (the integrated Rule Oriented Data System) is a distributed data-management system for creating data grids, digital libraries, persistent archives, and real-time data systems.
  Fossies Dox: irods-4.2.8.tar.gz  ("unofficial" and yet experimental doxygen-generated source code documentation)  

low_level_odbc.cpp
Go to the documentation of this file.
1 
3 /*
4 
5  These are the Catalog Low Level (cll) routines for talking to postgresql.
6 
7  For each of the supported database systems there is .c file like this
8  one with a set of routines by the same names.
9 
10  Callable functions:
11  cllOpenEnv
12  cllCloseEnv
13  cllConnect
14  cllDisconnect
15  cllGetRowCount
16  cllExecSqlNoResult
17  cllExecSqlWithResult
18  cllDoneWithResult
19  cllDoneWithDefaultResult
20  cllGetRow
21  cllGetRows
22  cllGetNumberOfColumns
23  cllGetColumnInfo
24  cllNextValueString
25 
26  Internal functions are those that do not begin with cll.
27  The external functions used are those that begin with SQL.
28 
29 */
30 
31 #include "low_level_odbc.hpp"
32 
33 #include "irods_log.hpp"
34 #include "irods_error.hpp"
35 #include "irods_stacktrace.hpp"
37 
38 #include <cctype>
39 #include <string>
40 
41 int _cllFreeStatementColumns( icatSessionStruct *icss, int statementNumber );
42 
43 int
44 _cllExecSqlNoResult( icatSessionStruct *icss, const char *sql, int option );
45 
46 
49 int cllBindVarCountPrev = 0; /* cllBindVarCount earlier in processing */
50 
51 SQLCHAR psgErrorMsg[SQL_MAX_MESSAGE_LENGTH + 10];
52 const static SQLLEN GLOBAL_SQL_NTS = SQL_NTS;
53 
54 /* Different argument types are needed on at least Ubuntu 11.04 on a
55  64-bit host when using MySQL, but may or may not apply to all
56  64-bit hosts. The ODBCVER in sql.h is the same, 0x0351, but some
57  of the defines differ. If it's using new defines and this isn't
58  used, there may be compiler warnings but it might link OK, but not
59  operate correctly consistently. */
60 #define SQL_INT_OR_LEN SQLLEN
61 #define SQL_UINT_OR_ULEN SQLULEN
62 
63 /* for now: */
64 #define MAX_TOKEN 256
65 
66 #define TMP_STR_LEN 1040
67 
68 SQLINTEGER columnLength[MAX_TOKEN]; /* change me ! */
69 
70 #include <stdio.h>
71 #include <pwd.h>
72 #include <ctype.h>
73 
74 #include <vector>
75 #include <string>
76 
77 #ifndef ORA_ICAT
78 static int didBegin = 0;
79 #endif
80 static int noResultRowCount = 0;
81 
82 // =-=-=-=-=-=-=-
83 // JMC :: Needed to add this due to crash issues with the SQLBindCol + SQLFetch
84 // :: combination where the fetch fails if a var is not passed to the bind for
85 // :: the result data size
86 static const short MAX_NUMBER_ICAT_COLUMS = 32;
88 
89 
90 /*
91  call SQLError to get error information and log it
92 */
93 int
94 logPsgError( int level, HENV henv, HDBC hdbc, HSTMT hstmt, int dbType ) {
95  SQLCHAR sqlstate[ SQL_SQLSTATE_SIZE + 10];
96  SQLINTEGER sqlcode;
97  SQLSMALLINT length;
98  int errorVal = -2;
99  while ( SQLError( henv, hdbc, hstmt, sqlstate, &sqlcode, psgErrorMsg,
100  SQL_MAX_MESSAGE_LENGTH + 1, &length ) == SQL_SUCCESS ) {
101  if ( dbType == DB_TYPE_MYSQL ) {
102  if ( strcmp( ( char * )sqlstate, "23000" ) == 0 &&
103  strstr( ( char * )psgErrorMsg, "Duplicate entry" ) ) {
105  }
106  } else if (dbType == DB_TYPE_POSTGRES ) {
107  if ( strcmp( ( char * )sqlstate, "23505" ) == 0 &&
108  strstr( ( char * )psgErrorMsg, "duplicate key" ) ) {
110  }
111  } else if ( dbType == DB_TYPE_ORACLE ) {
112  if ( strcmp( ( char * )sqlstate, "23000" ) == 0 &&
113  strstr( ( char * )psgErrorMsg, "unique constraint" ) ) {
115  }
116  }
117 
118  rodsLog( level, "SQLSTATE: %s", sqlstate );
119  rodsLog( level, "SQLCODE: %ld", sqlcode );
120  rodsLog( level, "SQL Error message: %s", psgErrorMsg );
121  }
122  return errorVal;
123 }
124 
125 int
126 cllGetLastErrorMessage( char *msg, int maxChars ) {
127  strncpy( msg, ( char * )&psgErrorMsg, maxChars );
128  return 0;
129 }
130 
131 /*
132  Allocate the environment structure for use by the SQL routines.
133 */
134 int
136 
137  HENV myHenv;
138  if ( SQLAllocEnv( &myHenv ) != SQL_SUCCESS ) {
139  rodsLog( LOG_ERROR, "cllOpenEnv: SQLAllocHandle failed for env" );
140  return -1;
141  }
142 
143  icss->environPtr = myHenv;
144  return 0;
145 }
146 
147 
148 /*
149  Deallocate the environment structure.
150 */
151 int
153 
154  SQLRETURN stat = SQLFreeEnv( icss->environPtr );
155 
156  if ( stat == SQL_SUCCESS ) {
157  icss->environPtr = NULL;
158  }
159  else {
160  rodsLog( LOG_ERROR, "cllCloseEnv: SQLFreeEnv failed" );
161  }
162  return stat;
163 }
164 
165 /*
166  Connect to the DBMS.
167 */
168 int
170 
171  HDBC myHdbc;
172  SQLRETURN stat = SQLAllocHandle( SQL_HANDLE_DBC, icss->environPtr, &myHdbc );
173  if ( stat != SQL_SUCCESS ) {
174  rodsLog( LOG_ERROR, "cllConnect: SQLAllocHandle failed for connect: %s", stat );
175  return -1;
176  }
177 
178  // =-=-=-=-=-=-=-
179  // ODBC Entry is defined as "iRODS Catalog" or an env variable
180  char odbcEntryName[ DB_TYPENAME_LEN ];
181  char* odbc_env = getenv( "irodsOdbcDSN" );
182  if ( odbc_env ) {
183  rodsLog( LOG_DEBUG, "Setting ODBC entry to ENV [%s]", odbc_env );
184  snprintf( odbcEntryName, sizeof( odbcEntryName ), "%s", odbc_env );
185  }
186  else {
187  snprintf( odbcEntryName, sizeof( odbcEntryName ), "iRODS Catalog" );
188  }
189 
190  // =-=-=-=-=-=-=-
191  // initialize a connection to the catalog
192  stat = SQLConnect(
193  myHdbc,
194  ( unsigned char * )odbcEntryName, strlen( odbcEntryName ),
195  ( unsigned char * )icss->databaseUsername, strlen( icss->databaseUsername ),
196  ( unsigned char * )icss->databasePassword, strlen( icss->databasePassword ) );
197  if ( stat != SQL_SUCCESS ) {
198  rodsLog( LOG_ERROR, "cllConnect: SQLConnect failed: %d", stat );
200  "cllConnect: SQLConnect failed:odbcEntry=%s,user=%s,pass=XXXXX\n",
201  odbcEntryName,
203  SQLCHAR sqlstate[SQL_SQLSTATE_SIZE + 1];
204  SQLINTEGER sqlcode;
205  SQLCHAR buffer[SQL_MAX_MESSAGE_LENGTH + 1];
206  SQLSMALLINT length;
207  while ( SQLError( icss->environPtr, myHdbc , 0, sqlstate, &sqlcode, buffer,
208  SQL_MAX_MESSAGE_LENGTH + 1, &length ) == SQL_SUCCESS ) {
209  rodsLog( LOG_ERROR, "cllConnect: SQLSTATE: %s\n", sqlstate );
210  rodsLog( LOG_ERROR, "cllConnect: Native Error Code: %ld\n", sqlcode );
211  rodsLog( LOG_ERROR, "cllConnect: %s \n", buffer );
212  }
213 
214  SQLDisconnect( myHdbc );
215  SQLFreeHandle( SQL_HANDLE_DBC, myHdbc );
216  return -1;
217  }
218 
219  icss->connectPtr = myHdbc;
220 
221  if ( icss->databaseType == DB_TYPE_MYSQL ) {
222  /* MySQL must be running in ANSI mode (or at least in
223  PIPES_AS_CONCAT mode) to be able to understand Postgres
224  SQL. STRICT_TRANS_TABLES must be st too, otherwise inserting NULL
225  into NOT NULL column does not produce error. */
226  cllExecSqlNoResult( icss, "SET SESSION autocommit=0" ) ;
227  cllExecSqlNoResult( icss, "SET SESSION sql_mode='ANSI,STRICT_TRANS_TABLES'" ) ;
228  cllExecSqlNoResult( icss, "SET character_set_client = utf8" ) ;
229  cllExecSqlNoResult( icss, "SET character_set_results = utf8" ) ;
230  cllExecSqlNoResult( icss, "SET character_set_connection = utf8" ) ;
231  }
232 
233  return 0;
234 }
235 
236 /*
237  This function is used to check that there are no DB-modifying SQLs pending
238  before a disconnect. If there are, it logs a warning.
239 
240  If option is 0, record some of the sql, or clear it (if commit or rollback).
241  If option is 1, issue warning the there are some pending (and include
242  some of the sql).
243  If option is 2, this is indicating that the previous option 0 call was
244  for an audit-type SQL.
245 */
246 #define maxPendingToRecord 5
247 #define pendingRecordSize 30
248 #define pBufferSize (maxPendingToRecord*pendingRecordSize)
249 int
250 cllCheckPending( const char *sql, int option, int dbType ) {
251  static int pendingCount = 0;
252  static int pendingIx = 0;
253  static int pendingAudits = 0;
254  static char pBuffer[pBufferSize + 2];
255  static int firstTime = 1;
256 
257  if ( firstTime ) {
258  firstTime = 0;
259  memset( pBuffer, 0, pBufferSize );
260  }
261  if ( option == 0 ) {
262  if ( strncmp( sql, "commit", 6 ) == 0 ||
263  strncmp( sql, "rollback", 8 ) == 0 ) {
264  pendingIx = 0;
265  pendingCount = 0;
266  pendingAudits = 0;
267  memset( pBuffer, 0, pBufferSize );
268  return 0;
269  }
270  if ( pendingIx < maxPendingToRecord ) {
271  strncpy( ( char * )&pBuffer[pendingIx * pendingRecordSize], sql,
272  pendingRecordSize - 1 );
273  pendingIx++;
274  }
275  pendingCount++;
276  return 0;
277  }
278  if ( option == 2 ) {
279  pendingAudits++;
280  return 0;
281  }
282 
283  /* if there are some non-Audit pending SQL, log them */
284  if ( pendingCount > pendingAudits ) {
285  /* but ignore a single pending "begin" which can be normal */
286  if ( pendingIx == 1 ) {
287  if ( strncmp( ( char * )&pBuffer[0], "begin", 5 ) == 0 ) {
288  return 0;
289  }
290  }
291  if ( dbType == DB_TYPE_MYSQL ) {
292  /* For mySQL, may have a few SET SESSION sql too, which we
293  should ignore */
294  int skip = 1;
295  if ( strncmp( ( char * )&pBuffer[0], "begin", 5 ) != 0 ) {
296  skip = 0;
297  }
298  int max = maxPendingToRecord;
299  if ( pendingIx < max ) {
300  max = pendingIx;
301  }
302  for ( int i = 1; i < max && skip == 1; i++ ) {
303  if ( (strncmp((char *)&pBuffer[i*pendingRecordSize], "SET SESSION", 11) !=0)
304  && (strncmp((char *)&pBuffer[i*pendingRecordSize], "SET character", 13) !=0)) {
305  skip = 0;
306  }
307  }
308  if ( skip ) {
309  return 0;
310  }
311  }
312 
313  rodsLog( LOG_NOTICE, "Warning, pending SQL at cllDisconnect, count: %d",
314  pendingCount );
315  int max = maxPendingToRecord;
316  if ( pendingIx < max ) {
317  max = pendingIx;
318  }
319  for ( int i = 0; i < max; i++ ) {
320  rodsLog( LOG_NOTICE, "Warning, pending SQL: %s ...",
321  ( char * )&pBuffer[i * pendingRecordSize] );
322  }
323  if ( pendingAudits > 0 ) {
324  rodsLog( LOG_NOTICE, "Warning, SQL will be commited with audits" );
325  }
326  }
327 
328  if ( pendingAudits > 0 ) {
330  "Notice, pending Auditing SQL committed at cllDisconnect" );
331  return 1; /* tell caller (cllDisconect) to do a commit */
332  }
333  return 0;
334 }
335 
336 /*
337  Disconnect from the DBMS.
338 */
339 int
341 
342  int i = cllCheckPending( "", 1, icss->databaseType );
343  if ( i == 1 ) {
344  i = cllExecSqlNoResult( icss, "commit" ); /* auto commit any
345  pending SQLs, including
346  the Audit ones */
347  /* Nothing to do if it fails */
348  }
349 
350  SQLRETURN stat = SQLDisconnect( icss->connectPtr );
351  if ( stat != SQL_SUCCESS ) {
352  rodsLog( LOG_ERROR, "cllDisconnect: SQLDisconnect failed: %d", stat );
353  return -1;
354  }
355 
356  stat = SQLFreeHandle( SQL_HANDLE_DBC, icss->connectPtr );
357  if ( stat == SQL_SUCCESS ) {
358  icss->connectPtr = NULL;
359  }
360  else {
361  rodsLog( LOG_ERROR, "cllDisconnect: SQLFreeHandle failed for connect: %d", stat );
362  return -2;
363  }
364 
365  return 0;
366 }
367 /*
368  Execute a SQL command which has no resulting table. Examples include
369  insert, delete, update, or ddl.
370  Insert a 'begin' statement, if necessary.
371 */
372 int
374 
375 #ifndef ORA_ICAT
376  if ( strncmp( sql, "commit", 6 ) == 0 ||
377  strncmp( sql, "rollback", 8 ) == 0 ) {
378  didBegin = 0;
379  }
380  else {
381  if ( didBegin == 0 ) {
382  int status = _cllExecSqlNoResult( icss, "begin", 1 );
383  if ( status != SQL_SUCCESS ) {
384  return status;
385  }
386  }
387  didBegin = 1;
388  }
389 #endif
390  return _cllExecSqlNoResult( icss, sql, 0 );
391 }
392 
393 /*
394  Log the bind variables from the global array (after an error)
395 */
396 void
397 logTheBindVariables( int level ) {
398  for ( int i = 0; i < cllBindVarCountPrev; i++ ) {
399  char tmpStr[TMP_STR_LEN + 2];
400  snprintf( tmpStr, TMP_STR_LEN, "bindVar[%d]=%s", i + 1, cllBindVars[i] );
401  rodsLog( level, "%s", tmpStr );
402  }
403 }
404 
405 /*
406  Bind variables from the global array.
407 */
408 int
409 bindTheVariables( HSTMT myHstmt, const char *sql ) {
410 
411  int myBindVarCount = cllBindVarCount;
412  cllBindVarCountPrev = cllBindVarCount; /* save in case we need to log error */
413  cllBindVarCount = 0; /* reset for next call */
414 
415  for ( int i = 0; i < myBindVarCount; ++i ) {
416  SQLRETURN stat = SQLBindParameter( myHstmt, i + 1, SQL_PARAM_INPUT, SQL_C_CHAR,
417  SQL_CHAR, 0, 0, const_cast<char*>( cllBindVars[i] ), strlen( cllBindVars[i] ), const_cast<SQLLEN*>( &GLOBAL_SQL_NTS ) );
418  char tmpStr[TMP_STR_LEN];
419  snprintf( tmpStr, sizeof( tmpStr ), "bindVar[%d]=%s", i + 1, cllBindVars[i] );
420  rodsLogSql( tmpStr );
421  if ( stat != SQL_SUCCESS ) {
423  "bindTheVariables: SQLBindParameter failed: %d", stat );
424  return -1;
425  }
426  }
427 
428  return 0;
429 }
430 
431 /*
432  Case-insensitive string comparison, first string can be any case and
433  contain leading and trailing spaces, second string must be lowercase,
434  no spaces.
435 */
436 static int cmp_stmt( const char *str1, const char *str2 ) {
437  /* skip leading spaces */
438  while ( isspace( *str1 ) ) {
439  ++str1 ;
440  }
441 
442  /* start comparing */
443  for ( ; *str1 && *str2 ; ++str1, ++str2 ) {
444  if ( tolower( *str1 ) != *str2 ) {
445  return 0 ;
446  }
447  }
448 
449  /* skip trailing spaces */
450  while ( isspace( *str1 ) ) {
451  ++str1 ;
452  }
453 
454  /* if we are at the end of the strings then they are equal */
455  return *str1 == *str2 ;
456 }
457 
458 /*
459  Execute a SQL command which has no resulting table. With optional
460  bind variables.
461  If option is 1, skip the bind variables.
462 */
463 int
466  const char* sql,
467  int option ) {
468  rodsLog( LOG_DEBUG10, "%s", sql );
469 
470  HDBC myHdbc = icss->connectPtr;
471  HSTMT myHstmt;
472  SQLRETURN stat = SQLAllocHandle( SQL_HANDLE_STMT, myHdbc, &myHstmt );
473  if ( stat != SQL_SUCCESS ) {
474  rodsLog( LOG_ERROR, "_cllExecSqlNoResult: SQLAllocHandle failed for statement: %d", stat );
475  return -1;
476  }
477 
478  if ( option == 0 && bindTheVariables( myHstmt, sql ) != 0 ) {
479  return -1;
480  }
481 
482  rodsLogSql( sql );
483 
484  stat = SQLExecDirect( myHstmt, ( unsigned char * )sql, strlen( sql ) );
485  SQL_INT_OR_LEN rowCount = 0;
486  SQLRowCount( myHstmt, ( SQL_INT_OR_LEN * )&rowCount );
487  switch ( stat ) {
488  case SQL_SUCCESS:
489  rodsLogSqlResult( "SUCCESS" );
490  break;
492  rodsLogSqlResult( "SUCCESS_WITH_INFO" );
493  break;
494  case SQL_NO_DATA_FOUND:
495  rodsLogSqlResult( "NO_DATA" );
496  break;
497  case SQL_ERROR:
498  rodsLogSqlResult( "SQL_ERROR" );
499  break;
500  case SQL_INVALID_HANDLE:
501  rodsLogSqlResult( "HANDLE_ERROR" );
502  break;
503  default:
504  rodsLogSqlResult( "UNKNOWN" );
505  }
506 
507  int result;
508  if ( stat == SQL_SUCCESS ||
509  stat == SQL_SUCCESS_WITH_INFO ||
510  stat == SQL_NO_DATA_FOUND ) {
511  cllCheckPending( sql, 0, icss->databaseType );
512  result = 0;
513  if ( stat == SQL_NO_DATA_FOUND ) {
515  }
516  /* ODBC says that if statement is not UPDATE, INSERT, or DELETE then
517  SQLRowCount may return anything. So for BEGIN, COMMIT and ROLLBACK
518  we don't want to call it but just return OK.
519  */
520  if ( ! cmp_stmt( sql, "begin" ) &&
521  ! cmp_stmt( sql, "commit" ) &&
522  ! cmp_stmt( sql, "rollback" ) ) {
523  /* Doesn't seem to return SQL_NO_DATA_FOUND, so check */
524  rowCount = 0;
525  if ( SQLRowCount( myHstmt, ( SQL_INT_OR_LEN * )&rowCount ) ) {
526  /* error getting rowCount???, just call it no_info */
528  }
529  if ( rowCount == 0 ) {
531  }
532  }
533  }
534  else {
535  if ( option == 0 ) {
537  }
538  rodsLog( LOG_NOTICE, "_cllExecSqlNoResult: SQLExecDirect error: %d sql:%s",
539  stat, sql );
540  result = logPsgError( LOG_NOTICE, icss->environPtr, myHdbc, myHstmt,
541  icss->databaseType );
542  }
543 
544  stat = SQLFreeHandle( SQL_HANDLE_STMT, myHstmt );
545  if ( stat != SQL_SUCCESS ) {
546  rodsLog( LOG_ERROR, "_cllExecSqlNoResult: SQLFreeHandle for statement error: %d", stat );
547  }
548 
549  noResultRowCount = rowCount;
550 
551  return result;
552 }
553 
554 /*
555  Execute a SQL command that returns a result table, and
556  and bind the default row.
557  This version now uses the global array of bind variables.
558 */
559 int
560 cllExecSqlWithResult( icatSessionStruct *icss, int *stmtNum, const char *sql ) {
561 
562 
563  /* In 2.2 and some versions before, this would call
564  _cllExecSqlNoResult with "begin", similar to how cllExecSqlNoResult
565  does. But since this function is called for 'select's, this is not
566  needed here, and in fact causes postgres processes to be in the
567  'idle in transaction' state which prevents some operations (such as
568  backup). So this was removed. */
569  rodsLog( LOG_DEBUG10, "%s", sql );
570 
571  HDBC myHdbc = icss->connectPtr;
572  HSTMT hstmt;
573  SQLRETURN stat = SQLAllocHandle( SQL_HANDLE_STMT, myHdbc, &hstmt );
574  if ( stat != SQL_SUCCESS ) {
575  rodsLog( LOG_ERROR, "cllExecSqlWithResult: SQLAllocHandle failed for statement: %d",
576  stat );
577  return -1;
578  }
579 
580  // Issue 3862: Set stmtNum to -1 and in cllFreeStatement if the stmtNum is negative do nothing
582 
583  int statementNumber = UNINITIALIZED_STATEMENT_NUMBER;
584  for ( int i = 0; i < MAX_NUM_OF_CONCURRENT_STMTS && statementNumber < 0; i++ ) {
585  if ( icss->stmtPtr[i] == 0 ) {
586  statementNumber = i;
587  }
588  }
589  if ( statementNumber < 0 ) {
591  "cllExecSqlWithResult: too many concurrent statements" );
593  }
594 
595  icatStmtStrct * myStatement = ( icatStmtStrct * )malloc( sizeof( icatStmtStrct ) );
596  memset( myStatement, 0, sizeof( icatStmtStrct ) );
597 
598  icss->stmtPtr[statementNumber] = myStatement;
599  *stmtNum = statementNumber;
600 
601  myStatement->stmtPtr = hstmt;
602 
603  if ( bindTheVariables( hstmt, sql ) != 0 ) {
604  return -1;
605  }
606 
607  rodsLogSql( sql );
608  stat = SQLExecDirect( hstmt, ( unsigned char * )sql, strlen( sql ) );
609 
610  switch ( stat ) {
611  case SQL_SUCCESS:
612  rodsLogSqlResult( "SUCCESS" );
613  break;
615  rodsLogSqlResult( "SUCCESS_WITH_INFO" );
616  break;
617  case SQL_NO_DATA_FOUND:
618  rodsLogSqlResult( "NO_DATA" );
619  break;
620  case SQL_ERROR:
621  rodsLogSqlResult( "SQL_ERROR" );
622  break;
623  case SQL_INVALID_HANDLE:
624  rodsLogSqlResult( "HANDLE_ERROR" );
625  break;
626  default:
627  rodsLogSqlResult( "UNKNOWN" );
628  }
629 
630  if ( stat != SQL_SUCCESS &&
631  stat != SQL_SUCCESS_WITH_INFO &&
632  stat != SQL_NO_DATA_FOUND ) {
635  "cllExecSqlWithResult: SQLExecDirect error: %d, sql:%s",
636  stat, sql );
637  logPsgError( LOG_NOTICE, icss->environPtr, myHdbc, hstmt,
638  icss->databaseType );
639  return -1;
640  }
641 
642  SQLSMALLINT numColumns;
643  stat = SQLNumResultCols( hstmt, &numColumns );
644  if ( stat != SQL_SUCCESS ) {
645  rodsLog( LOG_ERROR, "cllExecSqlWithResult: SQLNumResultCols failed: %d",
646  stat );
647  return -2;
648  }
649  myStatement->numOfCols = numColumns;
650  for ( int i = 0; i < numColumns; i++ ) {
651  SQLCHAR colName[MAX_TOKEN] = "";
652  SQLSMALLINT colNameLen;
653  SQLSMALLINT colType;
654  SQL_UINT_OR_ULEN precision;
655  SQLSMALLINT scale;
656  stat = SQLDescribeCol( hstmt, i + 1, colName, sizeof( colName ),
657  &colNameLen, &colType, &precision, &scale, NULL );
658  if ( stat != SQL_SUCCESS ) {
659  rodsLog( LOG_ERROR, "cllExecSqlWithResult: SQLDescribeCol failed: %d",
660  stat );
661  return -3;
662  }
663  /* printf("colName='%s' precision=%d\n",colName, precision); */
664  columnLength[i] = precision;
665  SQL_INT_OR_LEN displaysize;
666  stat = SQLColAttribute( hstmt, i + 1, SQL_COLUMN_DISPLAY_SIZE,
667  NULL, 0, NULL, &displaysize ); // JMC :: fixed for odbc
668  if ( stat != SQL_SUCCESS ) {
670  "cllExecSqlWithResult: SQLColAttributes failed: %d",
671  stat );
672  return -3;
673  }
674 
675  if ( displaysize > ( ( int )strlen( ( char * ) colName ) ) ) {
676  columnLength[i] = displaysize + 1;
677  }
678  else {
679  columnLength[i] = strlen( ( char * ) colName ) + 1;
680  }
681  /* printf("columnLength[%d]=%d\n",i,columnLength[i]); */
682 
683  myStatement->resultValue[i] = ( char* )malloc( ( int )columnLength[i] );
684  memset( myStatement->resultValue[i], 0, (int)columnLength[i] );
685 
686  strcpy( ( char * )myStatement->resultValue[i], "" );
687 
688  // =-=-=-=-=-=-=-
689  // JMC :: added static array to catch the result set size. this was necessary to
690  stat = SQLBindCol( hstmt, i + 1, SQL_C_CHAR, myStatement->resultValue[i], columnLength[i], &resultDataSizeArray[ i ] );
691  if ( stat != SQL_SUCCESS ) {
693  "cllExecSqlWithResult: SQLColAttributes failed: %d",
694  stat );
695  return -4;
696  }
697 
698 
699  myStatement->resultColName[i] = ( char* )malloc( ( int )columnLength[i] );
700  memset( myStatement->resultColName[i], 0, (int)columnLength[i] );
701 
702 #ifdef ORA_ICAT
703  //oracle prints column names (which are case-insensitive) in upper case,
704  //so to remain consistent with postgres and mysql, we convert them to lower case.
705  for ( int j = 0; j < columnLength[i] && colName[j] != '\0'; j++ ) {
706  colName[j] = tolower( colName[j] );
707  }
708 #endif
709  strncpy( myStatement->resultColName[i], ( char * )colName, columnLength[i] );
710 
711  }
712 
713  return 0;
714 }
715 
716 /* logBindVars
717  For when an error occurs, log the bind variables which were used
718  with the sql.
719 */
720 void
722  int level,
723  std::vector<std::string> &bindVars ) {
724  for ( std::size_t i = 0; i < bindVars.size(); i++ ) {
725  if ( !bindVars[i].empty() ) {
726  rodsLog( level, "bindVar%d=%s", i + 1, bindVars[i].c_str() );
727  }
728  }
729 }
730 
731 
732 /*
733  Execute a SQL command that returns a result table, and
734  and bind the default row; and allow optional bind variables.
735 */
736 int
739  int *stmtNum,
740  const char *sql,
741  std::vector< std::string > &bindVars ) {
742 
743  rodsLog( LOG_DEBUG10, "%s", sql );
744 
745  HDBC myHdbc = icss->connectPtr;
746  HSTMT hstmt;
747  SQLRETURN stat = SQLAllocHandle( SQL_HANDLE_STMT, myHdbc, &hstmt );
748  if ( stat != SQL_SUCCESS ) {
749  rodsLog( LOG_ERROR, "cllExecSqlWithResultBV: SQLAllocHandle failed for statement: %d",
750  stat );
751  return -1;
752  }
753 
754  // Issue 3862: Set stmtNum to -1 and in cllFreeStatement if the stmtNum is negative do nothing
756 
757  int statementNumber = UNINITIALIZED_STATEMENT_NUMBER;
758  for ( int i = 0; i < MAX_NUM_OF_CONCURRENT_STMTS && statementNumber < 0; i++ ) {
759  if ( icss->stmtPtr[i] == 0 ) {
760  statementNumber = i;
761  }
762  }
763  if ( statementNumber < 0 ) {
765  "cllExecSqlWithResultBV: too many concurrent statements" );
767  }
768 
769  icatStmtStrct * myStatement = ( icatStmtStrct * )malloc( sizeof( icatStmtStrct ) );
770  memset( myStatement, 0, sizeof( icatStmtStrct ) );
771  icss->stmtPtr[statementNumber] = myStatement;
772 
773  *stmtNum = statementNumber;
774 
775  myStatement->stmtPtr = hstmt;
776 
777  for ( std::size_t i = 0; i < bindVars.size(); i++ ) {
778  if ( !bindVars[i].empty() ) {
779 
780  stat = SQLBindParameter( hstmt, i + 1, SQL_PARAM_INPUT, SQL_C_CHAR,
781  SQL_CHAR, 0, 0, const_cast<char*>( bindVars[i].c_str() ), bindVars[i].size(), const_cast<SQLLEN*>( &GLOBAL_SQL_NTS ) );
782  char tmpStr[TMP_STR_LEN];
783  snprintf( tmpStr, sizeof( tmpStr ), "bindVar%ju=%s", static_cast<uintmax_t>(i + 1), bindVars[i].c_str() );
784  rodsLogSql( tmpStr );
785  if ( stat != SQL_SUCCESS ) {
787  "cllExecSqlWithResultBV: SQLBindParameter failed: %d", stat );
788  return -1;
789  }
790  }
791  }
792  rodsLogSql( sql );
793  stat = SQLExecDirect( hstmt, ( unsigned char * )sql, strlen( sql ) );
794 
795  switch ( stat ) {
796  case SQL_SUCCESS:
797  rodsLogSqlResult( "SUCCESS" );
798  break;
800  rodsLogSqlResult( "SUCCESS_WITH_INFO" );
801  break;
802  case SQL_NO_DATA_FOUND:
803  rodsLogSqlResult( "NO_DATA" );
804  break;
805  case SQL_ERROR:
806  rodsLogSqlResult( "SQL_ERROR" );
807  break;
808  case SQL_INVALID_HANDLE:
809  rodsLogSqlResult( "HANDLE_ERROR" );
810  break;
811  default:
812  rodsLogSqlResult( "UNKNOWN" );
813  }
814 
815  if ( stat != SQL_SUCCESS &&
816  stat != SQL_SUCCESS_WITH_INFO &&
817  stat != SQL_NO_DATA_FOUND ) {
818  logBindVars( LOG_NOTICE, bindVars );
820  "cllExecSqlWithResultBV: SQLExecDirect error: %d, sql:%s",
821  stat, sql );
822  logPsgError( LOG_NOTICE, icss->environPtr, myHdbc, hstmt,
823  icss->databaseType );
824  return -1;
825  }
826 
827  SQLSMALLINT numColumns;
828  stat = SQLNumResultCols( hstmt, &numColumns );
829  if ( stat != SQL_SUCCESS ) {
830  rodsLog( LOG_ERROR, "cllExecSqlWithResultBV: SQLNumResultCols failed: %d",
831  stat );
832  return -2;
833  }
834  myStatement->numOfCols = numColumns;
835 
836  for ( int i = 0; i < numColumns; i++ ) {
837  SQLCHAR colName[MAX_TOKEN] = "";
838  SQLSMALLINT colNameLen;
839  SQLSMALLINT colType;
840  SQL_UINT_OR_ULEN precision;
841  SQLSMALLINT scale;
842  stat = SQLDescribeCol( hstmt, i + 1, colName, sizeof( colName ),
843  &colNameLen, &colType, &precision, &scale, NULL );
844  if ( stat != SQL_SUCCESS ) {
845  rodsLog( LOG_ERROR, "cllExecSqlWithResultBV: SQLDescribeCol failed: %d",
846  stat );
847  return -3;
848  }
849  /* printf("colName='%s' precision=%d\n",colName, precision); */
850  columnLength[i] = precision;
851  SQL_INT_OR_LEN displaysize;
852  stat = SQLColAttribute( hstmt, i + 1, SQL_COLUMN_DISPLAY_SIZE,
853  NULL, 0, NULL, &displaysize ); // JMC :: changed to SQLColAttribute for odbc update
854  if ( stat != SQL_SUCCESS ) {
856  "cllExecSqlWithResultBV: SQLColAttributes failed: %d",
857  stat );
858  return -3;
859  }
860 
861  if ( displaysize > ( ( int )strlen( ( char * ) colName ) ) ) {
862  columnLength[i] = displaysize + 1;
863  }
864  else {
865  columnLength[i] = strlen( ( char * ) colName ) + 1;
866  }
867  /* printf("columnLength[%d]=%d\n",i,columnLength[i]); */
868 
869  myStatement->resultValue[i] = ( char* )malloc( ( int )columnLength[i] );
870  memset( myStatement->resultValue[i], 0, (int)columnLength[i] );
871 
872  myStatement->resultValue[i][0] = '\0';
873  // =-=-=-=-=-=-=-
874  // JMC :: added static array to catch the result set size. this was necessary to
875  stat = SQLBindCol( hstmt, i + 1, SQL_C_CHAR, myStatement->resultValue[i], columnLength[i], &resultDataSizeArray[i] );
876 
877  if ( stat != SQL_SUCCESS ) {
879  "cllExecSqlWithResultBV: SQLColAttributes failed: %d",
880  stat );
881  return -4;
882  }
883 
884 
885  myStatement->resultColName[i] = ( char* )malloc( ( int )columnLength[i] );
886  memset( myStatement->resultColName[i], 0, (int)columnLength[i] );
887 #ifdef ORA_ICAT
888  //oracle prints column names (which are case-insensitive) in upper case,
889  //so to remain consistent with postgres and mysql, we convert them to lower case.
890  for ( int j = 0; j < columnLength[i] && colName[j] != '\0'; j++ ) {
891  colName[j] = tolower( colName[j] );
892  }
893 #endif
894  strncpy( myStatement->resultColName[i], ( char * )colName, columnLength[i] );
895 
896  }
897 
898  return 0;
899 }
900 
901 /*
902  Return a row from a previous cllExecSqlWithResult call.
903 */
904 int
905 cllGetRow( icatSessionStruct *icss, int statementNumber ) {
906  icatStmtStrct *myStatement = icss->stmtPtr[statementNumber];
907 
908  for ( int i = 0; i < myStatement->numOfCols; i++ ) {
909  strcpy( ( char * )myStatement->resultValue[i], "" );
910  }
911  SQLRETURN stat = SQLFetch( myStatement->stmtPtr );
912  if ( stat != SQL_SUCCESS && stat != SQL_NO_DATA_FOUND ) {
913  rodsLog( LOG_ERROR, "cllGetRow: SQLFetch failed: %d", stat );
914  return -1;
915  }
916  if ( stat == SQL_NO_DATA_FOUND ) {
917  _cllFreeStatementColumns( icss, statementNumber );
918  myStatement->numOfCols = 0;
919  }
920  return 0;
921 }
922 
923 /*
924  Return the string needed to get the next value in a sequence item.
925  The syntax varies between RDBMSes, so it is here, in the DBMS-specific code.
926 */
927 int
928 cllNextValueString( const char *itemName, char *outString, int maxSize ) {
929 #ifdef ORA_ICAT
930  snprintf( outString, maxSize, "%s.nextval", itemName );
931 #elif MY_ICAT
932  snprintf( outString, maxSize, "%s_nextval()", itemName );
933 #else
934  snprintf( outString, maxSize, "nextval('%s')", itemName );
935 #endif
936  return 0;
937 }
938 
939 int
940 cllGetRowCount( icatSessionStruct *icss, int statementNumber ) {
941 
942  if ( statementNumber < 0 ) {
943  return noResultRowCount;
944  }
945 
946  icatStmtStrct * myStatement = icss->stmtPtr[statementNumber];
947  HSTMT hstmt = myStatement->stmtPtr;
948 
949  SQL_INT_OR_LEN RowCount;
950  int i = SQLRowCount( hstmt, ( SQL_INT_OR_LEN * )&RowCount );
951  if ( i ) {
952  return i;
953  }
954  return RowCount;
955 }
956 
957 int
958 cllCurrentValueString( const char *itemName, char *outString, int maxSize ) {
959 #ifdef ORA_ICAT
960  snprintf( outString, maxSize, "%s.currval", itemName );
961 #elif MY_ICAT
962  snprintf( outString, maxSize, "%s_currval()", itemName );
963 #else
964  snprintf( outString, maxSize, "currval('%s')", itemName );
965 #endif
966  return 0;
967 }
968 
969 /*
970  Free a statement (from a previous cllExecSqlWithResult call) and the
971  corresponding resultValue array.
972 */
973 int
974 cllFreeStatement( icatSessionStruct *icss, int& statementNumber ) {
975 
976  // Issue 3862 - Statement number is set to negative until it is
977  // created. When the statement is freed it is again set to negative.
978  // Do not free when statementNumber is negative. If this is called twice
979  // by a client, after the first call the statementNumber will be negative
980  // and nothing will be done.
981  if (statementNumber < 0) {
982  return 0;
983  }
984 
985  icatStmtStrct * myStatement = icss->stmtPtr[statementNumber];
986  if ( myStatement == NULL ) { /* already freed */
987  statementNumber = UNINITIALIZED_STATEMENT_NUMBER;
988  return 0;
989  }
990 
991  _cllFreeStatementColumns( icss, statementNumber );
992 
993  SQLRETURN stat = SQLFreeHandle( SQL_HANDLE_STMT, myStatement->stmtPtr );
994  if ( stat != SQL_SUCCESS ) {
995  statementNumber = UNINITIALIZED_STATEMENT_NUMBER;
996  rodsLog( LOG_ERROR, "cllFreeStatement SQLFreeHandle for statement error: %d", stat );
997  }
998 
999  free( myStatement );
1000  icss->stmtPtr[statementNumber] = NULL; /* indicate that the statement is free */
1001  statementNumber = UNINITIALIZED_STATEMENT_NUMBER;
1002 
1003  return 0;
1004 }
1005 
1006 /*
1007  Free the statement columns (from a previous cllExecSqlWithResult call),
1008  but not the whole statement.
1009 */
1010 int
1012 
1013  icatStmtStrct * myStatement = icss->stmtPtr[statementNumber];
1014 
1015  for ( int i = 0; i < myStatement->numOfCols; i++ ) {
1016  free( myStatement->resultValue[i] );
1017  myStatement->resultValue[i] = NULL;
1018  free( myStatement->resultColName[i] );
1019  myStatement->resultColName[i] = NULL;
1020  }
1021  return 0;
1022 }
rodsLog
void rodsLog(int level, const char *formatStr,...)
Definition: rodsLog.cpp:86
NULL
#define NULL
Definition: rodsDef.h:70
pBufferSize
#define pBufferSize
Definition: low_level_odbc.cpp:248
cllDisconnect
int cllDisconnect(icatSessionStruct *icss)
Definition: low_level_odbc.cpp:340
irods.pyparsing.empty
empty
Definition: pyparsing.py:3430
icatStmtStrct::resultColName
char * resultColName[30]
Definition: icatStructs.hpp:18
cllFreeStatement
int cllFreeStatement(icatSessionStruct *icss, int &statementNumber)
Definition: low_level_odbc.cpp:974
irods_server_properties.hpp
irods.pypyodbc.SQL_HANDLE_DBC
SQL_HANDLE_DBC
Definition: pypyodbc.py:83
cllGetRow
int cllGetRow(icatSessionStruct *icss, int statementNumber)
Definition: low_level_odbc.cpp:905
irods.pypyodbc.SQLBindParameter
SQLBindParameter
Definition: pypyodbc.py:913
irods_stacktrace.hpp
cllOpenEnv
int cllOpenEnv(icatSessionStruct *icss)
Definition: low_level_odbc.cpp:135
CATALOG_ALREADY_HAS_ITEM_BY_THAT_NAME
@ CATALOG_ALREADY_HAS_ITEM_BY_THAT_NAME
Definition: rodsErrorTable.h:424
icatStmtStrct
Definition: icatStructs.hpp:14
cllGetRowCount
int cllGetRowCount(icatSessionStruct *icss, int statementNumber)
Definition: low_level_odbc.cpp:940
rodsLogSqlResult
void rodsLogSqlResult(char *stat)
Definition: rodsLog.cpp:363
cllBindVars
const char * cllBindVars[32000]
Definition: low_level_odbc.cpp:48
cmp_stmt
static int cmp_stmt(const char *str1, const char *str2)
Definition: low_level_odbc.cpp:436
cllNextValueString
int cllNextValueString(const char *itemName, char *outString, int maxSize)
Definition: low_level_odbc.cpp:928
cllGetLastErrorMessage
int cllGetLastErrorMessage(char *msg, int maxChars)
Definition: low_level_odbc.cpp:126
irods.pypyodbc.SQL_C_CHAR
int SQL_C_CHAR
Definition: pypyodbc.py:544
icatSessionStruct::databaseUsername
char databaseUsername[64]
Definition: icatStructs.hpp:31
LOG_ERROR
#define LOG_ERROR
Definition: rodsLog.h:43
cllBindVarCountPrev
int cllBindVarCountPrev
Definition: low_level_odbc.cpp:49
irods.pypyodbc.SQLRowCount
SQLRowCount
Definition: pypyodbc.py:915
DB_TYPENAME_LEN
#define DB_TYPENAME_LEN
Definition: icatDefines.h:23
logPsgError
int logPsgError(int level, HENV henv, HDBC hdbc, HSTMT hstmt, int dbType)
Definition: low_level_odbc.cpp:94
resultDataSizeArray
static SQLLEN resultDataSizeArray[MAX_NUMBER_ICAT_COLUMS]
Definition: low_level_odbc.cpp:87
_cllFreeStatementColumns
int _cllFreeStatementColumns(icatSessionStruct *icss, int statementNumber)
Definition: low_level_odbc.cpp:1011
LOG_DEBUG
#define LOG_DEBUG
Definition: rodsLog.h:23
cllExecSqlNoResult
int cllExecSqlNoResult(icatSessionStruct *icss, const char *sql)
Definition: low_level_odbc.cpp:373
cllCheckPending
int cllCheckPending(const char *sql, int option, int dbType)
Definition: low_level_odbc.cpp:250
icatSessionStruct::databasePassword
char databasePassword[64]
Definition: icatStructs.hpp:32
logBindVars
void logBindVars(int level, std::vector< std::string > &bindVars)
Definition: low_level_odbc.cpp:721
GLOBAL_SQL_NTS
static const SQLLEN GLOBAL_SQL_NTS
Definition: low_level_odbc.cpp:52
pendingRecordSize
#define pendingRecordSize
Definition: low_level_odbc.cpp:247
irods.pypyodbc.buffer
buffer
Definition: pypyodbc.py:46
icatSessionStruct::stmtPtr
icatStmtStrct * stmtPtr[50]
Definition: icatStructs.hpp:30
cllExecSqlWithResult
int cllExecSqlWithResult(icatSessionStruct *icss, int *stmtNum, const char *sql)
Definition: low_level_odbc.cpp:560
icatStmtStrct::resultValue
char * resultValue[30]
Definition: icatStructs.hpp:21
irods.pypyodbc.status
status
Definition: pypyodbc.py:467
cllBindVarCount
int cllBindVarCount
Definition: low_level_odbc.cpp:47
irods.pypyodbc.SQL_HANDLE_STMT
SQL_HANDLE_STMT
Definition: pypyodbc.py:83
irods.pypyodbc.SQL_ERROR
SQL_ERROR
Definition: pypyodbc.py:84
maxPendingToRecord
#define maxPendingToRecord
Definition: low_level_odbc.cpp:246
cllConnect
int cllConnect(icatSessionStruct *icss)
Definition: low_level_odbc.cpp:169
SQL_UINT_OR_ULEN
#define SQL_UINT_OR_ULEN
Definition: low_level_odbc.cpp:61
irods.pypyodbc.SQL_INVALID_HANDLE
int SQL_INVALID_HANDLE
Definition: pypyodbc.py:99
LOG_NOTICE
#define LOG_NOTICE
Definition: rodsLog.h:33
CAT_SUCCESS_BUT_WITH_NO_INFO
@ CAT_SUCCESS_BUT_WITH_NO_INFO
Definition: rodsErrorTable.h:434
icss
icatSessionStruct icss
Definition: db_plugin.cpp:97
irods.pypyodbc.SQL_PARAM_INPUT
int SQL_PARAM_INPUT
Definition: pypyodbc.py:103
SQL_INT_OR_LEN
#define SQL_INT_OR_LEN
Definition: low_level_odbc.cpp:60
irods.pypyodbc.SQLNumResultCols
SQLNumResultCols
Definition: pypyodbc.py:916
UNINITIALIZED_STATEMENT_NUMBER
const int UNINITIALIZED_STATEMENT_NUMBER
Definition: mid_level.hpp:18
icatStmtStrct::numOfCols
int numOfCols
Definition: icatStructs.hpp:17
icatSessionStruct::databaseType
int databaseType
Definition: icatStructs.hpp:33
icatSessionStruct::environPtr
void * environPtr
Definition: icatStructs.hpp:28
icatSessionStruct::connectPtr
void * connectPtr
Definition: icatStructs.hpp:29
cllExecSqlWithResultBV
int cllExecSqlWithResultBV(icatSessionStruct *icss, int *stmtNum, const char *sql, std::vector< std::string > &bindVars)
Definition: low_level_odbc.cpp:737
psgErrorMsg
SQLCHAR psgErrorMsg[SQL_MAX_MESSAGE_LENGTH+10]
Definition: low_level_odbc.cpp:51
rodsLogSql
void rodsLogSql(const char *sql)
Definition: rodsLog.cpp:357
noResultRowCount
static int noResultRowCount
Definition: low_level_odbc.cpp:80
CAT_STATEMENT_TABLE_FULL
@ CAT_STATEMENT_TABLE_FULL
Definition: rodsErrorTable.h:466
MAX_NUM_OF_CONCURRENT_STMTS
#define MAX_NUM_OF_CONCURRENT_STMTS
Definition: icatDefines.h:14
low_level_odbc.hpp
irods.pypyodbc.SQL_NO_DATA_FOUND
int SQL_NO_DATA_FOUND
Definition: pypyodbc.py:100
irods.pypyodbc.SQL_SUCCESS_WITH_INFO
SQL_SUCCESS_WITH_INFO
Definition: pypyodbc.py:84
_cllExecSqlNoResult
int _cllExecSqlNoResult(icatSessionStruct *icss, const char *sql, int option)
Definition: low_level_odbc.cpp:464
irods.pypyodbc.SQL_SUCCESS
SQL_SUCCESS
Definition: pypyodbc.py:84
LOG_DEBUG10
#define LOG_DEBUG10
Definition: rodsLog.h:19
DB_TYPE_ORACLE
#define DB_TYPE_ORACLE
Definition: icatDefines.h:26
irods_error.hpp
icatStmtStrct::stmtPtr
void * stmtPtr
Definition: icatStructs.hpp:16
size
long long size
Definition: filesystem.cpp:102
MAX_BIND_VARS
#define MAX_BIND_VARS
Definition: low_level_odbc.hpp:20
TMP_STR_LEN
#define TMP_STR_LEN
Definition: low_level_odbc.cpp:66
bindTheVariables
int bindTheVariables(HSTMT myHstmt, const char *sql)
Definition: low_level_odbc.cpp:409
didBegin
static int didBegin
Definition: low_level_odbc.cpp:78
skip
int skip(Pointer *expr, char *text, Token **token, ParserContext *pc, int rulegen)
Definition: parser.cpp:147
logTheBindVariables
void logTheBindVariables(int level)
Definition: low_level_odbc.cpp:397
icatSessionStruct
Definition: icatStructs.hpp:26
MAX_TOKEN
#define MAX_TOKEN
Definition: low_level_odbc.cpp:64
DB_TYPE_MYSQL
#define DB_TYPE_MYSQL
Definition: icatDefines.h:27
irods.pypyodbc.SQLFetch
SQLFetch
Definition: pypyodbc.py:911
MAX_NUMBER_ICAT_COLUMS
static const short MAX_NUMBER_ICAT_COLUMS
Definition: low_level_odbc.cpp:86
cllCloseEnv
int cllCloseEnv(icatSessionStruct *icss)
Definition: low_level_odbc.cpp:152
irods.pypyodbc.SQL_COLUMN_DISPLAY_SIZE
int SQL_COLUMN_DISPLAY_SIZE
Definition: pypyodbc.py:98
columnLength
SQLINTEGER columnLength[256]
Definition: low_level_odbc.cpp:68
irods_log.hpp
cllCurrentValueString
int cllCurrentValueString(const char *itemName, char *outString, int maxSize)
Definition: low_level_odbc.cpp:958
DB_TYPE_POSTGRES
#define DB_TYPE_POSTGRES
Definition: icatDefines.h:25