| Summary: | ASTERISK-10300: [patch] Allow odbc queries using SQLExecDirect() (ie. non prepared statement execution) | ||
| Reporter: | Matthew Nicholson (mnicholson) | Labels: | |
| Date Opened: | 2007-09-14 11:15:34 | Date Closed: | 2007-09-14 12:10:47 |
| Priority: | Major | Regression? | No |
| Status: | Closed/Complete | Components: | Resources/res_odbc |
| Versions: | Frequency of Occurrence | ||
| Related Issues: | |||
| Environment: | Attachments: | ( 0) sql_patch2 ( 1) sql-exec-direct1.diff | |
| Description: | This patch adds a hook into res_odbc to allow non prepared statement execution. The idea behind prepared statements (SQLPrepare() -> SQLExecute()) in ODBC is to allow efficient execution of a single statement multiple times. I do not think there is a single place in asterisk where we execute the same query over and over again in this manner. Generally we prepare a query execute it, then free the statement handle. This patch provides a hook to execute a query with in the res_odbc framework (handling db reconnection and such) but use SQLExecDirect() which does not require SQLPrepare(). On MS SQL databases this can dramatically improve database performance. Patches to use this function from func_odbc and voicemail should follow from vovochka and myself. ****** ADDITIONAL INFORMATION ****** This issue was discovered by one of our customers using a MS SQL database. Performance gains would probably not be seen in most cases on a MySQL DB because most installations of MySQL do not support prepared statements. Using SQLExecDirect() instead of SQLPrepare() is a speed improvement because servicing the SQLPrepare() call uses resources in the database. If we called SQLExecute() multiple times after SQLPrepare() then it would be faster then calling SQLExecDirect() multiple times in the same situation, but we only call SQLExecute() once, in which case one SQLExecDirect() is faster than the SQLPrepare plus SQLExecute() pair. | ||
| Comments: | By: Tilghman Lesher (tilghman) 2007-09-14 11:26:05 You're ignoring the many places where we need to bind parameters prior to statement execution. All of those places (which is the majority of Asterisk) require the separate SQLPrepare, SQLBindParam, SQLExecute. We actually had the execdirect method in 1.2 and it was removed, because it was never used (or really, never used in an appropriate way). By: Matthew Nicholson (mnicholson) 2007-09-14 11:39:17 You are mistaken, you can bind parameters with SQLExecDirect(). I found code that does this on the internet, and vovochka will submit a patch for voicemail that does this as well. By: Digium Subversion (svnbot) 2007-09-14 12:10:47 Repository: asterisk Revision: 82393 ------------------------------------------------------------------------ r82393 | tilghman | 2007-09-14 12:10:46 -0500 (Fri, 14 Sep 2007) | 2 lines Add a direct execute method to res_odbc (closes issue ASTERISK-10300) ------------------------------------------------------------------------ | ||