SWITCH_DECLARE(switch_odbc_status_t) switch_odbc_handle_exec(switch_odbc_handle_t *handle, const char *sql, switch_odbc_statement_handle_t *rstmt, char **err) { #ifdef SWITCH_HAVE_ODBC SQLHSTMT stmt = NULL; int result; char *err_str = NULL; if (!db_is_up(handle)) { goto error; } if (SQLAllocHandle(SQL_HANDLE_STMT, handle->con, &stmt) != SQL_SUCCESS) { goto error; } if (SQLPrepare(stmt, (unsigned char *) sql, SQL_NTS) != SQL_SUCCESS) { goto error; } result = SQLExecute(stmt); if (result != SQL_SUCCESS && result != SQL_SUCCESS_WITH_INFO && result != SQL_NO_DATA) { goto error; } if (rstmt) { *rstmt = stmt; } else { SQLFreeHandle(SQL_HANDLE_STMT, stmt); } return SWITCH_ODBC_SUCCESS; error: if (stmt) { err_str = switch_odbc_handle_get_error(handle, stmt); } if (err_str) { if (!switch_stristr("already exists", err_str) && !switch_stristr("duplicate key name", err_str)) { switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_ERROR, "ERR: [%s]\n[%s]\n", sql, switch_str_nil(err_str)); } if (err) { *err = err_str; } else { free(err_str); } } if (rstmt) { *rstmt = stmt; } else if (stmt) { SQLFreeHandle(SQL_HANDLE_STMT, stmt); } #endif return SWITCH_ODBC_FAIL; }
/* At this time, billing never succeeds if you don't have a database. */ static switch_status_t bill_event(double billamount, const char *billaccount, switch_channel_t *channel) { char *sql = NULL, *dsql = NULL; switch_odbc_statement_handle_t stmt = NULL; switch_status_t status = SWITCH_STATUS_FALSE; if (!switch_odbc_available()) { return status; } if (globals.custom_sql_save) { if (switch_string_var_check_const(globals.custom_sql_save) || switch_string_has_escaped_data(globals.custom_sql_save)) { switch_channel_set_variable_printf(channel, "nibble_bill", "%f", billamount, SWITCH_FALSE); sql = switch_channel_expand_variables(channel, globals.custom_sql_save); if (sql != globals.custom_sql_save) dsql = sql; } else { sql = globals.custom_sql_save; } } else { sql = dsql = switch_mprintf("UPDATE %s SET %s=%s-%f WHERE %s='%s'", globals.db_table, globals.db_column_cash, globals.db_column_cash, billamount, globals.db_column_account, billaccount); } switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_DEBUG, "Doing update query\n[%s]\n", sql); if (switch_odbc_handle_exec(globals.master_odbc, sql, &stmt, NULL) != SWITCH_ODBC_SUCCESS) { char *err_str; err_str = switch_odbc_handle_get_error(globals.master_odbc, stmt); switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_ERROR, "ERR: [%s]\n[%s]\n", sql, switch_str_nil(err_str)); switch_safe_free(err_str); } else { status = SWITCH_STATUS_SUCCESS; } if (stmt) { switch_odbc_statement_handle_free(&stmt); } switch_safe_free(dsql); return status; }
SWITCH_DECLARE(switch_odbc_status_t) switch_odbc_handle_callback_exec_detailed(const char *file, const char *func, int line, switch_odbc_handle_t *handle, const char *sql, switch_core_db_callback_func_t callback, void *pdata, char **err) { #ifdef SWITCH_HAVE_ODBC SQLHSTMT stmt = NULL; SQLSMALLINT c = 0, x = 0; SQLLEN m = 0; char *x_err = NULL, *err_str = NULL; int result; int err_cnt = 0; int done = 0; handle->affected_rows = 0; switch_assert(callback != NULL); if (!db_is_up(handle)) { x_err = "DB is not up!"; goto error; } if (SQLAllocHandle(SQL_HANDLE_STMT, handle->con, &stmt) != SQL_SUCCESS) { x_err = "Unable to SQL allocate handle!"; goto error; } if (SQLPrepare(stmt, (unsigned char *) sql, SQL_NTS) != SQL_SUCCESS) { x_err = "Unable to prepare SQL statement!"; goto error; } result = SQLExecute(stmt); if (result != SQL_SUCCESS && result != SQL_SUCCESS_WITH_INFO && result != SQL_NO_DATA) { x_err = "execute error!"; goto error; } SQLNumResultCols(stmt, &c); SQLRowCount(stmt, &m); handle->affected_rows = (int) m; while (!done) { int name_len = 256; char **names; char **vals; int y = 0; result = SQLFetch(stmt); if (result != SQL_SUCCESS) { if (result != SQL_NO_DATA) { err_cnt++; } break; } names = calloc(c, sizeof(*names)); vals = calloc(c, sizeof(*vals)); switch_assert(names && vals); for (x = 1; x <= c; x++) { SQLSMALLINT NameLength = 0, DataType = 0, DecimalDigits = 0, Nullable = 0; SQLULEN ColumnSize = 0; names[y] = malloc(name_len); memset(names[y], 0, name_len); SQLDescribeCol(stmt, x, (SQLCHAR *) names[y], (SQLSMALLINT) name_len, &NameLength, &DataType, &ColumnSize, &DecimalDigits, &Nullable); if (!ColumnSize) { ColumnSize = 255; } ColumnSize++; vals[y] = malloc(ColumnSize); memset(vals[y], 0, ColumnSize); SQLGetData(stmt, x, SQL_C_CHAR, (SQLCHAR *) vals[y], ColumnSize, NULL); y++; } if (callback(pdata, y, vals, names)) { done = 1; } for (x = 0; x < y; x++) { free(names[x]); free(vals[x]); } free(names); free(vals); } SQLFreeHandle(SQL_HANDLE_STMT, stmt); stmt = NULL; /* Make sure we don't try to free this handle again */ if (!err_cnt) { return SWITCH_ODBC_SUCCESS; } error: if (stmt) { err_str = switch_odbc_handle_get_error(handle, stmt); } if (zstr(err_str) && !zstr(x_err)) { err_str = strdup(x_err); } if (err_str) { switch_log_printf(SWITCH_CHANNEL_ID_LOG, file, func, line, NULL, SWITCH_LOG_ERROR, "ERR: [%s]\n[%s]\n", sql, switch_str_nil(err_str)); if (err) { *err = err_str; } else { free(err_str); } } if (stmt) { SQLFreeHandle(SQL_HANDLE_STMT, stmt); } #endif return SWITCH_ODBC_FAIL; }
SWITCH_DECLARE(switch_odbc_status_t) switch_odbc_handle_exec(switch_odbc_handle_t *handle, const char *sql, switch_odbc_statement_handle_t *rstmt, char **err) { #ifdef SWITCH_HAVE_ODBC SQLHSTMT stmt = NULL; int result; char *err_str = NULL, *err2 = NULL; SQLLEN m = 0; handle->affected_rows = 0; if (!db_is_up(handle)) { goto error; } if (SQLAllocHandle(SQL_HANDLE_STMT, handle->con, &stmt) != SQL_SUCCESS) { err2 = "SQLAllocHandle failed."; goto error; } if (SQLPrepare(stmt, (unsigned char *) sql, SQL_NTS) != SQL_SUCCESS) { err2 = "SQLPrepare failed."; goto error; } result = SQLExecute(stmt); switch (result) { case SQL_SUCCESS: case SQL_SUCCESS_WITH_INFO: case SQL_NO_DATA: break; case SQL_ERROR: err2 = "SQLExecute returned SQL_ERROR."; goto error; break; case SQL_NEED_DATA: err2 = "SQLExecute returned SQL_NEED_DATA."; goto error; break; default: err2 = "SQLExecute returned unknown result code."; goto error; } SQLRowCount(stmt, &m); handle->affected_rows = (int) m; if (rstmt) { *rstmt = stmt; } else { SQLFreeHandle(SQL_HANDLE_STMT, stmt); } return SWITCH_ODBC_SUCCESS; error: if (stmt) { err_str = switch_odbc_handle_get_error(handle, stmt); } if (zstr(err_str)) { if (err2) { err_str = strdup(err2); } else { err_str = strdup((char *)"SQL ERROR!"); } } if (err_str) { if (!switch_stristr("already exists", err_str) && !switch_stristr("duplicate key name", err_str)) { switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_ERROR, "ERR: [%s]\n[%s]\n", sql, switch_str_nil(err_str)); } if (err) { *err = err_str; } else { free(err_str); } } if (rstmt) { *rstmt = stmt; } else if (stmt) { SQLFreeHandle(SQL_HANDLE_STMT, stmt); } #endif return SWITCH_ODBC_FAIL; }
SWITCH_DECLARE(switch_odbc_status_t) switch_odbc_handle_connect(switch_odbc_handle_t *handle) { #ifdef SWITCH_HAVE_ODBC int result; SQLINTEGER err; int16_t mlen; unsigned char msg[200] = "", stat[10] = ""; SQLSMALLINT valueLength = 0; int i = 0; init_odbc_handles(handle, SWITCH_FALSE); /* Init ODBC handles, if they are already initialized, don't do it again */ if (handle->state == SWITCH_ODBC_STATE_CONNECTED) { switch_odbc_handle_disconnect(handle); switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_DEBUG1, "Re-connecting %s\n", handle->dsn); } switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_DEBUG1, "Connecting %s\n", handle->dsn); if (!strstr(handle->dsn, "DRIVER")) { result = SQLConnect(handle->con, (SQLCHAR *) handle->dsn, SQL_NTS, (SQLCHAR *) handle->username, SQL_NTS, (SQLCHAR *) handle->password, SQL_NTS); } else { SQLCHAR outstr[1024] = { 0 }; SQLSMALLINT outstrlen = 0; result = SQLDriverConnect(handle->con, NULL, (SQLCHAR *) handle->dsn, (SQLSMALLINT) strlen(handle->dsn), outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_NOPROMPT); } if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) { char *err_str; if ((err_str = switch_odbc_handle_get_error(handle, NULL))) { switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_ERROR, "%s\n", err_str); free(err_str); } else { SQLGetDiagRec(SQL_HANDLE_DBC, handle->con, 1, stat, &err, msg, sizeof(msg), &mlen); switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_ERROR, "Error SQLConnect=%d errno=%d [%s]\n", result, (int) err, msg); } /* Deallocate handles again, more chanses to succeed when reconnecting */ init_odbc_handles(handle, SWITCH_TRUE); /* Reinit ODBC handles */ return SWITCH_ODBC_FAIL; } result = SQLGetInfo(handle->con, SQL_DRIVER_NAME, (SQLCHAR *) handle->odbc_driver, 255, &valueLength); if (result == SQL_SUCCESS || result == SQL_SUCCESS_WITH_INFO) { for (i = 0; i < valueLength; ++i) handle->odbc_driver[i] = (char) toupper(handle->odbc_driver[i]); } if (strstr(handle->odbc_driver, "SQORA32.DLL") != 0 || strstr(handle->odbc_driver, "SQORA64.DLL") != 0) { handle->is_firebird = FALSE; handle->is_oracle = TRUE; } else if (strstr(handle->odbc_driver, "FIREBIRD") != 0 || strstr(handle->odbc_driver, "FB32") != 0 || strstr(handle->odbc_driver, "FB64") != 0) { handle->is_firebird = TRUE; handle->is_oracle = FALSE; } else { handle->is_firebird = FALSE; handle->is_oracle = FALSE; } switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_DEBUG1, "Connected to [%s]\n", handle->dsn); handle->state = SWITCH_ODBC_STATE_CONNECTED; return SWITCH_ODBC_SUCCESS; #else return SWITCH_ODBC_FAIL; #endif }
static int db_is_up(switch_odbc_handle_t *handle) { int ret = 0; SQLHSTMT stmt = NULL; SQLLEN m = 0; int result; switch_event_t *event; switch_odbc_status_t recon = 0; char *err_str = NULL; SQLCHAR sql[255] = ""; int max_tries = DEFAULT_ODBC_RETRIES; int code = 0; SQLRETURN rc; SQLSMALLINT nresultcols; if (handle) { max_tries = handle->num_retries; if (max_tries < 1) max_tries = DEFAULT_ODBC_RETRIES; } top: if (!handle) { switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_CRIT, "No DB Handle\n"); goto done; } if (handle->is_oracle) { strcpy((char *) sql, "select 1 from dual"); } else if (handle->is_firebird) { strcpy((char *) sql, "select first 1 * from RDB$RELATIONS"); } else { strcpy((char *) sql, "select 1"); } if (SQLAllocHandle(SQL_HANDLE_STMT, handle->con, &stmt) != SQL_SUCCESS) { code = __LINE__; goto error; } SQLSetStmtAttr(stmt, SQL_ATTR_QUERY_TIMEOUT, (SQLPOINTER)30, 0); if (SQLPrepare(stmt, sql, SQL_NTS) != SQL_SUCCESS) { code = __LINE__; goto error; } result = SQLExecute(stmt); if (result != SQL_SUCCESS && result != SQL_SUCCESS_WITH_INFO) { code = __LINE__; goto error; } SQLRowCount(stmt, &m); rc = SQLNumResultCols(stmt, &nresultcols); if (rc != SQL_SUCCESS) { code = __LINE__; goto error; } ret = (int) nresultcols; /* determine statement type */ if (nresultcols <= 0) { /* statement is not a select statement */ code = __LINE__; goto error; } goto done; error: err_str = switch_odbc_handle_get_error(handle, stmt); /* Make sure to free the handle before we try to reconnect */ if (stmt) { SQLFreeHandle(SQL_HANDLE_STMT, stmt); stmt = NULL; } recon = switch_odbc_handle_connect(handle); max_tries--; if (switch_event_create(&event, SWITCH_EVENT_TRAP) == SWITCH_STATUS_SUCCESS) { switch_event_add_header(event, SWITCH_STACK_BOTTOM, "Failure-Message", "The sql server is not responding for DSN %s [%s][%d]", switch_str_nil(handle->dsn), switch_str_nil(err_str), code); switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_CRIT, "The sql server is not responding for DSN %s [%s][%d]\n", switch_str_nil(handle->dsn), switch_str_nil(err_str), code); if (recon == SWITCH_ODBC_SUCCESS) { switch_event_add_header(event, SWITCH_STACK_BOTTOM, "Additional-Info", "The connection has been re-established"); switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_INFO, "The connection has been re-established\n"); } else { switch_event_add_header(event, SWITCH_STACK_BOTTOM, "Additional-Info", "The connection could not be re-established"); switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_CRIT, "The connection could not be re-established\n"); } if (!max_tries) { switch_event_add_header(event, SWITCH_STACK_BOTTOM, "Additional-Info", "Giving up!"); switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_CRIT, "Giving up!\n"); } switch_event_fire(&event); } if (!max_tries) { goto done; } switch_safe_free(err_str); switch_yield(1000000); goto top; done: switch_safe_free(err_str); if (stmt) { SQLFreeHandle(SQL_HANDLE_STMT, stmt); } return ret; }
SWITCH_DECLARE(switch_odbc_status_t) switch_odbc_handle_connect(switch_odbc_handle_t *handle) { #ifdef SWITCH_HAVE_ODBC int result; SQLINTEGER err; int16_t mlen; unsigned char msg[200], stat[10]; SQLSMALLINT valueLength = 0; int i = 0; if (handle->env == SQL_NULL_HANDLE) { result = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &handle->env); if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) { switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_ERROR, "Error AllocHandle\n"); return SWITCH_ODBC_FAIL; } result = SQLSetEnvAttr(handle->env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0); if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) { switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_ERROR, "Error SetEnv\n"); SQLFreeHandle(SQL_HANDLE_ENV, handle->env); return SWITCH_ODBC_FAIL; } result = SQLAllocHandle(SQL_HANDLE_DBC, handle->env, &handle->con); if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) { switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_ERROR, "Error AllocHDB %d\n", result); SQLFreeHandle(SQL_HANDLE_ENV, handle->env); return SWITCH_ODBC_FAIL; } SQLSetConnectAttr(handle->con, SQL_LOGIN_TIMEOUT, (SQLPOINTER *) 10, 0); } if (handle->state == SWITCH_ODBC_STATE_CONNECTED) { switch_odbc_handle_disconnect(handle); switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_DEBUG1, "Re-connecting %s\n", handle->dsn); } switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_DEBUG1, "Connecting %s\n", handle->dsn); if (!strstr(handle->dsn, "DRIVER")) { result = SQLConnect(handle->con, (SQLCHAR *) handle->dsn, SQL_NTS, (SQLCHAR *) handle->username, SQL_NTS, (SQLCHAR *) handle->password, SQL_NTS); } else { SQLCHAR outstr[1024] = { 0 }; SQLSMALLINT outstrlen = 0; result = SQLDriverConnect(handle->con, NULL, (SQLCHAR *) handle->dsn, (SQLSMALLINT) strlen(handle->dsn), outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_NOPROMPT); } if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) { char *err_str; if ((err_str = switch_odbc_handle_get_error(handle, NULL))) { switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_ERROR, "%s\n", err_str); free(err_str); } else { SQLGetDiagRec(SQL_HANDLE_DBC, handle->con, 1, stat, &err, msg, 100, &mlen); switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_ERROR, "Error SQLConnect=%d errno=%d %s\n", result, (int) err, msg); } SQLFreeHandle(SQL_HANDLE_ENV, handle->env); return SWITCH_ODBC_FAIL; } result = SQLGetInfo(handle->con, SQL_DRIVER_NAME, (SQLCHAR *) handle->odbc_driver, 255, &valueLength); if (result == SQL_SUCCESS || result == SQL_SUCCESS_WITH_INFO) { for (i = 0; i < valueLength; ++i) handle->odbc_driver[i] = (char) toupper(handle->odbc_driver[i]); } if (strstr(handle->odbc_driver, "FIREBIRD") != 0 || strstr(handle->odbc_driver, "FB32") != 0 || strstr(handle->odbc_driver, "FB64") != 0) { handle->is_firebird = TRUE; } else { handle->is_firebird = FALSE; } switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_DEBUG1, "Connected to [%s]\n", handle->dsn); handle->state = SWITCH_ODBC_STATE_CONNECTED; return SWITCH_ODBC_SUCCESS; #else return SWITCH_ODBC_FAIL; #endif }