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