Merged revisions 277568 via svnmerge from
[asterisk/asterisk.git] / res / res_config_odbc.c
1 /*
2  * Asterisk -- An open source telephony toolkit.
3  *
4  * Copyright (C) 1999 - 2010, Digium, Inc.
5  *
6  * Mark Spencer <markster@digium.com>
7  *
8  * Copyright (C) 2004 - 2005 Anthony Minessale II <anthmct@yahoo.com>
9  *
10  * See http://www.asterisk.org for more information about
11  * the Asterisk project. Please do not directly contact
12  * any of the maintainers of this project for assistance;
13  * the project provides a web site, mailing lists and IRC
14  * channels for your use.
15  *
16  * This program is free software, distributed under the terms of
17  * the GNU General Public License Version 2. See the LICENSE file
18  * at the top of the source tree.
19  */
20
21 /*! \file
22  *
23  * \brief odbc+odbc plugin for portable configuration engine
24  *
25  * \author Mark Spencer <markster@digium.com>
26  * \author Anthony Minessale II <anthmct@yahoo.com>
27  *
28  * \arg http://www.unixodbc.org
29  */
30
31 /*** MODULEINFO
32         <depend>res_odbc</depend>
33  ***/
34
35 #include "asterisk.h"
36
37 ASTERISK_FILE_VERSION(__FILE__, "$Revision$")
38
39 #include "asterisk/file.h"
40 #include "asterisk/channel.h"
41 #include "asterisk/pbx.h"
42 #include "asterisk/config.h"
43 #include "asterisk/module.h"
44 #include "asterisk/lock.h"
45 #include "asterisk/res_odbc.h"
46 #include "asterisk/utils.h"
47 #include "asterisk/stringfields.h"
48
49 AST_THREADSTORAGE(sql_buf);
50
51 struct custom_prepare_struct {
52         const char *sql;
53         const char *extra;
54         AST_DECLARE_STRING_FIELDS(
55                 AST_STRING_FIELD(encoding)[256];
56         );
57         va_list ap;
58         unsigned long long skip;
59 };
60
61 static void decode_chunk(char *chunk)
62 {
63         for (; *chunk; chunk++) {
64                 if (*chunk == '^' && strchr("0123456789ABCDEFabcdef", chunk[1]) && strchr("0123456789ABCDEFabcdef", chunk[2])) {
65                         sscanf(chunk + 1, "%02hhd", chunk);
66                         memmove(chunk + 1, chunk + 3, strlen(chunk + 3) + 1);
67                 }
68         }
69 }
70
71 static SQLHSTMT custom_prepare(struct odbc_obj *obj, void *data)
72 {
73         int res, x = 1, count = 0;
74         struct custom_prepare_struct *cps = data;
75         const char *newparam, *newval;
76         char encodebuf[1024];
77         SQLHSTMT stmt;
78         va_list ap;
79
80         va_copy(ap, cps->ap);
81
82         res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &stmt);
83         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
84                 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
85                 return NULL;
86         }
87
88         ast_debug(1, "Skip: %lld; SQL: %s\n", cps->skip, cps->sql);
89
90         res = SQLPrepare(stmt, (unsigned char *)cps->sql, SQL_NTS);
91         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
92                 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", cps->sql);
93                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
94                 return NULL;
95         }
96
97         while ((newparam = va_arg(ap, const char *))) {
98                 newval = va_arg(ap, const char *);
99                 if ((1LL << count++) & cps->skip) {
100                         ast_debug(1, "Skipping field '%s'='%s' (%llo/%llo)\n", newparam, newval, 1LL << (count - 1), cps->skip);
101                         continue;
102                 }
103                 ast_debug(1, "Parameter %d ('%s') = '%s'\n", x, newparam, newval);
104                 if (strchr(newval, ';') || strchr(newval, '^')) {
105                         char *eptr = encodebuf;
106                         const char *vptr = newval;
107                         for (; *vptr && eptr < encodebuf + sizeof(encodebuf); vptr++) {
108                                 if (strchr("^;", *vptr)) {
109                                         /* We use ^XX, instead of %XX because '%' is a special character in SQL */
110                                         snprintf(eptr, encodebuf + sizeof(encodebuf) - eptr, "^%02hhX", *vptr);
111                                         eptr += 3;
112                                         vptr++;
113                                 } else {
114                                         *eptr++ = *vptr++;
115                                 }
116                         }
117                         if (eptr < encodebuf + sizeof(encodebuf)) {
118                                 *eptr = '\0';
119                         } else {
120                                 encodebuf[sizeof(encodebuf) - 1] = '\0';
121                         }
122                         ast_string_field_set(cps, encoding[x], encodebuf);
123                         newval = cps->encoding[x];
124                 }
125                 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
126         }
127         va_end(ap);
128
129         if (!ast_strlen_zero(cps->extra))
130                 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(cps->extra), 0, (void *)cps->extra, 0, NULL);
131         return stmt;
132 }
133
134 /*!
135  * \brief Excute an SQL query and return ast_variable list
136  * \param database
137  * \param table
138  * \param ap list containing one or more field/operator/value set.
139  *
140  * Select database and preform query on table, prepare the sql statement
141  * Sub-in the values to the prepared statement and execute it. Return results
142  * as a ast_variable list.
143  *
144  * \retval var on success
145  * \retval NULL on failure
146 */
147 static struct ast_variable *realtime_odbc(const char *database, const char *table, va_list ap)
148 {
149         struct odbc_obj *obj;
150         SQLHSTMT stmt;
151         char sql[1024];
152         char coltitle[256];
153         char rowdata[2048];
154         char *op;
155         const char *newparam, *newval;
156         char *stringp;
157         char *chunk;
158         SQLSMALLINT collen;
159         int res;
160         int x;
161         struct ast_variable *var=NULL, *prev=NULL;
162         SQLULEN colsize;
163         SQLSMALLINT colcount=0;
164         SQLSMALLINT datatype;
165         SQLSMALLINT decimaldigits;
166         SQLSMALLINT nullable;
167         SQLLEN indicator;
168         va_list aq;
169         struct custom_prepare_struct cps = { .sql = sql };
170
171         if (ast_string_field_init(&cps, 256)) {
172                 return NULL;
173         }
174         va_copy(cps.ap, ap);
175         va_copy(aq, ap);
176
177         if (!table) {
178                 ast_string_field_free_memory(&cps);
179                 return NULL;
180         }
181
182         obj = ast_odbc_request_obj(database, 0);
183
184         if (!obj) {
185                 ast_log(LOG_ERROR, "No database handle available with the name of '%s' (check res_odbc.conf)\n", database);
186                 ast_string_field_free_memory(&cps);
187                 return NULL;
188         }
189
190         newparam = va_arg(aq, const char *);
191         if (!newparam) {
192                 ast_odbc_release_obj(obj);
193                 ast_string_field_free_memory(&cps);
194                 return NULL;
195         }
196         newval = va_arg(aq, const char *);
197         op = !strchr(newparam, ' ') ? " =" : "";
198         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op,
199                 strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
200         while((newparam = va_arg(aq, const char *))) {
201                 op = !strchr(newparam, ' ') ? " =" : "";
202                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
203                         strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
204                 newval = va_arg(aq, const char *);
205         }
206         va_end(aq);
207
208         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
209
210         if (!stmt) {
211                 ast_odbc_release_obj(obj);
212                 ast_string_field_free_memory(&cps);
213                 return NULL;
214         }
215
216         res = SQLNumResultCols(stmt, &colcount);
217         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
218                 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
219                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
220                 ast_odbc_release_obj(obj);
221                 ast_string_field_free_memory(&cps);
222                 return NULL;
223         }
224
225         res = SQLFetch(stmt);
226         if (res == SQL_NO_DATA) {
227                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
228                 ast_odbc_release_obj(obj);
229                 ast_string_field_free_memory(&cps);
230                 return NULL;
231         }
232         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
233                 ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
234                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
235                 ast_odbc_release_obj(obj);
236                 ast_string_field_free_memory(&cps);
237                 return NULL;
238         }
239         for (x = 0; x < colcount; x++) {
240                 rowdata[0] = '\0';
241                 collen = sizeof(coltitle);
242                 res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen, 
243                                         &datatype, &colsize, &decimaldigits, &nullable);
244                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
245                         ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
246                         if (var)
247                                 ast_variables_destroy(var);
248                         ast_odbc_release_obj(obj);
249                         ast_string_field_free_memory(&cps);
250                         return NULL;
251                 }
252
253                 indicator = 0;
254                 res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
255                 if (indicator == SQL_NULL_DATA)
256                         rowdata[0] = '\0';
257                 else if (ast_strlen_zero(rowdata)) {
258                         /* Because we encode the empty string for a NULL, we will encode
259                          * actual empty strings as a string containing a single whitespace. */
260                         ast_copy_string(rowdata, " ", sizeof(rowdata));
261                 }
262
263                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
264                         ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
265                         if (var)
266                                 ast_variables_destroy(var);
267                         ast_odbc_release_obj(obj);
268                         return NULL;
269                 }
270                 stringp = rowdata;
271                 while (stringp) {
272                         chunk = strsep(&stringp, ";");
273                         if (!ast_strlen_zero(ast_strip(chunk))) {
274                                 if (strchr(chunk, '^')) {
275                                         decode_chunk(chunk);
276                                 }
277                                 if (prev) {
278                                         prev->next = ast_variable_new(coltitle, chunk, "");
279                                         if (prev->next) {
280                                                 prev = prev->next;
281                                         }
282                                 } else {
283                                         prev = var = ast_variable_new(coltitle, chunk, "");
284                                 }
285                         }
286                 }
287         }
288
289
290         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
291         ast_odbc_release_obj(obj);
292         ast_string_field_free_memory(&cps);
293         return var;
294 }
295
296 /*!
297  * \brief Excute an Select query and return ast_config list
298  * \param database
299  * \param table
300  * \param ap list containing one or more field/operator/value set.
301  *
302  * Select database and preform query on table, prepare the sql statement
303  * Sub-in the values to the prepared statement and execute it. 
304  * Execute this prepared query against several ODBC connected databases.
305  * Return results as an ast_config variable.
306  *
307  * \retval var on success
308  * \retval NULL on failure
309 */
310 static struct ast_config *realtime_multi_odbc(const char *database, const char *table, va_list ap)
311 {
312         struct odbc_obj *obj;
313         SQLHSTMT stmt;
314         char sql[1024];
315         char coltitle[256];
316         char rowdata[2048];
317         const char *initfield=NULL;
318         char *op;
319         const char *newparam, *newval;
320         char *stringp;
321         char *chunk;
322         SQLSMALLINT collen;
323         int res;
324         int x;
325         struct ast_variable *var=NULL;
326         struct ast_config *cfg=NULL;
327         struct ast_category *cat=NULL;
328         SQLULEN colsize;
329         SQLSMALLINT colcount=0;
330         SQLSMALLINT datatype;
331         SQLSMALLINT decimaldigits;
332         SQLSMALLINT nullable;
333         SQLLEN indicator;
334         struct custom_prepare_struct cps = { .sql = sql };
335         va_list aq;
336
337         if (!table || ast_string_field_init(&cps, 256)) {
338                 return NULL;
339         }
340         va_copy(cps.ap, ap);
341         va_copy(aq, ap);
342
343
344         obj = ast_odbc_request_obj(database, 0);
345         if (!obj) {
346                 ast_string_field_free_memory(&cps);
347                 return NULL;
348         }
349
350         newparam = va_arg(aq, const char *);
351         if (!newparam)  {
352                 ast_odbc_release_obj(obj);
353                 ast_string_field_free_memory(&cps);
354                 return NULL;
355         }
356         initfield = ast_strdupa(newparam);
357         if ((op = strchr(initfield, ' '))) 
358                 *op = '\0';
359         newval = va_arg(aq, const char *);
360         op = !strchr(newparam, ' ') ? " =" : "";
361         snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op,
362                 strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
363         while((newparam = va_arg(aq, const char *))) {
364                 op = !strchr(newparam, ' ') ? " =" : "";
365                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
366                         strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
367                 newval = va_arg(aq, const char *);
368         }
369         if (initfield)
370                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
371         va_end(aq);
372
373         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
374
375         if (!stmt) {
376                 ast_odbc_release_obj(obj);
377                 ast_string_field_free_memory(&cps);
378                 return NULL;
379         }
380
381         res = SQLNumResultCols(stmt, &colcount);
382         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
383                 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
384                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
385                 ast_odbc_release_obj(obj);
386                 ast_string_field_free_memory(&cps);
387                 return NULL;
388         }
389
390         cfg = ast_config_new();
391         if (!cfg) {
392                 ast_log(LOG_WARNING, "Out of memory!\n");
393                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
394                 ast_odbc_release_obj(obj);
395                 ast_string_field_free_memory(&cps);
396                 return NULL;
397         }
398
399         while ((res=SQLFetch(stmt)) != SQL_NO_DATA) {
400                 var = NULL;
401                 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
402                         ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
403                         continue;
404                 }
405                 cat = ast_category_new("","",99999);
406                 if (!cat) {
407                         ast_log(LOG_WARNING, "Out of memory!\n");
408                         continue;
409                 }
410                 for (x=0;x<colcount;x++) {
411                         rowdata[0] = '\0';
412                         collen = sizeof(coltitle);
413                         res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen, 
414                                                 &datatype, &colsize, &decimaldigits, &nullable);
415                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
416                                 ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
417                                 ast_category_destroy(cat);
418                                 continue;
419                         }
420
421                         indicator = 0;
422                         res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
423                         if (indicator == SQL_NULL_DATA)
424                                 continue;
425
426                         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
427                                 ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
428                                 ast_category_destroy(cat);
429                                 continue;
430                         }
431                         stringp = rowdata;
432                         while (stringp) {
433                                 chunk = strsep(&stringp, ";");
434                                 if (!ast_strlen_zero(ast_strip(chunk))) {
435                                         if (strchr(chunk, '^')) {
436                                                 decode_chunk(chunk);
437                                         }
438                                         if (initfield && !strcmp(initfield, coltitle)) {
439                                                 ast_category_rename(cat, chunk);
440                                         }
441                                         var = ast_variable_new(coltitle, chunk, "");
442                                         ast_variable_append(cat, var);
443                                 }
444                         }
445                 }
446                 ast_category_append(cfg, cat);
447         }
448
449         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
450         ast_odbc_release_obj(obj);
451         ast_string_field_free_memory(&cps);
452         return cfg;
453 }
454
455 /*!
456  * \brief Excute an UPDATE query
457  * \param database
458  * \param table
459  * \param keyfield where clause field
460  * \param lookup value of field for where clause
461  * \param ap list containing one or more field/value set(s).
462  *
463  * Update a database table, prepare the sql statement using keyfield and lookup
464  * control the number of records to change. All values to be changed are stored in ap list.
465  * Sub-in the values to the prepared statement and execute it.
466  *
467  * \retval number of rows affected
468  * \retval -1 on failure
469 */
470 static int update_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
471 {
472         struct odbc_obj *obj;
473         SQLHSTMT stmt;
474         char sql[256];
475         SQLLEN rowcount=0;
476         const char *newparam, *newval;
477         int res, count = 1;
478         va_list aq;
479         struct custom_prepare_struct cps = { .sql = sql, .extra = lookup };
480         struct odbc_cache_tables *tableptr;
481         struct odbc_cache_columns *column;
482
483         if (!table) {
484                 return -1;
485         }
486
487         va_copy(cps.ap, ap);
488         va_copy(aq, ap);
489
490         if (ast_string_field_init(&cps, 256)) {
491                 return -1;
492         }
493
494         tableptr = ast_odbc_find_table(database, table);
495         if (!(obj = ast_odbc_request_obj(database, 0))) {
496                 ast_odbc_release_table(tableptr);
497                 ast_string_field_free_memory(&cps);
498                 return -1;
499         }
500
501         newparam = va_arg(aq, const char *);
502         if (!newparam)  {
503                 ast_odbc_release_obj(obj);
504                 ast_odbc_release_table(tableptr);
505                 ast_string_field_free_memory(&cps);
506                 return -1;
507         }
508         newval = va_arg(aq, const char *);
509
510         if (tableptr && !(column = ast_odbc_find_column(tableptr, newparam))) {
511                 ast_log(LOG_WARNING, "Key field '%s' does not exist in table '%s@%s'.  Update will fail\n", newparam, table, database);
512         }
513
514         snprintf(sql, sizeof(sql), "UPDATE %s SET %s=?", table, newparam);
515         while((newparam = va_arg(aq, const char *))) {
516                 newval = va_arg(aq, const char *);
517                 if ((tableptr && (column = ast_odbc_find_column(tableptr, newparam))) || count > 63) {
518                         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s=?", newparam);
519                 } else { /* the column does not exist in the table */
520                         cps.skip |= (1LL << count);
521                 }
522                 count++;
523         }
524         va_end(aq);
525         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s=?", keyfield);
526         ast_odbc_release_table(tableptr);
527
528         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
529
530         if (!stmt) {
531                 ast_odbc_release_obj(obj);
532                 ast_string_field_free_memory(&cps);
533                 return -1;
534         }
535
536         res = SQLRowCount(stmt, &rowcount);
537         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
538         ast_odbc_release_obj(obj);
539         ast_string_field_free_memory(&cps);
540
541         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
542                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
543                 return -1;
544         }
545
546         if (rowcount >= 0) {
547                 return (int) rowcount;
548         }
549
550         return -1;
551 }
552
553 struct update2_prepare_struct {
554         const char *database;
555         const char *table;
556         va_list ap;
557 };
558
559 static SQLHSTMT update2_prepare(struct odbc_obj *obj, void *data)
560 {
561         int res, x = 1, first = 1;
562         struct update2_prepare_struct *ups = data;
563         const char *newparam, *newval;
564         struct ast_str *sql = ast_str_thread_get(&sql_buf, 16);
565         SQLHSTMT stmt;
566         va_list ap;
567         struct odbc_cache_tables *tableptr = ast_odbc_find_table(ups->database, ups->table);
568         struct odbc_cache_columns *column;
569
570         if (!sql) {
571                 if (tableptr) {
572                         ast_odbc_release_table(tableptr);
573                 }
574                 return NULL;
575         }
576
577         if (!tableptr) {
578                 ast_log(LOG_ERROR, "Could not retrieve metadata for table '%s@%s'.  Update will fail!\n", ups->table, ups->database);
579                 return NULL;
580         }
581
582         res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &stmt);
583         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
584                 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
585                 ast_odbc_release_table(tableptr);
586                 return NULL;
587         }
588
589         ast_str_set(&sql, 0, "UPDATE %s SET ", ups->table);
590
591         /* Start by finding the second set of parameters */
592         va_copy(ap, ups->ap);
593
594         while ((newparam = va_arg(ap, const char *))) {
595                 newval = va_arg(ap, const char *);
596         }
597
598         while ((newparam = va_arg(ap, const char *))) {
599                 newval = va_arg(ap, const char *);
600                 if ((column = ast_odbc_find_column(tableptr, newparam))) {
601                         ast_str_append(&sql, 0, "%s%s=? ", first ? "" : ", ", newparam);
602                         SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
603                         first = 0;
604                 } else {
605                         ast_log(LOG_NOTICE, "Not updating column '%s' in '%s@%s' because that column does not exist!\n", newparam, ups->table, ups->database);
606                 }
607         }
608         va_end(ap);
609
610         /* Restart search, because we need to add the search parameters */
611         va_copy(ap, ups->ap);
612         ast_str_append(&sql, 0, "WHERE");
613         first = 1;
614
615         while ((newparam = va_arg(ap, const char *))) {
616                 newval = va_arg(ap, const char *);
617                 if (!(column = ast_odbc_find_column(tableptr, newparam))) {
618                         ast_log(LOG_ERROR, "One or more of the criteria columns '%s' on '%s@%s' for this update does not exist!\n", newparam, ups->table, ups->database);
619                         ast_odbc_release_table(tableptr);
620                         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
621                         return NULL;
622                 }
623                 ast_str_append(&sql, 0, "%s %s=?", first ? "" : " AND", newparam);
624                 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
625                 first = 0;
626         }
627         va_end(ap);
628
629         /* Done with the table metadata */
630         ast_odbc_release_table(tableptr);
631
632         res = SQLPrepare(stmt, (unsigned char *)ast_str_buffer(sql), SQL_NTS);
633         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
634                 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", ast_str_buffer(sql));
635                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
636                 return NULL;
637         }
638
639         return stmt;
640 }
641
642 /*!
643  * \brief Execute an UPDATE query
644  * \param database
645  * \param table
646  * \param ap list containing one or more field/value set(s).
647  *
648  * Update a database table, preparing the sql statement from a list of
649  * key/value pairs specified in ap.  The lookup pairs are specified first
650  * and are separated from the update pairs by a sentinel value.
651  * Sub-in the values to the prepared statement and execute it.
652  *
653  * \retval number of rows affected
654  * \retval -1 on failure
655 */
656 static int update2_odbc(const char *database, const char *table, va_list ap)
657 {
658         struct odbc_obj *obj;
659         SQLHSTMT stmt;
660         struct update2_prepare_struct ups = { .database = database, .table = table, };
661         struct ast_str *sql;
662         int res;
663         SQLLEN rowcount = 0;
664
665         va_copy(ups.ap, ap);
666
667         if (!(obj = ast_odbc_request_obj(database, 0))) {
668                 return -1;
669         }
670
671         if (!(stmt = ast_odbc_prepare_and_execute(obj, update2_prepare, &ups))) {
672                 ast_odbc_release_obj(obj);
673                 return -1;
674         }
675
676         res = SQLRowCount(stmt, &rowcount);
677         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
678         ast_odbc_release_obj(obj);
679
680         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
681                 /* Since only a single thread can access this memory, we can retrieve what would otherwise be lost. */
682                 sql = ast_str_thread_get(&sql_buf, 16);
683                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n", ast_str_buffer(sql));
684                 return -1;
685         }
686
687         if (rowcount >= 0) {
688                 return (int)rowcount;
689         }
690
691         return -1;
692 }
693
694 /*!
695  * \brief Excute an INSERT query
696  * \param database
697  * \param table
698  * \param ap list containing one or more field/value set(s)
699  *
700  * Insert a new record into database table, prepare the sql statement.
701  * All values to be changed are stored in ap list.
702  * Sub-in the values to the prepared statement and execute it.
703  *
704  * \retval number of rows affected
705  * \retval -1 on failure
706 */
707 static int store_odbc(const char *database, const char *table, va_list ap)
708 {
709         struct odbc_obj *obj;
710         SQLHSTMT stmt;
711         char sql[256];
712         char keys[256];
713         char vals[256];
714         SQLLEN rowcount=0;
715         const char *newparam, *newval;
716         int res;
717         va_list aq;
718         struct custom_prepare_struct cps = { .sql = sql, .extra = NULL };
719
720         va_copy(cps.ap, ap);
721         va_copy(aq, ap);
722         
723         if (!table)
724                 return -1;
725
726         obj = ast_odbc_request_obj(database, 0);
727         if (!obj)
728                 return -1;
729
730         newparam = va_arg(aq, const char *);
731         if (!newparam)  {
732                 ast_odbc_release_obj(obj);
733                 return -1;
734         }
735         newval = va_arg(aq, const char *);
736         snprintf(keys, sizeof(keys), "%s", newparam);
737         ast_copy_string(vals, "?", sizeof(vals));
738         while ((newparam = va_arg(aq, const char *))) {
739                 snprintf(keys + strlen(keys), sizeof(keys) - strlen(keys), ", %s", newparam);
740                 snprintf(vals + strlen(vals), sizeof(vals) - strlen(vals), ", ?");
741                 newval = va_arg(aq, const char *);
742         }
743         va_end(aq);
744         snprintf(sql, sizeof(sql), "INSERT INTO %s (%s) VALUES (%s)", table, keys, vals);
745
746         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
747
748         if (!stmt) {
749                 ast_odbc_release_obj(obj);
750                 return -1;
751         }
752
753         res = SQLRowCount(stmt, &rowcount);
754         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
755         ast_odbc_release_obj(obj);
756
757         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
758                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
759                 return -1;
760         }
761
762         if (rowcount >= 0)
763                 return (int)rowcount;
764
765         return -1;
766 }
767
768 /*!
769  * \brief Excute an DELETE query
770  * \param database
771  * \param table
772  * \param keyfield where clause field
773  * \param lookup value of field for where clause
774  * \param ap list containing one or more field/value set(s)
775  *
776  * Delete a row from a database table, prepare the sql statement using keyfield and lookup
777  * control the number of records to change. Additional params to match rows are stored in ap list.
778  * Sub-in the values to the prepared statement and execute it.
779  *
780  * \retval number of rows affected
781  * \retval -1 on failure
782 */
783 static int destroy_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
784 {
785         struct odbc_obj *obj;
786         SQLHSTMT stmt;
787         char sql[256];
788         SQLLEN rowcount=0;
789         const char *newparam, *newval;
790         int res;
791         va_list aq;
792         struct custom_prepare_struct cps = { .sql = sql, .extra = lookup };
793
794         va_copy(cps.ap, ap);
795         va_copy(aq, ap);
796         
797         if (!table)
798                 return -1;
799
800         obj = ast_odbc_request_obj(database, 0);
801         if (!obj)
802                 return -1;
803
804         snprintf(sql, sizeof(sql), "DELETE FROM %s WHERE ", table);
805         while((newparam = va_arg(aq, const char *))) {
806                 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=? AND ", newparam);
807                 newval = va_arg(aq, const char *);
808         }
809         va_end(aq);
810         snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=?", keyfield);
811
812         stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
813
814         if (!stmt) {
815                 ast_odbc_release_obj(obj);
816                 return -1;
817         }
818
819         res = SQLRowCount(stmt, &rowcount);
820         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
821         ast_odbc_release_obj(obj);
822
823         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
824                 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
825                 return -1;
826         }
827
828         if (rowcount >= 0)
829                 return (int)rowcount;
830
831         return -1;
832 }
833
834
835 struct config_odbc_obj {
836         char *sql;
837         unsigned long cat_metric;
838         char category[128];
839         char var_name[128];
840         char var_val[1024]; /* changed from 128 to 1024 via bug 8251 */
841         SQLLEN err;
842 };
843
844 static SQLHSTMT config_odbc_prepare(struct odbc_obj *obj, void *data)
845 {
846         struct config_odbc_obj *q = data;
847         SQLHSTMT sth;
848         int res;
849
850         res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &sth);
851         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
852                 ast_verb(4, "Failure in AllocStatement %d\n", res);
853                 return NULL;
854         }
855
856         res = SQLPrepare(sth, (unsigned char *)q->sql, SQL_NTS);
857         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
858                 ast_verb(4, "Error in PREPARE %d\n", res);
859                 SQLFreeHandle(SQL_HANDLE_STMT, sth);
860                 return NULL;
861         }
862
863         SQLBindCol(sth, 1, SQL_C_ULONG, &q->cat_metric, sizeof(q->cat_metric), &q->err);
864         SQLBindCol(sth, 2, SQL_C_CHAR, q->category, sizeof(q->category), &q->err);
865         SQLBindCol(sth, 3, SQL_C_CHAR, q->var_name, sizeof(q->var_name), &q->err);
866         SQLBindCol(sth, 4, SQL_C_CHAR, q->var_val, sizeof(q->var_val), &q->err);
867
868         return sth;
869 }
870
871 static struct ast_config *config_odbc(const char *database, const char *table, const char *file, struct ast_config *cfg, struct ast_flags flags, const char *sugg_incl, const char *who_asked)
872 {
873         struct ast_variable *new_v;
874         struct ast_category *cur_cat;
875         int res = 0;
876         struct odbc_obj *obj;
877         char sqlbuf[1024] = "";
878         char *sql = sqlbuf;
879         size_t sqlleft = sizeof(sqlbuf);
880         unsigned int last_cat_metric = 0;
881         SQLSMALLINT rowcount = 0;
882         SQLHSTMT stmt;
883         char last[128] = "";
884         struct config_odbc_obj q;
885         struct ast_flags loader_flags = { 0 };
886
887         memset(&q, 0, sizeof(q));
888
889         if (!file || !strcmp (file, "res_config_odbc.conf"))
890                 return NULL;            /* cant configure myself with myself ! */
891
892         obj = ast_odbc_request_obj(database, 0);
893         if (!obj)
894                 return NULL;
895
896         ast_build_string(&sql, &sqlleft, "SELECT cat_metric, category, var_name, var_val FROM %s ", table);
897         ast_build_string(&sql, &sqlleft, "WHERE filename='%s' AND commented=0 ", file);
898         ast_build_string(&sql, &sqlleft, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
899         q.sql = sqlbuf;
900
901         stmt = ast_odbc_prepare_and_execute(obj, config_odbc_prepare, &q);
902
903         if (!stmt) {
904                 ast_log(LOG_WARNING, "SQL select error!\n[%s]\n\n", sql);
905                 ast_odbc_release_obj(obj);
906                 return NULL;
907         }
908
909         res = SQLNumResultCols(stmt, &rowcount);
910
911         if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
912                 ast_log(LOG_WARNING, "SQL NumResultCols error!\n[%s]\n\n", sql);
913                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
914                 ast_odbc_release_obj(obj);
915                 return NULL;
916         }
917
918         if (!rowcount) {
919                 ast_log(LOG_NOTICE, "found nothing\n");
920                 ast_odbc_release_obj(obj);
921                 return cfg;
922         }
923
924         cur_cat = ast_config_get_current_category(cfg);
925
926         while ((res = SQLFetch(stmt)) != SQL_NO_DATA) {
927                 if (!strcmp (q.var_name, "#include")) {
928                         if (!ast_config_internal_load(q.var_val, cfg, loader_flags, "", who_asked)) {
929                                 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
930                                 ast_odbc_release_obj(obj);
931                                 return NULL;
932                         }
933                         continue;
934                 } 
935                 if (strcmp(last, q.category) || last_cat_metric != q.cat_metric) {
936                         cur_cat = ast_category_new(q.category, "", 99999);
937                         if (!cur_cat) {
938                                 ast_log(LOG_WARNING, "Out of memory!\n");
939                                 break;
940                         }
941                         strcpy(last, q.category);
942                         last_cat_metric = q.cat_metric;
943                         ast_category_append(cfg, cur_cat);
944                 }
945
946                 new_v = ast_variable_new(q.var_name, q.var_val, "");
947                 ast_variable_append(cur_cat, new_v);
948         }
949
950         SQLFreeHandle(SQL_HANDLE_STMT, stmt);
951         ast_odbc_release_obj(obj);
952         return cfg;
953 }
954
955 #define warn_length(col, size)  ast_log(LOG_WARNING, "Realtime table %s@%s: column '%s' is not long enough to contain realtime data (needs %d)\n", table, database, col->name, size)
956 #define warn_type(col, type)    ast_log(LOG_WARNING, "Realtime table %s@%s: column '%s' is of the incorrect type (%d) to contain the required realtime data\n", table, database, col->name, col->type)
957
958 static int require_odbc(const char *database, const char *table, va_list ap)
959 {
960         struct odbc_cache_tables *tableptr = ast_odbc_find_table(database, table);
961         struct odbc_cache_columns *col;
962         char *elm;
963         int type, size;
964
965         if (!tableptr) {
966                 return -1;
967         }
968
969         while ((elm = va_arg(ap, char *))) {
970                 type = va_arg(ap, require_type);
971                 size = va_arg(ap, int);
972                 /* Check if the field matches the criteria */
973                 AST_RWLIST_TRAVERSE(&tableptr->columns, col, list) {
974                         if (strcmp(col->name, elm) == 0) {
975                                 /* Type check, first.  Some fields are more particular than others */
976                                 switch (col->type) {
977                                 case SQL_CHAR:
978                                 case SQL_VARCHAR:
979                                 case SQL_LONGVARCHAR:
980 #ifdef HAVE_ODBC_WCHAR
981                                 case SQL_WCHAR:
982                                 case SQL_WVARCHAR:
983                                 case SQL_WLONGVARCHAR:
984 #endif
985                                 case SQL_BINARY:
986                                 case SQL_VARBINARY:
987                                 case SQL_LONGVARBINARY:
988                                 case SQL_GUID:
989 #define CHECK_SIZE(n) \
990                                                 if (col->size < n) {      \
991                                                         warn_length(col, n);  \
992                                                 }                         \
993                                                 break;
994                                         switch (type) {
995                                         case RQ_UINTEGER1: CHECK_SIZE(3)  /*         255 */
996                                         case RQ_INTEGER1:  CHECK_SIZE(4)  /*        -128 */
997                                         case RQ_UINTEGER2: CHECK_SIZE(5)  /*       65535 */
998                                         case RQ_INTEGER2:  CHECK_SIZE(6)  /*      -32768 */
999                                         case RQ_UINTEGER3:                /*    16777215 */
1000                                         case RQ_INTEGER3:  CHECK_SIZE(8)  /*    -8388608 */
1001                                         case RQ_DATE:                     /*  2008-06-09 */
1002                                         case RQ_UINTEGER4: CHECK_SIZE(10) /*  4200000000 */
1003                                         case RQ_INTEGER4:  CHECK_SIZE(11) /* -2100000000 */
1004                                         case RQ_DATETIME:                 /* 2008-06-09 16:03:47 */
1005                                         case RQ_UINTEGER8: CHECK_SIZE(19) /* trust me    */
1006                                         case RQ_INTEGER8:  CHECK_SIZE(20) /* ditto       */
1007                                         case RQ_FLOAT:
1008                                         case RQ_CHAR:      CHECK_SIZE(size)
1009                                         }
1010 #undef CHECK_SIZE
1011                                         break;
1012                                 case SQL_TYPE_DATE:
1013                                         if (type != RQ_DATE) {
1014                                                 warn_type(col, type);
1015                                         }
1016                                         break;
1017                                 case SQL_TYPE_TIMESTAMP:
1018                                 case SQL_TIMESTAMP:
1019                                         if (type != RQ_DATE && type != RQ_DATETIME) {
1020                                                 warn_type(col, type);
1021                                         }
1022                                         break;
1023                                 case SQL_BIT:
1024                                         warn_length(col, size);
1025                                         break;
1026 #define WARN_TYPE_OR_LENGTH(n)  \
1027                                                 if (!ast_rq_is_int(type)) {  \
1028                                                         warn_type(col, type);    \
1029                                                 } else {                     \
1030                                                         warn_length(col, n);  \
1031                                                 }
1032                                 case SQL_TINYINT:
1033                                         if (type != RQ_UINTEGER1) {
1034                                                 WARN_TYPE_OR_LENGTH(size)
1035                                         }
1036                                         break;
1037                                 case SQL_C_STINYINT:
1038                                         if (type != RQ_INTEGER1) {
1039                                                 WARN_TYPE_OR_LENGTH(size)
1040                                         }
1041                                         break;
1042                                 case SQL_C_USHORT:
1043                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_UINTEGER2) {
1044                                                 WARN_TYPE_OR_LENGTH(size)
1045                                         }
1046                                         break;
1047                                 case SQL_SMALLINT:
1048                                 case SQL_C_SSHORT:
1049                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_INTEGER2) {
1050                                                 WARN_TYPE_OR_LENGTH(size)
1051                                         }
1052                                         break;
1053                                 case SQL_C_ULONG:
1054                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
1055                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
1056                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
1057                                                 type != RQ_INTEGER4) {
1058                                                 WARN_TYPE_OR_LENGTH(size)
1059                                         }
1060                                         break;
1061                                 case SQL_INTEGER:
1062                                 case SQL_C_SLONG:
1063                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
1064                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
1065                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
1066                                                 type != RQ_INTEGER4) {
1067                                                 WARN_TYPE_OR_LENGTH(size)
1068                                         }
1069                                         break;
1070                                 case SQL_C_UBIGINT:
1071                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
1072                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
1073                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
1074                                                 type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
1075                                                 type != RQ_INTEGER8) {
1076                                                 WARN_TYPE_OR_LENGTH(size)
1077                                         }
1078                                         break;
1079                                 case SQL_BIGINT:
1080                                 case SQL_C_SBIGINT:
1081                                         if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
1082                                                 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
1083                                                 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
1084                                                 type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
1085                                                 type != RQ_INTEGER8) {
1086                                                 WARN_TYPE_OR_LENGTH(size)
1087                                         }
1088                                         break;
1089 #undef WARN_TYPE_OR_LENGTH
1090                                 case SQL_NUMERIC:
1091                                 case SQL_DECIMAL:
1092                                 case SQL_FLOAT:
1093                                 case SQL_REAL:
1094                                 case SQL_DOUBLE:
1095                                         if (!ast_rq_is_int(type) && type != RQ_FLOAT) {
1096                                                 warn_type(col, type);
1097                                         }
1098                                         break;
1099                                 default:
1100                                         ast_log(LOG_WARNING, "Realtime table %s@%s: column type (%d) unrecognized for column '%s'\n", table, database, col->type, elm);
1101                                 }
1102                                 break;
1103                         }
1104                 }
1105                 if (!col) {
1106                         ast_log(LOG_WARNING, "Realtime table %s@%s requires column '%s', but that column does not exist!\n", table, database, elm);
1107                 }
1108         }
1109         va_end(ap);
1110         AST_RWLIST_UNLOCK(&tableptr->columns);
1111         return 0;
1112 }
1113 #undef warn_length
1114 #undef warn_type
1115
1116 static struct ast_config_engine odbc_engine = {
1117         .name = "odbc",
1118         .load_func = config_odbc,
1119         .realtime_func = realtime_odbc,
1120         .realtime_multi_func = realtime_multi_odbc,
1121         .store_func = store_odbc,
1122         .destroy_func = destroy_odbc,
1123         .update_func = update_odbc,
1124         .update2_func = update2_odbc,
1125         .require_func = require_odbc,
1126         .unload_func = ast_odbc_clear_cache,
1127 };
1128
1129 static int unload_module (void)
1130 {
1131         ast_config_engine_deregister(&odbc_engine);
1132
1133         ast_verb(1, "res_config_odbc unloaded.\n");
1134         return 0;
1135 }
1136
1137 static int load_module (void)
1138 {
1139         ast_config_engine_register(&odbc_engine);
1140         ast_verb(1, "res_config_odbc loaded.\n");
1141         return 0;
1142 }
1143
1144 static int reload_module(void)
1145 {
1146         return 0;
1147 }
1148
1149 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_DEFAULT, "Realtime ODBC configuration",
1150                 .load = load_module,
1151                 .unload = unload_module,
1152                 .reload = reload_module,
1153                 );