From 57fae2076c90be01264435a12a12e133e875614f Mon Sep 17 00:00:00 2001 From: markster Date: Thu, 17 Feb 2005 16:31:08 +0000 Subject: Merge anthm's ODBC sanity check fix (bug #3529) git-svn-id: http://svn.digium.com/svn/asterisk/trunk@5042 f38db490-d61c-443f-a65b-d21fe96a405b --- res/res_config_odbc.c | 21 ++++----- res/res_odbc.c | 119 +++++++++++++++++++++++++++++++++++++++++++------- 2 files changed, 114 insertions(+), 26 deletions(-) (limited to 'res') diff --git a/res/res_config_odbc.c b/res/res_config_odbc.c index d83a4b5e2..7e1d146d5 100755 --- a/res/res_config_odbc.c +++ b/res/res_config_odbc.c @@ -57,7 +57,7 @@ static struct ast_variable *realtime_odbc(const char *database, const char *tabl if (!table) return NULL; - obj = fetch_odbc_obj(database); + obj = fetch_odbc_obj(database, 0); if (!obj) return NULL; @@ -95,8 +95,8 @@ static struct ast_variable *realtime_odbc(const char *database, const char *tabl newval = va_arg(ap, const char *); SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL); } - - res = SQLExecute(stmt); + + res = odbc_smart_execute(obj, stmt); if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) { ast_log(LOG_WARNING, "SQL Execute error!\n[%s]\n\n", sql); @@ -198,7 +198,7 @@ static struct ast_config *realtime_multi_odbc(const char *database, const char * return NULL; memset(&ra, 0, sizeof(ra)); - obj = fetch_odbc_obj(database); + obj = fetch_odbc_obj(database, 0); if (!obj) return NULL; @@ -242,7 +242,7 @@ static struct ast_config *realtime_multi_odbc(const char *database, const char * SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL); } - res = SQLExecute(stmt); + res = odbc_smart_execute(obj, stmt); if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) { ast_log(LOG_WARNING, "SQL Execute error!\n[%s]\n\n", sql); @@ -333,7 +333,7 @@ static int update_odbc(const char *database, const char *table, const char *keyf if (!table) return -1; - obj = fetch_odbc_obj (database); + obj = fetch_odbc_obj (database, 0); if (!obj) return -1; @@ -374,7 +374,7 @@ static int update_odbc(const char *database, const char *table, const char *keyf SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(lookup), 0, (void *)lookup, 0, NULL); - res = SQLExecute(stmt); + res = odbc_smart_execute(obj, stmt); if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) { ast_log(LOG_WARNING, "SQL Execute error!\n[%s]\n\n", sql); @@ -411,7 +411,7 @@ static struct ast_config *config_odbc(const char *database, const char *table, c if (!file || !strcmp (file, "res_config_odbc.conf")) return NULL; /* cant configure myself with myself ! */ - obj = fetch_odbc_obj(database); + obj = fetch_odbc_obj(database, 0); if (!obj) return NULL; @@ -425,10 +425,11 @@ static struct ast_config *config_odbc(const char *database, const char *table, c SQLBindCol (stmt, 6, SQL_C_CHAR, &category, sizeof (category), &err); SQLBindCol (stmt, 7, SQL_C_CHAR, &var_name, sizeof (var_name), &err); SQLBindCol (stmt, 8, SQL_C_CHAR, &var_val, sizeof (var_val), &err); - + snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE filename='%s' and commented=0 ORDER BY filename,cat_metric desc,var_metric asc,category,var_name,var_val,id", table, file); - res = SQLExecDirect (stmt, sql, SQL_NTS); + res = odbc_smart_direct_execute(obj, stmt, sql); + if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) { ast_log (LOG_WARNING, "SQL select error!\n[%s]\n\n", sql); SQLFreeHandle (SQL_HANDLE_STMT, stmt); diff --git a/res/res_odbc.c b/res/res_odbc.c index f843860aa..40cda2750 100755 --- a/res/res_odbc.c +++ b/res/res_odbc.c @@ -33,6 +33,7 @@ struct odbc_list static struct odbc_list ODBC_REGISTRY[MAX_ODBC_HANDLES]; + static void odbc_destroy(void) { int x = 0; @@ -56,7 +57,7 @@ static odbc_obj *odbc_read(struct odbc_list *registry, const char *name) return NULL; } -static int odbc_write(struct odbc_list *registry, char *name, odbc_obj * obj) +static int odbc_write(struct odbc_list *registry, char *name, odbc_obj *obj) { int x = 0; for (x = 0; x < MAX_ODBC_HANDLES; x++) { @@ -81,6 +82,82 @@ static void odbc_init(void) static char *tdesc = "ODBC Resource"; /* internal stuff */ +int odbc_smart_execute(odbc_obj *obj, SQLHSTMT stmt) +{ + int res = 0; + res = SQLExecute(stmt); + if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) { + ast_log(LOG_WARNING, "SQL Execute error! Attempting a reconnect...\n"); + ast_mutex_lock(&obj->lock); + obj->up = 0; + ast_mutex_unlock(&obj->lock); + odbc_obj_disconnect(obj); + odbc_obj_connect(obj); + res = SQLExecute(stmt); + } + + return res; +} + + +int odbc_smart_direct_execute(odbc_obj *obj, SQLHSTMT stmt, char *sql) +{ + int res = 0; + + res = SQLExecDirect (stmt, sql, SQL_NTS); + if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) { + ast_log(LOG_WARNING, "SQL Execute error! Attempting a reconnect...\n"); + ast_mutex_lock(&obj->lock); + obj->up = 0; + ast_mutex_unlock(&obj->lock); + odbc_obj_disconnect(obj); + odbc_obj_connect(obj); + res = SQLExecDirect (stmt, sql, SQL_NTS); + } + + return res; +} + +int odbc_sanity_check(odbc_obj *obj) +{ + char *test_sql = "select 1"; + SQLHSTMT stmt; + int res = 0; + SQLLEN rowcount = 0; + + ast_mutex_lock(&obj->lock); + if(obj->up) { /* so you say... let's make sure */ + res = SQLAllocHandle (SQL_HANDLE_STMT, obj->con, &stmt); + if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) { + obj->up = 0; /* Liar!*/ + } else { + res = SQLPrepare(stmt, test_sql, SQL_NTS); + if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) { + obj->up = 0; /* Liar!*/ + } else { + res = SQLExecute(stmt); + if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) { + obj->up = 0; /* Liar!*/ + } else { + res = SQLRowCount(stmt, &rowcount); + if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) { + obj->up = 0; /* Liar!*/ + } + } + } + } + SQLFreeHandle (SQL_HANDLE_STMT, stmt); + } + ast_mutex_unlock(&obj->lock); + + if(!obj->up) { /* Try to reconnect! */ + ast_log(LOG_WARNING, "Connection is down attempting to reconnect...\n"); + odbc_obj_disconnect(obj); + odbc_obj_connect(obj); + } + return obj->up; +} + static int load_odbc_config(void) { static char *cfg = "res_odbc.conf"; @@ -145,9 +222,11 @@ static int load_odbc_config(void) return 0; } -int odbc_dump_fd(int fd, odbc_obj * obj) +int odbc_dump_fd(int fd, odbc_obj *obj) { - ast_cli(fd, "Name: %s\nDSN: %s\nConnected: %s\n", obj->name, obj->dsn, obj->up ? "yes" : "no"); + /* make sure the connection is up before we lie to our master.*/ + odbc_sanity_check(obj); + ast_cli(fd, "Name: %s\nDSN: %s\nConnected: %s\n\n", obj->name, obj->dsn, obj->up ? "yes" : "no"); return 0; } @@ -244,16 +323,21 @@ static struct ast_cli_entry odbc_show_struct = /* api calls */ -int register_odbc_obj(char *name, odbc_obj * obj) +int register_odbc_obj(char *name, odbc_obj *obj) { if (obj != NULL) return odbc_write(ODBC_REGISTRY, name, obj); return 0; } -odbc_obj *fetch_odbc_obj(const char *name) +odbc_obj *fetch_odbc_obj(const char *name, int check) { - return (odbc_obj *) odbc_read(ODBC_REGISTRY, name); + odbc_obj *obj = NULL; + if((obj = (odbc_obj *) odbc_read(ODBC_REGISTRY, name))) { + if(check) + odbc_sanity_check(obj); + } + return obj; } odbc_obj *new_odbc_obj(char *name, char *dsn, char *username, char *password) @@ -295,7 +379,7 @@ odbc_obj *new_odbc_obj(char *name, char *dsn, char *username, char *password) return new; } -void destroy_obdc_obj(odbc_obj ** obj) +void destroy_obdc_obj(odbc_obj **obj) { odbc_obj_disconnect(*obj); @@ -311,32 +395,30 @@ void destroy_obdc_obj(odbc_obj ** obj) if ((*obj)->password) free((*obj)->password); ast_mutex_unlock(&(*obj)->lock); + ast_mutex_destroy(&(*obj)->lock); free(*obj); } -odbc_status odbc_obj_disconnect(odbc_obj * obj) +odbc_status odbc_obj_disconnect(odbc_obj *obj) { int res; ast_mutex_lock(&obj->lock); - if (obj->up) { - res = SQLDisconnect(obj->con); - } else { - res = -1; - } + res = SQLDisconnect(obj->con); + if (res == ODBC_SUCCESS) { ast_log(LOG_WARNING, "res_odbc: disconnected %d from %s [%s]\n", res, obj->name, obj->dsn); - obj->up = 0; } else { ast_log(LOG_WARNING, "res_odbc: %s [%s] already disconnected\n", obj->name, obj->dsn); } + obj->up = 0; ast_mutex_unlock(&obj->lock); return ODBC_SUCCESS; } -odbc_status odbc_obj_connect(odbc_obj * obj) +odbc_status odbc_obj_connect(odbc_obj *obj) { int res; long int err; @@ -378,8 +460,13 @@ odbc_status odbc_obj_connect(odbc_obj * obj) } SQLSetConnectAttr(obj->con, SQL_LOGIN_TIMEOUT, (SQLPOINTER *) 10, 0); } + if(obj->up) { + odbc_obj_disconnect(obj); + ast_log(LOG_NOTICE,"Re-connecting %s\n", obj->name); + } + + ast_log(LOG_NOTICE, "Connecting %s\n", obj->name); - ast_log(LOG_NOTICE, "Calling %p/%p\n", obj->username, obj->password); res = SQLConnect(obj->con, (SQLCHAR *) obj->dsn, SQL_NTS, (SQLCHAR *) obj->username, SQL_NTS, -- cgit v1.2.3