Thu Jul 9 13:41:30 2009

Asterisk developer's documentation


res_odbc.h File Reference

ODBC resource manager. More...

#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>

Go to the source code of this file.

Data Structures

struct  odbc_obj
 ODBC container. More...

Enumerations

enum  odbc_status { ODBC_SUCCESS = 0, ODBC_FAIL = -1 }

Functions

int ast_odbc_backslash_is_escape (struct odbc_obj *obj)
 Checks if the database natively supports backslash as an escape character.
SQLHSTMT ast_odbc_direct_execute (struct odbc_obj *obj, SQLHSTMT(*exec_cb)(struct odbc_obj *obj, void *data), void *data)
 Executes an non prepared statement and returns the resulting statement handle.
SQLHSTMT ast_odbc_prepare_and_execute (struct odbc_obj *obj, SQLHSTMT(*prepare_cb)(struct odbc_obj *obj, void *data), void *data)
 Prepares, executes, and returns the resulting statement handle.
void ast_odbc_release_obj (struct odbc_obj *obj)
 Releases an ODBC object previously allocated by odbc_request_obj().
odbc_objast_odbc_request_obj (const char *name, int check)
 Retrieves a connected ODBC object.
int ast_odbc_sanity_check (struct odbc_obj *obj)
 Checks an ODBC object to ensure it is still connected.
int ast_odbc_smart_execute (struct odbc_obj *obj, SQLHSTMT stmt)
 Executes a prepared statement handle.


Detailed Description

ODBC resource manager.

Definition in file res_odbc.h.


Enumeration Type Documentation

enum odbc_status

Enumerator:
ODBC_SUCCESS 
ODBC_FAIL 

Definition at line 34 of file res_odbc.h.


Function Documentation

int ast_odbc_backslash_is_escape ( struct odbc_obj obj  ) 

Checks if the database natively supports backslash as an escape character.

Parameters:
obj The ODBC object
Returns:
Returns 1 if backslash is a native escape character, 0 if an ESCAPE clause is needed to support '\'

Definition at line 445 of file res_odbc.c.

References odbc_class::backslash_is_escape, and odbc_obj::parent.

Referenced by odbc_log(), realtime_multi_odbc(), and realtime_odbc().

00446 {
00447    return obj->parent->backslash_is_escape;
00448 }

SQLHSTMT ast_odbc_direct_execute ( struct odbc_obj obj,
SQLHSTMT(*)(struct odbc_obj *obj, void *data)  exec_cb,
void *  data 
)

Executes an non prepared statement and returns the resulting statement handle.

Parameters:
obj The ODBC object
exec_cb A function callback, which, when called, should return a statement handle with result columns bound.
data A parameter to be passed to the exec_cb parameter function, indicating which statement handle is to be prepared.
Return values:
a statement handle
NULL on error

Definition at line 76 of file res_odbc.c.

References ast_log(), LOG_WARNING, odbc_obj_connect(), and odbc_obj_disconnect().

Referenced by acf_odbc_read(), and acf_odbc_write().

00077 {
00078    int attempt;
00079    SQLHSTMT stmt;
00080 
00081    for (attempt = 0; attempt < 2; attempt++) {
00082       stmt = exec_cb(obj, data);
00083 
00084       if (stmt) {
00085          break;
00086       } else {
00087          obj->up = 0;
00088          ast_log(LOG_WARNING, "SQL Exec Direct failed.  Attempting a reconnect...\n");
00089 
00090          odbc_obj_disconnect(obj);
00091          odbc_obj_connect(obj);
00092       }
00093    }
00094 
00095    return stmt;
00096 }

SQLHSTMT ast_odbc_prepare_and_execute ( struct odbc_obj obj,
SQLHSTMT(*)(struct odbc_obj *obj, void *data)  prepare_cb,
void *  data 
)

Prepares, executes, and returns the resulting statement handle.

Parameters:
obj The ODBC object
prepare_cb A function callback, which, when called, should return a statement handle prepared, with any necessary parameters or result columns bound.
data A parameter to be passed to the prepare_cb parameter function, indicating which statement handle is to be prepared.
Return values:
a statement handle
NULL on error

Definition at line 98 of file res_odbc.c.

