Wed Apr 6 11:29:45 2011

Asterisk developer's documentation


func_odbc.c

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

Generated on Wed Apr 6 11:29:45 2011 for Asterisk - The Open Source Telephony Project by  doxygen 1.4.7