c8b65eb226b0477959dd66dcd3ed45547b7ff73a
[asterisk/asterisk.git] / funcs / func_odbc.c
1 /*
2  * Asterisk -- An open source telephony toolkit.
3  *
4  * Copyright (c) 2005, 2006 Tilghman Lesher
5  * Copyright (c) 2008 Digium, Inc.
6  *
7  * Tilghman Lesher <func_odbc__200508@the-tilghman.com>
8  *
9  * See http://www.asterisk.org for more information about
10  * the Asterisk project. Please do not directly contact
11  * any of the maintainers of this project for assistance;
12  * the project provides a web site, mailing lists and IRC
13  * channels for your use.
14  *
15  * This program is free software, distributed under the terms of
16  * the GNU General Public License Version 2. See the LICENSE file
17  * at the top of the source tree.
18  */
19
20 /*!
21  * \file
22  *
23  * \brief ODBC lookups
24  *
25  * \author Tilghman Lesher <func_odbc__200508@the-tilghman.com>
26  *
27  * \ingroup functions
28  */
29
30 /*** MODULEINFO
31         <depend>res_odbc</depend>
32  ***/
33
34 #include "asterisk.h"
35
36 ASTERISK_FILE_VERSION(__FILE__, "$Revision$")
37
38 #include "asterisk/module.h"
39 #include "asterisk/file.h"
40 #include "asterisk/channel.h"
41 #include "asterisk/pbx.h"
42 #include "asterisk/config.h"
43 #include "asterisk/res_odbc.h"
44 #include "asterisk/app.h"
45 #include "asterisk/cli.h"
46 #include "asterisk/strings.h"
47
48 /*** DOCUMENTATION
49         <function name="ODBC_FETCH" language="en_US">
50                 <synopsis>
51                         Fetch a row from a multirow query.
52                 </synopsis>
53                 <syntax>
54                         <parameter name="result-id" required="true" />
55                 </syntax>
56                 <description>
57                         <para>For queries which are marked as mode=multirow, the original 
58                         query returns a <replaceable>result-id</replaceable> from which results 
59                         may be fetched.  This function implements the actual fetch of the results.</para>
60                         <para>This also sets <variable>ODBC_FETCH_STATUS</variable>.</para>
61                         <variablelist>
62                                 <variable name="ODBC_FETCH_STATUS">
63                                         <value name="SUCESS">
64                                                 If rows are available.
65                                         </value>
66                                         <value name="FAILURE">
67                                                 If no rows are available.
68                                         </value>
69                                 </variable>
70                         </variablelist>
71                 </description>
72         </function>
73         <application name="ODBCFinish" language="en_US">
74                 <synopsis>
75                         Clear the resultset of a sucessful multirow query.
76                 </synopsis>
77                 <syntax>
78                         <parameter name="result-id" required="true" />
79                 </syntax>
80                 <description>
81                         <para>For queries which are marked as mode=multirow, this will clear 
82                         any remaining rows of the specified resultset.</para>
83                 </description>
84         </application>
85         <function name="SQL_ESC" language="en_US">
86                 <synopsis>
87                         Escapes single ticks for use in SQL statements.
88                 </synopsis>
89                 <syntax>
90                         <parameter name="string" required="true" />
91                 </syntax>
92                 <description>
93                         <para>Used in SQL templates to escape data which may contain single ticks 
94                         <literal>'</literal> which are otherwise used to delimit data.</para>
95                         <para>Example: SELECT foo FROM bar WHERE baz='${SQL_ESC(${ARG1})}'</para>
96                 </description>
97         </function>
98  ***/
99
100 static char *config = "func_odbc.conf";
101
102 enum odbc_option_flags {
103         OPT_ESCAPECOMMAS =      (1 << 0),
104         OPT_MULTIROW     =      (1 << 1),
105 };
106
107 struct acf_odbc_query {
108         AST_RWLIST_ENTRY(acf_odbc_query) list;
109         char readhandle[5][30];
110         char writehandle[5][30];
111         char sql_read[2048];
112         char sql_write[2048];
113         char sql_insert[2048];
114         unsigned int flags;
115         int rowlimit;
116         struct ast_custom_function *acf;
117 };
118
119 static void odbc_datastore_free(void *data);
120
121 static struct ast_datastore_info odbc_info = {
122         .type = "FUNC_ODBC",
123         .destroy = odbc_datastore_free,
124 };
125
126 /* For storing each result row */
127 struct odbc_datastore_row {
128         AST_LIST_ENTRY(odbc_datastore_row) list;
129         char data[0];
130 };
131
132 /* For storing each result set */
133 struct odbc_datastore {
134         AST_LIST_HEAD(, odbc_datastore_row);
135         char names[0];
136 };
137
138 static AST_RWLIST_HEAD_STATIC(queries, acf_odbc_query);
139
140 static int resultcount = 0;
141
142 AST_THREADSTORAGE(sql_buf);
143 AST_THREADSTORAGE(sql2_buf);
144 AST_THREADSTORAGE(coldata_buf);
145 AST_THREADSTORAGE(colnames_buf);
146
147 static void odbc_datastore_free(void *data)
148 {
149         struct odbc_datastore *result = data;
150         struct odbc_datastore_row *row;
151         AST_LIST_LOCK(result);
152         while ((row = AST_LIST_REMOVE_HEAD(result, list))) {
153                 ast_free(row);
154         }
155         AST_LIST_UNLOCK(result);
156         AST_LIST_HEAD_DESTROY(result);
157         ast_free(result);
158 }
159
160 static SQLHSTMT generic_execute(struct odbc_obj *obj, void *data)
161 {
162         int res;
163         char *sql = data;
164         SQLHSTMT stmt;
165
166         res = SQLAllocHandle (SQL_HANDLE_STMT, obj->con, &stmt);
167         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
168                 ast_log(LOG_WARNING, "SQL Alloc Handle failed (%d)!\n", res);
169                 return NULL;
170         }
171
172         res = SQLExecDirect(stmt, (unsigned char *)sql, SQL_NTS);
173         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
174                 if (res == SQL_ERROR) {
175                         int i;
176                         SQLINTEGER nativeerror=0, numfields=0;
177                         SQLSMALLINT diagbytes=0;
178                         unsigned char state[10], diagnostic[256];
179
180                         SQLGetDiagField(SQL_HANDLE_STMT, stmt, 1, SQL_DIAG_NUMBER, &numfields, SQL_IS_INTEGER, &diagbytes);
181                         for (i = 0; i < numfields; i++) {
182                                 SQLGetDiagRec(SQL_HANDLE_STMT, stmt, i + 1, state, &nativeerror, diagnostic, sizeof(diagnostic), &diagbytes);
183                                 ast_log(LOG_WARNING, "SQL Execute returned an error %d: %s: %s (%d)\n", res, state, diagnostic, diagbytes);
184                                 if (i > 10) {
185                                         ast_log(LOG_WARNING, "Oh, that was good.  There are really %d diagnostics?\n", (int)numfields);
186                                         break;
187                                 }
188                         }
189                 }
190
191                 ast_log(LOG_WARNING, "SQL Exec Direct failed (%d)![%s]\n", res, sql);
192                 SQLCloseCursor(stmt);
193                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
194                 return NULL;
195         }
196
197         return stmt;
198 }
199
200 /*
201  * Master control routine
202  */
203 static int acf_odbc_write(struct ast_channel *chan, const char *cmd, char *s, const char *value)
204 {
205         struct odbc_obj *obj = NULL;
206         struct acf_odbc_query *query;
207         char *t, varname[15];
208         int i, dsn, bogus_chan = 0;
209         int transactional = 0;
210         AST_DECLARE_APP_ARGS(values,
211                 AST_APP_ARG(field)[100];
212         );
213         AST_DECLARE_APP_ARGS(args,
214                 AST_APP_ARG(field)[100];
215         );
216         SQLHSTMT stmt = NULL;
217         SQLLEN rows=0;
218         struct ast_str *buf = ast_str_thread_get(&sql_buf, 16);
219         struct ast_str *insertbuf = ast_str_thread_get(&sql2_buf, 16);
220         const char *status = "FAILURE";
221
222         if (!buf) {
223                 return -1;
224         }
225
226         AST_RWLIST_RDLOCK(&queries);
227         AST_RWLIST_TRAVERSE(&queries, query, list) {
228                 if (!strcmp(query->acf->name, cmd)) {
229                         break;
230                 }
231         }
232
233         if (!query) {
234                 ast_log(LOG_ERROR, "No such function '%s'\n", cmd);
235                 AST_RWLIST_UNLOCK(&queries);
236                 pbx_builtin_setvar_helper(chan, "ODBCSTATUS", status);
237                 return -1;
238         }
239
240         if (!chan) {
241                 if ((chan = ast_channel_alloc(0, AST_STATE_DOWN, "", "", "", "", "", "", 0, "Bogus/func_odbc")))
242                         bogus_chan = 1;
243         }
244
245         if (chan)
246                 ast_autoservice_start(chan);
247
248         ast_str_make_space(&buf, strlen(query->sql_write) * 2 + 300);
249         ast_str_make_space(&insertbuf, strlen(query->sql_insert) * 2 + 300);
250
251         /* Parse our arguments */
252         t = value ? ast_strdupa(value) : "";
253
254         if (!s || !t) {
255                 ast_log(LOG_ERROR, "Out of memory\n");
256                 AST_RWLIST_UNLOCK(&queries);
257                 if (chan)
258                         ast_autoservice_stop(chan);
259                 if (bogus_chan) {
260                         ast_channel_release(chan);
261                 } else {
262                         pbx_builtin_setvar_helper(chan, "ODBCSTATUS", status);
263                 }
264                 return -1;
265         }
266
267         AST_STANDARD_APP_ARGS(args, s);
268         for (i = 0; i < args.argc; i++) {
269                 snprintf(varname, sizeof(varname), "ARG%d", i + 1);
270                 pbx_builtin_pushvar_helper(chan, varname, args.field[i]);
271         }
272
273         /* Parse values, just like arguments */
274         AST_STANDARD_APP_ARGS(values, t);
275         for (i = 0; i < values.argc; i++) {
276                 snprintf(varname, sizeof(varname), "VAL%d", i + 1);
277                 pbx_builtin_pushvar_helper(chan, varname, values.field[i]);
278         }
279
280         /* Additionally set the value as a whole (but push an empty string if value is NULL) */
281         pbx_builtin_pushvar_helper(chan, "VALUE", value ? value : "");
282
283         ast_str_substitute_variables(&buf, 0, chan, query->sql_write);
284         ast_str_substitute_variables(&insertbuf, 0, chan, query->sql_insert);
285
286         /* Restore prior values */
287         for (i = 0; i < args.argc; i++) {
288                 snprintf(varname, sizeof(varname), "ARG%d", i + 1);
289                 pbx_builtin_setvar_helper(chan, varname, NULL);
290         }
291
292         for (i = 0; i < values.argc; i++) {
293                 snprintf(varname, sizeof(varname), "VAL%d", i + 1);
294                 pbx_builtin_setvar_helper(chan, varname, NULL);
295         }
296         pbx_builtin_setvar_helper(chan, "VALUE", NULL);
297
298         /*!\note
299          * Okay, this part is confusing.  Transactions belong to a single database
300          * handle.  Therefore, when working with transactions, we CANNOT failover
301          * to multiple DSNs.  We MUST have a single handle all the way through the
302          * transaction, or else we CANNOT enforce atomicity.
303          */
304         for (dsn = 0; dsn < 5; dsn++) {
305                 if (transactional) {
306                         /* This can only happen second time through or greater. */
307                         ast_log(LOG_WARNING, "Transactions do not work well with multiple DSNs for 'writehandle'\n");
308                 }
309
310                 if (!ast_strlen_zero(query->writehandle[dsn])) {
311                         if ((obj = ast_odbc_retrieve_transaction_obj(chan, query->writehandle[dsn]))) {
312                                 transactional = 1;
313                         } else {
314                                 obj = ast_odbc_request_obj(query->writehandle[dsn], 0);
315                                 transactional = 0;
316                         }
317                         if (obj && (stmt = ast_odbc_direct_execute(obj, generic_execute, ast_str_buffer(buf)))) {
318                                 break;
319                         }
320                 }
321
322                 if (obj && !transactional) {
323                         ast_odbc_release_obj(obj);
324                 }
325         }
326
327         if (stmt && rows == 0 && ast_str_strlen(insertbuf) != 0) {
328                 SQLCloseCursor(stmt);
329                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
330                 for (dsn = 0; dsn < 5; dsn++) {
331                         if (!ast_strlen_zero(query->writehandle[dsn])) {
332                                 obj = ast_odbc_request_obj(query->writehandle[dsn], 0);
333                                 if (obj) {
334                                         stmt = ast_odbc_direct_execute(obj, generic_execute, ast_str_buffer(insertbuf));
335                                 }
336                         }
337                         if (stmt) {
338                                 status = "FAILOVER";
339                                 SQLRowCount(stmt, &rows);
340                                 break;
341                         }
342                 }
343         } else if (stmt) {
344                 status = "SUCCESS";
345                 SQLRowCount(stmt, &rows);
346         }
347
348         AST_RWLIST_UNLOCK(&queries);
349
350         /* Output the affected rows, for all cases.  In the event of failure, we
351          * flag this as -1 rows.  Note that this is different from 0 affected rows
352          * which would be the case if we succeeded in our query, but the values did
353          * not change. */
354         snprintf(varname, sizeof(varname), "%d", (int)rows);
355         pbx_builtin_setvar_helper(chan, "ODBCROWS", varname);
356         pbx_builtin_setvar_helper(chan, "ODBCSTATUS", status);
357
358         if (stmt) {
359                 SQLCloseCursor(stmt);
360                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
361         }
362         if (obj && !transactional) {
363                 ast_odbc_release_obj(obj);
364                 obj = NULL;
365         }
366
367         if (chan)
368                 ast_autoservice_stop(chan);
369         if (bogus_chan)
370                 ast_channel_release(chan);
371
372         return 0;
373 }
374
375 static int acf_odbc_read(struct ast_channel *chan, const char *cmd, char *s, char *buf, size_t len)
376 {
377         struct odbc_obj *obj = NULL;
378         struct acf_odbc_query *query;
379         char varname[15], rowcount[12] = "-1";
380         struct ast_str *colnames = ast_str_thread_get(&colnames_buf, 16);
381         int res, x, y, buflen = 0, escapecommas, rowlimit = 1, dsn, bogus_chan = 0;
382         AST_DECLARE_APP_ARGS(args,
383                 AST_APP_ARG(field)[100];
384         );
385         SQLHSTMT stmt = NULL;
386         SQLSMALLINT colcount=0;
387         SQLLEN indicator;
388         SQLSMALLINT collength;
389         struct odbc_datastore *resultset = NULL;
390         struct odbc_datastore_row *row = NULL;
391         struct ast_str *sql = ast_str_thread_get(&sql_buf, 16);
392         const char *status = "FAILURE";
393
394         if (!sql) {
395                 pbx_builtin_setvar_helper(chan, "ODBCSTATUS", status);
396                 return -1;
397         }
398
399         ast_str_reset(colnames);
400
401         AST_RWLIST_RDLOCK(&queries);
402         AST_RWLIST_TRAVERSE(&queries, query, list) {
403                 if (!strcmp(query->acf->name, cmd)) {
404                         break;
405                 }
406         }
407
408         if (!query) {
409                 ast_log(LOG_ERROR, "No such function '%s'\n", cmd);
410                 AST_RWLIST_UNLOCK(&queries);
411                 pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
412                 pbx_builtin_setvar_helper(chan, "ODBCSTATUS", status);
413                 return -1;
414         }
415
416         if (!chan) {
417                 if ((chan = ast_channel_alloc(0, AST_STATE_DOWN, "", "", "", "", "", "", 0, "Bogus/func_odbc"))) {
418                         bogus_chan = 1;
419                 }
420         }
421
422         if (chan) {
423                 ast_autoservice_start(chan);
424         }
425
426         AST_STANDARD_APP_ARGS(args, s);
427         for (x = 0; x < args.argc; x++) {
428                 snprintf(varname, sizeof(varname), "ARG%d", x + 1);
429                 pbx_builtin_pushvar_helper(chan, varname, args.field[x]);
430         }
431
432         ast_str_substitute_variables(&sql, 0, chan, query->sql_read);
433
434         /* Restore prior values */
435         for (x = 0; x < args.argc; x++) {
436                 snprintf(varname, sizeof(varname), "ARG%d", x + 1);
437                 pbx_builtin_setvar_helper(chan, varname, NULL);
438         }
439
440         /* Save these flags, so we can release the lock */
441         escapecommas = ast_test_flag(query, OPT_ESCAPECOMMAS);
442         if (ast_test_flag(query, OPT_MULTIROW)) {
443                 resultset = ast_calloc(1, sizeof(*resultset));
444                 AST_LIST_HEAD_INIT(resultset);
445                 if (query->rowlimit) {
446                         rowlimit = query->rowlimit;
447                 } else {
448                         rowlimit = INT_MAX;
449                 }
450         }
451         AST_RWLIST_UNLOCK(&queries);
452
453         for (dsn = 0; dsn < 5; dsn++) {
454                 if (!ast_strlen_zero(query->readhandle[dsn])) {
455                         obj = ast_odbc_request_obj(query->readhandle[dsn], 0);
456                         if (obj) {
457                                 stmt = ast_odbc_direct_execute(obj, generic_execute, ast_str_buffer(sql));
458                         }
459                 }
460                 if (stmt) {
461                         break;
462                 }
463         }
464
465         if (!stmt) {
466                 ast_log(LOG_ERROR, "Unable to execute query [%s]\n", ast_str_buffer(sql));
467                 if (obj) {
468                         ast_odbc_release_obj(obj);
469                         obj = NULL;
470                 }
471                 pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
472                 if (chan) {
473                         ast_autoservice_stop(chan);
474                 }
475                 if (bogus_chan) {
476                         ast_channel_release(chan);
477                 }
478                 return -1;
479         }
480
481         res = SQLNumResultCols(stmt, &colcount);
482         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
483                 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", ast_str_buffer(sql));
484                 SQLCloseCursor(stmt);
485                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
486                 ast_odbc_release_obj(obj);
487                 obj = NULL;
488                 pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
489                 if (chan) {
490                         ast_autoservice_stop(chan);
491                 }
492                 if (bogus_chan) {
493                         ast_channel_release(chan);
494                 }
495                 return -1;
496         }
497
498         res = SQLFetch(stmt);
499         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
500                 int res1 = -1;
501                 if (res == SQL_NO_DATA) {
502                         ast_verb(4, "Found no rows [%s]\n", ast_str_buffer(sql));
503                         res1 = 0;
504                         buf[0] = '\0';
505                         ast_copy_string(rowcount, "0", sizeof(rowcount));
506                         status = "NODATA";
507                 } else {
508                         ast_log(LOG_WARNING, "Error %d in FETCH [%s]\n", res, ast_str_buffer(sql));
509                         status = "FETCHERROR";
510                 }
511                 SQLCloseCursor(stmt);
512                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
513                 ast_odbc_release_obj(obj);
514                 obj = NULL;
515                 pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
516                 pbx_builtin_setvar_helper(chan, "ODBCSTATUS", status);
517                 if (chan)
518                         ast_autoservice_stop(chan);
519                 if (bogus_chan)
520                         ast_channel_release(chan);
521                 return res1;
522         }
523
524         status = "SUCCESS";
525
526         for (y = 0; y < rowlimit; y++) {
527                 buf[0] = '\0';
528                 for (x = 0; x < colcount; x++) {
529                         int i;
530                         struct ast_str *coldata = ast_str_thread_get(&coldata_buf, 16);
531                         char *ptrcoldata;
532
533                         if (y == 0) {
534                                 char colname[256];
535                                 SQLULEN maxcol;
536
537                                 res = SQLDescribeCol(stmt, x + 1, (unsigned char *)colname, sizeof(colname), &collength, NULL, &maxcol, NULL, NULL);
538                                 ast_debug(3, "Got collength of %d and maxcol of %d for column '%s' (offset %d)\n", (int)collength, (int)maxcol, colname, x);
539                                 if (((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) || collength == 0) {
540                                         snprintf(colname, sizeof(colname), "field%d", x);
541                                 }
542
543                                 ast_str_make_space(&coldata, maxcol + 1);
544
545                                 if (ast_str_strlen(colnames)) {
546                                         ast_str_append(&colnames, 0, ",");
547                                 }
548                                 ast_str_append_escapecommas(&colnames, 0, colname, sizeof(colname));
549
550                                 if (resultset) {
551                                         void *tmp = ast_realloc(resultset, sizeof(*resultset) + ast_str_strlen(colnames) + 1);
552                                         if (!tmp) {
553                                                 ast_log(LOG_ERROR, "No space for a new resultset?\n");
554                                                 ast_free(resultset);
555                                                 SQLCloseCursor(stmt);
556                                                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
557                                                 ast_odbc_release_obj(obj);
558                                                 obj = NULL;
559                                                 pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
560                                                 pbx_builtin_setvar_helper(chan, "ODBCSTATUS", "MEMERROR");
561                                                 if (chan)
562                                                         ast_autoservice_stop(chan);
563                                                 if (bogus_chan)
564                                                         ast_channel_release(chan);
565                                                 return -1;
566                                         }
567                                         resultset = tmp;
568                                         strcpy((char *)resultset + sizeof(*resultset), ast_str_buffer(colnames));
569                                 }
570                         }
571
572                         buflen = strlen(buf);
573                         res = ast_odbc_ast_str_SQLGetData(&coldata, -1, stmt, x + 1, SQL_CHAR, &indicator);
574                         if (indicator == SQL_NULL_DATA) {
575                                 ast_debug(3, "Got NULL data\n");
576                                 ast_str_reset(coldata);
577                                 res = SQL_SUCCESS;
578                         }
579
580                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
581                                 ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", ast_str_buffer(sql));
582                                 y = -1;
583                                 buf[0] = '\0';
584                                 goto end_acf_read;
585                         }
586
587                         ast_debug(2, "Got coldata of '%s'\n", ast_str_buffer(coldata));
588
589                         if (x) {
590                                 buf[buflen++] = ',';
591                         }
592
593                         /* Copy data, encoding '\' and ',' for the argument parser */
594                         ptrcoldata = ast_str_buffer(coldata);
595                         for (i = 0; i < ast_str_strlen(coldata); i++) {
596                                 if (escapecommas && (ptrcoldata[i] == '\\' || ptrcoldata[i] == ',')) {
597                                         buf[buflen++] = '\\';
598                                 }
599                                 buf[buflen++] = ptrcoldata[i];
600
601                                 if (buflen >= len - 2) {
602                                         break;
603                                 }
604
605                                 if (ptrcoldata[i] == '\0') {
606                                         break;
607                                 }
608                         }
609
610                         buf[buflen] = '\0';
611                         ast_debug(2, "buf is now set to '%s'\n", buf);
612                 }
613                 ast_debug(2, "buf is now set to '%s'\n", buf);
614
615                 if (resultset) {
616                         row = ast_calloc(1, sizeof(*row) + buflen + 1);
617                         if (!row) {
618                                 ast_log(LOG_ERROR, "Unable to allocate space for more rows in this resultset.\n");
619                                 status = "MEMERROR";
620                                 goto end_acf_read;
621                         }
622                         strcpy((char *)row + sizeof(*row), buf);
623                         AST_LIST_INSERT_TAIL(resultset, row, list);
624
625                         /* Get next row */
626                         res = SQLFetch(stmt);
627                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
628                                 if (res != SQL_NO_DATA) {
629                                         ast_log(LOG_WARNING, "Error %d in FETCH [%s]\n", res, ast_str_buffer(sql));
630                                 }
631                                 /* Number of rows in the resultset */
632                                 y++;
633                                 break;
634                         }
635                 }
636         }
637
638 end_acf_read:
639         snprintf(rowcount, sizeof(rowcount), "%d", y);
640         pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
641         pbx_builtin_setvar_helper(chan, "ODBCSTATUS", status);
642         pbx_builtin_setvar_helper(chan, "~ODBCFIELDS~", ast_str_buffer(colnames));
643         if (resultset) {
644                 int uid;
645                 struct ast_datastore *odbc_store;
646                 uid = ast_atomic_fetchadd_int(&resultcount, +1) + 1;
647                 snprintf(buf, len, "%d", uid);
648                 odbc_store = ast_datastore_alloc(&odbc_info, buf);
649                 if (!odbc_store) {
650                         ast_log(LOG_ERROR, "Rows retrieved, but unable to store it in the channel.  Results fail.\n");
651                         pbx_builtin_setvar_helper(chan, "ODBCSTATUS", "MEMERROR");
652                         odbc_datastore_free(resultset);
653                         SQLCloseCursor(stmt);
654                         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
655                         ast_odbc_release_obj(obj);
656                         obj = NULL;
657                         if (chan)
658                                 ast_autoservice_stop(chan);
659                         if (bogus_chan)
660                                 ast_channel_release(chan);
661                         return -1;
662                 }
663                 odbc_store->data = resultset;
664                 ast_channel_datastore_add(chan, odbc_store);
665         }
666         SQLCloseCursor(stmt);
667         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
668         ast_odbc_release_obj(obj);
669         obj = NULL;
670         if (chan)
671                 ast_autoservice_stop(chan);
672         if (bogus_chan)
673                 ast_channel_release(chan);
674         return 0;
675 }
676
677 static int acf_escape(struct ast_channel *chan, const char *cmd, char *data, char *buf, size_t len)
678 {
679         char *out = buf;
680
681         for (; *data && out - buf < len; data++) {
682                 if (*data == '\'') {
683                         *out = '\'';
684                         out++;
685                 }
686                 *out++ = *data;
687         }
688         *out = '\0';
689
690         return 0;
691 }
692
693 static struct ast_custom_function escape_function = {
694         .name = "SQL_ESC",
695         .read = acf_escape,
696         .write = NULL,
697 };
698
699 static int acf_fetch(struct ast_channel *chan, const char *cmd, char *data, char *buf, size_t len)
700 {
701         struct ast_datastore *store;
702         struct odbc_datastore *resultset;
703         struct odbc_datastore_row *row;
704         store = ast_channel_datastore_find(chan, &odbc_info, data);
705         if (!store) {
706                 pbx_builtin_setvar_helper(chan, "ODBC_FETCH_STATUS", "FAILURE");
707                 return -1;
708         }
709         resultset = store->data;
710         AST_LIST_LOCK(resultset);
711         row = AST_LIST_REMOVE_HEAD(resultset, list);
712         AST_LIST_UNLOCK(resultset);
713         if (!row) {
714                 /* Cleanup datastore */
715                 ast_channel_datastore_remove(chan, store);
716                 ast_datastore_free(store);
717                 pbx_builtin_setvar_helper(chan, "ODBC_FETCH_STATUS", "FAILURE");
718                 return -1;
719         }
720         pbx_builtin_setvar_helper(chan, "~ODBCFIELDS~", resultset->names);
721         ast_copy_string(buf, row->data, len);
722         ast_free(row);
723         pbx_builtin_setvar_helper(chan, "ODBC_FETCH_STATUS", "SUCCESS");
724         return 0;
725 }
726
727 static struct ast_custom_function fetch_function = {
728         .name = "ODBC_FETCH",
729         .read = acf_fetch,
730         .write = NULL,
731 };
732
733 static char *app_odbcfinish = "ODBCFinish";
734
735 static int exec_odbcfinish(struct ast_channel *chan, const char *data)
736 {
737         struct ast_datastore *store = ast_channel_datastore_find(chan, &odbc_info, data);
738         if (!store) /* Already freed; no big deal. */
739                 return 0;
740         ast_channel_datastore_remove(chan, store);
741         ast_datastore_free(store);
742         return 0;
743 }
744
745 static int init_acf_query(struct ast_config *cfg, char *catg, struct acf_odbc_query **query)
746 {
747         const char *tmp;
748         int i;
749
750         if (!cfg || !catg) {
751                 return EINVAL;
752         }
753
754         *query = ast_calloc(1, sizeof(struct acf_odbc_query));
755         if (! (*query))
756                 return ENOMEM;
757
758         if (((tmp = ast_variable_retrieve(cfg, catg, "writehandle"))) || ((tmp = ast_variable_retrieve(cfg, catg, "dsn")))) {
759                 char *tmp2 = ast_strdupa(tmp);
760                 AST_DECLARE_APP_ARGS(writeconf,
761                         AST_APP_ARG(dsn)[5];
762                 );
763                 AST_STANDARD_APP_ARGS(writeconf, tmp2);
764                 for (i = 0; i < 5; i++) {
765                         if (!ast_strlen_zero(writeconf.dsn[i]))
766                                 ast_copy_string((*query)->writehandle[i], writeconf.dsn[i], sizeof((*query)->writehandle[i]));
767                 }
768         }
769
770         if ((tmp = ast_variable_retrieve(cfg, catg, "readhandle"))) {
771                 char *tmp2 = ast_strdupa(tmp);
772                 AST_DECLARE_APP_ARGS(readconf,
773                         AST_APP_ARG(dsn)[5];
774                 );
775                 AST_STANDARD_APP_ARGS(readconf, tmp2);
776                 for (i = 0; i < 5; i++) {
777                         if (!ast_strlen_zero(readconf.dsn[i]))
778                                 ast_copy_string((*query)->readhandle[i], readconf.dsn[i], sizeof((*query)->readhandle[i]));
779                 }
780         } else {
781                 /* If no separate readhandle, then use the writehandle for reading */
782                 for (i = 0; i < 5; i++) {
783                         if (!ast_strlen_zero((*query)->writehandle[i]))
784                                 ast_copy_string((*query)->readhandle[i], (*query)->writehandle[i], sizeof((*query)->readhandle[i]));
785                 }
786         }
787
788         if ((tmp = ast_variable_retrieve(cfg, catg, "readsql")))
789                 ast_copy_string((*query)->sql_read, tmp, sizeof((*query)->sql_read));
790         else if ((tmp = ast_variable_retrieve(cfg, catg, "read"))) {
791                 ast_log(LOG_WARNING, "Parameter 'read' is deprecated for category %s.  Please use 'readsql' instead.\n", catg);
792                 ast_copy_string((*query)->sql_read, tmp, sizeof((*query)->sql_read));
793         }
794
795         if (!ast_strlen_zero((*query)->sql_read) && ast_strlen_zero((*query)->readhandle[0])) {
796                 ast_free(*query);
797                 *query = NULL;
798                 ast_log(LOG_ERROR, "There is SQL, but no ODBC class to be used for reading: %s\n", catg);
799                 return EINVAL;
800         }
801
802         if ((tmp = ast_variable_retrieve(cfg, catg, "writesql")))
803                 ast_copy_string((*query)->sql_write, tmp, sizeof((*query)->sql_write));
804         else if ((tmp = ast_variable_retrieve(cfg, catg, "write"))) {
805                 ast_log(LOG_WARNING, "Parameter 'write' is deprecated for category %s.  Please use 'writesql' instead.\n", catg);
806                 ast_copy_string((*query)->sql_write, tmp, sizeof((*query)->sql_write));
807         }
808
809         if (!ast_strlen_zero((*query)->sql_write) && ast_strlen_zero((*query)->writehandle[0])) {
810                 ast_free(*query);
811                 *query = NULL;
812                 ast_log(LOG_ERROR, "There is SQL, but no ODBC class to be used for writing: %s\n", catg);
813                 return EINVAL;
814         }
815
816         if ((tmp = ast_variable_retrieve(cfg, catg, "insertsql"))) {
817                 ast_copy_string((*query)->sql_insert, tmp, sizeof((*query)->sql_insert));
818         }
819
820         /* Allow escaping of embedded commas in fields to be turned off */
821         ast_set_flag((*query), OPT_ESCAPECOMMAS);
822         if ((tmp = ast_variable_retrieve(cfg, catg, "escapecommas"))) {
823                 if (ast_false(tmp))
824                         ast_clear_flag((*query), OPT_ESCAPECOMMAS);
825         }
826
827         if ((tmp = ast_variable_retrieve(cfg, catg, "mode"))) {
828                 if (strcasecmp(tmp, "multirow") == 0)
829                         ast_set_flag((*query), OPT_MULTIROW);
830                 if ((tmp = ast_variable_retrieve(cfg, catg, "rowlimit")))
831                         sscanf(tmp, "%d", &((*query)->rowlimit));
832         }
833
834         (*query)->acf = ast_calloc(1, sizeof(struct ast_custom_function));
835         if (! (*query)->acf) {
836                 ast_free(*query);
837                 *query = NULL;
838                 return ENOMEM;
839         }
840         if (ast_string_field_init((*query)->acf, 128)) {
841                 ast_free((*query)->acf);
842                 ast_free(*query);
843                 *query = NULL;
844                 return ENOMEM;
845         }
846
847         if ((tmp = ast_variable_retrieve(cfg, catg, "prefix")) && !ast_strlen_zero(tmp)) {
848                 if (asprintf((char **)&((*query)->acf->name), "%s_%s", tmp, catg) < 0) {
849                         ast_log(LOG_WARNING, "asprintf() failed: %s\n", strerror(errno));
850                 }
851         } else {
852                 if (asprintf((char **)&((*query)->acf->name), "ODBC_%s", catg) < 0) {
853                         ast_log(LOG_WARNING, "asprintf() failed: %s\n", strerror(errno));
854                 }
855         }
856
857         if (!((*query)->acf->name)) {
858                 ast_string_field_free_memory((*query)->acf);
859                 ast_free((*query)->acf);
860                 ast_free(*query);
861                 *query = NULL;
862                 return ENOMEM;
863         }
864
865         if ((tmp = ast_variable_retrieve(cfg, catg, "syntax")) && !ast_strlen_zero(tmp)) {
866                 ast_string_field_build((*query)->acf, syntax, "%s(%s)", (*query)->acf->name, tmp);
867         } else {
868                 ast_string_field_build((*query)->acf, syntax, "%s(<arg1>[...[,<argN>]])", (*query)->acf->name);
869         }
870
871         if (ast_strlen_zero((*query)->acf->syntax)) {
872                 ast_free((char *)(*query)->acf->name);
873                 ast_string_field_free_memory((*query)->acf);
874                 ast_free((*query)->acf);
875                 ast_free(*query);
876                 *query = NULL;
877                 return ENOMEM;
878         }
879
880         if ((tmp = ast_variable_retrieve(cfg, catg, "synopsis")) && !ast_strlen_zero(tmp)) {
881                 ast_string_field_set((*query)->acf, synopsis, tmp);
882         } else {
883                 ast_string_field_set((*query)->acf, synopsis, "Runs the referenced query with the specified arguments");
884         }
885
886         if (ast_strlen_zero((*query)->acf->synopsis)) {
887                 ast_free((char *)(*query)->acf->name);
888                 ast_string_field_free_memory((*query)->acf);
889                 ast_free((*query)->acf);
890                 ast_free(*query);
891                 *query = NULL;
892                 return ENOMEM;
893         }
894
895         if (!ast_strlen_zero((*query)->sql_read) && !ast_strlen_zero((*query)->sql_write)) {
896                 ast_string_field_build((*query)->acf, desc,
897                                         "Runs the following query, as defined in func_odbc.conf, performing\n"
898                                         "substitution of the arguments into the query as specified by ${ARG1},\n"
899                                         "${ARG2}, ... ${ARGn}.  When setting the function, the values are provided\n"
900                                         "either in whole as ${VALUE} or parsed as ${VAL1}, ${VAL2}, ... ${VALn}.\n"
901                                         "%s"
902                                         "\nRead:\n%s\n\nWrite:\n%s\n%s%s%s",
903                                         ast_strlen_zero((*query)->sql_insert) ? "" :
904                                                 "If the write query affects no rows, the insert query will be\n"
905                                                 "performed.\n",
906                                         (*query)->sql_read,
907                                         (*query)->sql_write,
908                                         ast_strlen_zero((*query)->sql_insert) ? "" : "Insert:\n",
909                                         ast_strlen_zero((*query)->sql_insert) ? "" : (*query)->sql_insert,
910                                         ast_strlen_zero((*query)->sql_insert) ? "" : "\n");
911         } else if (!ast_strlen_zero((*query)->sql_read)) {
912                 ast_string_field_build((*query)->acf, desc,
913                                                 "Runs the following query, as defined in func_odbc.conf, performing\n"
914                                                 "substitution of the arguments into the query as specified by ${ARG1},\n"
915                                                 "${ARG2}, ... ${ARGn}.  This function may only be read, not set.\n\nSQL:\n%s\n",
916                                                 (*query)->sql_read);
917         } else if (!ast_strlen_zero((*query)->sql_write)) {
918                 ast_string_field_build((*query)->acf, desc,     
919                                         "Runs the following query, as defined in func_odbc.conf, performing\n"
920                                         "substitution of the arguments into the query as specified by ${ARG1},\n"
921                                         "${ARG2}, ... ${ARGn}.  The values are provided either in whole as\n"
922                                         "${VALUE} or parsed as ${VAL1}, ${VAL2}, ... ${VALn}.\n"
923                                         "This function may only be set.\n%sSQL:\n%s\n%s%s%s",
924                                         ast_strlen_zero((*query)->sql_insert) ? "" :
925                                                 "If the write query affects no rows, the insert query will be\n"
926                                                 "performed.\n",
927                                         (*query)->sql_write,
928                                         ast_strlen_zero((*query)->sql_insert) ? "" : "Insert:\n",
929                                         ast_strlen_zero((*query)->sql_insert) ? "" : (*query)->sql_insert,
930                                         ast_strlen_zero((*query)->sql_insert) ? "" : "\n");
931         } else {
932                 ast_string_field_free_memory((*query)->acf);
933                 ast_free((char *)(*query)->acf->name);
934                 ast_free((*query)->acf);
935                 ast_free(*query);
936                 ast_log(LOG_WARNING, "Section '%s' was found, but there was no SQL to execute.  Ignoring.\n", catg);
937                 return EINVAL;
938         }
939
940         if (ast_strlen_zero((*query)->acf->desc)) {
941                 ast_string_field_free_memory((*query)->acf);
942                 ast_free((char *)(*query)->acf->name);
943                 ast_free((*query)->acf);
944                 ast_free(*query);
945                 *query = NULL;
946                 return ENOMEM;
947         }
948
949         if (ast_strlen_zero((*query)->sql_read)) {
950                 (*query)->acf->read = NULL;
951         } else {
952                 (*query)->acf->read = acf_odbc_read;
953         }
954
955         if (ast_strlen_zero((*query)->sql_write)) {
956                 (*query)->acf->write = NULL;
957         } else {
958                 (*query)->acf->write = acf_odbc_write;
959         }
960
961         return 0;
962 }
963
964 static int free_acf_query(struct acf_odbc_query *query)
965 {
966         if (query) {
967                 if (query->acf) {
968                         if (query->acf->name)
969                                 ast_free((char *)query->acf->name);
970                         ast_string_field_free_memory(query->acf);
971                         ast_free(query->acf);
972                 }
973                 ast_free(query);
974         }
975         return 0;
976 }
977
978 static char *cli_odbc_read(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
979 {
980         AST_DECLARE_APP_ARGS(args,
981                 AST_APP_ARG(field)[100];
982         );
983         struct ast_str *sql;
984         char *char_args, varname[10];
985         struct acf_odbc_query *query;
986         struct ast_channel *chan;
987         int i;
988
989         switch (cmd) {
990         case CLI_INIT:
991                 e->command = "odbc read";
992                 e->usage =
993                         "Usage: odbc read <name> <args> [exec]\n"
994                         "       Evaluates the SQL provided in the ODBC function <name>, and\n"
995                         "       optionally executes the function.  This function is intended for\n"
996                         "       testing purposes.  Remember to quote arguments containing spaces.\n";
997                 return NULL;
998         case CLI_GENERATE:
999                 if (a->pos == 2) {
1000                         int wordlen = strlen(a->word), which = 0;
1001                         /* Complete function name */
1002                         AST_RWLIST_RDLOCK(&queries);
1003                         AST_RWLIST_TRAVERSE(&queries, query, list) {
1004                                 if (!strncasecmp(query->acf->name, a->word, wordlen)) {
1005                                         if (++which > a->n) {
1006                                                 char *res = ast_strdup(query->acf->name);
1007                                                 AST_RWLIST_UNLOCK(&queries);
1008                                                 return res;
1009                                         }
1010                                 }
1011                         }
1012                         AST_RWLIST_UNLOCK(&queries);
1013                         return NULL;
1014                 } else if (a->pos == 4) {
1015                         return a->n == 0 ? ast_strdup("exec") : NULL;
1016                 } else {
1017                         return NULL;
1018                 }
1019         }
1020
1021         if (a->argc < 4 || a->argc > 5) {
1022                 return CLI_SHOWUSAGE;
1023         }
1024
1025         sql = ast_str_thread_get(&sql_buf, 16);
1026         if (!sql) {
1027                 return CLI_FAILURE;
1028         }
1029
1030         AST_RWLIST_RDLOCK(&queries);
1031         AST_RWLIST_TRAVERSE(&queries, query, list) {
1032                 if (!strcmp(query->acf->name, a->argv[2])) {
1033                         break;
1034                 }
1035         }
1036
1037         if (!query) {
1038                 ast_cli(a->fd, "No such query '%s'\n", a->argv[2]);
1039                 AST_RWLIST_UNLOCK(&queries);
1040                 return CLI_SHOWUSAGE;
1041         }
1042
1043         if (ast_strlen_zero(query->sql_read)) {
1044                 ast_cli(a->fd, "The function %s has no writesql parameter.\n", a->argv[2]);
1045                 AST_RWLIST_UNLOCK(&queries);
1046                 return CLI_SUCCESS;
1047         }
1048
1049         ast_str_make_space(&sql, strlen(query->sql_read) * 2 + 300);
1050
1051         /* Evaluate function */
1052         char_args = ast_strdupa(a->argv[3]);
1053
1054         chan = ast_dummy_channel_alloc();
1055
1056         AST_STANDARD_APP_ARGS(args, char_args);
1057         for (i = 0; i < args.argc; i++) {
1058                 snprintf(varname, sizeof(varname), "ARG%d", i + 1);
1059                 pbx_builtin_pushvar_helper(chan, varname, args.field[i]);
1060         }
1061
1062         ast_str_substitute_variables(&sql, 0, chan, query->sql_read);
1063         chan = ast_channel_release(chan);
1064
1065         if (a->argc == 5 && !strcmp(a->argv[4], "exec")) {
1066                 /* Execute the query */
1067                 struct odbc_obj *obj = NULL;
1068                 int dsn, executed = 0;
1069                 SQLHSTMT stmt;
1070                 int rows = 0, res, x;
1071                 SQLSMALLINT colcount = 0, collength;
1072                 SQLLEN indicator;
1073                 struct ast_str *coldata = ast_str_thread_get(&coldata_buf, 16);
1074                 char colname[256];
1075                 SQLULEN maxcol;
1076
1077                 for (dsn = 0; dsn < 5; dsn++) {
1078                         if (ast_strlen_zero(query->readhandle[dsn])) {
1079                                 continue;
1080                         }
1081                         ast_debug(1, "Found handle %s\n", query->readhandle[dsn]);
1082                         if (!(obj = ast_odbc_request_obj(query->readhandle[dsn], 0))) {
1083                                 continue;
1084                         }
1085
1086                         ast_debug(1, "Got obj\n");
1087                         if (!(stmt = ast_odbc_direct_execute(obj, generic_execute, ast_str_buffer(sql)))) {
1088                                 ast_odbc_release_obj(obj);
1089                                 obj = NULL;
1090                                 continue;
1091                         }
1092
1093                         executed = 1;
1094
1095                         res = SQLNumResultCols(stmt, &colcount);
1096                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
1097                                 ast_cli(a->fd, "SQL Column Count error!\n[%s]\n\n", ast_str_buffer(sql));
1098                                 SQLCloseCursor(stmt);
1099                                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
1100                                 ast_odbc_release_obj(obj);
1101                                 obj = NULL;
1102                                 AST_RWLIST_UNLOCK(&queries);
1103                                 return CLI_SUCCESS;
1104                         }
1105
1106                         res = SQLFetch(stmt);
1107                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
1108                                 SQLCloseCursor(stmt);
1109                                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
1110                                 ast_odbc_release_obj(obj);
1111                                 obj = NULL;
1112                                 if (res == SQL_NO_DATA) {
1113                                         ast_cli(a->fd, "Returned %d rows.  Query executed on handle %d:%s [%s]\n", rows, dsn, query->readhandle[dsn], ast_str_buffer(sql));
1114                                         break;
1115                                 } else {
1116                                         ast_cli(a->fd, "Error %d in FETCH [%s]\n", res, ast_str_buffer(sql));
1117                                 }
1118                                 AST_RWLIST_UNLOCK(&queries);
1119                                 return CLI_SUCCESS;
1120                         }
1121                         for (;;) {
1122                                 for (x = 0; x < colcount; x++) {
1123                                         res = SQLDescribeCol(stmt, x + 1, (unsigned char *)colname, sizeof(colname), &collength, NULL, &maxcol, NULL, NULL);
1124                                         if (((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) || collength == 0) {
1125                                                 snprintf(colname, sizeof(colname), "field%d", x);
1126                                         }
1127
1128                                         res = ast_odbc_ast_str_SQLGetData(&coldata, maxcol, stmt, x + 1, SQL_CHAR, &indicator);
1129                                         if (indicator == SQL_NULL_DATA) {
1130                                                 ast_str_set(&coldata, 0, "(nil)");
1131                                                 res = SQL_SUCCESS;
1132                                         }
1133
1134                                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
1135                                                 ast_cli(a->fd, "SQL Get Data error %d!\n[%s]\n\n", res, ast_str_buffer(sql));
1136                                                 SQLCloseCursor(stmt);
1137                                                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
1138                                                 ast_odbc_release_obj(obj);
1139                                                 obj = NULL;
1140                                                 AST_RWLIST_UNLOCK(&queries);
1141                                                 return CLI_SUCCESS;
1142                                         }
1143
1144                                         ast_cli(a->fd, "%-20.20s  %s\n", colname, ast_str_buffer(coldata));
1145                                 }
1146                                 rows++;
1147
1148                                 /* Get next row */
1149                                 res = SQLFetch(stmt);
1150                                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
1151                                         break;
1152                                 }
1153                                 ast_cli(a->fd, "%-20.20s  %s\n", "----------", "----------");
1154                         }
1155                         SQLCloseCursor(stmt);
1156                         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
1157                         ast_odbc_release_obj(obj);
1158                         obj = NULL;
1159                         ast_cli(a->fd, "Returned %d row%s.  Query executed on handle %d [%s]\n", rows, rows == 1 ? "" : "s", dsn, query->readhandle[dsn]);
1160                         break;
1161                 }
1162                 if (obj) {
1163                         ast_odbc_release_obj(obj);
1164                         obj = NULL;
1165                 }
1166
1167                 if (!executed) {
1168                         ast_cli(a->fd, "Failed to execute query. [%s]\n", ast_str_buffer(sql));
1169                 }
1170         } else { /* No execution, just print out the resulting SQL */
1171                 ast_cli(a->fd, "%s\n", ast_str_buffer(sql));
1172         }
1173         AST_RWLIST_UNLOCK(&queries);
1174         return CLI_SUCCESS;
1175 }
1176
1177 static char *cli_odbc_write(struct ast_cli_entry *e, int cmd, struct ast_cli_args *a)
1178 {
1179         AST_DECLARE_APP_ARGS(values,
1180                 AST_APP_ARG(field)[100];
1181         );
1182         AST_DECLARE_APP_ARGS(args,
1183                 AST_APP_ARG(field)[100];
1184         );
1185         struct ast_str *sql;
1186         char *char_args, *char_values, varname[10];
1187         struct acf_odbc_query *query;
1188         struct ast_channel *chan;
1189         int i;
1190
1191         switch (cmd) {
1192         case CLI_INIT:
1193                 e->command = "odbc write";
1194                 e->usage =
1195                         "Usage: odbc write <name> <args> <value> [exec]\n"
1196                         "       Evaluates the SQL provided in the ODBC function <name>, and\n"
1197                         "       optionally executes the function.  This function is intended for\n"
1198                         "       testing purposes.  Remember to quote arguments containing spaces.\n";
1199                 return NULL;
1200         case CLI_GENERATE:
1201                 if (a->pos == 2) {
1202                         int wordlen = strlen(a->word), which = 0;
1203                         /* Complete function name */
1204                         AST_RWLIST_RDLOCK(&queries);
1205                         AST_RWLIST_TRAVERSE(&queries, query, list) {
1206                                 if (!strncasecmp(query->acf->name, a->word, wordlen)) {
1207                                         if (++which > a->n) {
1208                                                 char *res = ast_strdup(query->acf->name);
1209                                                 AST_RWLIST_UNLOCK(&queries);
1210                                                 return res;
1211                                         }
1212                                 }
1213                         }
1214                         AST_RWLIST_UNLOCK(&queries);
1215                         return NULL;
1216                 } else if (a->pos == 5) {
1217                         return a->n == 0 ? ast_strdup("exec") : NULL;
1218                 } else {
1219                         return NULL;
1220                 }
1221         }
1222
1223         if (a->argc < 5 || a->argc > 6) {
1224                 return CLI_SHOWUSAGE;
1225         }
1226
1227         sql = ast_str_thread_get(&sql_buf, 16);
1228         if (!sql) {
1229                 return CLI_FAILURE;
1230         }
1231
1232         AST_RWLIST_RDLOCK(&queries);
1233         AST_RWLIST_TRAVERSE(&queries, query, list) {
1234                 if (!strcmp(query->acf->name, a->argv[2])) {
1235                         break;
1236                 }
1237         }
1238
1239         if (!query) {
1240                 ast_cli(a->fd, "No such query '%s'\n", a->argv[2]);
1241                 AST_RWLIST_UNLOCK(&queries);
1242                 return CLI_SHOWUSAGE;
1243         }
1244
1245         if (ast_strlen_zero(query->sql_write)) {
1246                 ast_cli(a->fd, "The function %s has no writesql parameter.\n", a->argv[2]);
1247                 AST_RWLIST_UNLOCK(&queries);
1248                 return CLI_SUCCESS;
1249         }
1250
1251         ast_str_make_space(&sql, strlen(query->sql_write) * 2 + 300);
1252
1253         /* Evaluate function */
1254         char_args = ast_strdupa(a->argv[3]);
1255         char_values = ast_strdupa(a->argv[4]);
1256
1257         chan = ast_dummy_channel_alloc();
1258
1259         AST_STANDARD_APP_ARGS(args, char_args);
1260         for (i = 0; i < args.argc; i++) {
1261                 snprintf(varname, sizeof(varname), "ARG%d", i + 1);
1262                 pbx_builtin_pushvar_helper(chan, varname, args.field[i]);
1263         }
1264
1265         /* Parse values, just like arguments */
1266         AST_STANDARD_APP_ARGS(values, char_values);
1267         for (i = 0; i < values.argc; i++) {
1268                 snprintf(varname, sizeof(varname), "VAL%d", i + 1);
1269                 pbx_builtin_pushvar_helper(chan, varname, values.field[i]);
1270         }
1271
1272         /* Additionally set the value as a whole (but push an empty string if value is NULL) */
1273         pbx_builtin_pushvar_helper(chan, "VALUE", S_OR(a->argv[4], ""));
1274         ast_str_substitute_variables(&sql, 0, chan, query->sql_write);
1275         ast_debug(1, "SQL is %s\n", ast_str_buffer(sql));
1276         chan = ast_channel_release(chan);
1277
1278         if (a->argc == 6 && !strcmp(a->argv[5], "exec")) {
1279                 /* Execute the query */
1280                 struct odbc_obj *obj = NULL;
1281                 int dsn, executed = 0;
1282                 SQLHSTMT stmt;
1283                 SQLLEN rows = -1;
1284
1285                 for (dsn = 0; dsn < 5; dsn++) {
1286                         if (ast_strlen_zero(query->writehandle[dsn])) {
1287                                 continue;
1288                         }
1289                         if (!(obj = ast_odbc_request_obj(query->writehandle[dsn], 0))) {
1290                                 continue;
1291                         }
1292                         if (!(stmt = ast_odbc_direct_execute(obj, generic_execute, ast_str_buffer(sql)))) {
1293                                 ast_odbc_release_obj(obj);
1294                                 obj = NULL;
1295                                 continue;
1296                         }
1297
1298                         SQLRowCount(stmt, &rows);
1299                         SQLCloseCursor(stmt);
1300                         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
1301                         ast_odbc_release_obj(obj);
1302                         obj = NULL;
1303                         ast_cli(a->fd, "Affected %d rows.  Query executed on handle %d [%s]\n", (int)rows, dsn, query->writehandle[dsn]);
1304                         executed = 1;
1305                         break;
1306                 }
1307
1308                 if (!executed) {
1309                         ast_cli(a->fd, "Failed to execute query.\n");
1310                 }
1311         } else { /* No execution, just print out the resulting SQL */
1312                 ast_cli(a->fd, "%s\n", ast_str_buffer(sql));
1313         }
1314         AST_RWLIST_UNLOCK(&queries);
1315         return CLI_SUCCESS;
1316 }
1317
1318 static struct ast_cli_entry cli_func_odbc[] = {
1319         AST_CLI_DEFINE(cli_odbc_write, "Test setting a func_odbc function"),
1320         AST_CLI_DEFINE(cli_odbc_read, "Test reading a func_odbc function"),
1321 };
1322
1323 static int load_module(void)
1324 {
1325         int res = 0;
1326         struct ast_config *cfg;
1327         char *catg;
1328         struct ast_flags config_flags = { 0 };
1329
1330         res |= ast_custom_function_register(&fetch_function);
1331         res |= ast_register_application_xml(app_odbcfinish, exec_odbcfinish);
1332         AST_RWLIST_WRLOCK(&queries);
1333
1334         cfg = ast_config_load(config, config_flags);
1335         if (!cfg || cfg == CONFIG_STATUS_FILEINVALID) {
1336                 ast_log(LOG_NOTICE, "Unable to load config for func_odbc: %s\n", config);
1337                 AST_RWLIST_UNLOCK(&queries);
1338                 return AST_MODULE_LOAD_DECLINE;
1339         }
1340
1341         for (catg = ast_category_browse(cfg, NULL);
1342              catg;
1343              catg = ast_category_browse(cfg, catg)) {
1344                 struct acf_odbc_query *query = NULL;
1345                 int err;
1346
1347                 if ((err = init_acf_query(cfg, catg, &query))) {
1348                         if (err == ENOMEM)
1349                                 ast_log(LOG_ERROR, "Out of memory\n");
1350                         else if (err == EINVAL)
1351                                 ast_log(LOG_ERROR, "Invalid parameters for category %s\n", catg);
1352                         else
1353                                 ast_log(LOG_ERROR, "%s (%d)\n", strerror(err), err);
1354                 } else {
1355                         AST_RWLIST_INSERT_HEAD(&queries, query, list);
1356                         ast_custom_function_register(query->acf);
1357                 }
1358         }
1359
1360         ast_config_destroy(cfg);
1361         res |= ast_custom_function_register(&escape_function);
1362         ast_cli_register_multiple(cli_func_odbc, ARRAY_LEN(cli_func_odbc));
1363
1364         AST_RWLIST_UNLOCK(&queries);
1365         return res;
1366 }
1367
1368 static int unload_module(void)
1369 {
1370         struct acf_odbc_query *query;
1371         int res = 0;
1372
1373         AST_RWLIST_WRLOCK(&queries);
1374         while (!AST_RWLIST_EMPTY(&queries)) {
1375                 query = AST_RWLIST_REMOVE_HEAD(&queries, list);
1376                 ast_custom_function_unregister(query->acf);
1377                 free_acf_query(query);
1378         }
1379
1380         res |= ast_custom_function_unregister(&escape_function);
1381         res |= ast_custom_function_unregister(&fetch_function);
1382         res |= ast_unregister_application(app_odbcfinish);
1383         ast_cli_unregister_multiple(cli_func_odbc, ARRAY_LEN(cli_func_odbc));
1384
1385         /* Allow any threads waiting for this lock to pass (avoids a race) */
1386         AST_RWLIST_UNLOCK(&queries);
1387         usleep(1);
1388         AST_RWLIST_WRLOCK(&queries);
1389
1390         AST_RWLIST_UNLOCK(&queries);
1391         return 0;
1392 }
1393
1394 static int reload(void)
1395 {
1396         int res = 0;
1397         struct ast_config *cfg;
1398         struct acf_odbc_query *oldquery;
1399         char *catg;
1400         struct ast_flags config_flags = { CONFIG_FLAG_FILEUNCHANGED };
1401
1402         cfg = ast_config_load(config, config_flags);
1403         if (cfg == CONFIG_STATUS_FILEUNCHANGED || cfg == CONFIG_STATUS_FILEINVALID)
1404                 return 0;
1405
1406         AST_RWLIST_WRLOCK(&queries);
1407
1408         while (!AST_RWLIST_EMPTY(&queries)) {
1409                 oldquery = AST_RWLIST_REMOVE_HEAD(&queries, list);
1410                 ast_custom_function_unregister(oldquery->acf);
1411                 free_acf_query(oldquery);
1412         }
1413
1414         if (!cfg) {
1415                 ast_log(LOG_WARNING, "Unable to load config for func_odbc: %s\n", config);
1416                 goto reload_out;
1417         }
1418
1419         for (catg = ast_category_browse(cfg, NULL);
1420              catg;
1421              catg = ast_category_browse(cfg, catg)) {
1422                 struct acf_odbc_query *query = NULL;
1423
1424                 if (init_acf_query(cfg, catg, &query)) {
1425                         ast_log(LOG_ERROR, "Cannot initialize query %s\n", catg);
1426                 } else {
1427                         AST_RWLIST_INSERT_HEAD(&queries, query, list);
1428                         ast_custom_function_register(query->acf);
1429                 }
1430         }
1431
1432         ast_config_destroy(cfg);
1433 reload_out:
1434         AST_RWLIST_UNLOCK(&queries);
1435         return res;
1436 }
1437
1438 /* XXX need to revise usecount - set if query_lock is set */
1439
1440 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_DEFAULT, "ODBC lookups",
1441                 .load = load_module,
1442                 .unload = unload_module,
1443                 .reload = reload,
1444                );
1445