funcs/func_odbc.c

   1 /*
   2  * Asterisk -- An open source telephony toolkit.
   3  *
   4  * Copyright (c) 2005, 2006 Tilghman Lesher
   5  *
   6  * Tilghman Lesher <func_odbc__200508@the-tilghman.com>
   7  *
   8  * See http://www.asterisk.org for more information about
   9  * the Asterisk project. Please do not directly contact
  10  * any of the maintainers of this project for assistance;
  11  * the project provides a web site, mailing lists and IRC
  12  * channels for your use.
  13  *
  14  * This program is free software, distributed under the terms of
  15  * the GNU General Public License Version 2. See the LICENSE file
  16  * at the top of the source tree.
  17  */
  18
  19 /*!
  20  * \file
  21  *
  22  * \brief ODBC lookups
  23  *
  24  * \author Tilghman Lesher <func_odbc__200508@the-tilghman.com>
  25  *
  26  * \ingroup functions
  27  */
  28
  29 /*** MODULEINFO
  30         <depend>unixodbc</depend>
  31         <depend>ltdl</depend>
  32         <depend>res_odbc</depend>
  33  ***/
  34
  35 #include "asterisk.h"
  36
  37 ASTERISK_FILE_VERSION(__FILE__, "$Revision$")
  38
  39 #include <sys/types.h>
  40 #include <stdio.h>
  41 #include <stdlib.h>
  42 #include <unistd.h>
  43 #include <string.h>
  44 #include <errno.h>
  45
  46 #include "asterisk/module.h"
  47 #include "asterisk/file.h"
  48 #include "asterisk/logger.h"
  49 #include "asterisk/options.h"
  50 #include "asterisk/channel.h"
  51 #include "asterisk/pbx.h"
  52 #include "asterisk/module.h"
  53 #include "asterisk/config.h"
  54 #include "asterisk/res_odbc.h"
  55 #include "asterisk/app.h"
  56
  57 static char *config = "func_odbc.conf";
  58
  59 enum {
  60         OPT_ESCAPECOMMAS =      (1 << 0),
  61         OPT_MULTIROW     =      (1 << 1),
  62 } odbc_option_flags;
  63
  64 struct acf_odbc_query {
  65         AST_LIST_ENTRY(acf_odbc_query) list;
  66         char readhandle[5][30];
  67         char writehandle[5][30];
  68         char sql_read[2048];
  69         char sql_write[2048];
  70         unsigned int flags;
  71         int rowlimit;
  72         struct ast_custom_function *acf;
  73 };
  74
  75 static void odbc_datastore_free(void *data);
  76
  77 struct ast_datastore_info odbc_info = {
  78         .type = "FUNC_ODBC",
  79         .destroy = odbc_datastore_free,
  80 };
  81
  82 /* For storing each result row */
  83 struct odbc_datastore_row {
  84         AST_LIST_ENTRY(odbc_datastore_row) list;
  85         char data[0];
  86 };
  87
  88 /* For storing each result set */
  89 struct odbc_datastore {
  90         AST_LIST_HEAD(, odbc_datastore_row);
  91         char names[0];
  92 };
  93
  94 AST_LIST_HEAD_STATIC(queries, acf_odbc_query);
  95
  96 static int resultcount = 0;
  97
  98 static void odbc_datastore_free(void *data)
  99 {
 100         struct odbc_datastore *result = data;
 101         struct odbc_datastore_row *row;
 102         AST_LIST_LOCK(result);
 103         while ((row = AST_LIST_REMOVE_HEAD(result, list))) {
 104                 ast_free(row);
 105         }
 106         AST_LIST_UNLOCK(result);
 107         AST_LIST_HEAD_DESTROY(result);
 108         ast_free(result);
 109 }
 110
 111 static SQLHSTMT generic_prepare(struct odbc_obj *obj, void *data)
 112 {
 113         int res;
 114         char *sql = data;
 115         SQLHSTMT stmt;
 116
 117         res = SQLAllocHandle (SQL_HANDLE_STMT, obj->con, &stmt);
 118         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 119                 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
 120                 return NULL;
 121         }
 122
 123         res = SQLPrepare(stmt, (unsigned char *)sql, SQL_NTS);
 124         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 125                 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", sql);
 126                 SQLCloseCursor(stmt);
 127                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
 128                 return NULL;
 129         }
 130
 131         return stmt;
 132 }
 133
 134 /*
 135  * Master control routine
 136  */
 137 static int acf_odbc_write(struct ast_channel *chan, const char *cmd, char *s, const char *value)
 138 {
 139         struct odbc_obj *obj = NULL;
 140         struct acf_odbc_query *query;
 141         char *t, buf[2048]="", varname[15];
 142         int i, dsn, bogus_chan = 0;
 143         AST_DECLARE_APP_ARGS(values,
 144                 AST_APP_ARG(field)[100];
 145         );
 146         AST_DECLARE_APP_ARGS(args,
 147                 AST_APP_ARG(field)[100];
 148         );
 149         SQLHSTMT stmt = NULL;
 150         SQLLEN rows=0;
 151
 152         AST_LIST_LOCK(&queries);
 153         AST_LIST_TRAVERSE(&queries, query, list) {
 154                 if (!strcmp(query->acf->name, cmd)) {
 155                         break;
 156                 }
 157         }
 158
 159         if (!query) {
 160                 ast_log(LOG_ERROR, "No such function '%s'\n", cmd);
 161                 AST_LIST_UNLOCK(&queries);
 162                 return -1;
 163         }
 164
 165         if (!chan) {
 166                 if ((chan = ast_channel_alloc(0, 0, "", "", "", "", "", 0, "Bogus/func_odbc")))
 167                         bogus_chan = 1;
 168         }
 169
 170         if (chan)
 171                 ast_autoservice_start(chan);
 172
 173         /* Parse our arguments */
 174         t = value ? ast_strdupa(value) : "";
 175
 176         if (!s || !t) {
 177                 ast_log(LOG_ERROR, "Out of memory\n");
 178                 AST_LIST_UNLOCK(&queries);
 179                 if (chan)
 180                         ast_autoservice_stop(chan);
 181                 if (bogus_chan)
 182                         ast_channel_free(chan);
 183                 return -1;
 184         }
 185
 186         AST_STANDARD_APP_ARGS(args, s);
 187         for (i = 0; i < args.argc; i++) {
 188                 snprintf(varname, sizeof(varname), "ARG%d", i + 1);
 189                 pbx_builtin_pushvar_helper(chan, varname, args.field[i]);
 190         }
 191
 192         /* Parse values, just like arguments */
 193         AST_STANDARD_APP_ARGS(values, t);
 194         for (i = 0; i < values.argc; i++) {
 195                 snprintf(varname, sizeof(varname), "VAL%d", i + 1);
 196                 pbx_builtin_pushvar_helper(chan, varname, values.field[i]);
 197         }
 198
 199         /* Additionally set the value as a whole (but push an empty string if value is NULL) */
 200         pbx_builtin_pushvar_helper(chan, "VALUE", value ? value : "");
 201
 202         pbx_substitute_variables_helper(chan, query->sql_write, buf, sizeof(buf) - 1);
 203
 204         /* Restore prior values */
 205         for (i = 0; i < args.argc; i++) {
 206                 snprintf(varname, sizeof(varname), "ARG%d", i + 1);
 207                 pbx_builtin_setvar_helper(chan, varname, NULL);
 208         }
 209
 210         for (i = 0; i < values.argc; i++) {
 211                 snprintf(varname, sizeof(varname), "VAL%d", i + 1);
 212                 pbx_builtin_setvar_helper(chan, varname, NULL);
 213         }
 214         pbx_builtin_setvar_helper(chan, "VALUE", NULL);
 215
 216         AST_LIST_UNLOCK(&queries);
 217
 218         for (dsn = 0; dsn < 5; dsn++) {
 219                 if (!ast_strlen_zero(query->writehandle[dsn])) {
 220                         obj = ast_odbc_request_obj(query->writehandle[dsn], 0);
 221                         if (obj)
 222                                 stmt = ast_odbc_prepare_and_execute(obj, generic_prepare, buf);
 223                 }
 224                 if (stmt)
 225                         break;
 226         }
 227
 228         if (stmt) {
 229                 /* Rows affected */
 230                 SQLRowCount(stmt, &rows);
 231         }
 232
 233         /* Output the affected rows, for all cases.  In the event of failure, we
 234          * flag this as -1 rows.  Note that this is different from 0 affected rows
 235          * which would be the case if we succeeded in our query, but the values did
 236          * not change. */
 237         snprintf(varname, sizeof(varname), "%d", (int)rows);
 238         pbx_builtin_setvar_helper(chan, "ODBCROWS", varname);
 239
 240         if (stmt) {
 241                 SQLCloseCursor(stmt);
 242                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 243         }
 244         if (obj)
 245                 ast_odbc_release_obj(obj);
 246
 247         if (chan)
 248                 ast_autoservice_stop(chan);
 249         if (bogus_chan)
 250                 ast_channel_free(chan);
 251
 252         return 0;
 253 }
 254
 255 static int acf_odbc_read(struct ast_channel *chan, const char *cmd, char *s, char *buf, size_t len)
 256 {
 257         struct odbc_obj *obj = NULL;
 258         struct acf_odbc_query *query;
 259         char sql[2048] = "", varname[15], colnames[2048] = "", rowcount[12] = "-1";
 260         int res, x, y, buflen = 0, escapecommas, rowlimit = 1, dsn, bogus_chan = 0;
 261         AST_DECLARE_APP_ARGS(args,
 262                 AST_APP_ARG(field)[100];
 263         );
 264         SQLHSTMT stmt = NULL;
 265         SQLSMALLINT colcount=0;
 266         SQLLEN indicator;
 267         SQLSMALLINT collength;
 268         struct odbc_datastore *resultset = NULL;
 269         struct odbc_datastore_row *row = NULL;
 270
 271         AST_LIST_LOCK(&queries);
 272         AST_LIST_TRAVERSE(&queries, query, list) {
 273                 if (!strcmp(query->acf->name, cmd)) {
 274                         break;
 275                 }
 276         }
 277
 278         if (!query) {
 279                 ast_log(LOG_ERROR, "No such function '%s'\n", cmd);
 280                 AST_LIST_UNLOCK(&queries);
 281                 pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
 282                 return -1;
 283         }
 284
 285         if (!chan) {
 286                 if ((chan = ast_channel_alloc(0, 0, "", "", "", "", "", 0, "Bogus/func_odbc")))
 287                         bogus_chan = 1;
 288         }
 289
 290         if (chan)
 291                 ast_autoservice_start(chan);
 292
 293         AST_STANDARD_APP_ARGS(args, s);
 294         for (x = 0; x < args.argc; x++) {
 295                 snprintf(varname, sizeof(varname), "ARG%d", x + 1);
 296                 pbx_builtin_pushvar_helper(chan, varname, args.field[x]);
 297         }
 298
 299         pbx_substitute_variables_helper(chan, query->sql_read, sql, sizeof(sql) - 1);
 300
 301         /* Restore prior values */
 302         for (x = 0; x < args.argc; x++) {
 303                 snprintf(varname, sizeof(varname), "ARG%d", x + 1);
 304                 pbx_builtin_setvar_helper(chan, varname, NULL);
 305         }
 306
 307         /* Save these flags, so we can release the lock */
 308         escapecommas = ast_test_flag(query, OPT_ESCAPECOMMAS);
 309         if (ast_test_flag(query, OPT_MULTIROW)) {
 310                 resultset = ast_calloc(1, sizeof(*resultset));
 311                 AST_LIST_HEAD_INIT(resultset);
 312                 if (query->rowlimit)
 313                         rowlimit = query->rowlimit;
 314                 else
 315                         rowlimit = INT_MAX;
 316         }
 317         AST_LIST_UNLOCK(&queries);
 318
 319         for (dsn = 0; dsn < 5; dsn++) {
 320                 if (!ast_strlen_zero(query->writehandle[dsn])) {
 321                         obj = ast_odbc_request_obj(query->writehandle[dsn], 0);
 322                         if (obj)
 323                                 stmt = ast_odbc_prepare_and_execute(obj, generic_prepare, sql);
 324                 }
 325                 if (stmt)
 326                         break;
 327         }
 328
 329         if (!stmt) {
 330                 ast_log(LOG_ERROR, "Unable to execute query [%s]\n", sql);
 331                 if (obj)
 332                         ast_odbc_release_obj(obj);
 333                 pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
 334                 if (chan)
 335                         ast_autoservice_stop(chan);
 336                 if (bogus_chan)
 337                         ast_channel_free(chan);
 338                 return -1;
 339         }
 340
 341         res = SQLNumResultCols(stmt, &colcount);
 342         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 343                 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
 344                 SQLCloseCursor(stmt);
 345                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
 346                 ast_odbc_release_obj(obj);
 347                 pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
 348                 if (chan)
 349                         ast_autoservice_stop(chan);
 350                 if (bogus_chan)
 351                         ast_channel_free(chan);
 352                 return -1;
 353         }
 354
 355         res = SQLFetch(stmt);
 356         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 357                 int res1 = -1;
 358                 if (res == SQL_NO_DATA) {
 359                         ast_verb(4, "Found no rows [%s]\n", sql);
 360                         res1 = 0;
 361                         ast_copy_string(rowcount, "0", sizeof(rowcount));
 362                 } else {
 363                         ast_log(LOG_WARNING, "Error %d in FETCH [%s]\n", res, sql);
 364                 }
 365                 SQLCloseCursor(stmt);
 366                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 367                 ast_odbc_release_obj(obj);
 368                 pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
 369                 if (chan)
 370                         ast_autoservice_stop(chan);
 371                 if (bogus_chan)
 372                         ast_channel_free(chan);
 373                 return res1;
 374         }
 375
 376         for (y = 0; y < rowlimit; y++) {
 377                 *buf = '\0';
 378                 for (x = 0; x < colcount; x++) {
 379                         int i;
 380                         char coldata[256];
 381
 382                         if (y == 0) {
 383                                 char colname[256];
 384                                 int namelen;
 385
 386                                 res = SQLDescribeCol(stmt, x + 1, (unsigned char *)colname, sizeof(colname), &collength, NULL, NULL, NULL, NULL);
 387                                 if (((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) || collength == 0) {
 388                                         snprintf(colname, sizeof(colname), "field%d", x);
 389                                 }
 390
 391                                 if (!ast_strlen_zero(colnames))
 392                                         strncat(colnames, ",", sizeof(colnames) - 1);
 393                                 namelen = strlen(colnames);
 394
 395                                 /* Copy data, encoding '\' and ',' for the argument parser */
 396                                 for (i = 0; i < sizeof(colname); i++) {
 397                                         if (escapecommas && (colname[i] == '\\' || colname[i] == ',')) {
 398                                                 colnames[namelen++] = '\\';
 399                                         }
 400                                         colnames[namelen++] = colname[i];
 401
 402                                         if (namelen >= sizeof(colnames) - 2) {
 403                                                 colnames[namelen >= sizeof(colnames) ? sizeof(colnames) - 1 : namelen] = '\0';
 404                                                 break;
 405                                         }
 406
 407                                         if (colname[i] == '\0')
 408                                                 break;
 409                                 }
 410
 411                                 if (resultset) {
 412                                         void *tmp = ast_realloc(resultset, sizeof(*resultset) + strlen(colnames) + 1);
 413                                         if (!tmp) {
 414                                                 ast_log(LOG_ERROR, "No space for a new resultset?\n");
 415                                                 ast_free(resultset);
 416                                                 SQLCloseCursor(stmt);
 417                                                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 418                                                 ast_odbc_release_obj(obj);
 419                                                 pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
 420                                                 if (chan)
 421                                                         ast_autoservice_stop(chan);
 422                                                 if (bogus_chan)
 423                                                         ast_channel_free(chan);
 424                                                 return -1;
 425                                         }
 426                                         resultset = tmp;
 427                                         strcpy((char *)resultset + sizeof(*resultset), colnames);
 428                                 }
 429                         }
 430
 431                         buflen = strlen(buf);
 432                         res = SQLGetData(stmt, x + 1, SQL_CHAR, coldata, sizeof(coldata), &indicator);
 433                         if (indicator == SQL_NULL_DATA) {
 434                                 coldata[0] = '\0';
 435                                 res = SQL_SUCCESS;
 436                         }
 437
 438                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 439                                 ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
 440                                 y = -1;
 441                                 goto end_acf_read;
 442                         }
 443
 444                         /* Copy data, encoding '\' and ',' for the argument parser */
 445                         for (i = 0; i < sizeof(coldata); i++) {
 446                                 if (escapecommas && (coldata[i] == '\\' || coldata[i] == ',')) {
 447                                         buf[buflen++] = '\\';
 448                                 }
 449                                 buf[buflen++] = coldata[i];
 450
 451                                 if (buflen >= len - 2)
 452                                         break;
 453
 454                                 if (coldata[i] == '\0')
 455                                         break;
 456                         }
 457
 458                         buf[buflen - 1] = ',';
 459                         buf[buflen] = '\0';
 460                 }
 461                 /* Trim trailing comma */
 462                 buf[buflen - 1] = '\0';
 463
 464                 if (resultset) {
 465                         row = ast_calloc(1, sizeof(*row) + buflen);
 466                         if (!row) {
 467                                 ast_log(LOG_ERROR, "Unable to allocate space for more rows in this resultset.\n");
 468                                 goto end_acf_read;
 469                         }
 470                         strcpy((char *)row + sizeof(*row), buf);
 471                         AST_LIST_INSERT_TAIL(resultset, row, list);
 472
 473                         /* Get next row */
 474                         res = SQLFetch(stmt);
 475                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
 476                                 if (res != SQL_NO_DATA)
 477                                         ast_log(LOG_WARNING, "Error %d in FETCH [%s]\n", res, sql);
 478                                 y++;
 479                                 break;
 480                         }
 481                 }
 482         }
 483
 484 end_acf_read:
 485         snprintf(rowcount, sizeof(rowcount), "%d", y);
 486         pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
 487         pbx_builtin_setvar_helper(chan, "~ODBCFIELDS~", colnames);
 488         if (resultset) {
 489                 int uid;
 490                 struct ast_datastore *odbc_store;
 491                 uid = ast_atomic_fetchadd_int(&resultcount, +1) + 1;
 492                 snprintf(buf, len, "%d", uid);
 493                 odbc_store = ast_channel_datastore_alloc(&odbc_info, buf);
 494                 if (!odbc_store) {
 495                         ast_log(LOG_ERROR, "Rows retrieved, but unable to store it in the channel.  Results fail.\n");
 496                         odbc_datastore_free(resultset);
 497                         SQLCloseCursor(stmt);
 498                         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 499                         ast_odbc_release_obj(obj);
 500                         if (chan)
 501                                 ast_autoservice_stop(chan);
 502                         if (bogus_chan)
 503                                 ast_channel_free(chan);
 504                         return -1;
 505                 }
 506                 odbc_store->data = resultset;
 507                 ast_channel_datastore_add(chan, odbc_store);
 508         }
 509         SQLCloseCursor(stmt);
 510         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
 511         ast_odbc_release_obj(obj);
 512         if (chan)
 513                 ast_autoservice_stop(chan);
 514         if (bogus_chan)
 515                 ast_channel_free(chan);
 516         return 0;
 517 }
 518
 519 static int acf_escape(struct ast_channel *chan, const char *cmd, char *data, char *buf, size_t len)
 520 {
 521         char *out = buf;
 522
 523         for (; *data && out - buf < len; data++) {
 524                 if (*data == '\'') {
 525                         *out = '\'';
 526                         out++;
 527                 }
 528                 *out++ = *data;
 529         }
 530         *out = '\0';
 531
 532         return 0;
 533 }
 534
 535 static struct ast_custom_function escape_function = {
 536         .name = "SQL_ESC",
 537         .synopsis = "Escapes single ticks for use in SQL statements",
 538         .syntax = "SQL_ESC(<string>)",
 539         .desc =
 540 "Used in SQL templates to escape data which may contain single ticks (') which\n"
 541 "are otherwise used to delimit data.  For example:\n"
 542 "SELECT foo FROM bar WHERE baz='${SQL_ESC(${ARG1})}'\n",
 543         .read = acf_escape,
 544         .write = NULL,
 545 };
 546
 547 static int acf_fetch(struct ast_channel *chan, const char *cmd, char *data, char *buf, size_t len)
 548 {
 549         struct ast_datastore *store;
 550         struct odbc_datastore *resultset;
 551         struct odbc_datastore_row *row;
 552         store = ast_channel_datastore_find(chan, &odbc_info, data);
 553         if (!store) {
 554                 return -1;
 555         }
 556         resultset = store->data;
 557         AST_LIST_LOCK(resultset);
 558         row = AST_LIST_REMOVE_HEAD(resultset, list);
 559         AST_LIST_UNLOCK(resultset);
 560         if (!row) {
 561                 /* Cleanup datastore */
 562                 ast_channel_datastore_remove(chan, store);
 563                 ast_channel_datastore_free(store);
 564                 return -1;
 565         }
 566         pbx_builtin_setvar_helper(chan, "~ODBCFIELDS~", resultset->names);
 567         ast_copy_string(buf, row->data, len);
 568         ast_free(row);
 569         return 0;
 570 }
 571
 572 static struct ast_custom_function fetch_function = {
 573         .name = "ODBC_FETCH",
 574         .synopsis = "Fetch a row from a multirow query",
 575         .syntax = "ODBC_FETCH(<result-id>)",
 576         .desc =
 577 "For queries which are marked as mode=multirow, the original query returns a\n"
 578 "result-id from which results may be fetched.  This function implements the\n"
 579 "actual fetch of the results.\n",
 580         .read = acf_fetch,
 581         .write = NULL,
 582 };
 583
 584 static char *app_odbcfinish = "ODBCFinish";
 585 static char *syn_odbcfinish = "Clear the resultset of a successful multirow query";
 586 static char *desc_odbcfinish =
 587 "ODBCFinish(<result-id>)\n"
 588 "  Clears any remaining rows of the specified resultset\n";
 589
 590
 591 static int exec_odbcfinish(struct ast_channel *chan, void *data)
 592 {
 593         struct ast_datastore *store = ast_channel_datastore_find(chan, &odbc_info, data);
 594         if (!store) /* Already freed; no big deal. */
 595                 return 0;
 596         ast_channel_datastore_remove(chan, store);
 597         ast_channel_datastore_free(store);
 598         return 0;
 599 }
 600
 601 static int init_acf_query(struct ast_config *cfg, char *catg, struct acf_odbc_query **query)
 602 {
 603         const char *tmp;
 604         int i;
 605
 606         if (!cfg || !catg) {
 607                 return EINVAL;
 608         }
 609
 610         *query = ast_calloc(1, sizeof(struct acf_odbc_query));
 611         if (! (*query))
 612                 return ENOMEM;
 613
 614         if (((tmp = ast_variable_retrieve(cfg, catg, "writehandle"))) || ((tmp = ast_variable_retrieve(cfg, catg, "dsn")))) {
 615                 char *tmp2 = ast_strdupa(tmp);
 616                 AST_DECLARE_APP_ARGS(write,
 617                         AST_APP_ARG(dsn)[5];
 618                 );
 619                 AST_NONSTANDARD_APP_ARGS(write, tmp2, ',');
 620                 for (i = 0; i < 5; i++) {
 621                         if (!ast_strlen_zero(write.dsn[i]))
 622                                 ast_copy_string((*query)->writehandle[i], write.dsn[i], sizeof((*query)->writehandle[i]));
 623                 }
 624         }
 625
 626         if ((tmp = ast_variable_retrieve(cfg, catg, "readhandle"))) {
 627                 char *tmp2 = ast_strdupa(tmp);
 628                 AST_DECLARE_APP_ARGS(read,
 629                         AST_APP_ARG(dsn)[5];
 630                 );
 631                 AST_NONSTANDARD_APP_ARGS(read, tmp2, ',');
 632                 for (i = 0; i < 5; i++) {
 633                         if (!ast_strlen_zero(read.dsn[i]))
 634                                 ast_copy_string((*query)->readhandle[i], read.dsn[i], sizeof((*query)->readhandle[i]));
 635                 }
 636         } else {
 637                 /* If no separate readhandle, then use the writehandle for reading */
 638                 for (i = 0; i < 5; i++) {
 639                         if (!ast_strlen_zero((*query)->writehandle[i]))
 640                                 ast_copy_string((*query)->readhandle[i], (*query)->writehandle[i], sizeof((*query)->readhandle[i]));
 641                 }
 642         }
 643
 644         if ((tmp = ast_variable_retrieve(cfg, catg, "readsql")))
 645                 ast_copy_string((*query)->sql_read, tmp, sizeof((*query)->sql_read));
 646         else if ((tmp = ast_variable_retrieve(cfg, catg, "read"))) {
 647                 ast_log(LOG_WARNING, "Parameter 'read' is deprecated for category %s.  Please use 'readsql' instead.\n", catg);
 648                 ast_copy_string((*query)->sql_read, tmp, sizeof((*query)->sql_read));
 649         }
 650
 651         if (!ast_strlen_zero((*query)->sql_read) && ast_strlen_zero((*query)->readhandle[0])) {
 652                 ast_free(*query);
 653                 *query = NULL;
 654                 ast_log(LOG_ERROR, "There is SQL, but no ODBC class to be used for reading: %s\n", catg);
 655                 return EINVAL;
 656         }
 657
 658         if ((tmp = ast_variable_retrieve(cfg, catg, "writesql")))
 659                 ast_copy_string((*query)->sql_write, tmp, sizeof((*query)->sql_write));
 660         else if ((tmp = ast_variable_retrieve(cfg, catg, "write"))) {
 661                 ast_log(LOG_WARNING, "Parameter 'write' is deprecated for category %s.  Please use 'writesql' instead.\n", catg);
 662                 ast_copy_string((*query)->sql_write, tmp, sizeof((*query)->sql_write));
 663         }
 664
 665         if (!ast_strlen_zero((*query)->sql_write) && ast_strlen_zero((*query)->writehandle[0])) {
 666                 ast_free(*query);
 667                 *query = NULL;
 668                 ast_log(LOG_ERROR, "There is SQL, but no ODBC class to be used for writing: %s\n", catg);
 669                 return EINVAL;
 670         }
 671
 672         /* Allow escaping of embedded commas in fields to be turned off */
 673         ast_set_flag((*query), OPT_ESCAPECOMMAS);
 674         if ((tmp = ast_variable_retrieve(cfg, catg, "escapecommas"))) {
 675                 if (ast_false(tmp))
 676                         ast_clear_flag((*query), OPT_ESCAPECOMMAS);
 677         }
 678
 679         if ((tmp = ast_variable_retrieve(cfg, catg, "mode"))) {
 680                 if (strcasecmp(tmp, "multirow") == 0)
 681                         ast_set_flag((*query), OPT_MULTIROW);
 682                 if ((tmp = ast_variable_retrieve(cfg, catg, "rowlimit")))
 683                         sscanf(tmp, "%d", &((*query)->rowlimit));
 684         }
 685
 686         (*query)->acf = ast_calloc(1, sizeof(struct ast_custom_function));
 687         if (! (*query)->acf) {
 688                 ast_free(*query);
 689                 *query = NULL;
 690                 return ENOMEM;
 691         }
 692
 693         if ((tmp = ast_variable_retrieve(cfg, catg, "prefix")) && !ast_strlen_zero(tmp)) {
 694                 asprintf((char **)&((*query)->acf->name), "%s_%s", tmp, catg);
 695         } else {
 696                 asprintf((char **)&((*query)->acf->name), "ODBC_%s", catg);
 697         }
 698
 699         if (!((*query)->acf->name)) {
 700                 ast_free((*query)->acf);
 701                 ast_free(*query);
 702                 *query = NULL;
 703                 return ENOMEM;
 704         }
 705
 706         asprintf((char **)&((*query)->acf->syntax), "%s(<arg1>[...[,<argN>]])", (*query)->acf->name);
 707
 708         if (!((*query)->acf->syntax)) {
 709                 ast_free((char *)(*query)->acf->name);
 710                 ast_free((*query)->acf);
 711                 ast_free(*query);
 712                 *query = NULL;
 713                 return ENOMEM;
 714         }
 715
 716         (*query)->acf->synopsis = "Runs the referenced query with the specified arguments";
 717         if (!ast_strlen_zero((*query)->sql_read) && !ast_strlen_zero((*query)->sql_write)) {
 718                 asprintf((char **)&((*query)->acf->desc),
 719                                         "Runs the following query, as defined in func_odbc.conf, performing\n"
 720                                         "substitution of the arguments into the query as specified by ${ARG1},\n"
 721                                         "${ARG2}, ... ${ARGn}.  When setting the function, the values are provided\n"
 722                                         "either in whole as ${VALUE} or parsed as ${VAL1}, ${VAL2}, ... ${VALn}.\n"
 723                                         "\nRead:\n%s\n\nWrite:\n%s\n",
 724                                         (*query)->sql_read,
 725                                         (*query)->sql_write);
 726         } else if (!ast_strlen_zero((*query)->sql_read)) {
 727                 asprintf((char **)&((*query)->acf->desc),
 728                                         "Runs the following query, as defined in func_odbc.conf, performing\n"
 729                                         "substitution of the arguments into the query as specified by ${ARG1},\n"
 730                                         "${ARG2}, ... ${ARGn}.  This function may only be read, not set.\n\nSQL:\n%s\n",
 731                                         (*query)->sql_read);
 732         } else if (!ast_strlen_zero((*query)->sql_write)) {
 733                 asprintf((char **)&((*query)->acf->desc),
 734                                         "Runs the following query, as defined in func_odbc.conf, performing\n"
 735                                         "substitution of the arguments into the query as specified by ${ARG1},\n"
 736                                         "${ARG2}, ... ${ARGn}.  The values are provided either in whole as\n"
 737                                         "${VALUE} or parsed as ${VAL1}, ${VAL2}, ... ${VALn}.\n"
 738                                         "This function may only be set.\nSQL:\n%s\n",
 739                                         (*query)->sql_write);
 740         } else {
 741                 ast_free((char *)(*query)->acf->syntax);
 742                 ast_free((char *)(*query)->acf->name);
 743                 ast_free((*query)->acf);
 744                 ast_free(*query);
 745                 ast_log(LOG_WARNING, "Section %s was found, but there was no SQL to execute.  Ignoring.\n", catg);
 746                 return EINVAL;
 747         }
 748
 749         if (! ((*query)->acf->desc)) {
 750                 ast_free((char *)(*query)->acf->syntax);
 751                 ast_free((char *)(*query)->acf->name);
 752                 ast_free((*query)->acf);
 753                 ast_free(*query);
 754                 *query = NULL;
 755                 return ENOMEM;
 756         }
 757
 758         if (ast_strlen_zero((*query)->sql_read)) {
 759                 (*query)->acf->read = NULL;
 760         } else {
 761                 (*query)->acf->read = acf_odbc_read;
 762         }
 763
 764         if (ast_strlen_zero((*query)->sql_write)) {
 765                 (*query)->acf->write = NULL;
 766         } else {
 767                 (*query)->acf->write = acf_odbc_write;
 768         }
 769
 770         return 0;
 771 }
 772
 773 static int free_acf_query(struct acf_odbc_query *query)
 774 {
 775         if (query) {
 776                 if (query->acf) {
 777                         if (query->acf->name)
 778                                 ast_free((char *)query->acf->name);
 779                         if (query->acf->syntax)
 780                                 ast_free((char *)query->acf->syntax);
 781                         if (query->acf->desc)
 782                                 ast_free((char *)query->acf->desc);
 783                         ast_free(query->acf);
 784                 }
 785                 ast_free(query);
 786         }
 787         return 0;
 788 }
 789
 790 static int load_module(void)
 791 {
 792         int res = 0;
 793         struct ast_config *cfg;
 794         char *catg;
 795         struct ast_flags config_flags = { 0 };
 796
 797         res |= ast_custom_function_register(&fetch_function);
 798         res |= ast_register_application(app_odbcfinish, exec_odbcfinish, syn_odbcfinish, desc_odbcfinish);
 799         AST_LIST_LOCK(&queries);
 800
 801         cfg = ast_config_load(config, config_flags);
 802         if (!cfg) {
 803                 ast_log(LOG_NOTICE, "Unable to load config for func_odbc: %s\n", config);
 804                 AST_LIST_UNLOCK(&queries);
 805                 return AST_MODULE_LOAD_DECLINE;
 806         }
 807
 808         for (catg = ast_category_browse(cfg, NULL);
 809              catg;
 810              catg = ast_category_browse(cfg, catg)) {
 811                 struct acf_odbc_query *query = NULL;
 812                 int err;
 813
 814                 if ((err = init_acf_query(cfg, catg, &query))) {
 815                         if (err == ENOMEM)
 816                                 ast_log(LOG_ERROR, "Out of memory\n");
 817                         else if (err == EINVAL)
 818                                 ast_log(LOG_ERROR, "Invalid parameters for category %s\n", catg);
 819                         else
 820                                 ast_log(LOG_ERROR, "%s (%d)\n", strerror(err), err);
 821                 } else {
 822                         AST_LIST_INSERT_HEAD(&queries, query, list);
 823                         ast_custom_function_register(query->acf);
 824                 }
 825         }
 826
 827         ast_config_destroy(cfg);
 828         res |= ast_custom_function_register(&escape_function);
 829
 830         AST_LIST_UNLOCK(&queries);
 831         return res;
 832 }
 833
 834 static int unload_module(void)
 835 {
 836         struct acf_odbc_query *query;
 837         int res = 0;
 838
 839         AST_LIST_LOCK(&queries);
 840         while (!AST_LIST_EMPTY(&queries)) {
 841                 query = AST_LIST_REMOVE_HEAD(&queries, list);
 842                 ast_custom_function_unregister(query->acf);
 843                 free_acf_query(query);
 844         }
 845
 846         res |= ast_custom_function_unregister(&escape_function);
 847         res |= ast_custom_function_unregister(&fetch_function);
 848         res |= ast_unregister_application(app_odbcfinish);
 849
 850         /* Allow any threads waiting for this lock to pass (avoids a race) */
 851         AST_LIST_UNLOCK(&queries);
 852         usleep(1);
 853         AST_LIST_LOCK(&queries);
 854
 855         AST_LIST_UNLOCK(&queries);
 856         return 0;
 857 }
 858
 859 static int reload(void)
 860 {
 861         int res = 0;
 862         struct ast_config *cfg;
 863         struct acf_odbc_query *oldquery;
 864         char *catg;
 865         struct ast_flags config_flags = { CONFIG_FLAG_FILEUNCHANGED };
 866
 867         cfg = ast_config_load(config, config_flags);
 868         if (cfg == CONFIG_STATUS_FILEUNCHANGED)
 869                 return 0;
 870
 871         AST_LIST_LOCK(&queries);
 872
 873         while (!AST_LIST_EMPTY(&queries)) {
 874                 oldquery = AST_LIST_REMOVE_HEAD(&queries, list);
 875                 ast_custom_function_unregister(oldquery->acf);
 876                 free_acf_query(oldquery);
 877         }
 878
 879         if (!cfg) {
 880                 ast_log(LOG_WARNING, "Unable to load config for func_odbc: %s\n", config);
 881                 goto reload_out;
 882         }
 883
 884         for (catg = ast_category_browse(cfg, NULL);
 885              catg;
 886              catg = ast_category_browse(cfg, catg)) {
 887                 struct acf_odbc_query *query = NULL;
 888
 889                 if (init_acf_query(cfg, catg, &query)) {
 890                         ast_log(LOG_ERROR, "Cannot initialize query %s\n", catg);
 891                 } else {
 892                         AST_LIST_INSERT_HEAD(&queries, query, list);
 893                         ast_custom_function_register(query->acf);
 894                 }
 895         }
 896
 897         ast_config_destroy(cfg);
 898 reload_out:
 899         AST_LIST_UNLOCK(&queries);
 900         return res;
 901 }
 902
 903 /* XXX need to revise usecount - set if query_lock is set */
 904
 905 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_DEFAULT, "ODBC lookups",
 906                 .load = load_module,
 907                 .unload = unload_module,
 908                 .reload = reload,
 909                );
 910