Mon Oct 8 12:39:02 2012

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

Generated on Mon Oct 8 12:39:02 2012 for Asterisk - The Open Source Telephony Project by  doxygen 1.4.7