References ast_log(), ast_odbc_sanity_check(), ast_tvnow(), LOG_WARNING, and prepare_cb().

Referenced by config_odbc(), destroy_odbc(), odbc_log(), realtime_multi_odbc(), realtime_odbc(), store_odbc(), and update_odbc().

00099 {
00100    int res = 0, i, attempt;
00101    SQLINTEGER nativeerror=0, numfields=0;
00102    SQLSMALLINT diagbytes=0;
00103    unsigned char state[10], diagnostic[256];
00104    SQLHSTMT stmt;
00105 
00106    for (attempt = 0; attempt < 2; attempt++) {
00107       /* This prepare callback may do more than just prepare -- it may also
00108        * bind parameters, bind results, etc.  The real key, here, is that
00109        * when we disconnect, all handles become invalid for most databases.
00110        * We must therefore redo everything when we establish a new
00111        * connection. */
00112       stmt = prepare_cb(obj, data);
00113 
00114       if (stmt) {
00115          res = SQLExecute(stmt);
00116          if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO) && (res != SQL_NO_DATA)) {
00117             if (res == SQL_ERROR) {
00118                SQLGetDiagField(SQL_HANDLE_STMT, stmt, 1, SQL_DIAG_NUMBER, &numfields, SQL_IS_INTEGER, &diagbytes);
00119                for (i = 0; i < numfields; i++) {
00120                   SQLGetDiagRec(SQL_HANDLE_STMT, stmt, i + 1, state, &nativeerror, diagnostic, sizeof(diagnostic), &diagbytes);
00121                   ast_log(LOG_WARNING, "SQL Execute returned an error %d: %s: %s (%d)\n", res, state, diagnostic, diagbytes);
00122                   if (i > 10) {
00123                      ast_log(LOG_WARNING, "Oh, that was good.  There are really %d diagnostics?\n", (int)numfields);
00124                      break;
00125                   }
00126                }
00127             }
00128 
00129             ast_log(LOG_WARNING, "SQL Execute error %d! Attempting a reconnect...\n", res);
00130             SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00131             stmt = NULL;
00132 
00133             obj->up = 0;
00134             /*
00135              * While this isn't the best way to try to correct an error, this won't automatically
00136              * fail when the statement handle invalidates.
00137              */
00138             ast_odbc_sanity_check(obj);
00139             continue;
00140          } else
00141             obj->last_used = ast_tvnow();
00142          break;
00143       } else if (attempt == 0)
00144          ast_odbc_sanity_check(obj);
00145    }
00146 
00147    return stmt;
00148 }

void ast_odbc_release_obj ( struct odbc_obj obj  ) 

Releases an ODBC object previously allocated by odbc_request_obj().

Parameters:
obj The ODBC object

Definition at line 438 of file res_odbc.c.

References odbc_obj::used.

Referenced by config_odbc(), destroy_odbc(), load_config(), odbc_log(), odbc_register_class(), realtime_multi_odbc(), realtime_odbc(), store_odbc(), and update_odbc().

00439 {
00440    /* For pooled connections, this frees the connection to be
00441     * reused.  For non-pooled connections, it does nothing. */
00442    obj->used = 0;
00443 }

struct odbc_obj* ast_odbc_request_obj ( const char *  name,
int  check 
)

Retrieves a connected ODBC object.

Parameters:
name The name of the ODBC class for which a connection is needed.
check Whether to ensure that a connection is valid before returning the handle. Usually unnecessary.
Return values:
ODBC object
NULL if there is no connection available with the requested name.
Connection classes may, in fact, contain multiple connection handles. If the connection is pooled, then each connection will be dedicated to the thread which requests it. Note that all connections should be released when the thread is done by calling odbc_release_obj(), below.

Definition at line 450 of file res_odbc.c.

References ast_calloc, ast_free, AST_LIST_INSERT_HEAD, AST_LIST_INSERT_TAIL, AST_LIST_LOCK, AST_LIST_TRAVERSE, AST_LIST_UNLOCK, ast_log(), ast_mutex_destroy(), ast_mutex_init(), ast_mutex_lock(), ast_mutex_unlock(), ast_odbc_sanity_check(), ast_tvdiff_sec(), ast_tvnow(), odbc_class::idlecheck, odbc_obj::last_used, odbc_class::list, odbc_obj::lock, LOG_WARNING, ODBC_FAIL, odbc_obj_connect(), odbc_obj::parent, and odbc_obj::used.

