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

Generated on Thu Apr 16 06:27:36 2015 for Asterisk - The Open Source Telephony Project by  doxygen 1.5.6