Fri Sep 25 19:28:43 2009

Asterisk developer's documentation


res_odbc.h File Reference

ODBC resource manager. More...

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

Include dependency graph for res_odbc.h:

This graph shows which files directly or indirectly include this file:

Go to the source code of this file.

Data Structures

struct  odbc_obj

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_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().
struct 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

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 an ESCAPE clause is needed to support '\', 0 otherwise

Definition at line 388 of file res_odbc.c.

References odbc_obj::parent.

Referenced by realtime_multi_odbc(), and realtime_odbc().

00389 {
00390    return obj->parent->backslash_is_escape;
00391 }

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.
Returns:
Returns a statement handle or NULL on error.

Definition at line 80 of file res_odbc.c.

References ast_log(), LOG_WARNING, odbc_obj_connect(), odbc_obj_disconnect(), and odbc_obj::up.

Referenced by acf_odbc_read(), acf_odbc_write(), config_odbc(), realtime_multi_odbc(), realtime_odbc(), and update_odbc().

00081 {
00082    int res = 0, i, attempt;
00083    SQLINTEGER nativeerror=0, numfields=0;
00084    SQLSMALLINT diagbytes=0;
00085    unsigned char state[10], diagnostic[256];
00086    SQLHSTMT stmt;
00087 
00088    for (attempt = 0; attempt < 2; attempt++) {
00089       /* This prepare callback may do more than just prepare -- it may also
00090        * bind parameters, bind results, etc.  The real key, here, is that
00091        * when we disconnect, all handles become invalid for most databases.
00092        * We must therefore redo everything when we establish a new
00093        * connection. */
00094       stmt = prepare_cb(obj, data);
00095 
00096       if (stmt) {
00097          res = SQLExecute(stmt);
00098          if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO) && (res != SQL_NO_DATA)) {
00099             if (res == SQL_ERROR) {
00100                SQLGetDiagField(SQL_HANDLE_STMT, stmt, 1, SQL_DIAG_NUMBER, &numfields, SQL_IS_INTEGER, &diagbytes);
00101                for (i = 0; i < numfields; i++) {
00102                   SQLGetDiagRec(SQL_HANDLE_STMT, stmt, i + 1, state, &nativeerror, diagnostic, sizeof(diagnostic), &diagbytes);
00103                   ast_log(LOG_WARNING, "SQL Execute returned an error %d: %s: %s (%d)\n", res, state, diagnostic, diagbytes);
00104                   if (i > 10) {
00105                      ast_log(LOG_WARNING, "Oh, that was good.  There are really %d diagnostics?\n", (int)numfields);
00106                      break;
00107                   }
00108                }
00109             }
00110 
00111             ast_log(LOG_WARNING, "SQL Execute error %d! Attempting a reconnect...\n", res);
00112             SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00113             stmt = NULL;
00114 
00115             obj->up = 0;
00116             /*
00117              * While this isn't the best way to try to correct an error, this won't automatically
00118              * fail when the statement handle invalidates.
00119              */
00120             /* XXX Actually, it might, if we're using a non-pooled connection. Possible race here. XXX */
00121             odbc_obj_disconnect(obj);
00122             odbc_obj_connect(obj);
00123             continue;
00124          }
00125          break;
00126       } else {
00127          ast_log(LOG_WARNING, "SQL Prepare failed.  Attempting a reconnect...\n");
00128          odbc_obj_disconnect(obj);
00129          odbc_obj_connect(obj);
00130       }
00131    }
00132 
00133    return stmt;
00134 }

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 381 of file res_odbc.c.

References odbc_obj::used.

Referenced by acf_odbc_read(), acf_odbc_write(), config_odbc(), odbc_register_class(), realtime_multi_odbc(), realtime_odbc(), and update_odbc().

00382 {
00383    /* For pooled connections, this frees the connection to be
00384     * reused.  For non-pooled connections, it does nothing. */
00385    obj->used = 0;
00386 }

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

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.
Returns:
Returns an ODBC object or 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 393 of file res_odbc.c.

References ast_calloc, 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_odbc_sanity_check(), free, 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(), odbc_register_class(), realtime_multi_odbc(), realtime_odbc(), and update_odbc().

