Mon Jun 27 16:50:54 2011

Asterisk developer's documentation


func_odbc.c

Go to the documentation of this file.
00001 /*
00002  * Asterisk -- An open source telephony toolkit.
00003  *
00004  * Copyright (c) 2005, 2006 Tilghman Lesher
00005  * Copyright (c) 2008, 2009 Digium, Inc.
00006  *
00007  * Tilghman Lesher <func_odbc__200508@the-tilghman.com>
00008  *
00009  * See http://www.asterisk.org for more information about
00010  * the Asterisk project. Please do not directly contact
00011  * any of the maintainers of this project for assistance;
00012  * the project provides a web site, mailing lists and IRC
00013  * channels for your use.
00014  *
00015  * This program is free software, distributed under the terms of
00016  * the GNU General Public License Version 2. See the LICENSE file
00017  * at the top of the source tree.
00018  */
00019 
00020 /*!
00021  * \file
00022  *
00023  * \brief ODBC lookups
00024  *
00025  * \author Tilghman Lesher <func_odbc__200508@the-tilghman.com>
00026  *
00027  * \ingroup functions
00028  */
00029 
00030 /*** MODULEINFO
00031    <depend>res_odbc</depend>
00032  ***/
00033 
00034 #include "asterisk.h"
00035 
00036 ASTERISK_FILE_VERSION(__FILE__, "$Revision: 307837 $")
00037 
00038 #include "asterisk/module.h"
00039 #include "asterisk/file.h"
00040 #include "asterisk/channel.h"
00041 #include "asterisk/pbx.h"
00042 #include "asterisk/config.h"
00043 #include "asterisk/res_odbc.h"
00044 #include "asterisk/app.h"
00045 #include "asterisk/cli.h"
00046 #include "asterisk/strings.h"
00047 
00048 /*** DOCUMENTATION
00049    <function name="ODBC_FETCH" language="en_US">
00050       <synopsis>
00051          Fetch a row from a multirow query.
00052       </synopsis>
00053       <syntax>
00054          <parameter name="result-id" required="true" />
00055       </syntax>
00056       <description>
00057          <para>For queries which are marked as mode=multirow, the original 
00058          query returns a <replaceable>result-id</replaceable> from which results 
00059          may be fetched.  This function implements the actual fetch of the results.</para>
00060          <para>This also sets <variable>ODBC_FETCH_STATUS</variable>.</para>
00061          <variablelist>
00062             <variable name="ODBC_FETCH_STATUS">
00063                <value name="SUCESS">
00064                   If rows are available.
00065                </value>
00066                <value name="FAILURE">
00067                   If no rows are available.
00068                </value>
00069             </variable>
00070          </variablelist>
00071       </description>
00072    </function>
00073    <application name="ODBCFinish" language="en_US">
00074       <synopsis>
00075          Clear the resultset of a sucessful multirow query.
00076       </synopsis>
00077       <syntax>
00078          <parameter name="result-id" required="true" />
00079       </syntax>
00080       <description>
00081          <para>For queries which are marked as mode=multirow, this will clear 
00082          any remaining rows of the specified resultset.</para>
00083       </description>
00084    </application>
00085    <function name="SQL_ESC" language="en_US">
00086       <synopsis>
00087          Escapes single ticks for use in SQL statements.
00088       </synopsis>
00089       <syntax>
00090          <parameter name="string" required="true" />
00091       </syntax>
00092       <description>
00093          <para>Used in SQL templates to escape data which may contain single ticks 
00094          <literal>'</literal> which are otherwise used to delimit data.</para>
00095          <para>Example: SELECT foo FROM bar WHERE baz='${SQL_ESC(${ARG1})}'</para>
00096       </description>
00097    </function>
00098  ***/
00099 
00100 static char *config = "func_odbc.conf";
00101 
00102 enum odbc_option_flags {
00103    OPT_ESCAPECOMMAS =   (1 << 0),
00104    OPT_MULTIROW     =   (1 << 1),
00105 };
00106 
00107 struct acf_odbc_query {
00108    AST_RWLIST_ENTRY(acf_odbc_query) list;
00109    char readhandle[5][30];
00110    char writehandle[5][30];
00111    char sql_read[2048];
00112    char sql_write[2048];
00113    char sql_insert[2048];
00114    unsigned int flags;
00115    int rowlimit;
00116    struct ast_custom_function *acf;
00117 };
00118 
00119 static void odbc_datastore_free(void *data);
00120 
00121 static struct ast_datastore_info odbc_info = {
00122    .type = "FUNC_ODBC",
00123    .destroy = odbc_datastore_free,
00124 };
00125 
00126 /* For storing each result row */
00127 struct odbc_datastore_row {
00128    AST_LIST_ENTRY(odbc_datastore_row) list;
00129    char data[0];
00130 };
00131 
00132 /* For storing each result set */
00133 struct odbc_datastore {
00134    AST_LIST_HEAD(, odbc_datastore_row);
00135    char names[0];
00136 };
00137 
00138 static AST_RWLIST_HEAD_STATIC(queries, acf_odbc_query);
00139 
00140 static int resultcount = 0;
00141 
00142 AST_THREADSTORAGE(sql_buf);
00143 AST_THREADSTORAGE(sql2_buf);
00144 AST_THREADSTORAGE(coldata_buf);
00145 AST_THREADSTORAGE(colnames_buf);
00146 
00147 static int acf_fetch(struct ast_channel *chan, const char *cmd, char *data, char *buf, size_t len);
00148 
00149 static void odbc_datastore_free(void *data)
00150 {
00151    struct odbc_datastore *result = data;
00152    struct odbc_datastore_row *row;
00153    AST_LIST_LOCK(result);
00154    while ((row = AST_LIST_REMOVE_HEAD(result, list))) {
00155       ast_free(row);
00156    }
00157    AST_LIST_UNLOCK(result);
00158    AST_LIST_HEAD_DESTROY(result);
00159    ast_free(result);
00160 }
00161 
00162 static SQLHSTMT generic_execute(struct odbc_obj *obj, void *data)
00163 {
00164    int res;
00165    char *sql = data;
00166    SQLHSTMT stmt;
00167 
00168    res = SQLAllocHandle (SQL_HANDLE_STMT, obj->con, &stmt);
00169    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00170       ast_log(LOG_WARNING, "SQL Alloc Handle failed (%d)!\n", res);
00171       return NULL;
00172    }
00173 
00174    res = SQLExecDirect(stmt, (unsigned char *)sql, SQL_NTS);
00175    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00176       if (res == SQL_ERROR) {
00177          int i;
00178          SQLINTEGER nativeerror=0, numfields=0;
00179          SQLSMALLINT diagbytes=0;
00180          unsigned char state[10], diagnostic[256];
00181 
00182          SQLGetDiagField(SQL_HANDLE_STMT, stmt, 1, SQL_DIAG_NUMBER, &numfields, SQL_IS_INTEGER, &diagbytes);
00183          for (i = 0; i < numfields; i++) {
00184             SQLGetDiagRec(SQL_HANDLE_STMT, stmt, i + 1, state, &nativeerror, diagnostic, sizeof(diagnostic), &diagbytes);
00185             ast_log(LOG_WARNING, "SQL Execute returned an error %d: %s: %s (%d)\n", res, state, diagnostic, diagbytes);
00186             if (i > 10) {
00187                ast_log(LOG_WARNING, "Oh, that was good.  There are really %d diagnostics?\n", (int)numfields);
00188                break;
00189             }
00190          }
00191       }
00192 
00193       ast_log(LOG_WARNING, "SQL Exec Direct failed (%d)![%s]\n", res, sql);
00194       SQLCloseCursor(stmt);
00195       SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00196       return NULL;
00197    }
00198 
00199    return stmt;
00200 }
00201 
00202 /*
00203  * Master control routine
00204  */
00205 static int acf_odbc_write(struct ast_channel *chan, const char *cmd, char *s, const char *value)
00206 {
00207    struct odbc_obj *obj = NULL;
00208    struct acf_odbc_query *query;
00209    char *t, varname[15];
00210    int i, dsn, bogus_chan = 0;
00211    int transactional = 0;
00212    AST_DECLARE_APP_ARGS(values,
00213       AST_APP_ARG(field)[100];
00214    );
00215    AST_DECLARE_APP_ARGS(args,
00216       AST_APP_ARG(field)[100];
00217    );
00218    SQLHSTMT stmt = NULL;
00219    SQLLEN rows=0;
00220    struct ast_str *buf = ast_str_thread_get(&sql_buf, 16);
00221    struct ast_str *insertbuf = ast_str_thread_get(&sql2_buf, 16);
00222    const char *status = "FAILURE";
00223 
00224    if (!buf || !insertbuf) {
00225       return -1;
00226    }
00227 
00228    AST_RWLIST_RDLOCK(&queries);
00229    AST_RWLIST_TRAVERSE(&queries, query, list) {
00230       if (!strcmp(query->acf->name, cmd)) {
00231          break;
00232       }
00233    }
00234 
00235    if (!query) {
00236       ast_log(LOG_ERROR, "No such function '%s'\n", cmd);
00237       AST_RWLIST_UNLOCK(&queries);
00238       if (chan) {
00239          pbx_builtin_setvar_helper(chan, "ODBCSTATUS", status);
00240       }
00241       return -1;
00242    }
00243 
00244    if (!chan) {
00245       if (!(chan = ast_dummy_channel_alloc())) {
00246          AST_RWLIST_UNLOCK(&queries);
00247          return -1;
00248       }
00249       bogus_chan = 1;
00250    }
00251 
00252    if (!bogus_chan) {
00253       ast_autoservice_start(chan);
00254    }
00255 
00256    ast_str_make_space(&buf, strlen(query->sql_write) * 2 + 300);
00257    ast_str_make_space(&insertbuf, strlen(query->sql_insert) * 2 + 300);
00258 
00259    /* Parse our arguments */
00260    t = value ? ast_strdupa(value) : "";
00261 
00262    if (!s || !t) {
00263       ast_log(LOG_ERROR, "Out of memory\n");
00264       AST_RWLIST_UNLOCK(&queries);
00265       if (!bogus_chan) {
00266          ast_autoservice_stop(chan);
00267          pbx_builtin_setvar_helper(chan, "ODBCSTATUS", status);
00268       } else {
00269          ast_channel_release(chan);
00270       }
00271       return -1;
00272    }
00273 
00274    AST_STANDARD_APP_ARGS(args, s);
00275    for (i = 0; i < args.argc; i++) {
00276       snprintf(varname, sizeof(varname), "ARG%d", i + 1);
00277       pbx_builtin_pushvar_helper(chan, varname, args.field[i]);
00278    }
00279 
00280    /* Parse values, just like arguments */
00281    AST_STANDARD_APP_ARGS(values, t);
00282    for (i = 0; i < values.argc; i++) {
00283       snprintf(varname, sizeof(varname), "VAL%d", i + 1);
00284       pbx_builtin_pushvar_helper(chan, varname, values.field[i]);
00285    }
00286 
00287    /* Additionally set the value as a whole (but push an empty string if value is NULL) */
00288    pbx_builtin_pushvar_helper(chan, "VALUE", value ? value : "");
00289 
00290    ast_str_substitute_variables(&buf, 0, chan, query->sql_write);
00291    ast_str_substitute_variables(&insertbuf, 0, chan, query->sql_insert);
00292 
00293    if (bogus_chan) {
00294       chan = ast_channel_release(chan);
00295    } else {
00296       /* Restore prior values */
00297       for (i = 0; i < args.argc; i++) {
00298          snprintf(varname, sizeof(varname), "ARG%d", i + 1);
00299          pbx_builtin_setvar_helper(chan, varname, NULL);
00300       }
00301 
00302       for (i = 0; i < values.argc; i++) {
00303          snprintf(varname, sizeof(varname), "VAL%d", i + 1);
00304          pbx_builtin_setvar_helper(chan, varname, NULL);
00305       }
00306       pbx_builtin_setvar_helper(chan, "VALUE", NULL);
00307 
00308       /*!\note
00309        * Okay, this part is confusing.  Transactions belong to a single database
00310        * handle.  Therefore, when working with transactions, we CANNOT failover
00311        * to multiple DSNs.  We MUST have a single handle all the way through the
00312        * transaction, or else we CANNOT enforce atomicity.
00313        */
00314       for (dsn = 0; dsn < 5; dsn++) {
00315          if (transactional) {
00316             /* This can only happen second time through or greater. */
00317             ast_log(LOG_WARNING, "Transactions do not work well with multiple DSNs for 'writehandle'\n");
00318          }
00319 
00320          if (!ast_strlen_zero(query->writehandle[dsn])) {
00321             if ((obj = ast_odbc_retrieve_transaction_obj(chan, query->writehandle[dsn]))) {
00322                transactional = 1;
00323             } else {
00324                obj = ast_odbc_request_obj(query->writehandle[dsn], 0);
00325                transactional = 0;
00326             }
00327             if (obj && (stmt = ast_odbc_direct_execute(obj, generic_execute, ast_str_buffer(buf)))) {
00328                break;
00329             }
00330          }
00331 
00332          if (obj && !transactional) {
00333             ast_odbc_release_obj(obj);
00334             obj = NULL;
00335          }
00336       }
00337    }
00338 
00339    if (stmt) {
00340       SQLRowCount(stmt, &rows);
00341    }
00342 
00343    if (stmt && rows == 0 && ast_str_strlen(insertbuf) != 0) {
00344       SQLCloseCursor(stmt);
00345       SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00346       for (dsn = 0; dsn < 5; dsn++) {
00347          if (!ast_strlen_zero(query->writehandle[dsn])) {
00348             obj = ast_odbc_request_obj(query->writehandle[dsn], 0);
00349             if (obj) {
00350                stmt = ast_odbc_direct_execute(obj, generic_execute, ast_str_buffer(insertbuf));
00351             }
00352          }
00353          if (stmt) {
00354             status = "FAILOVER";
00355             SQLRowCount(stmt, &rows);
00356             break;
00357          }
00358          ast_odbc_release_obj(obj);
00359          obj = NULL;
00360       }
00361    } else if (stmt) {
00362       status = "SUCCESS";
00363    }
00364 
00365    AST_RWLIST_UNLOCK(&queries);
00366 
00367    /* Output the affected rows, for all cases.  In the event of failure, we
00368     * flag this as -1 rows.  Note that this is different from 0 affected rows
00369     * which would be the case if we succeeded in our query, but the values did
00370     * not change. */
00371    if (!bogus_chan) {
00372       snprintf(varname, sizeof(varname), "%d", (int)rows);
00373       pbx_builtin_setvar_helper(chan, "ODBCROWS", varname);
00374       pbx_builtin_setvar_helper(chan, "ODBCSTATUS", status);
00375    }
00376 
00377    if (stmt) {
00378       SQLCloseCursor(stmt);
00379       SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00380    }
00381    if (obj && !transactional) {
00382       ast_odbc_release_obj(obj);
00383       obj = NULL;
00384    }
00385 
00386    if (!bogus_chan) {
00387       ast_autoservice_stop(chan);
00388    }
00389 
00390    return 0;
00391 }
00392 
00393 static int acf_odbc_read(struct ast_channel *chan, const char *cmd, char *s, char *buf, size_t len)
00394 {
00395    struct odbc_obj *obj = NULL;
00396    struct acf_odbc_query *query;
00397    char varname[15], rowcount[12] = "-1";
00398    struct ast_str *colnames = ast_str_thread_get(&colnames_buf, 16);
00399    int res, x, y, buflen = 0, escapecommas, rowlimit = 1, multirow = 0, dsn, bogus_chan = 0;
00400    AST_DECLARE_APP_ARGS(args,
00401       AST_APP_ARG(field)[100];
00402    );
00403    SQLHSTMT stmt = NULL;
00404    SQLSMALLINT colcount=0;
00405    SQLLEN indicator;
00406    SQLSMALLINT collength;
00407    struct odbc_datastore *resultset = NULL;
00408    struct odbc_datastore_row *row = NULL;
00409    struct ast_str *sql = ast_str_thread_get(&sql_buf, 16);
00410    const char *status = "FAILURE";
00411 
00412    if (!sql || !colnames) {
00413       if (chan) {
00414          pbx_builtin_setvar_helper(chan, "ODBCSTATUS", status);
00415       }
00416       return -1;
00417    }
00418 
00419    ast_str_reset(colnames);
00420 
00421    AST_RWLIST_RDLOCK(&queries);
00422    AST_RWLIST_TRAVERSE(&queries, query, list) {
00423       if (!strcmp(query->acf->name, cmd)) {
00424          break;
00425       }
00426    }
00427 
00428    if (!query) {
00429       ast_log(LOG_ERROR, "No such function '%s'\n", cmd);
00430       AST_RWLIST_UNLOCK(&queries);
00431       if (chan) {
00432          pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
00433          pbx_builtin_setvar_helper(chan, "ODBCSTATUS", status);
00434       }
00435       return -1;
00436    }
00437 
00438    if (!chan) {
00439       if (!(chan = ast_dummy_channel_alloc())) {
00440          AST_RWLIST_UNLOCK(&queries);
00441          return -1;
00442       }
00443       bogus_chan = 1;
00444    }
00445 
00446    if (!bogus_chan) {
00447       ast_autoservice_start(chan);
00448    }
00449 
00450    AST_STANDARD_APP_ARGS(args, s);
00451    for (x = 0; x < args.argc; x++) {
00452       snprintf(varname, sizeof(varname), "ARG%d", x + 1);
00453       pbx_builtin_pushvar_helper(chan, varname, args.field[x]);
00454    }
00455 
00456    ast_str_substitute_variables(&sql, 0, chan, query->sql_read);
00457 
00458    if (bogus_chan) {
00459       chan = ast_channel_release(chan);
00460    } else {
00461       /* Restore prior values */
00462       for (x = 0; x < args.argc; x++) {
00463          snprintf(varname, sizeof(varname), "ARG%d", x + 1);
00464          pbx_builtin_setvar_helper(chan, varname, NULL);
00465       }
00466    }
00467 
00468    /* Save these flags, so we can release the lock */
00469    escapecommas = ast_test_flag(query, OPT_ESCAPECOMMAS);
00470    if (!bogus_chan && ast_test_flag(query, OPT_MULTIROW)) {
00471       if (!(resultset = ast_calloc(1, sizeof(*resultset)))) {
00472          pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
00473          pbx_builtin_setvar_helper(chan, "ODBCSTATUS", status);
00474          ast_autoservice_stop(chan);
00475          return -1;
00476       }
00477       AST_LIST_HEAD_INIT(resultset);
00478       if (query->rowlimit) {
00479          rowlimit = query->rowlimit;
00480       } else {
00481          rowlimit = INT_MAX;
00482       }
00483       multirow = 1;
00484    } else if (!bogus_chan) {
00485       if (query->rowlimit > 1) {
00486          rowlimit = query->rowlimit;
00487          if (!(resultset = ast_calloc(1, sizeof(*resultset)))) {
00488             pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
00489             pbx_builtin_setvar_helper(chan, "ODBCSTATUS", status);
00490             ast_autoservice_stop(chan);
00491             return -1;
00492          }
00493          AST_LIST_HEAD_INIT(resultset);
00494       }
00495    }
00496    AST_RWLIST_UNLOCK(&queries);
00497 
00498    for (dsn = 0; dsn < 5; dsn++) {
00499       if (!ast_strlen_zero(query->readhandle[dsn])) {
00500          obj = ast_odbc_request_obj(query->readhandle[dsn], 0);
00501          if (obj) {
00502             stmt = ast_odbc_direct_execute(obj, generic_execute, ast_str_buffer(sql));
00503          }
00504       }
00505       if (stmt) {
00506          break;
00507       }
00508       if (obj) {
00509          ast_odbc_release_obj(obj);
00510          obj = NULL;
00511       }
00512    }
00513 
00514    if (!stmt) {
00515       ast_log(LOG_ERROR, "Unable to execute query [%s]\n", ast_str_buffer(sql));
00516       if (obj) {
00517          ast_odbc_release_obj(obj);
00518          obj = NULL;
00519       }
00520       if (!bogus_chan) {
00521          pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
00522          ast_autoservice_stop(chan);
00523       }
00524       return -1;
00525    }
00526 
00527    res = SQLNumResultCols(stmt, &colcount);
00528    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00529       ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", ast_str_buffer(sql));
00530       SQLCloseCursor(stmt);
00531       SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00532       ast_odbc_release_obj(obj);
00533       obj = NULL;
00534       if (!bogus_chan) {
00535          pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
00536          ast_autoservice_stop(chan);
00537       }
00538       return -1;
00539    }
00540 
00541    res = SQLFetch(stmt);
00542    if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00543       int res1 = -1;
00544       if (res == SQL_NO_DATA) {
00545          ast_verb(4, "Found no rows [%s]\n", ast_str_buffer(sql));
00546          res1 = 0;
00547          buf[0] = '\0';
00548          ast_copy_string(rowcount, "0", sizeof(rowcount));
00549          status = "NODATA";
00550       } else {
00551          ast_log(LOG_WARNING, "Error %d in FETCH [%s]\n", res, ast_str_buffer(sql));
00552          status = "FETCHERROR";
00553       }
00554       SQLCloseCursor(stmt);
00555       SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00556       ast_odbc_release_obj(obj);
00557       obj = NULL;
00558       if (!bogus_chan) {
00559          pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
00560          pbx_builtin_setvar_helper(chan, "ODBCSTATUS", status);
00561          ast_autoservice_stop(chan);
00562       }
00563       return res1;
00564    }
00565 
00566    status = "SUCCESS";
00567 
00568    for (y = 0; y < rowlimit; y++) {
00569       buf[0] = '\0';
00570       for (x = 0; x < colcount; x++) {
00571          int i;
00572          struct ast_str *coldata = ast_str_thread_get(&coldata_buf, 16);
00573          char *ptrcoldata;
00574 
00575          if (!coldata) {
00576             ast_free(resultset);
00577             SQLCloseCursor(stmt);
00578             SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00579             ast_odbc_release_obj(obj);
00580             obj = NULL;
00581             pbx_builtin_setvar_helper(chan, "ODBCSTATUS", "MEMERROR");
00582             if (chan)
00583                ast_autoservice_stop(chan);
00584             if (bogus_chan) {
00585                ast_channel_release(chan);
00586             }
00587             return -1;
00588          }
00589 
00590          if (y == 0) {
00591             char colname[256];
00592             SQLULEN maxcol;
00593 
00594             res = SQLDescribeCol(stmt, x + 1, (unsigned char *)colname, sizeof(colname), &collength, NULL, &maxcol, NULL, NULL);
00595             ast_debug(3, "Got collength of %d and maxcol of %d for column '%s' (offset %d)\n", (int)collength, (int)maxcol, colname, x);
00596             if (((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) || collength == 0) {
00597                snprintf(colname, sizeof(colname), "field%d", x);
00598             }
00599 
00600             ast_str_make_space(&coldata, maxcol + 1);
00601 
00602             if (ast_str_strlen(colnames)) {
00603                ast_str_append(&colnames, 0, ",");
00604             }
00605             ast_str_append_escapecommas(&colnames, 0, colname, sizeof(colname));
00606 
00607             if (resultset) {
00608                void *tmp = ast_realloc(resultset, sizeof(*resultset) + ast_str_strlen(colnames) + 1);
00609                if (!tmp) {
00610                   ast_log(LOG_ERROR, "No space for a new resultset?\n");
00611                   ast_free(resultset);
00612                   SQLCloseCursor(stmt);
00613                   SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00614                   ast_odbc_release_obj(obj);
00615                   obj = NULL;
00616                   pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
00617                   pbx_builtin_setvar_helper(chan, "ODBCSTATUS", "MEMERROR");
00618                   ast_autoservice_stop(chan);
00619                   return -1;
00620                }
00621                resultset = tmp;
00622                strcpy((char *)resultset + sizeof(*resultset), ast_str_buffer(colnames));
00623             }
00624          }
00625 
00626          buflen = strlen(buf);
00627          res = ast_odbc_ast_str_SQLGetData(&coldata, -1, stmt, x + 1, SQL_CHAR, &indicator);
00628          if (indicator == SQL_NULL_DATA) {
00629             ast_debug(3, "Got NULL data\n");
00630             ast_str_reset(coldata);
00631             res = SQL_SUCCESS;
00632          }
00633 
00634          if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00635             ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", ast_str_buffer(sql));
00636             y = -1;
00637             buf[0] = '\0';
00638             goto end_acf_read;
00639          }
00640 
00641          ast_debug(2, "Got coldata of '%s'\n", ast_str_buffer(coldata));
00642 
00643          if (x) {
00644             buf[buflen++] = ',';
00645          }
00646 
00647          /* Copy data, encoding '\' and ',' for the argument parser */
00648          ptrcoldata = ast_str_buffer(coldata);
00649          for (i = 0; i < ast_str_strlen(coldata); i++) {
00650             if (escapecommas && (ptrcoldata[i] == '\\' || ptrcoldata[i] == ',')) {
00651                buf[buflen++] = '\\';
00652             }
00653             buf[buflen++] = ptrcoldata[i];
00654 
00655             if (buflen >= len - 2) {
00656                break;
00657             }
00658 
00659             if (ptrcoldata[i] == '\0') {
00660                break;
00661             }
00662          }
00663 
00664          buf[buflen] = '\0';
00665          ast_debug(2, "buf is now set to '%s'\n", buf);
00666       }
00667       ast_debug(2, "buf is now set to '%s'\n", buf);
00668 
00669       if (resultset) {
00670          row = ast_calloc(1, sizeof(*row) + buflen + 1);
00671          if (!row) {
00672             ast_log(LOG_ERROR, "Unable to allocate space for more rows in this resultset.\n");
00673             status = "MEMERROR";
00674             goto end_acf_read;
00675          }
00676          strcpy((char *)row + sizeof(*row), buf);
00677          AST_LIST_INSERT_TAIL(resultset, row, list);
00678 
00679          /* Get next row */
00680          res = SQLFetch(stmt);
00681          if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00682             if (res != SQL_NO_DATA) {
00683                ast_log(LOG_WARNING, "Error %d in FETCH [%s]\n", res, ast_str_buffer(sql));
00684             }
00685             /* Number of rows in the resultset */
00686             y++;
00687             break;
00688          }
00689       }
00690    }
00691 
00692 end_acf_read:
00693    if (!bogus_chan) {
00694       snprintf(rowcount, sizeof(rowcount), "%d", y);
00695       pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
00696       pbx_builtin_setvar_helper(chan, "ODBCSTATUS", status);
00697       pbx_builtin_setvar_helper(chan, "~ODBCFIELDS~", ast_str_buffer(colnames));
00698       if (resultset) {
00699          int uid;
00700          struct ast_datastore *odbc_store;
00701          if (multirow) {
00702             uid = ast_atomic_fetchadd_int(&resultcount, +1) + 1;
00703             snprintf(buf, len, "%d", uid);
00704          } else {
00705             /* Name of the query is name of the resultset */
00706             ast_copy_string(buf, cmd, len);
00707 
00708             /* If there's one with the same name already, free it */
00709             ast_channel_lock(chan);
00710             if ((odbc_store = ast_channel_datastore_find(chan, &odbc_info, buf))) {
00711                ast_channel_datastore_remove(chan, odbc_store);
00712                odbc_datastore_free(odbc_store->data);
00713                ast_free(odbc_store);
00714             }
00715             ast_channel_unlock(chan);
00716          }
00717          odbc_store = ast_datastore_alloc(&odbc_info, buf);
00718          if (!odbc_store) {
00719             ast_log(LOG_ERROR, "Rows retrieved, but unable to store it in the channel.  Results fail.\n");
00720             odbc_datastore_free(resultset);
00721             SQLCloseCursor(stmt);
00722             SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00723             ast_odbc_release_obj(obj);
00724             obj = NULL;
00725             pbx_builtin_setvar_helper(chan, "ODBCSTATUS", "MEMERROR");
00726             ast_autoservice_stop(chan);
00727             return -1;
00728          }
00729          odbc_store->data = resultset;
00730          ast_channel_datastore_add(chan, odbc_store);
00731       }
00732    }
00733    SQLCloseCursor(stmt);
00734    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00735    ast_odbc_release_obj(obj);
00736    obj = NULL;
00737    if (resultset && !multirow) {
00738       /* Fetch the first resultset */
00739       if (!acf_fetch(chan, "", buf, buf, len)) {
00740          buf[0] = '\0';
00741       }
00742    }
00743    if (!bogus_chan) {
00744       ast_autoservice_stop(chan);
00745    }
00746    return 0;
00747 }
00748 
00749 static int acf_escape(struct ast_channel *chan, const char *cmd, char *data, char *buf, size_t len)
00750 {
00751    char *out = buf;
00752 
00753    for (; *data && out - buf < len; data++) {
00754       if (*data == '\'') {
00755          *out = '\'';
00756          out++;
00757       }
00758       *out++ = *data;
00759    }
00760    *out = '\0';
00761 
00762    return 0;
00763 }
00764 
00765 static struct ast_custom_function escape_function = {
00766    .name = "SQL_ESC",
00767    .read = acf_escape,
00768    .write = NULL,
00769 };
00770 
00771 static int acf_fetch(struct ast_channel *chan, const char *cmd, char *data, char *buf, size_t len)
00772 {
00773    struct ast_datastore *store;
00774    struct odbc_datastore *resultset;
00775    struct odbc_datastore_row *row;
00776    store = ast_channel_datastore_find(chan, &odbc_info, data);
00777    if (!store) {
00778       pbx_builtin_setvar_helper(chan, "ODBC_FETCH_STATUS", "FAILURE");
00779       return -1;
00780    }
00781    resultset = store->data;
00782    AST_LIST_LOCK(resultset);
00783    row = AST_LIST_REMOVE_HEAD(resultset, list);
00784    AST_LIST_UNLOCK(resultset);
00785    if (!row) {
00786       /* Cleanup datastore */
00787       ast_channel_datastore_remove(chan, store);
00788       ast_datastore_free(store);
00789       pbx_builtin_setvar_helper(chan, "ODBC_FETCH_STATUS", "FAILURE");
00790       return -1;
00791    }
00792    pbx_builtin_setvar_helper(chan, "~ODBCFIELDS~", resultset->names);
00793    ast_copy_string(buf, row->data, len);
00794    ast_free(row);
00795    pbx_builtin_setvar_helper(chan, "ODBC_FETCH_STATUS", "SUCCESS");
00796    return 0;
00797 }
00798 
00799 static struct ast_custom_function fetch_function = {
00800    .name = "ODBC_FETCH",
00801    .read = acf_fetch,
00802    .write = NULL,
00803 };
00804 
00805 static char *app_odbcfinish = "ODBCFinish";
00806 
00807 static int exec_odbcfinish(struct ast_channel *chan, const char *data)
00808 {
00809    struct ast_datastore *store = ast_channel_datastore_find(chan, &odbc_info, data);
00810    if (!store) /* Already freed; no big deal. */
00811       return 0;
00812    ast_channel_datastore_remove(chan, store);
00813    ast_datastore_free(store);
00814    return 0;
00815 }
00816 
00817 static int init_acf_query(struct ast_config *cfg, char *catg, struct acf_odbc_query **query)
00818 {
00819    const char *tmp;
00820    int i;
00821 
00822    if (!cfg || !catg) {
00823       return EINVAL;
00824    }
00825 
00826    *query = ast_calloc(1, sizeof(struct acf_odbc_query));
00827    if (! (*query))
00828       return ENOMEM;
00829 
00830    if (((tmp = ast_variable_retrieve(cfg, catg, "writehandle"))) || ((tmp = ast_variable_retrieve(cfg, catg, "dsn")))) {
00831       char *tmp2 = ast_strdupa(tmp);
00832       AST_DECLARE_APP_ARGS(writeconf,
00833          AST_APP_ARG(dsn)[5];
00834       );
00835       AST_STANDARD_APP_ARGS(writeconf, tmp2);
00836       for (i = 0; i < 5; i++) {
00837          if (!ast_strlen_zero(writeconf.dsn[i]))
00838             ast_copy_string((*query)->writehandle[i], writeconf.dsn[i], sizeof((*query)->writehandle[i]));
00839       }
00840    }
00841 
00842    if ((tmp = ast_variable_retrieve(cfg, catg, "readhandle"))) {
00843       char *tmp2 = ast_strdupa(tmp);
00844       AST_DECLARE_APP_ARGS(readconf,
00845          AST_APP_ARG(dsn)[5];
00846       );
00847       AST_STANDARD_APP_ARGS(readconf, tmp2);
00848       for (i = 0; i < 5; i++) {
00849          if (!ast_strlen_zero(readconf.dsn[i]))
00850             ast_copy_string((*query)->readhandle[i], readconf.dsn[i], sizeof((*query)->readhandle[i]));
00851       }
00852    } else {
00853       /* If no separate readhandle, then use the writehandle for reading */
00854       for (i = 0; i < 5; i++) {
00855          if (!ast_strlen_zero((*query)->writehandle[i]))
00856             ast_copy_string((*query)->readhandle[i], (*query)->writehandle[i], sizeof((*query)->readhandle[i]));
00857       }
00858    }
00859 
00860    if ((tmp = ast_variable_retrieve(cfg, catg, "readsql")))
00861       ast_copy_string((*query)->sql_read, tmp, sizeof((*query)->sql_read));
00862    else if ((tmp = ast_variable_retrieve(cfg, catg, "read"))) {
00863       ast_log(LOG_WARNING, "Parameter 'read' is deprecated for category %s.  Please use 'readsql' instead.\n", catg);
00864       ast_copy_string((*query)->sql_read, tmp, sizeof((*query)->sql_read));
00865    }
00866 
00867    if (!ast_strlen_zero((*query)->sql_read) && ast_strlen_zero((*query)->readhandle[0])) {
00868       ast_free(*query);
00869       *query = NULL;
00870       ast_log(LOG_ERROR, "There is SQL, but no ODBC class to be used for reading: %s\n", catg);
00871       return EINVAL;
00872    }
00873 
00874    if ((tmp = ast_variable_retrieve(cfg, catg, "writesql")))
00875       ast_copy_string((*query)->sql_write, tmp, sizeof((*query)->sql_write));
00876    else if ((tmp = ast_variable_retrieve(cfg, catg, "write"))) {
00877       ast_log(LOG_WARNING, "Parameter 'write' is deprecated for category %s.  Please use 'writesql' instead.\n", catg);
00878       ast_copy_string((*query)->sql_write, tmp, sizeof((*query)->sql_write));
00879    }
00880 
00881    if (!ast_strlen_zero((*query)->sql_write) && ast_strlen_zero((*query)->writehandle[0])) {
00882       ast_free(*query);
00883       *query = NULL;
00884       ast_log(LOG_ERROR, "There is SQL, but no ODBC class to be used for writing: %s\n", catg);
00885       return EINVAL;
00886    }
00887 
00888    if ((tmp = ast_variable_retrieve(cfg, catg, "insertsql"))) {
00889       ast_copy_string((*query)->sql_insert, tmp, sizeof((*query)->sql_insert));
00890    }
00891 
00892    /* Allow escaping of embedded commas in fields to be turned off */
00893    ast_set_flag((*query), OPT_ESCAPECOMMAS);
00894    if ((tmp = ast_variable_retrieve(cfg, catg, "escapecommas"))) {
00895       if (ast_false(tmp))
00896          ast_clear_flag((*query), OPT_ESCAPECOMMAS);
00897    }
00898 
00899    if ((tmp = ast_variable_retrieve(cfg, catg, "mode"))) {
00900       if (strcasecmp(tmp, "multirow") == 0)
00901          ast_set_flag((*query), OPT_MULTIROW);
00902       if ((tmp = ast_variable_retrieve(cfg, catg, "rowlimit")))
00903          sscanf(tmp, "%30d", &((*query)->rowlimit));
00904    }
00905 
00906    (*query)->acf = ast_calloc(1, sizeof(struct ast_custom_function));
00907    if (! (*query)->acf) {
00908       ast_free(*query);
00909       *query = NULL;
00910       return ENOMEM;
00911    }
00912    if (ast_string_field_init((*query)->acf, 128)) {
00913       ast_free((*query)->acf);
00914       ast_free(*query);
00915       *query = NULL;
00916       return ENOMEM;
00917    }
00918 
00919    if ((tmp = ast_variable_retrieve(cfg, catg, "prefix")) && !ast_strlen_zero(tmp)) {
00920       if (asprintf((char **)&((*query)->acf->name), "%s_%s", tmp, catg) < 0) {
00921          ast_log(LOG_WARNING, "asprintf() failed: %s\n", strerror(errno));
00922       }
00923    } else {
00924       if (asprintf((char **)&((*query)->acf->name), "ODBC_%s", catg) < 0) {
00925          ast_log(LOG_WARNING, "asprintf() failed: %s\n", strerror(errno));
00926       }
00927    }
00928 
00929    if (!((*query)->acf->name)) {
00930       ast_string_field_free_memory((*query)->acf);
00931       ast_free((*query)->acf);
00932       ast_free(*query);
00933       *query = NULL;
00934       return ENOMEM;
00935    }
00936 
00937    if ((tmp = ast_variable_retrieve(cfg, catg, "syntax")) && !ast_strlen_zero(tmp)) {
00938       ast_string_field_build((*query)->acf, syntax, "%s(%s)", (*query)->acf->name, tmp);
00939    } else {
00940       ast_string_field_build((*query)->acf, syntax, "%s(<arg1>[...[,<argN>]])", (*query)->acf->name);
00941    }
00942 
00943    if (ast_strlen_zero((*query)->acf->syntax)) {
00944       ast_free((char *)(*query)->acf->name);
00945       ast_string_field_free_memory((*query)->acf);
00946       ast_free((*query)->acf);
00947       ast_free(*query);
00948       *query = NULL;
00949       return ENOMEM;
00950    }
00951 
00952    if ((tmp = ast_variable_retrieve(cfg, catg, "synopsis")) && !ast_strlen_zero(tmp)) {
00953       ast_string_field_set((*query)->acf, synopsis, tmp);
00954    } else {
00955       ast_string_field_set((*query)->acf, synopsis, "Runs the referenced query with the specified arguments");
00956    }
00957 
00958    if (ast_strlen_zero((*query)->acf->synopsis)) {
00959       ast_free((char *)(*query)->acf->name);
00960       ast_string_field_free_memory((*query)->acf);
00961       ast_free((*query)->acf);
00962       ast_free(*query);
00963       *query = NULL;
00964       return ENOMEM;
00965    }
00966 
00967    if (!ast_strlen_zero((*query)->sql_read) && !ast_strlen_zero((*query)->sql_write)) {
00968       ast_string_field_build((*query)->acf, desc,
00969                "Runs the following query, as defined in func_odbc.conf, performing\n"
00970                   "substitution of the arguments into the query as specified by ${ARG1},\n"
00971                "${ARG2}, ... ${ARGn}.  When setting the function, the values are provided\n"
00972                "either in whole as ${VALUE} or parsed as ${VAL1}, ${VAL2}, ... ${VALn}.\n"
00973                "%s"
00974                "\nRead:\n%s\n\nWrite:\n%s\n%s%s%s",
00975                ast_strlen_zero((*query)->sql_insert) ? "" :
00976                   "If the write query affects no rows, the insert query will be\n"
00977                   "performed.\n",
00978                (*query)->sql_read,
00979                (*query)->sql_write,
00980                ast_strlen_zero((*query)->sql_insert) ? "" : "Insert:\n",
00981                ast_strlen_zero((*query)->sql_insert) ? "" : (*query)->sql_insert,
00982                ast_strlen_zero((*query)->sql_insert) ? "" : "\n");
00983    } else if (!ast_strlen_zero((*query)->sql_read)) {
00984       ast_string_field_build((*query)->acf, desc,
00985                   "Runs the following query, as defined in func_odbc.conf, performing\n"
00986                      "substitution of the arguments into the query as specified by ${ARG1},\n"
00987                   "${ARG2}, ... ${ARGn}.  This function may only be read, not set.\n\nSQL:\n%s\n",
00988                   (*query)->sql_read);
00989    } else if (!ast_strlen_zero((*query)->sql_write)) {
00990       ast_string_field_build((*query)->acf, desc,  
00991                "Runs the following query, as defined in func_odbc.conf, performing\n"
00992                   "substitution of the arguments into the query as specified by ${ARG1},\n"
00993                "${ARG2}, ... ${ARGn}.  The values are provided either in whole as\n"
00994                "${VALUE} or parsed as ${VAL1}, ${VAL2}, ... ${VALn}.\n"
00995                "This function may only be set.\n%sSQL:\n%s\n%s%s%s",
00996                ast_strlen_zero((*query)->sql_insert) ? "" :
00997                   "If the write query affects no rows, the insert query will be\n"
00998                   "performed.\n",
00999                (*query)->sql_write,
01000                ast_strlen_zero((*query)->sql_insert) ? "" : "Insert:\n",
01001                ast_strlen_zero((*query)->sql_insert) ? "" : (*query)->sql_insert,
01002                ast_strlen_zero((*query)->sql_insert) ? "" : "\n");
01003    } else {
01004       ast_string_field_free_memory((*query)->acf);
01005       ast_free((char *)(*query)->acf->name);
01006       ast_free((*query)->acf);
01007       ast_free(*query);
01008       ast_log(LOG_WARNING, "Section '%s' was found, but there was no SQL to execute.  Ignoring.\n", catg);
01009       return EINVAL;
01010    }
01011 
01012    if (ast_strlen_zero((*query)->acf->desc)) {
01013       ast_string_field_free_memory((*query)->acf);
01014       ast_free((char *)(*query)->acf->name);
01015       ast_free((*query)->acf);
01016       ast_free(*query);
01017       *query = NULL;
01018       return ENOMEM;
01019    }
01020 
01021    if (ast_strlen_zero((*query)->sql_read)) {
01022       (*query)->acf->read = NULL;
01023    } else {
01024       (*query)->acf->read = acf_odbc_read;
01025    }
01026 
01027    if (ast_strlen_zero((*query)->sql_write)) {
01028       (*query)->acf->write = NULL;
01029    } else {
01030       (*query)->acf->write = acf_odbc_write;
01031    }
01032 
01033    return 0;
01034 }
01035 
01036 static int free_acf_query(struct acf_odbc_query *query)
01037 {
01038    if (query) {
01039       if (query->acf) {
01040          if (query->acf->name)
01041             ast_free((char *)query->acf->name);
01042          ast_string_field_free_memory(query->acf);
01043          ast_free(query->acf);
01044       }
01045       ast_free(query);
01046    }
01047    return 0;
01048 }
01049 
01050 static char *cli_odbc_read(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
01051 {
01052    AST_DECLARE_APP_ARGS(args,
01053       AST_APP_ARG(field)[100];
01054    );
01055    struct ast_str *sql;
01056    char *char_args, varname[10];
01057    struct acf_odbc_query *query;
01058    struct ast_channel *chan;
01059    int i;
01060 
01061    switch (cmd) {
01062    case CLI_INIT:
01063       e->command = "odbc read";
01064       e->usage =
01065          "Usage: odbc read <name> <args> [exec]\n"
01066          "       Evaluates the SQL provided in the ODBC function <name>, and\n"
01067          "       optionally executes the function.  This function is intended for\n"
01068          "       testing purposes.  Remember to quote arguments containing spaces.\n";
01069       return NULL;
01070    case CLI_GENERATE:
01071       if (a->pos == 2) {
01072          int wordlen = strlen(a->word), which = 0;
01073          /* Complete function name */
01074          AST_RWLIST_RDLOCK(&queries);
01075          AST_RWLIST_TRAVERSE(&queries, query, list) {
01076             if (!strncasecmp(query->acf->name, a->word, wordlen)) {
01077                if (++which > a->n) {
01078                   char *res = ast_strdup(query->acf->name);
01079                   AST_RWLIST_UNLOCK(&queries);
01080                   return res;
01081                }
01082             }
01083          }
01084          AST_RWLIST_UNLOCK(&queries);
01085          return NULL;
01086       } else if (a->pos == 4) {
01087          return a->n == 0 ? ast_strdup("exec") : NULL;
01088       } else {
01089          return NULL;
01090       }
01091    }
01092 
01093    if (a->argc < 4 || a->argc > 5) {
01094       return CLI_SHOWUSAGE;
01095    }
01096 
01097    sql = ast_str_thread_get(&sql_buf, 16);
01098    if (!sql) {
01099       return CLI_FAILURE;
01100    }
01101 
01102    AST_RWLIST_RDLOCK(&queries);
01103    AST_RWLIST_TRAVERSE(&queries, query, list) {
01104       if (!strcmp(query->acf->name, a->argv[2])) {
01105          break;
01106       }
01107    }
01108 
01109    if (!query) {
01110       ast_cli(a->fd, "No such query '%s'\n", a->argv[2]);
01111       AST_RWLIST_UNLOCK(&queries);
01112       return CLI_SHOWUSAGE;
01113    }
01114 
01115    if (ast_strlen_zero(query->sql_read)) {
01116       ast_cli(a->fd, "The function %s has no writesql parameter.\n", a->argv[2]);
01117       AST_RWLIST_UNLOCK(&queries);
01118       return CLI_SUCCESS;
01119    }
01120 
01121    ast_str_make_space(&sql, strlen(query->sql_read) * 2 + 300);
01122 
01123    /* Evaluate function */
01124    char_args = ast_strdupa(a->argv[3]);
01125 
01126    chan = ast_dummy_channel_alloc();
01127 
01128    AST_STANDARD_APP_ARGS(args, char_args);
01129    for (i = 0; i < args.argc; i++) {
01130       snprintf(varname, sizeof(varname), "ARG%d", i + 1);
01131       pbx_builtin_pushvar_helper(chan, varname, args.field[i]);
01132    }
01133 
01134    ast_str_substitute_variables(&sql, 0, chan, query->sql_read);
01135    chan = ast_channel_release(chan);
01136 
01137    if (a->argc == 5 && !strcmp(a->argv[4], "exec")) {
01138       /* Execute the query */
01139       struct odbc_obj *obj = NULL;
01140       int dsn, executed = 0;
01141       SQLHSTMT stmt;
01142       int rows = 0, res, x;
01143       SQLSMALLINT colcount = 0, collength;
01144       SQLLEN indicator;
01145       struct ast_str *coldata = ast_str_thread_get(&coldata_buf, 16);
01146       char colname[256];
01147       SQLULEN maxcol;
01148 
01149       if (!coldata) {
01150          AST_RWLIST_UNLOCK(&queries);
01151          return CLI_SUCCESS;
01152       }
01153 
01154       for (dsn = 0; dsn < 5; dsn++) {
01155          if (ast_strlen_zero(query->readhandle[dsn])) {
01156             continue;
01157          }
01158          ast_debug(1, "Found handle %s\n", query->readhandle[dsn]);
01159          if (!(obj = ast_odbc_request_obj(query->readhandle[dsn], 0))) {
01160             continue;
01161          }
01162 
01163          ast_debug(1, "Got obj\n");
01164          if (!(stmt = ast_odbc_direct_execute(obj, generic_execute, ast_str_buffer(sql)))) {
01165             ast_odbc_release_obj(obj);
01166             obj = NULL;
01167             continue;
01168          }
01169 
01170          executed = 1;
01171 
01172          res = SQLNumResultCols(stmt, &colcount);
01173          if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
01174             ast_cli(a->fd, "SQL Column Count error!\n[%s]\n\n", ast_str_buffer(sql));
01175             SQLCloseCursor(stmt);
01176             SQLFreeHandle (SQL_HANDLE_STMT, stmt);
01177             ast_odbc_release_obj(obj);
01178             obj = NULL;
01179             AST_RWLIST_UNLOCK(&queries);
01180             return CLI_SUCCESS;
01181          }
01182 
01183          res = SQLFetch(stmt);
01184          if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
01185             SQLCloseCursor(stmt);
01186             SQLFreeHandle(SQL_HANDLE_STMT, stmt);
01187             ast_odbc_release_obj(obj);
01188             obj = NULL;
01189             if (res == SQL_NO_DATA) {
01190                ast_cli(a->fd, "Returned %d rows.  Query executed on handle %d:%s [%s]\n", rows, dsn, query->readhandle[dsn], ast_str_buffer(sql));
01191                break;
01192             } else {
01193                ast_cli(a->fd, "Error %d in FETCH [%s]\n", res, ast_str_buffer(sql));
01194             }
01195             AST_RWLIST_UNLOCK(&queries);
01196             return CLI_SUCCESS;
01197          }
01198          for (;;) {
01199             for (x = 0; x < colcount; x++) {
01200                res = SQLDescribeCol(stmt, x + 1, (unsigned char *)colname, sizeof(colname), &collength, NULL, &maxcol, NULL, NULL);
01201                if (((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) || collength == 0) {
01202                   snprintf(colname, sizeof(colname), "field%d", x);
01203                }
01204 
01205                res = ast_odbc_ast_str_SQLGetData(&coldata, maxcol, stmt, x + 1, SQL_CHAR, &indicator);
01206                if (indicator == SQL_NULL_DATA) {
01207                   ast_str_set(&coldata, 0, "(nil)");
01208                   res = SQL_SUCCESS;
01209                }
01210 
01211                if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
01212                   ast_cli(a->fd, "SQL Get Data error %d!\n[%s]\n\n", res, ast_str_buffer(sql));
01213                   SQLCloseCursor(stmt);
01214                   SQLFreeHandle(SQL_HANDLE_STMT, stmt);
01215                   ast_odbc_release_obj(obj);
01216                   obj = NULL;
01217                   AST_RWLIST_UNLOCK(&queries);
01218                   return CLI_SUCCESS;
01219                }
01220 
01221                ast_cli(a->fd, "%-20.20s  %s\n", colname, ast_str_buffer(coldata));
01222             }
01223             rows++;
01224 
01225             /* Get next row */
01226             res = SQLFetch(stmt);
01227             if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
01228                break;
01229             }
01230             ast_cli(a->fd, "%-20.20s  %s\n", "----------", "----------");
01231          }
01232          SQLCloseCursor(stmt);
01233          SQLFreeHandle(SQL_HANDLE_STMT, stmt);
01234          ast_odbc_release_obj(obj);
01235          obj = NULL;
01236          ast_cli(a->fd, "Returned %d row%s.  Query executed on handle %d [%s]\n", rows, rows == 1 ? "" : "s", dsn, query->readhandle[dsn]);
01237          break;
01238       }
01239       if (obj) {
01240          ast_odbc_release_obj(obj);
01241          obj = NULL;
01242       }
01243 
01244       if (!executed) {
01245          ast_cli(a->fd, "Failed to execute query. [%s]\n", ast_str_buffer(sql));
01246       }
01247    } else { /* No execution, just print out the resulting SQL */
01248       ast_cli(a->fd, "%s\n", ast_str_buffer(sql));
01249    }
01250    AST_RWLIST_UNLOCK(&queries);
01251    return CLI_SUCCESS;
01252 }
01253 
01254 static char *cli_odbc_write(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
01255 {
01256    AST_DECLARE_APP_ARGS(values,
01257       AST_APP_ARG(field)[100];
01258    );
01259    AST_DECLARE_APP_ARGS(args,
01260       AST_APP_ARG(field)[100];
01261    );
01262    struct ast_str *sql;
01263    char *char_args, *char_values, varname[10];
01264    struct acf_odbc_query *query;
01265    struct ast_channel *chan;
01266    int i;
01267 
01268    switch (cmd) {
01269    case CLI_INIT:
01270       e->command = "odbc write";
01271       e->usage =
01272          "Usage: odbc write <name> <args> <value> [exec]\n"
01273          "       Evaluates the SQL provided in the ODBC function <name>, and\n"
01274          "       optionally executes the function.  This function is intended for\n"
01275          "       testing purposes.  Remember to quote arguments containing spaces.\n";
01276       return NULL;
01277    case CLI_GENERATE:
01278       if (a->pos == 2) {
01279          int wordlen = strlen(a->word), which = 0;
01280          /* Complete function name */
01281          AST_RWLIST_RDLOCK(&queries);
01282          AST_RWLIST_TRAVERSE(&queries, query, list) {
01283             if (!strncasecmp(query->acf->name, a->word, wordlen)) {
01284                if (++which > a->n) {
01285                   char *res = ast_strdup(query->acf->name);
01286                   AST_RWLIST_UNLOCK(&queries);
01287                   return res;
01288                }
01289             }
01290          }
01291          AST_RWLIST_UNLOCK(&queries);
01292          return NULL;
01293       } else if (a->pos == 5) {
01294          return a->n == 0 ? ast_strdup("exec") : NULL;
01295       } else {
01296          return NULL;
01297       }
01298    }
01299 
01300    if (a->argc < 5 || a->argc > 6) {
01301       return CLI_SHOWUSAGE;
01302    }
01303 
01304    sql = ast_str_thread_get(&sql_buf, 16);
01305    if (!sql) {
01306       return CLI_FAILURE;
01307    }
01308 
01309    AST_RWLIST_RDLOCK(&queries);
01310    AST_RWLIST_TRAVERSE(&queries, query, list) {
01311       if (!strcmp(query->acf->name, a->argv[2])) {
01312          break;
01313       }
01314    }
01315 
01316    if (!query) {
01317       ast_cli(a->fd, "No such query '%s'\n", a->argv[2]);
01318       AST_RWLIST_UNLOCK(&queries);
01319       return CLI_SHOWUSAGE;
01320    }
01321 
01322    if (ast_strlen_zero(query->sql_write)) {
01323       ast_cli(a->fd, "The function %s has no writesql parameter.\n", a->argv[2]);
01324       AST_RWLIST_UNLOCK(&queries);
01325       return CLI_SUCCESS;
01326    }
01327 
01328    ast_str_make_space(&sql, strlen(query->sql_write) * 2 + 300);
01329 
01330    /* Evaluate function */
01331    char_args = ast_strdupa(a->argv[3]);
01332    char_values = ast_strdupa(a->argv[4]);
01333 
01334    chan = ast_dummy_channel_alloc();
01335 
01336    AST_STANDARD_APP_ARGS(args, char_args);
01337    for (i = 0; i < args.argc; i++) {
01338       snprintf(varname, sizeof(varname), "ARG%d", i + 1);
01339       pbx_builtin_pushvar_helper(chan, varname, args.field[i]);
01340    }
01341 
01342    /* Parse values, just like arguments */
01343    AST_STANDARD_APP_ARGS(values, char_values);
01344    for (i = 0; i < values.argc; i++) {
01345       snprintf(varname, sizeof(varname), "VAL%d", i + 1);
01346       pbx_builtin_pushvar_helper(chan, varname, values.field[i]);
01347    }
01348 
01349    /* Additionally set the value as a whole (but push an empty string if value is NULL) */
01350    pbx_builtin_pushvar_helper(chan, "VALUE", S_OR(a->argv[4], ""));
01351    ast_str_substitute_variables(&sql, 0, chan, query->sql_write);
01352    ast_debug(1, "SQL is %s\n", ast_str_buffer(sql));
01353    chan = ast_channel_release(chan);
01354 
01355    if (a->argc == 6 && !strcmp(a->argv[5], "exec")) {
01356       /* Execute the query */
01357       struct odbc_obj *obj = NULL;
01358       int dsn, executed = 0;
01359       SQLHSTMT stmt;
01360       SQLLEN rows = -1;
01361 
01362       for (dsn = 0; dsn < 5; dsn++) {
01363          if (ast_strlen_zero(query->writehandle[dsn])) {
01364             continue;
01365          }
01366          if (!(obj = ast_odbc_request_obj(query->writehandle[dsn], 0))) {
01367             continue;
01368          }
01369          if (!(stmt = ast_odbc_direct_execute(obj, generic_execute, ast_str_buffer(sql)))) {
01370             ast_odbc_release_obj(obj);
01371             obj = NULL;
01372             continue;
01373          }
01374 
01375          SQLRowCount(stmt, &rows);
01376          SQLCloseCursor(stmt);
01377          SQLFreeHandle(SQL_HANDLE_STMT, stmt);
01378          ast_odbc_release_obj(obj);
01379          obj = NULL;
01380          ast_cli(a->fd, "Affected %d rows.  Query executed on handle %d [%s]\n", (int)rows, dsn, query->writehandle[dsn]);
01381          executed = 1;
01382          break;
01383       }
01384 
01385       if (!executed) {
01386          ast_cli(a->fd, "Failed to execute query.\n");
01387       }
01388    } else { /* No execution, just print out the resulting SQL */
01389       ast_cli(a->fd, "%s\n", ast_str_buffer(sql));
01390    }
01391    AST_RWLIST_UNLOCK(&queries);
01392    return CLI_SUCCESS;
01393 }
01394 
01395 static struct ast_cli_entry cli_func_odbc[] = {
01396    AST_CLI_DEFINE(cli_odbc_write, "Test setting a func_odbc function"),
01397    AST_CLI_DEFINE(cli_odbc_read, "Test reading a func_odbc function"),
01398 };
01399 
01400 static int load_module(void)
01401 {
01402    int res = 0;
01403    struct ast_config *cfg;
01404    char *catg;
01405    struct ast_flags config_flags = { 0 };
01406 
01407    res |= ast_custom_function_register(&fetch_function);
01408    res |= ast_register_application_xml(app_odbcfinish, exec_odbcfinish);
01409    AST_RWLIST_WRLOCK(&queries);
01410 
01411    cfg = ast_config_load(config, config_flags);
01412    if (!cfg || cfg == CONFIG_STATUS_FILEINVALID) {
01413       ast_log(LOG_NOTICE, "Unable to load config for func_odbc: %s\n", config);
01414       AST_RWLIST_UNLOCK(&queries);
01415       return AST_MODULE_LOAD_DECLINE;
01416    }
01417 
01418    for (catg = ast_category_browse(cfg, NULL);
01419         catg;
01420         catg = ast_category_browse(cfg, catg)) {
01421       struct acf_odbc_query *query = NULL;
01422       int err;
01423 
01424       if ((err = init_acf_query(cfg, catg, &query))) {
01425          if (err == ENOMEM)
01426             ast_log(LOG_ERROR, "Out of memory\n");
01427          else if (err == EINVAL)
01428             ast_log(LOG_ERROR, "Invalid parameters for category %s\n", catg);
01429          else
01430             ast_log(LOG_ERROR, "%s (%d)\n", strerror(err), err);
01431       } else {
01432          AST_RWLIST_INSERT_HEAD(&queries, query, list);
01433          ast_custom_function_register(query->acf);
01434       }
01435    }
01436 
01437    ast_config_destroy(cfg);
01438    res |= ast_custom_function_register(&escape_function);
01439    ast_cli_register_multiple(cli_func_odbc, ARRAY_LEN(cli_func_odbc));
01440 
01441    AST_RWLIST_UNLOCK(&queries);
01442    return res;
01443 }
01444 
01445 static int unload_module(void)
01446 {
01447    struct acf_odbc_query *query;
01448    int res = 0;
01449 
01450    AST_RWLIST_WRLOCK(&queries);
01451    while (!AST_RWLIST_EMPTY(&queries)) {
01452       query = AST_RWLIST_REMOVE_HEAD(&queries, list);
01453       ast_custom_function_unregister(query->acf);
01454       free_acf_query(query);
01455    }
01456 
01457    res |= ast_custom_function_unregister(&escape_function);
01458    res |= ast_custom_function_unregister(&fetch_function);
01459    res |= ast_unregister_application(app_odbcfinish);
01460    ast_cli_unregister_multiple(cli_func_odbc, ARRAY_LEN(cli_func_odbc));
01461 
01462    /* Allow any threads waiting for this lock to pass (avoids a race) */
01463    AST_RWLIST_UNLOCK(&queries);
01464    usleep(1);
01465    AST_RWLIST_WRLOCK(&queries);
01466 
01467    AST_RWLIST_UNLOCK(&queries);
01468    return 0;
01469 }
01470 
01471 static int reload(void)
01472 {
01473    int res = 0;
01474    struct ast_config *cfg;
01475    struct acf_odbc_query *oldquery;
01476    char *catg;
01477    struct ast_flags config_flags = { CONFIG_FLAG_FILEUNCHANGED };
01478 
01479    cfg = ast_config_load(config, config_flags);
01480    if (cfg == CONFIG_STATUS_FILEUNCHANGED || cfg == CONFIG_STATUS_FILEINVALID)
01481       return 0;
01482 
01483    AST_RWLIST_WRLOCK(&queries);
01484 
01485    while (!AST_RWLIST_EMPTY(&queries)) {
01486       oldquery = AST_RWLIST_REMOVE_HEAD(&queries, list);
01487       ast_custom_function_unregister(oldquery->acf);
01488       free_acf_query(oldquery);
01489    }
01490 
01491    if (!cfg) {
01492       ast_log(LOG_WARNING, "Unable to load config for func_odbc: %s\n", config);
01493       goto reload_out;
01494    }
01495 
01496    for (catg = ast_category_browse(cfg, NULL);
01497         catg;
01498         catg = ast_category_browse(cfg, catg)) {
01499       struct acf_odbc_query *query = NULL;
01500 
01501       if (init_acf_query(cfg, catg, &query)) {
01502          ast_log(LOG_ERROR, "Cannot initialize query %s\n", catg);
01503       } else {
01504          AST_RWLIST_INSERT_HEAD(&queries, query, list);
01505          ast_custom_function_register(query->acf);
01506       }
01507    }
01508 
01509    ast_config_destroy(cfg);
01510 reload_out:
01511    AST_RWLIST_UNLOCK(&queries);
01512    return res;
01513 }
01514 
01515 /* XXX need to revise usecount - set if query_lock is set */
01516 
01517 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_DEFAULT, "ODBC lookups",
01518       .load = load_module,
01519       .unload = unload_module,
01520       .reload = reload,
01521           );
01522 

Generated on Mon Jun 27 16:50:54 2011 for Asterisk - The Open Source Telephony Project by  doxygen 1.4.7