Referenced by acf_odbc_read(), acf_odbc_write(), config_odbc(), destroy_odbc(), load_config(), odbc_log(), odbc_register_class(), realtime_multi_odbc(), realtime_odbc(), store_odbc(), and update_odbc().

00451 {
00452    struct odbc_obj *obj = NULL;
00453    struct odbc_class *class;
00454 
00455    AST_LIST_LOCK(&odbc_list);
00456    AST_LIST_TRAVERSE(&odbc_list, class, list) {
00457       if (!strcmp(class->name, name))
00458          break;
00459    }
00460    AST_LIST_UNLOCK(&odbc_list);
00461 
00462    if (!class)
00463       return NULL;
00464 
00465    AST_LIST_LOCK(&class->odbc_obj);
00466    if (class->haspool) {
00467       /* Recycle connections before building another */
00468       AST_LIST_TRAVERSE(&class->odbc_obj, obj, list) {
00469          if (! obj->used) {
00470             ast_mutex_lock(&obj->lock);
00471             obj->used = 1;
00472             ast_mutex_unlock(&obj->lock);
00473             break;
00474          }
00475       }
00476 
00477       if (!obj && (class->count < class->limit)) {
00478          class->count++;
00479          obj = ast_calloc(1, sizeof(*obj));
00480          if (!obj) {
00481             AST_LIST_UNLOCK(&class->odbc_obj);
00482             return NULL;
00483          }
00484          ast_mutex_init(&obj->lock);
00485          obj->parent = class;
00486          if (odbc_obj_connect(obj) == ODBC_FAIL) {
00487             ast_log(LOG_WARNING, "Failed to connect to %s\n", name);
00488             ast_mutex_destroy(&obj->lock);
00489             ast_free(obj);
00490             obj = NULL;
00491             class->count--;
00492          } else {
00493             obj->used = 1;
00494             AST_LIST_INSERT_TAIL(&class->odbc_obj, obj, list);
00495          }
00496       }
00497    } else {
00498       /* Non-pooled connection: multiple modules can use the same connection. */
00499       AST_LIST_TRAVERSE(&class->odbc_obj, obj, list) {
00500          /* Non-pooled connection: if there is an entry, return it */
00501          break;
00502       }
00503 
00504       if (!obj) {
00505          /* No entry: build one */
00506          obj = ast_calloc(1, sizeof(*obj));
00507          if (!obj) {
00508             AST_LIST_UNLOCK(&class->odbc_obj);
00509             return NULL;
00510          }
00511          ast_mutex_init(&obj->lock);
00512          obj->parent = class;
00513          if (odbc_obj_connect(obj) == ODBC_FAIL) {
00514             ast_log(LOG_WARNING, "Failed to connect to %s\n", name);
00515             ast_mutex_destroy(&obj->lock);
00516             ast_free(obj);
00517             obj = NULL;
00518          } else {
00519             AST_LIST_INSERT_HEAD(&class->odbc_obj, obj, list);
00520          }
00521       }
00522    }
00523    AST_LIST_UNLOCK(&class->odbc_obj);
00524 
00525    if (obj && check) {
00526       ast_odbc_sanity_check(obj);
00527    } else if (obj && obj->parent->idlecheck > 0 && ast_tvdiff_sec(ast_tvnow(), obj->last_used) > obj->parent->idlecheck)
00528       odbc_obj_connect(obj);
00529 
00530    return obj;
00531 }

int ast_odbc_sanity_check ( struct odbc_obj obj  ) 

Checks an ODBC object to ensure it is still connected.

Parameters:
obj The ODBC object
Return values:
0 if connected
-1 otherwise.

Definition at line 193 of file res_odbc.c.

References ast_log(), ast_strlen_zero(), odbc_obj::con, LOG_WARNING, odbc_obj_connect(), odbc_obj_disconnect(), odbc_obj::parent, odbc_class::sanitysql, and odbc_obj::up.

Referenced by ast_odbc_prepare_and_execute(), ast_odbc_request_obj(), and handle_cli_odbc_show().