00394 {
00395    struct odbc_obj *obj = NULL;
00396    struct odbc_class *class;
00397 
00398    AST_LIST_LOCK(&odbc_list);
00399    AST_LIST_TRAVERSE(&odbc_list, class, list) {
00400       if (!strcmp(class->name, name))
00401          break;
00402    }
00403    AST_LIST_UNLOCK(&odbc_list);
00404 
00405    if (!class)
00406       return NULL;
00407 
00408    AST_LIST_LOCK(&class->odbc_obj);
00409    if (class->haspool) {
00410       /* Recycle connections before building another */
00411       AST_LIST_TRAVERSE(&class->odbc_obj, obj, list) {
00412          if (! obj->used) {
00413             obj->used = 1;
00414             break;
00415          }
00416       }
00417 
00418       if (!obj && (class->count < class->limit)) {
00419          class->count++;
00420          obj = ast_calloc(1, sizeof(*obj));
00421          if (!obj) {
00422             AST_LIST_UNLOCK(&class->odbc_obj);
00423             return NULL;
00424          }
00425          ast_mutex_init(&obj->lock);
00426          obj->parent = class;
00427          if (odbc_obj_connect(obj) == ODBC_FAIL) {
00428             ast_log(LOG_WARNING, "Failed to connect to %s\n", name);
00429             ast_mutex_destroy(&obj->lock);
00430             free(obj);
00431             obj = NULL;
00432             class->count--;
00433          } else {
00434             obj->used = 1;
00435             AST_LIST_INSERT_TAIL(&class->odbc_obj, obj, list);
00436          }
00437       }
00438    } else {
00439       /* Non-pooled connection: multiple modules can use the same connection. */
00440       AST_LIST_TRAVERSE(&class->odbc_obj, obj, list) {
00441          /* Non-pooled connection: if there is an entry, return it */
00442          break;
00443       }
00444 
00445       if (!obj) {
00446          /* No entry: build one */
00447          obj = ast_calloc(1, sizeof(*obj));
00448          if (!obj) {
00449             AST_LIST_UNLOCK(&class->odbc_obj);
00450             return NULL;
00451          }
00452          ast_mutex_init(&obj->lock);
00453          obj->parent = class;
00454          if (odbc_obj_connect(obj) == ODBC_FAIL) {
00455             ast_log(LOG_WARNING, "Failed to connect to %s\n", name);
00456             ast_mutex_destroy(&obj->lock);
00457             free(obj);
00458             obj = NULL;
00459          } else {
00460             AST_LIST_INSERT_HEAD(&class->odbc_obj, obj, list);
00461          }
00462       }
00463    }
00464    AST_LIST_UNLOCK(&class->odbc_obj);
00465 
00466    if (obj && check) {
00467       ast_odbc_sanity_check(obj);
00468    }
00469    return obj;
00470 }

int ast_odbc_sanity_check ( struct odbc_obj obj  ) 

Checks an ODBC object to ensure it is still connected.

Parameters:
obj The ODBC object
Returns:
Returns 0 if connected, -1 otherwise.

Definition at line 178 of file res_odbc.c.

References ast_log(), odbc_obj::con, LOG_WARNING, odbc_obj_connect(), odbc_obj_disconnect(), and odbc_obj::up.

Referenced by ast_odbc_request_obj(), and odbc_show_command().

00179 {
00180    char *test_sql = "select 1";
00181    SQLHSTMT stmt;
00182    int res = 0;
00183 
00184    if (obj->up) {
00185       res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &stmt);
00186       if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00187          obj->up = 0;
00188       } else {
00189          res = SQLPrepare(stmt, (unsigned char *)test_sql, SQL_NTS);
00190          if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00191             obj->up = 0;
00192          } else {
00193             res = SQLExecute(stmt);
00194             if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00195                obj->up = 0;
00196             }
00197          }
00198       }
00199       SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00200    }
00201 
00202    if (!obj->up) { /* Try to reconnect! */
00203       ast_log(LOG_WARNING, "Connection is down attempting to reconnect...\n");
00204       odbc_obj_disconnect(obj);
00205       odbc_obj_connect(obj);
00206    }
00207    return obj->up;
00208 }

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
Returns:
Returns 0 on success or -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 136 of file res_odbc.c.

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

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


Generated on Fri Sep 25 19:28:43 2009 for Asterisk - the Open Source PBX by  doxygen 1.5.5