Thu Apr 28 2011 17:15:22

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