00194 {
00195    char *test_sql = "select 1";
00196    SQLHSTMT stmt;
00197    int res = 0;
00198 
00199    if (!ast_strlen_zero(obj->parent->sanitysql))
00200       test_sql = obj->parent->sanitysql;
00201 
00202    if (obj->up) {
00203       res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &stmt);
00204       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00205          obj->up = 0;
00206       } else {
00207          res = SQLPrepare(stmt, (unsigned char *)test_sql, SQL_NTS);
00208          if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00209             obj->up = 0;
00210          } else {
00211             res = SQLExecute(stmt);
00212             if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00213                obj->up = 0;
00214             }
00215          }
00216       }
00217       SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00218    }
00219 
00220    if (!obj->up) { /* Try to reconnect! */
00221       ast_log(LOG_WARNING, "Connection is down attempting to reconnect...\n");
00222       odbc_obj_disconnect(obj);
00223       odbc_obj_connect(obj);
00224    }
00225    return obj->up;
00226 }

int ast_odbc_smart_execute ( struct odbc_obj obj,
SQLHSTMT  stmt 
)

Executes a prepared statement handle.

Parameters:
obj The non-NULL result of odbc_request_obj()
stmt The prepared statement handle
Return values:
0 on success
-1 on failure
This function was originally designed simply to execute a prepared statement handle and to retry if the initial execution failed. Unfortunately, it did this by disconnecting and reconnecting the database handle which on most databases causes the statement handle to become invalid. Therefore, this method has been deprecated in favor of odbc_prepare_and_execute() which allows the statement to be prepared multiple times, if necessary, in case of a loss of connection.

This function really only ever worked with MySQL, where the statement handle is not prepared on the server. If you are not using MySQL, you should avoid it.

Definition at line 150 of file res_odbc.c.

References ast_log(), ast_mutex_lock(), ast_mutex_unlock(), ast_tvnow(), odbc_obj::last_used, odbc_obj::lock, LOG_WARNING, odbc_obj_connect(), odbc_obj_disconnect(), and odbc_obj::up.

00151 {
00152    int res = 0, i;
00153    SQLINTEGER nativeerror=0, numfields=0;
00154    SQLSMALLINT diagbytes=0;
00155    unsigned char state[10], diagnostic[256];
00156 
00157    res = SQLExecute(stmt);
00158    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO) && (res != SQL_NO_DATA)) {
00159       if (res == SQL_ERROR) {
00160          SQLGetDiagField(SQL_HANDLE_STMT, stmt, 1, SQL_DIAG_NUMBER, &numfields, SQL_IS_INTEGER, &diagbytes);
00161          for (i = 0; i < numfields; i++) {
00162             SQLGetDiagRec(SQL_HANDLE_STMT, stmt, i + 1, state, &nativeerror, diagnostic, sizeof(diagnostic), &diagbytes);
00163             ast_log(LOG_WARNING, "SQL Execute returned an error %d: %s: %s (%d)\n", res, state, diagnostic, diagbytes);
00164             if (i > 10) {
00165                ast_log(LOG_WARNING, "Oh, that was good.  There are really %d diagnostics?\n", (int)numfields);
00166                break;
00167             }
00168          }
00169       }
00170 #if 0
00171       /* This is a really bad method of trying to correct a dead connection.  It
00172        * only ever really worked with MySQL.  It will not work with any other
00173        * database, since most databases prepare their statements on the server,
00174        * and if you disconnect, you invalidate the statement handle.  Hence, if
00175        * you disconnect, you're going to fail anyway, whether you try to execute
00176        * a second time or not.
00177        */
00178       ast_log(LOG_WARNING, "SQL Execute error %d! Attempting a reconnect...\n", res);
00179       ast_mutex_lock(&obj->lock);
00180       obj->up = 0;
00181       ast_mutex_unlock(&obj->lock);
00182       odbc_obj_disconnect(obj);
00183       odbc_obj_connect(obj);
00184       res = SQLExecute(stmt);
00185 #endif
00186    } else
00187       obj->last_used = ast_tvnow();
00188    
00189    return res;
00190 }


Generated on Thu Jul 9 13:41:30 2009 for Asterisk - the Open Source PBX by  doxygen 1.4.7