1 /* $OpenLDAP: pkg/ldap/servers/slapd/back-sql/sql-wrap.c,v 1.43.2.5 2008/02/11 23:26:48 kurt Exp $ */ 2 /* This work is part of OpenLDAP Software <http://www.openldap.org/>. 3 * 4 * Copyright 1999-2008 The OpenLDAP Foundation. 5 * Portions Copyright 1999 Dmitry Kovalev. 6 * Portions Copyright 2002 Pierangelo Masarati. 7 * Portions Copyright 2004 Mark Adamson. 8 * All rights reserved. 9 * 10 * Redistribution and use in source and binary forms, with or without 11 * modification, are permitted only as authorized by the OpenLDAP 12 * Public License. 13 * 14 * A copy of this license is available in the file LICENSE in the 15 * top-level directory of the distribution or, alternatively, at 16 * <http://www.OpenLDAP.org/license.html>. 17 */ 18 /* ACKNOWLEDGEMENTS: 19 * This work was initially developed by Dmitry Kovalev for inclusion 20 * by OpenLDAP Software. Additional significant contributors include 21 * Pierangelo Masarati and Mark Adamson. 22 */ 23 24 #include "portable.h" 25 26 #include <stdio.h> 27 #include "ac/string.h" 28 #include <sys/types.h> 29 30 #include "slap.h" 31 #include "proto-sql.h" 32 33 #define MAX_ATTR_LEN 16384 34 35 void 36 backsql_PrintErrors( SQLHENV henv, SQLHDBC hdbc, SQLHSTMT sth, int rc ) 37 { 38 SQLCHAR msg[SQL_MAX_MESSAGE_LENGTH]; /* msg. buffer */ 39 SQLCHAR state[SQL_SQLSTATE_SIZE]; /* statement buf. */ 40 SDWORD iSqlCode; /* return code */ 41 SWORD len = SQL_MAX_MESSAGE_LENGTH - 1; /* return length */ 42 43 Debug( LDAP_DEBUG_TRACE, "Return code: %d\n", rc, 0, 0 ); 44 45 for ( ; rc = SQLError( henv, hdbc, sth, state, &iSqlCode, msg, 46 SQL_MAX_MESSAGE_LENGTH - 1, &len ), BACKSQL_SUCCESS( rc ); ) 47 { 48 Debug( LDAP_DEBUG_TRACE, 49 " nativeErrCode=%d SQLengineState=%s msg=\"%s\"\n", 50 (int)iSqlCode, state, msg ); 51 } 52 } 53 54 RETCODE 55 backsql_Prepare( SQLHDBC dbh, SQLHSTMT *sth, char *query, int timeout ) 56 { 57 RETCODE rc; 58 59 rc = SQLAllocStmt( dbh, sth ); 60 if ( rc != SQL_SUCCESS ) { 61 return rc; 62 } 63 64 #ifdef BACKSQL_TRACE 65 Debug( LDAP_DEBUG_TRACE, "==>backsql_Prepare()\n", 0, 0, 0 ); 66 #endif /* BACKSQL_TRACE */ 67 68 #ifdef BACKSQL_MSSQL_WORKAROUND 69 { 70 char drv_name[ 30 ]; 71 SWORD len; 72 73 SQLGetInfo( dbh, SQL_DRIVER_NAME, drv_name, sizeof( drv_name ), &len ); 74 75 #ifdef BACKSQL_TRACE 76 Debug( LDAP_DEBUG_TRACE, "backsql_Prepare(): driver name=\"%s\"\n", 77 drv_name, 0, 0 ); 78 #endif /* BACKSQL_TRACE */ 79 80 ldap_pvt_str2upper( drv_name ); 81 if ( !strncmp( drv_name, "SQLSRV32.DLL", STRLENOF( "SQLSRV32.DLL" ) ) ) { 82 /* 83 * stupid default result set in MS SQL Server 84 * does not support multiple active statements 85 * on the same connection -- so we are trying 86 * to make it not to use default result set... 87 */ 88 Debug( LDAP_DEBUG_TRACE, "_SQLprepare(): " 89 "enabling MS SQL Server default result " 90 "set workaround\n", 0, 0, 0 ); 91 rc = SQLSetStmtOption( *sth, SQL_CONCURRENCY, 92 SQL_CONCUR_ROWVER ); 93 if ( rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO ) { 94 Debug( LDAP_DEBUG_TRACE, "backsql_Prepare(): " 95 "SQLSetStmtOption(SQL_CONCURRENCY," 96 "SQL_CONCUR_ROWVER) failed:\n", 97 0, 0, 0 ); 98 backsql_PrintErrors( SQL_NULL_HENV, dbh, *sth, rc ); 99 SQLFreeStmt( *sth, SQL_DROP ); 100 return rc; 101 } 102 } 103 } 104 #endif /* BACKSQL_MSSQL_WORKAROUND */ 105 106 if ( timeout > 0 ) { 107 Debug( LDAP_DEBUG_TRACE, "_SQLprepare(): " 108 "setting query timeout to %d sec.\n", 109 timeout, 0, 0 ); 110 rc = SQLSetStmtOption( *sth, SQL_QUERY_TIMEOUT, timeout ); 111 if ( rc != SQL_SUCCESS ) { 112 backsql_PrintErrors( SQL_NULL_HENV, dbh, *sth, rc ); 113 SQLFreeStmt( *sth, SQL_DROP ); 114 return rc; 115 } 116 } 117 118 #ifdef BACKSQL_TRACE 119 Debug( LDAP_DEBUG_TRACE, "<==backsql_Prepare() calling SQLPrepare()\n", 120 0, 0, 0 ); 121 #endif /* BACKSQL_TRACE */ 122 123 return SQLPrepare( *sth, (SQLCHAR *)query, SQL_NTS ); 124 } 125 126 RETCODE 127 backsql_BindRowAsStrings_x( SQLHSTMT sth, BACKSQL_ROW_NTS *row, void *ctx ) 128 { 129 RETCODE rc; 130 131 if ( row == NULL ) { 132 return SQL_ERROR; 133 } 134 135 #ifdef BACKSQL_TRACE 136 Debug( LDAP_DEBUG_TRACE, "==> backsql_BindRowAsStrings()\n", 0, 0, 0 ); 137 #endif /* BACKSQL_TRACE */ 138 139 rc = SQLNumResultCols( sth, &row->ncols ); 140 if ( rc != SQL_SUCCESS ) { 141 #ifdef BACKSQL_TRACE 142 Debug( LDAP_DEBUG_TRACE, "backsql_BindRowAsStrings(): " 143 "SQLNumResultCols() failed:\n", 0, 0, 0 ); 144 #endif /* BACKSQL_TRACE */ 145 146 backsql_PrintErrors( SQL_NULL_HENV, SQL_NULL_HDBC, sth, rc ); 147 148 } else { 149 SQLCHAR colname[ 64 ]; 150 SQLSMALLINT name_len, col_type, col_scale, col_null; 151 UDWORD col_prec; 152 int i; 153 154 #ifdef BACKSQL_TRACE 155 Debug( LDAP_DEBUG_TRACE, "backsql_BindRowAsStrings: " 156 "ncols=%d\n", (int)row->ncols, 0, 0 ); 157 #endif /* BACKSQL_TRACE */ 158 159 row->col_names = (BerVarray)ber_memcalloc_x( row->ncols + 1, 160 sizeof( struct berval ), ctx ); 161 if ( row->col_names == NULL ) { 162 goto nomem; 163 } 164 165 row->col_prec = (UDWORD *)ber_memcalloc_x( row->ncols, 166 sizeof( UDWORD ), ctx ); 167 if ( row->col_prec == NULL ) { 168 goto nomem; 169 } 170 171 row->col_type = (SQLSMALLINT *)ber_memcalloc_x( row->ncols, 172 sizeof( SQLSMALLINT ), ctx ); 173 if ( row->col_type == NULL ) { 174 goto nomem; 175 } 176 177 row->cols = (char **)ber_memcalloc_x( row->ncols + 1, 178 sizeof( char * ), ctx ); 179 if ( row->cols == NULL ) { 180 goto nomem; 181 } 182 183 row->value_len = (SQLINTEGER *)ber_memcalloc_x( row->ncols, 184 sizeof( SQLINTEGER ), ctx ); 185 if ( row->value_len == NULL ) { 186 goto nomem; 187 } 188 189 if ( 0 ) { 190 nomem: 191 ber_memfree_x( row->col_names, ctx ); 192 row->col_names = NULL; 193 ber_memfree_x( row->col_prec, ctx ); 194 row->col_prec = NULL; 195 ber_memfree_x( row->col_type, ctx ); 196 row->col_type = NULL; 197 ber_memfree_x( row->cols, ctx ); 198 row->cols = NULL; 199 ber_memfree_x( row->value_len, ctx ); 200 row->value_len = NULL; 201 202 Debug( LDAP_DEBUG_ANY, "backsql_BindRowAsStrings: " 203 "out of memory\n", 0, 0, 0 ); 204 205 return LDAP_NO_MEMORY; 206 } 207 208 for ( i = 0; i < row->ncols; i++ ) { 209 SQLSMALLINT TargetType; 210 211 rc = SQLDescribeCol( sth, (SQLSMALLINT)(i + 1), &colname[ 0 ], 212 (SQLUINTEGER)( sizeof( colname ) - 1 ), 213 &name_len, &col_type, 214 &col_prec, &col_scale, &col_null ); 215 /* FIXME: test rc? */ 216 217 ber_str2bv_x( (char *)colname, 0, 1, 218 &row->col_names[ i ], ctx ); 219 #ifdef BACKSQL_TRACE 220 Debug( LDAP_DEBUG_TRACE, "backsql_BindRowAsStrings: " 221 "col_name=%s, col_prec[%d]=%d\n", 222 colname, (int)(i + 1), (int)col_prec ); 223 #endif /* BACKSQL_TRACE */ 224 if ( col_type != SQL_CHAR && col_type != SQL_VARCHAR ) 225 { 226 col_prec = MAX_ATTR_LEN; 227 } 228 229 row->cols[ i ] = (char *)ber_memcalloc_x( col_prec + 1, 230 sizeof( char ), ctx ); 231 row->col_prec[ i ] = col_prec; 232 row->col_type[ i ] = col_type; 233 234 /* 235 * ITS#3386, ITS#3113 - 20070308 236 * Note: there are many differences between various DPMS and ODBC 237 * Systems; some support SQL_C_BLOB, SQL_C_BLOB_LOCATOR. YMMV: 238 * This has only been tested on Linux/MySQL/UnixODBC 239 * For BINARY-type Fields (BLOB, etc), read the data as BINARY 240 */ 241 if ( BACKSQL_IS_BINARY( col_type ) ) { 242 #ifdef BACKSQL_TRACE 243 Debug( LDAP_DEBUG_TRACE, "backsql_BindRowAsStrings: " 244 "col_name=%s, col_type[%d]=%d: reading binary data\n", 245 colname, (int)(i + 1), (int)col_type); 246 #endif /* BACKSQL_TRACE */ 247 TargetType = SQL_C_BINARY; 248 249 } else { 250 /* Otherwise read it as Character data */ 251 #ifdef BACKSQL_TRACE 252 Debug( LDAP_DEBUG_TRACE, "backsql_BindRowAsStrings: " 253 "col_name=%s, col_type[%d]=%d: reading character data\n", 254 colname, (int)(i + 1), (int)col_type); 255 #endif /* BACKSQL_TRACE */ 256 TargetType = SQL_C_CHAR; 257 } 258 259 rc = SQLBindCol( sth, (SQLUSMALLINT)(i + 1), 260 TargetType, 261 (SQLPOINTER)row->cols[ i ], 262 col_prec + 1, 263 &row->value_len[ i ] ); 264 265 /* FIXME: test rc? */ 266 } 267 268 BER_BVZERO( &row->col_names[ i ] ); 269 row->cols[ i ] = NULL; 270 } 271 272 #ifdef BACKSQL_TRACE 273 Debug( LDAP_DEBUG_TRACE, "<== backsql_BindRowAsStrings()\n", 0, 0, 0 ); 274 #endif /* BACKSQL_TRACE */ 275 276 return rc; 277 } 278 279 RETCODE 280 backsql_BindRowAsStrings( SQLHSTMT sth, BACKSQL_ROW_NTS *row ) 281 { 282 return backsql_BindRowAsStrings_x( sth, row, NULL ); 283 } 284 285 RETCODE 286 backsql_FreeRow_x( BACKSQL_ROW_NTS *row, void *ctx ) 287 { 288 if ( row->cols == NULL ) { 289 return SQL_ERROR; 290 } 291 292 ber_bvarray_free_x( row->col_names, ctx ); 293 ber_memfree_x( row->col_prec, ctx ); 294 ber_memfree_x( row->col_type, ctx ); 295 ber_memvfree_x( (void **)row->cols, ctx ); 296 ber_memfree_x( row->value_len, ctx ); 297 298 return SQL_SUCCESS; 299 } 300 301 302 RETCODE 303 backsql_FreeRow( BACKSQL_ROW_NTS *row ) 304 { 305 return backsql_FreeRow_x( row, NULL ); 306 } 307 308 static void 309 backsql_close_db_handle( SQLHDBC dbh ) 310 { 311 if ( dbh == SQL_NULL_HDBC ) { 312 return; 313 } 314 315 Debug( LDAP_DEBUG_TRACE, "==>backsql_close_db_handle(%p)\n", 316 (void *)dbh, 0, 0 ); 317 318 /* 319 * Default transact is SQL_ROLLBACK; commit is required only 320 * by write operations, and it is explicitly performed after 321 * each atomic operation succeeds. 322 */ 323 324 /* TimesTen */ 325 SQLTransact( SQL_NULL_HENV, dbh, SQL_ROLLBACK ); 326 SQLDisconnect( dbh ); 327 SQLFreeConnect( dbh ); 328 329 Debug( LDAP_DEBUG_TRACE, "<==backsql_close_db_handle(%p)\n", 330 (void *)dbh, 0, 0 ); 331 } 332 333 int 334 backsql_conn_destroy( 335 backsql_info *bi ) 336 { 337 return 0; 338 } 339 340 int 341 backsql_init_db_env( backsql_info *bi ) 342 { 343 RETCODE rc; 344 int ret = SQL_SUCCESS; 345 346 Debug( LDAP_DEBUG_TRACE, "==>backsql_init_db_env()\n", 0, 0, 0 ); 347 348 rc = SQLAllocEnv( &bi->sql_db_env ); 349 if ( rc != SQL_SUCCESS ) { 350 Debug( LDAP_DEBUG_TRACE, "init_db_env: SQLAllocEnv failed:\n", 351 0, 0, 0 ); 352 backsql_PrintErrors( SQL_NULL_HENV, SQL_NULL_HDBC, 353 SQL_NULL_HENV, rc ); 354 ret = SQL_ERROR; 355 } 356 357 Debug( LDAP_DEBUG_TRACE, "<==backsql_init_db_env()=%d\n", ret, 0, 0 ); 358 359 return ret; 360 } 361 362 int 363 backsql_free_db_env( backsql_info *bi ) 364 { 365 Debug( LDAP_DEBUG_TRACE, "==>backsql_free_db_env()\n", 0, 0, 0 ); 366 367 (void)SQLFreeEnv( bi->sql_db_env ); 368 bi->sql_db_env = SQL_NULL_HENV; 369 370 /* 371 * stop, if frontend waits for all threads to shutdown 372 * before calling this -- then what are we going to delete?? 373 * everything is already deleted... 374 */ 375 Debug( LDAP_DEBUG_TRACE, "<==backsql_free_db_env()\n", 0, 0, 0 ); 376 377 return SQL_SUCCESS; 378 } 379 380 static int 381 backsql_open_db_handle( 382 backsql_info *bi, 383 SQLHDBC *dbhp ) 384 { 385 /* TimesTen */ 386 char DBMSName[ 32 ]; 387 int rc; 388 389 assert( dbhp != NULL ); 390 *dbhp = SQL_NULL_HDBC; 391 392 Debug( LDAP_DEBUG_TRACE, "==>backsql_open_db_handle()\n", 393 0, 0, 0 ); 394 395 rc = SQLAllocConnect( bi->sql_db_env, dbhp ); 396 if ( !BACKSQL_SUCCESS( rc ) ) { 397 Debug( LDAP_DEBUG_TRACE, "backsql_open_db_handle(): " 398 "SQLAllocConnect() failed:\n", 399 0, 0, 0 ); 400 backsql_PrintErrors( bi->sql_db_env, SQL_NULL_HDBC, 401 SQL_NULL_HENV, rc ); 402 return LDAP_UNAVAILABLE; 403 } 404 405 rc = SQLConnect( *dbhp, 406 (SQLCHAR*)bi->sql_dbname, SQL_NTS, 407 (SQLCHAR*)bi->sql_dbuser, SQL_NTS, 408 (SQLCHAR*)bi->sql_dbpasswd, SQL_NTS ); 409 if ( rc != SQL_SUCCESS ) { 410 Debug( LDAP_DEBUG_TRACE, "backsql_open_db_handle(): " 411 "SQLConnect() to database \"%s\" %s.\n", 412 bi->sql_dbname, 413 rc == SQL_SUCCESS_WITH_INFO ? 414 "succeeded with info" : "failed", 415 0 ); 416 backsql_PrintErrors( bi->sql_db_env, *dbhp, SQL_NULL_HENV, rc ); 417 if ( rc != SQL_SUCCESS_WITH_INFO ) { 418 SQLFreeConnect( *dbhp ); 419 return LDAP_UNAVAILABLE; 420 } 421 } 422 423 /* 424 * TimesTen : Turn off autocommit. We must explicitly 425 * commit any transactions. 426 */ 427 SQLSetConnectOption( *dbhp, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF ); 428 429 /* 430 * See if this connection is to TimesTen. If it is, 431 * remember that fact for later use. 432 */ 433 /* Assume until proven otherwise */ 434 bi->sql_flags &= ~BSQLF_USE_REVERSE_DN; 435 DBMSName[ 0 ] = '\0'; 436 rc = SQLGetInfo( *dbhp, SQL_DBMS_NAME, (PTR)&DBMSName, 437 sizeof( DBMSName ), NULL ); 438 if ( rc == SQL_SUCCESS ) { 439 if ( strcmp( DBMSName, "TimesTen" ) == 0 || 440 strcmp( DBMSName, "Front-Tier" ) == 0 ) 441 { 442 Debug( LDAP_DEBUG_TRACE, "backsql_open_db_handle(): " 443 "TimesTen database!\n", 444 0, 0, 0 ); 445 bi->sql_flags |= BSQLF_USE_REVERSE_DN; 446 } 447 448 } else { 449 Debug( LDAP_DEBUG_TRACE, "backsql_open_db_handle(): " 450 "SQLGetInfo() failed.\n", 451 0, 0, 0 ); 452 backsql_PrintErrors( bi->sql_db_env, *dbhp, SQL_NULL_HENV, rc ); 453 SQLDisconnect( *dbhp ); 454 SQLFreeConnect( *dbhp ); 455 return LDAP_UNAVAILABLE; 456 } 457 /* end TimesTen */ 458 459 Debug( LDAP_DEBUG_TRACE, "<==backsql_open_db_handle()\n", 460 0, 0, 0 ); 461 462 return LDAP_SUCCESS; 463 } 464 465 int 466 backsql_free_db_conn( Operation *op, SQLHDBC dbh ) 467 { 468 Debug( LDAP_DEBUG_TRACE, "==>backsql_free_db_conn()\n", 0, 0, 0 ); 469 470 (void)backsql_close_db_handle( dbh ); 471 472 Debug( LDAP_DEBUG_TRACE, "<==backsql_free_db_conn()\n", 0, 0, 0 ); 473 474 return LDAP_SUCCESS; 475 } 476 477 static void *backsql_db_conn_dummy; 478 479 static void 480 backsql_db_conn_keyfree( 481 void *key, 482 void *data ) 483 { 484 backsql_close_db_handle( (SQLHDBC)data ); 485 } 486 487 int 488 backsql_get_db_conn( Operation *op, SQLHDBC *dbhp ) 489 { 490 backsql_info *bi = (backsql_info *)op->o_bd->be_private; 491 int rc = LDAP_SUCCESS; 492 SQLHDBC dbh = SQL_NULL_HDBC; 493 494 Debug( LDAP_DEBUG_TRACE, "==>backsql_get_db_conn()\n", 0, 0, 0 ); 495 496 assert( dbhp != NULL ); 497 *dbhp = SQL_NULL_HDBC; 498 499 if ( op->o_threadctx ) { 500 void *data = NULL; 501 502 ldap_pvt_thread_pool_getkey( op->o_threadctx, 503 &backsql_db_conn_dummy, &data, NULL ); 504 dbh = (SQLHDBC)data; 505 506 } else { 507 dbh = bi->sql_dbh; 508 } 509 510 if ( dbh == SQL_NULL_HDBC ) { 511 rc = backsql_open_db_handle( bi, &dbh ); 512 if ( rc != LDAP_SUCCESS ) { 513 return rc; 514 } 515 516 if ( op->o_threadctx ) { 517 void *data = NULL; 518 519 data = (void *)dbh; 520 ldap_pvt_thread_pool_setkey( op->o_threadctx, 521 &backsql_db_conn_dummy, data, 522 backsql_db_conn_keyfree, NULL, NULL ); 523 524 } else { 525 bi->sql_dbh = dbh; 526 } 527 } 528 529 *dbhp = dbh; 530 531 Debug( LDAP_DEBUG_TRACE, "<==backsql_get_db_conn()\n", 0, 0, 0 ); 532 533 return LDAP_SUCCESS; 534 } 535 536