Index: apps/app_voicemail.c =================================================================== --- apps/app_voicemail.c (revision 81365) +++ apps/app_voicemail.c (working copy) @@ -942,7 +942,6 @@ } } - #ifdef ODBC_STORAGE struct generic_prepare_struct { char *sql; @@ -950,6 +949,10 @@ char **argv; }; +/* Custom Change: SQLPrepare-SQLExecute pair was replaced by a + * single SQLExecDirect to lower the numberof temporary stored + * procedures on customer's MS SQL server. + */ static SQLHSTMT generic_prepare(struct odbc_obj *obj, void *data) { struct generic_prepare_struct *gps = data; @@ -961,15 +964,18 @@ ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n"); return NULL; } - res = SQLPrepare(stmt, (unsigned char *)gps->sql, SQL_NTS); - if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) { - ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", gps->sql); - SQLFreeHandle(SQL_HANDLE_STMT, stmt); - return NULL; - } + for (i = 0; i < gps->argc; i++) SQLBindParameter(stmt, i + 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(gps->argv[i]), 0, gps->argv[i], 0, NULL); + res = SQLExecDirect(stmt, (unsigned char *)gps->sql, SQL_NTS); + if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) { + ast_log(LOG_WARNING, "SQL Exec Direct failed![%s]\n", gps->sql); + SQLCloseCursor(stmt); + SQLFreeHandle (SQL_HANDLE_STMT, stmt); + return NULL; + } + return stmt; } @@ -1023,7 +1029,9 @@ snprintf(full_fn, sizeof(full_fn), "%s.%s", fn, fmt); snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE dir=? AND msgnum=?",odbc_table); - stmt = ast_odbc_prepare_and_execute(obj, generic_prepare, &gps); + + stmt = ast_odbc_direct_execute(obj, generic_prepare, &gps); + if (!stmt) { ast_log(LOG_WARNING, "SQL Execute error!\n[%s]\n\n", sql); ast_odbc_release_obj(obj); @@ -1156,7 +1164,8 @@ obj = ast_odbc_request_obj(odbc_database, 0); if (obj) { snprintf(sql, sizeof(sql), "SELECT COUNT(*) FROM %s WHERE dir=?",odbc_table); - stmt = ast_odbc_prepare_and_execute(obj, generic_prepare, &gps); + stmt = ast_odbc_direct_execute(obj, generic_prepare, &gps); + if (!stmt) { ast_log(LOG_WARNING, "SQL Execute error!\n[%s]\n\n", sql); ast_odbc_release_obj(obj); @@ -1202,7 +1211,8 @@ if (obj) { snprintf(msgnums, sizeof(msgnums), "%d", msgnum); snprintf(sql, sizeof(sql), "SELECT COUNT(*) FROM %s WHERE dir=? AND msgnum=?",odbc_table); - stmt = ast_odbc_prepare_and_execute(obj, generic_prepare, &gps); + stmt = ast_odbc_direct_execute(obj, generic_prepare, &gps); + if (!stmt) { ast_log(LOG_WARNING, "SQL Execute error!\n[%s]\n\n", sql); ast_odbc_release_obj(obj); @@ -1250,7 +1260,8 @@ if (obj) { snprintf(msgnums, sizeof(msgnums), "%d", smsg); snprintf(sql, sizeof(sql), "DELETE FROM %s WHERE dir=? AND msgnum=?",odbc_table); - stmt = ast_odbc_prepare_and_execute(obj, generic_prepare, &gps); + stmt = ast_odbc_direct_execute(obj, generic_prepare, &gps); + if (!stmt) ast_log(LOG_WARNING, "SQL Execute error!\n[%s]\n\n", sql); else @@ -1277,7 +1288,8 @@ snprintf(msgnums, sizeof(msgnums), "%d", smsg); snprintf(msgnumd, sizeof(msgnumd), "%d", dmsg); snprintf(sql, sizeof(sql), "INSERT INTO %s (dir, msgnum, context, macrocontext, callerid, origtime, duration, recording, mailboxuser, mailboxcontext) SELECT ?,?,context,macrocontext,callerid,origtime,duration,recording,?,? FROM %s WHERE dir=? AND msgnum=?",odbc_table,odbc_table); - stmt = ast_odbc_prepare_and_execute(obj, generic_prepare, &gps); + stmt = ast_odbc_direct_execute(obj, generic_prepare, &gps); + if (!stmt) ast_log(LOG_WARNING, "SQL Execute error!\n[%s] (You probably don't have MySQL 4.1 or later installed)\n\n", sql); else @@ -1364,13 +1376,6 @@ snprintf(sql, sizeof(sql), "INSERT INTO %s (dir,msgnum,recording,context,macrocontext,callerid,origtime,duration,mailboxuser,mailboxcontext,category) VALUES (?,?,?,?,?,?,?,?,?,?,?)",odbc_table); else snprintf(sql, sizeof(sql), "INSERT INTO %s (dir,msgnum,recording,context,macrocontext,callerid,origtime,duration,mailboxuser,mailboxcontext) VALUES (?,?,?,?,?,?,?,?,?,?)",odbc_table); - res = SQLPrepare(stmt, (unsigned char *)sql, SQL_NTS); - if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) { - ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", sql); - SQLFreeHandle (SQL_HANDLE_STMT, stmt); - ast_odbc_release_obj(obj); - goto yuck; - } len = fdlen; /* SQL_LEN_DATA_AT_EXEC(fdlen); */ SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(dir), 0, (void *)dir, 0, NULL); SQLBindParameter(stmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(msgnums), 0, (void *)msgnums, 0, NULL); @@ -1384,7 +1389,7 @@ SQLBindParameter(stmt, 10, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(mailboxcontext), 0, (void *)mailboxcontext, 0, NULL); if (!ast_strlen_zero(category)) SQLBindParameter(stmt, 11, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(category), 0, (void *)category, 0, NULL); - res = ast_odbc_smart_execute(obj, stmt); + res = ast_odbc_smart_direct_execute(obj, stmt, sql); if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) { ast_log(LOG_WARNING, "SQL Execute error!\n[%s]\n\n", sql); SQLFreeHandle (SQL_HANDLE_STMT, stmt); @@ -1421,7 +1426,8 @@ snprintf(msgnums, sizeof(msgnums), "%d", smsg); snprintf(msgnumd, sizeof(msgnumd), "%d", dmsg); snprintf(sql, sizeof(sql), "UPDATE %s SET dir=?, msgnum=?, mailboxuser=?, mailboxcontext=? WHERE dir=? AND msgnum=?",odbc_table); - stmt = ast_odbc_prepare_and_execute(obj, generic_prepare, &gps); + stmt = ast_odbc_direct_execute(obj, generic_prepare, &gps); + if (!stmt) ast_log(LOG_WARNING, "SQL Execute error!\n[%s]\n\n", sql); else @@ -2144,7 +2150,8 @@ obj = ast_odbc_request_obj(odbc_database, 0); if (obj) { snprintf(sql, sizeof(sql), "SELECT COUNT(*) FROM %s WHERE dir = '%s%s/%s/%s'", odbc_table, VM_SPOOL_DIR, context, tmp, "INBOX"); - stmt = ast_odbc_prepare_and_execute(obj, generic_prepare, &gps); + stmt = ast_odbc_direct_execute(obj, generic_prepare, &gps); + if (!stmt) { ast_log(LOG_WARNING, "SQL Execute error!\n[%s]\n\n", sql); ast_odbc_release_obj(obj); @@ -2174,7 +2181,8 @@ goto yuck; } snprintf(sql, sizeof(sql), "SELECT COUNT(*) FROM %s WHERE dir = '%s%s/%s/%s'", odbc_table, VM_SPOOL_DIR, context, tmp, "Old"); - stmt = ast_odbc_prepare_and_execute(obj, generic_prepare, &gps); + stmt = ast_odbc_direct_execute(obj, generic_prepare, &gps); + if (!stmt) { ast_log(LOG_WARNING, "SQL Execute error!\n[%s]\n\n", sql); ast_odbc_release_obj(obj); @@ -2223,7 +2231,8 @@ obj = ast_odbc_request_obj(odbc_database, 0); if (obj) { snprintf(sql, sizeof(sql), "SELECT COUNT(*) FROM %s WHERE dir = '%s%s/%s/%s'", odbc_table, VM_SPOOL_DIR, context, mailbox, folder); - stmt = ast_odbc_prepare_and_execute(obj, generic_prepare, &gps); + stmt = ast_odbc_direct_execute(obj, generic_prepare, &gps); + if (!stmt) { ast_log(LOG_WARNING, "SQL Execute error!\n[%s]\n\n", sql); goto yuck; Index: include/asterisk/res_odbc.h =================================================================== --- include/asterisk/res_odbc.h (revision 81365) +++ include/asterisk/res_odbc.h (working copy) @@ -62,6 +62,16 @@ */ int ast_odbc_smart_execute(struct odbc_obj *obj, SQLHSTMT stmt); /* DEPRECATED */ +/* Two functions below are custom made. It replaces SQLPrepare-SQLExecute pair by single + * SQLExecDirect to avoid temporary stored procedures created by SQLPrepare on + * customer's MS SQL server. Stored procedures are system expensive and are + * recommended only when SQL statement is submited by application more then 3 + * times. + */ +SQLHSTMT ast_odbc_direct_execute(struct odbc_obj *obj, SQLHSTMT (*exec_cb)(struct odbc_obj *obj, void *data), void *data); + +int ast_odbc_smart_direct_execute(struct odbc_obj *obj, SQLHSTMT stmt, char* sql); + /*! \brief Retrieves a connected ODBC object * \param name The name of the ODBC class for which a connection is needed. * \param check Whether to ensure that a connection is valid before returning the handle. Usually unnecessary. Index: res/res_config_odbc.c =================================================================== --- res/res_config_odbc.c (revision 81365) +++ res/res_config_odbc.c (working copy) @@ -76,13 +76,6 @@ return NULL; } - res = SQLPrepare(stmt, (unsigned char *)cps->sql, SQL_NTS); - if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) { - ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", cps->sql); - SQLFreeHandle (SQL_HANDLE_STMT, stmt); - return NULL; - } - while ((newparam = va_arg(ap, const char *))) { newval = va_arg(ap, const char *); SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL); @@ -91,6 +84,14 @@ if (!ast_strlen_zero(cps->extra)) SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(cps->extra), 0, (void *)cps->extra, 0, NULL); + + res = SQLExecDirect(stmt, (unsigned char *)cps->sql, SQL_NTS); + if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) { + ast_log(LOG_WARNING, "SQL Exec Direct failed![%s]\n", cps->sql); + SQLCloseCursor(stmt); + SQLFreeHandle (SQL_HANDLE_STMT, stmt); + return NULL; + } return stmt; } @@ -145,7 +146,7 @@ } va_end(aq); - stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps); + stmt = ast_odbc_direct_execute(obj, custom_prepare, &cps); if (!stmt) { ast_odbc_release_obj(obj); @@ -277,7 +278,7 @@ snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield); va_end(aq); - stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps); + stmt = ast_odbc_direct_execute(obj, custom_prepare, &cps); if (!stmt) { ast_odbc_release_obj(obj); @@ -386,7 +387,7 @@ va_end(aq); snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s=?", keyfield); - stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps); + stmt = ast_odbc_direct_execute(obj, custom_prepare, &cps); if (!stmt) { ast_odbc_release_obj(obj); @@ -437,7 +438,9 @@ SQLFreeHandle(SQL_HANDLE_STMT, sth); return NULL; } - + + + SQLBindCol(sth, 1, SQL_C_ULONG, &q->cat_metric, sizeof(q->cat_metric), &q->err); SQLBindCol(sth, 2, SQL_C_CHAR, q->category, sizeof(q->category), &q->err); SQLBindCol(sth, 3, SQL_C_CHAR, q->var_name, sizeof(q->var_name), &q->err); Index: res/res_odbc.c =================================================================== --- res/res_odbc.c (revision 81365) +++ res/res_odbc.c (working copy) @@ -75,7 +75,31 @@ static odbc_status odbc_obj_disconnect(struct odbc_obj *obj); static int odbc_register_class(struct odbc_class *class, int connect); +int ast_odbc_smart_direct_execute(struct odbc_obj *obj, SQLHSTMT stmt, char* sql) +{ + int res = 0, i; + SQLINTEGER nativeerror=0, numfields=0; + SQLSMALLINT diagbytes=0; + unsigned char state[10], diagnostic[256]; + res = SQLExecDirect(stmt, (unsigned char *)sql, SQL_NTS); + if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO) && (res != SQL_NO_DATA)) { + if (res == SQL_ERROR) { + SQLGetDiagField(SQL_HANDLE_STMT, stmt, 1, SQL_DIAG_NUMBER, &numfields, SQL_IS_INTEGER, &diagbytes); + for (i = 0; i < numfields; i++) { + SQLGetDiagRec(SQL_HANDLE_STMT, stmt, i + 1, state, &nativeerror, diagnostic, sizeof(diagnostic), &diagbytes); + ast_log(LOG_WARNING, "SQL Execute returned an error %d: %s: %s (%d)\n", res, state, diagnostic, diagbytes); + if (i > 10) { + ast_log(LOG_WARNING, "Oh, that was good. There are really %d diagnostics?\n", (int)numfields); + break; + } + } + } + } + + return res; +} + SQLHSTMT ast_odbc_prepare_and_execute(struct odbc_obj *obj, SQLHSTMT (*prepare_cb)(struct odbc_obj *obj, void *data), void *data) { int res = 0, i, attempt; @@ -185,15 +209,10 @@ if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) { obj->up = 0; } else { - res = SQLPrepare(stmt, (unsigned char *)test_sql, SQL_NTS); - if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) { - obj->up = 0; - } else { - res = SQLExecute(stmt); + res = SQLExecDirect(stmt, (unsigned char *)test_sql, SQL_NTS); if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) { obj->up = 0; } - } } SQLFreeHandle (SQL_HANDLE_STMT, stmt); }