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