00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019
00020
00021
00022
00023
00024
00025
00026
00027
00028
00029
00030
00031
00032
00033
00034
00035
00036 #include "asterisk.h"
00037
00038 ASTERISK_FILE_VERSION(__FILE__, "$Revision: 334229 $")
00039
00040 #include "asterisk/file.h"
00041 #include "asterisk/channel.h"
00042 #include "asterisk/pbx.h"
00043 #include "asterisk/config.h"
00044 #include "asterisk/module.h"
00045 #include "asterisk/lock.h"
00046 #include "asterisk/res_odbc.h"
00047 #include "asterisk/utils.h"
00048 #include "asterisk/stringfields.h"
00049
00050 AST_THREADSTORAGE(sql_buf);
00051
00052 struct custom_prepare_struct {
00053 const char *sql;
00054 const char *extra;
00055 AST_DECLARE_STRING_FIELDS(
00056 AST_STRING_FIELD(encoding)[256];
00057 );
00058 va_list ap;
00059 unsigned long long skip;
00060 };
00061
00062 static void decode_chunk(char *chunk)
00063 {
00064 for (; *chunk; chunk++) {
00065 if (*chunk == '^' && strchr("0123456789ABCDEF", chunk[1]) && strchr("0123456789ABCDEF", chunk[2])) {
00066 sscanf(chunk + 1, "%02hhX", chunk);
00067 memmove(chunk + 1, chunk + 3, strlen(chunk + 3) + 1);
00068 }
00069 }
00070 }
00071
00072 static SQLHSTMT custom_prepare(struct odbc_obj *obj, void *data)
00073 {
00074 int res, x = 1, count = 0;
00075 struct custom_prepare_struct *cps = data;
00076 const char *newparam, *newval;
00077 char encodebuf[1024];
00078 SQLHSTMT stmt;
00079 va_list ap;
00080
00081 va_copy(ap, cps->ap);
00082
00083 res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &stmt);
00084 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00085 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
00086 return NULL;
00087 }
00088
00089 ast_debug(1, "Skip: %lld; SQL: %s\n", cps->skip, cps->sql);
00090
00091 res = SQLPrepare(stmt, (unsigned char *)cps->sql, SQL_NTS);
00092 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00093 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", cps->sql);
00094 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00095 return NULL;
00096 }
00097
00098 while ((newparam = va_arg(ap, const char *))) {
00099 newval = va_arg(ap, const char *);
00100 if ((1LL << count++) & cps->skip) {
00101 ast_debug(1, "Skipping field '%s'='%s' (%llo/%llo)\n", newparam, newval, 1LL << (count - 1), cps->skip);
00102 continue;
00103 }
00104 ast_debug(1, "Parameter %d ('%s') = '%s'\n", x, newparam, newval);
00105 if (strchr(newval, ';') || strchr(newval, '^')) {
00106 char *eptr = encodebuf;
00107 const char *vptr = newval;
00108 for (; *vptr && eptr < encodebuf + sizeof(encodebuf); vptr++) {
00109 if (strchr("^;", *vptr)) {
00110
00111 snprintf(eptr, encodebuf + sizeof(encodebuf) - eptr, "^%02hhX", *vptr);
00112 eptr += 3;
00113 } else {
00114 *eptr++ = *vptr;
00115 }
00116 }
00117 if (eptr < encodebuf + sizeof(encodebuf)) {
00118 *eptr = '\0';
00119 } else {
00120 encodebuf[sizeof(encodebuf) - 1] = '\0';
00121 }
00122 ast_string_field_set(cps, encoding[x], encodebuf);
00123 newval = cps->encoding[x];
00124 }
00125 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
00126 }
00127 va_end(ap);
00128
00129 if (!ast_strlen_zero(cps->extra))
00130 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(cps->extra), 0, (void *)cps->extra, 0, NULL);
00131 return stmt;
00132 }
00133
00134
00135
00136
00137
00138
00139
00140
00141
00142
00143
00144
00145
00146
00147 static struct ast_variable *realtime_odbc(const char *database, const char *table, va_list ap)
00148 {
00149 struct odbc_obj *obj;
00150 SQLHSTMT stmt;
00151 char sql[1024];
00152 char coltitle[256];
00153 char rowdata[2048];
00154 char *op;
00155 const char *newparam;
00156 char *stringp;
00157 char *chunk;
00158 SQLSMALLINT collen;
00159 int res;
00160 int x;
00161 struct ast_variable *var=NULL, *prev=NULL;
00162 SQLULEN colsize;
00163 SQLSMALLINT colcount=0;
00164 SQLSMALLINT datatype;
00165 SQLSMALLINT decimaldigits;
00166 SQLSMALLINT nullable;
00167 SQLLEN indicator;
00168 va_list aq;
00169 struct custom_prepare_struct cps = { .sql = sql };
00170 struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
00171
00172 if (ast_string_field_init(&cps, 256)) {
00173 return NULL;
00174 }
00175 va_copy(cps.ap, ap);
00176 va_copy(aq, ap);
00177
00178 if (!table) {
00179 ast_string_field_free_memory(&cps);
00180 return NULL;
00181 }
00182
00183 obj = ast_odbc_request_obj2(database, connected_flag);
00184
00185 if (!obj) {
00186 ast_log(LOG_ERROR, "No database handle available with the name of '%s' (check res_odbc.conf)\n", database);
00187 ast_string_field_free_memory(&cps);
00188 return NULL;
00189 }
00190
00191 newparam = va_arg(aq, const char *);
00192 if (!newparam) {
00193 ast_odbc_release_obj(obj);
00194 ast_string_field_free_memory(&cps);
00195 return NULL;
00196 }
00197 va_arg(aq, const char *);
00198 op = !strchr(newparam, ' ') ? " =" : "";
00199 snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op,
00200 strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
00201 while((newparam = va_arg(aq, const char *))) {
00202 op = !strchr(newparam, ' ') ? " =" : "";
00203 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
00204 strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
00205 va_arg(aq, const char *);
00206 }
00207 va_end(aq);
00208
00209 stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00210
00211 if (!stmt) {
00212 ast_odbc_release_obj(obj);
00213 ast_string_field_free_memory(&cps);
00214 return NULL;
00215 }
00216
00217 res = SQLNumResultCols(stmt, &colcount);
00218 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00219 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
00220 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00221 ast_odbc_release_obj(obj);
00222 ast_string_field_free_memory(&cps);
00223 return NULL;
00224 }
00225
00226 res = SQLFetch(stmt);
00227 if (res == SQL_NO_DATA) {
00228 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00229 ast_odbc_release_obj(obj);
00230 ast_string_field_free_memory(&cps);
00231 return NULL;
00232 }
00233 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00234 ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
00235 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00236 ast_odbc_release_obj(obj);
00237 ast_string_field_free_memory(&cps);
00238 return NULL;
00239 }
00240 for (x = 0; x < colcount; x++) {
00241 rowdata[0] = '\0';
00242 colsize = 0;
00243 collen = sizeof(coltitle);
00244 res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen,
00245 &datatype, &colsize, &decimaldigits, &nullable);
00246 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00247 ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
00248 if (var)
00249 ast_variables_destroy(var);
00250 ast_odbc_release_obj(obj);
00251 ast_string_field_free_memory(&cps);
00252 return NULL;
00253 }
00254
00255 indicator = 0;
00256 res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
00257 if (indicator == SQL_NULL_DATA)
00258 rowdata[0] = '\0';
00259 else if (ast_strlen_zero(rowdata)) {
00260
00261
00262 ast_copy_string(rowdata, " ", sizeof(rowdata));
00263 }
00264
00265 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00266 ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
00267 if (var)
00268 ast_variables_destroy(var);
00269 ast_odbc_release_obj(obj);
00270 return NULL;
00271 }
00272 stringp = rowdata;
00273 while (stringp) {
00274 chunk = strsep(&stringp, ";");
00275 if (!ast_strlen_zero(ast_strip(chunk))) {
00276 if (strchr(chunk, '^')) {
00277 decode_chunk(chunk);
00278 }
00279 if (prev) {
00280 prev->next = ast_variable_new(coltitle, chunk, "");
00281 if (prev->next) {
00282 prev = prev->next;
00283 }
00284 } else {
00285 prev = var = ast_variable_new(coltitle, chunk, "");
00286 }
00287 }
00288 }
00289 }
00290
00291
00292 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00293 ast_odbc_release_obj(obj);
00294 ast_string_field_free_memory(&cps);
00295 return var;
00296 }
00297
00298
00299
00300
00301
00302
00303
00304
00305
00306
00307
00308
00309
00310
00311
00312 static struct ast_config *realtime_multi_odbc(const char *database, const char *table, va_list ap)
00313 {
00314 struct odbc_obj *obj;
00315 SQLHSTMT stmt;
00316 char sql[1024];
00317 char coltitle[256];
00318 char rowdata[2048];
00319 const char *initfield=NULL;
00320 char *op;
00321 const char *newparam;
00322 char *stringp;
00323 char *chunk;
00324 SQLSMALLINT collen;
00325 int res;
00326 int x;
00327 struct ast_variable *var=NULL;
00328 struct ast_config *cfg=NULL;
00329 struct ast_category *cat=NULL;
00330 struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
00331 SQLULEN colsize;
00332 SQLSMALLINT colcount=0;
00333 SQLSMALLINT datatype;
00334 SQLSMALLINT decimaldigits;
00335 SQLSMALLINT nullable;
00336 SQLLEN indicator;
00337 struct custom_prepare_struct cps = { .sql = sql };
00338 va_list aq;
00339
00340 if (!table || ast_string_field_init(&cps, 256)) {
00341 return NULL;
00342 }
00343 va_copy(cps.ap, ap);
00344 va_copy(aq, ap);
00345
00346
00347 obj = ast_odbc_request_obj2(database, connected_flag);
00348 if (!obj) {
00349 ast_string_field_free_memory(&cps);
00350 return NULL;
00351 }
00352
00353 newparam = va_arg(aq, const char *);
00354 if (!newparam) {
00355 ast_odbc_release_obj(obj);
00356 ast_string_field_free_memory(&cps);
00357 return NULL;
00358 }
00359 initfield = ast_strdupa(newparam);
00360 if ((op = strchr(initfield, ' ')))
00361 *op = '\0';
00362 va_arg(aq, const char *);
00363 op = !strchr(newparam, ' ') ? " =" : "";
00364 snprintf(sql, sizeof(sql), "SELECT * FROM %s WHERE %s%s ?%s", table, newparam, op,
00365 strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
00366 while((newparam = va_arg(aq, const char *))) {
00367 op = !strchr(newparam, ' ') ? " =" : "";
00368 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " AND %s%s ?%s", newparam, op,
00369 strcasestr(newparam, "LIKE") && !ast_odbc_backslash_is_escape(obj) ? " ESCAPE '\\'" : "");
00370 va_arg(aq, const char *);
00371 }
00372 if (initfield)
00373 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " ORDER BY %s", initfield);
00374 va_end(aq);
00375
00376 stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00377
00378 if (!stmt) {
00379 ast_odbc_release_obj(obj);
00380 ast_string_field_free_memory(&cps);
00381 return NULL;
00382 }
00383
00384 res = SQLNumResultCols(stmt, &colcount);
00385 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00386 ast_log(LOG_WARNING, "SQL Column Count error!\n[%s]\n\n", sql);
00387 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00388 ast_odbc_release_obj(obj);
00389 ast_string_field_free_memory(&cps);
00390 return NULL;
00391 }
00392
00393 cfg = ast_config_new();
00394 if (!cfg) {
00395 ast_log(LOG_WARNING, "Out of memory!\n");
00396 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00397 ast_odbc_release_obj(obj);
00398 ast_string_field_free_memory(&cps);
00399 return NULL;
00400 }
00401
00402 while ((res=SQLFetch(stmt)) != SQL_NO_DATA) {
00403 var = NULL;
00404 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00405 ast_log(LOG_WARNING, "SQL Fetch error!\n[%s]\n\n", sql);
00406 continue;
00407 }
00408 cat = ast_category_new("","",99999);
00409 if (!cat) {
00410 ast_log(LOG_WARNING, "Out of memory!\n");
00411 continue;
00412 }
00413 for (x=0;x<colcount;x++) {
00414 rowdata[0] = '\0';
00415 colsize = 0;
00416 collen = sizeof(coltitle);
00417 res = SQLDescribeCol(stmt, x + 1, (unsigned char *)coltitle, sizeof(coltitle), &collen,
00418 &datatype, &colsize, &decimaldigits, &nullable);
00419 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00420 ast_log(LOG_WARNING, "SQL Describe Column error!\n[%s]\n\n", sql);
00421 ast_category_destroy(cat);
00422 goto next_sql_fetch;
00423 }
00424
00425 indicator = 0;
00426 res = SQLGetData(stmt, x + 1, SQL_CHAR, rowdata, sizeof(rowdata), &indicator);
00427 if (indicator == SQL_NULL_DATA)
00428 continue;
00429
00430 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00431 ast_log(LOG_WARNING, "SQL Get Data error!\n[%s]\n\n", sql);
00432 ast_category_destroy(cat);
00433 goto next_sql_fetch;
00434 }
00435 stringp = rowdata;
00436 while (stringp) {
00437 chunk = strsep(&stringp, ";");
00438 if (!ast_strlen_zero(ast_strip(chunk))) {
00439 if (strchr(chunk, '^')) {
00440 decode_chunk(chunk);
00441 }
00442 if (initfield && !strcmp(initfield, coltitle)) {
00443 ast_category_rename(cat, chunk);
00444 }
00445 var = ast_variable_new(coltitle, chunk, "");
00446 ast_variable_append(cat, var);
00447 }
00448 }
00449 }
00450 ast_category_append(cfg, cat);
00451 next_sql_fetch:;
00452 }
00453
00454 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00455 ast_odbc_release_obj(obj);
00456 ast_string_field_free_memory(&cps);
00457 return cfg;
00458 }
00459
00460
00461
00462
00463
00464
00465
00466
00467
00468
00469
00470
00471
00472
00473
00474
00475 static int update_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
00476 {
00477 struct odbc_obj *obj;
00478 SQLHSTMT stmt;
00479 char sql[256];
00480 SQLLEN rowcount=0;
00481 const char *newparam;
00482 int res, count = 1;
00483 va_list aq;
00484 struct custom_prepare_struct cps = { .sql = sql, .extra = lookup };
00485 struct odbc_cache_tables *tableptr;
00486 struct odbc_cache_columns *column = NULL;
00487 struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
00488
00489 if (!table) {
00490 return -1;
00491 }
00492
00493 va_copy(cps.ap, ap);
00494 va_copy(aq, ap);
00495
00496 if (ast_string_field_init(&cps, 256)) {
00497 return -1;
00498 }
00499
00500 tableptr = ast_odbc_find_table(database, table);
00501 if (!(obj = ast_odbc_request_obj2(database, connected_flag))) {
00502 ast_odbc_release_table(tableptr);
00503 ast_string_field_free_memory(&cps);
00504 return -1;
00505 }
00506
00507 newparam = va_arg(aq, const char *);
00508 if (!newparam) {
00509 ast_odbc_release_obj(obj);
00510 ast_odbc_release_table(tableptr);
00511 ast_string_field_free_memory(&cps);
00512 return -1;
00513 }
00514 va_arg(aq, const char *);
00515
00516 if (tableptr && !(column = ast_odbc_find_column(tableptr, newparam))) {
00517 ast_log(LOG_WARNING, "Key field '%s' does not exist in table '%s@%s'. Update will fail\n", newparam, table, database);
00518 }
00519
00520 snprintf(sql, sizeof(sql), "UPDATE %s SET %s=?", table, newparam);
00521 while((newparam = va_arg(aq, const char *))) {
00522 va_arg(aq, const char *);
00523 if ((tableptr && (column = ast_odbc_find_column(tableptr, newparam))) || count > 63) {
00524
00525 if (ast_strlen_zero(newparam) && tableptr && column && column->nullable && count < 64 &&
00526 (column->type == SQL_INTEGER || column->type == SQL_BIGINT ||
00527 column->type == SQL_SMALLINT || column->type == SQL_TINYINT ||
00528 column->type == SQL_NUMERIC || column->type == SQL_DECIMAL)) {
00529 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s=NULL", newparam);
00530 cps.skip |= (1LL << count);
00531 } else {
00532 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s=?", newparam);
00533 }
00534 } else {
00535 cps.skip |= (1LL << count);
00536 }
00537 count++;
00538 }
00539 va_end(aq);
00540 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s=?", keyfield);
00541 ast_odbc_release_table(tableptr);
00542
00543 stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00544
00545 if (!stmt) {
00546 ast_odbc_release_obj(obj);
00547 ast_string_field_free_memory(&cps);
00548 return -1;
00549 }
00550
00551 res = SQLRowCount(stmt, &rowcount);
00552 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00553 ast_odbc_release_obj(obj);
00554 ast_string_field_free_memory(&cps);
00555
00556 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00557 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
00558 return -1;
00559 }
00560
00561 if (rowcount >= 0) {
00562 return (int) rowcount;
00563 }
00564
00565 return -1;
00566 }
00567
00568 struct update2_prepare_struct {
00569 const char *database;
00570 const char *table;
00571 va_list ap;
00572 };
00573
00574 static SQLHSTMT update2_prepare(struct odbc_obj *obj, void *data)
00575 {
00576 int res, x = 1, first = 1;
00577 struct update2_prepare_struct *ups = data;
00578 const char *newparam, *newval;
00579 struct ast_str *sql = ast_str_thread_get(&sql_buf, 16);
00580 SQLHSTMT stmt;
00581 va_list ap;
00582 struct odbc_cache_tables *tableptr = ast_odbc_find_table(ups->database, ups->table);
00583 struct odbc_cache_columns *column;
00584
00585 if (!sql) {
00586 if (tableptr) {
00587 ast_odbc_release_table(tableptr);
00588 }
00589 return NULL;
00590 }
00591
00592 if (!tableptr) {
00593 ast_log(LOG_ERROR, "Could not retrieve metadata for table '%s@%s'. Update will fail!\n", ups->table, ups->database);
00594 return NULL;
00595 }
00596
00597 res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &stmt);
00598 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00599 ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
00600 ast_odbc_release_table(tableptr);
00601 return NULL;
00602 }
00603
00604 ast_str_set(&sql, 0, "UPDATE %s SET ", ups->table);
00605
00606
00607 va_copy(ap, ups->ap);
00608
00609 while ((newparam = va_arg(ap, const char *))) {
00610 newval = va_arg(ap, const char *);
00611 }
00612
00613 while ((newparam = va_arg(ap, const char *))) {
00614 newval = va_arg(ap, const char *);
00615 if ((column = ast_odbc_find_column(tableptr, newparam))) {
00616 ast_str_append(&sql, 0, "%s%s=? ", first ? "" : ", ", newparam);
00617 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
00618 first = 0;
00619 } else {
00620 ast_log(LOG_NOTICE, "Not updating column '%s' in '%s@%s' because that column does not exist!\n", newparam, ups->table, ups->database);
00621 }
00622 }
00623 va_end(ap);
00624
00625
00626 va_copy(ap, ups->ap);
00627 ast_str_append(&sql, 0, "WHERE");
00628 first = 1;
00629
00630 while ((newparam = va_arg(ap, const char *))) {
00631 newval = va_arg(ap, const char *);
00632 if (!(column = ast_odbc_find_column(tableptr, newparam))) {
00633 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);
00634 ast_odbc_release_table(tableptr);
00635 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00636 return NULL;
00637 }
00638 ast_str_append(&sql, 0, "%s %s=?", first ? "" : " AND", newparam);
00639 SQLBindParameter(stmt, x++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, strlen(newval), 0, (void *)newval, 0, NULL);
00640 first = 0;
00641 }
00642 va_end(ap);
00643
00644
00645 ast_odbc_release_table(tableptr);
00646
00647 res = SQLPrepare(stmt, (unsigned char *)ast_str_buffer(sql), SQL_NTS);
00648 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00649 ast_log(LOG_WARNING, "SQL Prepare failed![%s]\n", ast_str_buffer(sql));
00650 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00651 return NULL;
00652 }
00653
00654 return stmt;
00655 }
00656
00657
00658
00659
00660
00661
00662
00663
00664
00665
00666
00667
00668
00669
00670
00671 static int update2_odbc(const char *database, const char *table, va_list ap)
00672 {
00673 struct odbc_obj *obj;
00674 SQLHSTMT stmt;
00675 struct update2_prepare_struct ups = { .database = database, .table = table, };
00676 struct ast_str *sql;
00677 int res;
00678 SQLLEN rowcount = 0;
00679
00680 va_copy(ups.ap, ap);
00681
00682 if (!(obj = ast_odbc_request_obj(database, 0))) {
00683 return -1;
00684 }
00685
00686 if (!(stmt = ast_odbc_prepare_and_execute(obj, update2_prepare, &ups))) {
00687 ast_odbc_release_obj(obj);
00688 return -1;
00689 }
00690
00691 res = SQLRowCount(stmt, &rowcount);
00692 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00693 ast_odbc_release_obj(obj);
00694
00695 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00696
00697 sql = ast_str_thread_get(&sql_buf, 16);
00698 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n", ast_str_buffer(sql));
00699 return -1;
00700 }
00701
00702 if (rowcount >= 0) {
00703 return (int)rowcount;
00704 }
00705
00706 return -1;
00707 }
00708
00709
00710
00711
00712
00713
00714
00715
00716
00717
00718
00719
00720
00721
00722 static int store_odbc(const char *database, const char *table, va_list ap)
00723 {
00724 struct odbc_obj *obj;
00725 SQLHSTMT stmt;
00726 char sql[256];
00727 char keys[256];
00728 char vals[256];
00729 SQLLEN rowcount=0;
00730 const char *newparam;
00731 int res;
00732 va_list aq;
00733 struct custom_prepare_struct cps = { .sql = sql, .extra = NULL };
00734 struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
00735
00736 va_copy(cps.ap, ap);
00737 va_copy(aq, ap);
00738
00739 if (!table)
00740 return -1;
00741
00742 obj = ast_odbc_request_obj2(database, connected_flag);
00743 if (!obj)
00744 return -1;
00745
00746 newparam = va_arg(aq, const char *);
00747 if (!newparam) {
00748 ast_odbc_release_obj(obj);
00749 return -1;
00750 }
00751 va_arg(aq, const char *);
00752 snprintf(keys, sizeof(keys), "%s", newparam);
00753 ast_copy_string(vals, "?", sizeof(vals));
00754 while ((newparam = va_arg(aq, const char *))) {
00755 snprintf(keys + strlen(keys), sizeof(keys) - strlen(keys), ", %s", newparam);
00756 snprintf(vals + strlen(vals), sizeof(vals) - strlen(vals), ", ?");
00757 va_arg(aq, const char *);
00758 }
00759 va_end(aq);
00760 snprintf(sql, sizeof(sql), "INSERT INTO %s (%s) VALUES (%s)", table, keys, vals);
00761
00762 stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00763
00764 if (!stmt) {
00765 ast_odbc_release_obj(obj);
00766 return -1;
00767 }
00768
00769 res = SQLRowCount(stmt, &rowcount);
00770 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00771 ast_odbc_release_obj(obj);
00772
00773 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00774 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
00775 return -1;
00776 }
00777
00778 if (rowcount >= 0)
00779 return (int)rowcount;
00780
00781 return -1;
00782 }
00783
00784
00785
00786
00787
00788
00789
00790
00791
00792
00793
00794
00795
00796
00797
00798
00799 static int destroy_odbc(const char *database, const char *table, const char *keyfield, const char *lookup, va_list ap)
00800 {
00801 struct odbc_obj *obj;
00802 SQLHSTMT stmt;
00803 char sql[256];
00804 SQLLEN rowcount=0;
00805 const char *newparam;
00806 int res;
00807 va_list aq;
00808 struct custom_prepare_struct cps = { .sql = sql, .extra = lookup };
00809 struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
00810
00811 va_copy(cps.ap, ap);
00812 va_copy(aq, ap);
00813
00814 if (!table)
00815 return -1;
00816
00817 obj = ast_odbc_request_obj2(database, connected_flag);
00818 if (!obj)
00819 return -1;
00820
00821 snprintf(sql, sizeof(sql), "DELETE FROM %s WHERE ", table);
00822 while((newparam = va_arg(aq, const char *))) {
00823 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=? AND ", newparam);
00824 va_arg(aq, const char *);
00825 }
00826 va_end(aq);
00827 snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=?", keyfield);
00828
00829 stmt = ast_odbc_prepare_and_execute(obj, custom_prepare, &cps);
00830
00831 if (!stmt) {
00832 ast_odbc_release_obj(obj);
00833 return -1;
00834 }
00835
00836 res = SQLRowCount(stmt, &rowcount);
00837 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
00838 ast_odbc_release_obj(obj);
00839
00840 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00841 ast_log(LOG_WARNING, "SQL Row Count error!\n[%s]\n\n", sql);
00842 return -1;
00843 }
00844
00845 if (rowcount >= 0)
00846 return (int)rowcount;
00847
00848 return -1;
00849 }
00850
00851
00852 struct config_odbc_obj {
00853 char *sql;
00854 unsigned long cat_metric;
00855 char category[128];
00856 char var_name[128];
00857 char var_val[1024];
00858 SQLLEN err;
00859 };
00860
00861 static SQLHSTMT config_odbc_prepare(struct odbc_obj *obj, void *data)
00862 {
00863 struct config_odbc_obj *q = data;
00864 SQLHSTMT sth;
00865 int res;
00866
00867 res = SQLAllocHandle(SQL_HANDLE_STMT, obj->con, &sth);
00868 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00869 ast_verb(4, "Failure in AllocStatement %d\n", res);
00870 return NULL;
00871 }
00872
00873 res = SQLPrepare(sth, (unsigned char *)q->sql, SQL_NTS);
00874 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00875 ast_verb(4, "Error in PREPARE %d\n", res);
00876 SQLFreeHandle(SQL_HANDLE_STMT, sth);
00877 return NULL;
00878 }
00879
00880 SQLBindCol(sth, 1, SQL_C_ULONG, &q->cat_metric, sizeof(q->cat_metric), &q->err);
00881 SQLBindCol(sth, 2, SQL_C_CHAR, q->category, sizeof(q->category), &q->err);
00882 SQLBindCol(sth, 3, SQL_C_CHAR, q->var_name, sizeof(q->var_name), &q->err);
00883 SQLBindCol(sth, 4, SQL_C_CHAR, q->var_val, sizeof(q->var_val), &q->err);
00884
00885 return sth;
00886 }
00887
00888 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)
00889 {
00890 struct ast_variable *new_v;
00891 struct ast_category *cur_cat;
00892 int res = 0;
00893 struct odbc_obj *obj;
00894 char sqlbuf[1024] = "";
00895 char *sql = sqlbuf;
00896 size_t sqlleft = sizeof(sqlbuf);
00897 unsigned int last_cat_metric = 0;
00898 SQLSMALLINT rowcount = 0;
00899 SQLHSTMT stmt;
00900 char last[128] = "";
00901 struct config_odbc_obj q;
00902 struct ast_flags loader_flags = { 0 };
00903 struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
00904
00905 memset(&q, 0, sizeof(q));
00906
00907 if (!file || !strcmp (file, "res_config_odbc.conf"))
00908 return NULL;
00909
00910 obj = ast_odbc_request_obj2(database, connected_flag);
00911 if (!obj)
00912 return NULL;
00913
00914 ast_build_string(&sql, &sqlleft, "SELECT cat_metric, category, var_name, var_val FROM %s ", table);
00915 ast_build_string(&sql, &sqlleft, "WHERE filename='%s' AND commented=0 ", file);
00916 ast_build_string(&sql, &sqlleft, "ORDER BY cat_metric DESC, var_metric ASC, category, var_name ");
00917 q.sql = sqlbuf;
00918
00919 stmt = ast_odbc_prepare_and_execute(obj, config_odbc_prepare, &q);
00920
00921 if (!stmt) {
00922 ast_log(LOG_WARNING, "SQL select error!\n[%s]\n\n", sql);
00923 ast_odbc_release_obj(obj);
00924 return NULL;
00925 }
00926
00927 res = SQLNumResultCols(stmt, &rowcount);
00928
00929 if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
00930 ast_log(LOG_WARNING, "SQL NumResultCols error!\n[%s]\n\n", sql);
00931 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00932 ast_odbc_release_obj(obj);
00933 return NULL;
00934 }
00935
00936 if (!rowcount) {
00937 ast_log(LOG_NOTICE, "found nothing\n");
00938 ast_odbc_release_obj(obj);
00939 return cfg;
00940 }
00941
00942 cur_cat = ast_config_get_current_category(cfg);
00943
00944 while ((res = SQLFetch(stmt)) != SQL_NO_DATA) {
00945 if (!strcmp (q.var_name, "#include")) {
00946 if (!ast_config_internal_load(q.var_val, cfg, loader_flags, "", who_asked)) {
00947 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00948 ast_odbc_release_obj(obj);
00949 return NULL;
00950 }
00951 continue;
00952 }
00953 if (strcmp(last, q.category) || last_cat_metric != q.cat_metric) {
00954 cur_cat = ast_category_new(q.category, "", 99999);
00955 if (!cur_cat) {
00956 ast_log(LOG_WARNING, "Out of memory!\n");
00957 break;
00958 }
00959 strcpy(last, q.category);
00960 last_cat_metric = q.cat_metric;
00961 ast_category_append(cfg, cur_cat);
00962 }
00963
00964 new_v = ast_variable_new(q.var_name, q.var_val, "");
00965 ast_variable_append(cur_cat, new_v);
00966 }
00967
00968 SQLFreeHandle(SQL_HANDLE_STMT, stmt);
00969 ast_odbc_release_obj(obj);
00970 return cfg;
00971 }
00972
00973 #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)
00974 #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)
00975
00976 static int require_odbc(const char *database, const char *table, va_list ap)
00977 {
00978 struct odbc_cache_tables *tableptr = ast_odbc_find_table(database, table);
00979 struct odbc_cache_columns *col;
00980 char *elm;
00981 int type, size;
00982
00983 if (!tableptr) {
00984 return -1;
00985 }
00986
00987 while ((elm = va_arg(ap, char *))) {
00988 type = va_arg(ap, require_type);
00989 size = va_arg(ap, int);
00990
00991 AST_RWLIST_TRAVERSE(&tableptr->columns, col, list) {
00992 if (strcmp(col->name, elm) == 0) {
00993
00994 switch (col->type) {
00995 case SQL_CHAR:
00996 case SQL_VARCHAR:
00997 case SQL_LONGVARCHAR:
00998 #ifdef HAVE_ODBC_WCHAR
00999 case SQL_WCHAR:
01000 case SQL_WVARCHAR:
01001 case SQL_WLONGVARCHAR:
01002 #endif
01003 case SQL_BINARY:
01004 case SQL_VARBINARY:
01005 case SQL_LONGVARBINARY:
01006 case SQL_GUID:
01007 #define CHECK_SIZE(n) \
01008 if (col->size < n) { \
01009 warn_length(col, n); \
01010 } \
01011 break;
01012 switch (type) {
01013 case RQ_UINTEGER1: CHECK_SIZE(3)
01014 case RQ_INTEGER1: CHECK_SIZE(4)
01015 case RQ_UINTEGER2: CHECK_SIZE(5)
01016 case RQ_INTEGER2: CHECK_SIZE(6)
01017 case RQ_UINTEGER3:
01018 case RQ_INTEGER3: CHECK_SIZE(8)
01019 case RQ_DATE:
01020 case RQ_UINTEGER4: CHECK_SIZE(10)
01021 case RQ_INTEGER4: CHECK_SIZE(11)
01022 case RQ_DATETIME:
01023 case RQ_UINTEGER8: CHECK_SIZE(19)
01024 case RQ_INTEGER8: CHECK_SIZE(20)
01025 case RQ_FLOAT:
01026 case RQ_CHAR: CHECK_SIZE(size)
01027 }
01028 #undef CHECK_SIZE
01029 break;
01030 case SQL_TYPE_DATE:
01031 if (type != RQ_DATE) {
01032 warn_type(col, type);
01033 }
01034 break;
01035 case SQL_TYPE_TIMESTAMP:
01036 case SQL_TIMESTAMP:
01037 if (type != RQ_DATE && type != RQ_DATETIME) {
01038 warn_type(col, type);
01039 }
01040 break;
01041 case SQL_BIT:
01042 warn_length(col, size);
01043 break;
01044 #define WARN_TYPE_OR_LENGTH(n) \
01045 if (!ast_rq_is_int(type)) { \
01046 warn_type(col, type); \
01047 } else { \
01048 warn_length(col, n); \
01049 }
01050 case SQL_TINYINT:
01051 if (type != RQ_UINTEGER1) {
01052 WARN_TYPE_OR_LENGTH(size)
01053 }
01054 break;
01055 case SQL_C_STINYINT:
01056 if (type != RQ_INTEGER1) {
01057 WARN_TYPE_OR_LENGTH(size)
01058 }
01059 break;
01060 case SQL_C_USHORT:
01061 if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_UINTEGER2) {
01062 WARN_TYPE_OR_LENGTH(size)
01063 }
01064 break;
01065 case SQL_SMALLINT:
01066 case SQL_C_SSHORT:
01067 if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 && type != RQ_INTEGER2) {
01068 WARN_TYPE_OR_LENGTH(size)
01069 }
01070 break;
01071 case SQL_C_ULONG:
01072 if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
01073 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
01074 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
01075 type != RQ_INTEGER4) {
01076 WARN_TYPE_OR_LENGTH(size)
01077 }
01078 break;
01079 case SQL_INTEGER:
01080 case SQL_C_SLONG:
01081 if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
01082 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
01083 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
01084 type != RQ_INTEGER4) {
01085 WARN_TYPE_OR_LENGTH(size)
01086 }
01087 break;
01088 case SQL_C_UBIGINT:
01089 if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
01090 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
01091 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
01092 type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
01093 type != RQ_INTEGER8) {
01094 WARN_TYPE_OR_LENGTH(size)
01095 }
01096 break;
01097 case SQL_BIGINT:
01098 case SQL_C_SBIGINT:
01099 if (type != RQ_UINTEGER1 && type != RQ_INTEGER1 &&
01100 type != RQ_UINTEGER2 && type != RQ_INTEGER2 &&
01101 type != RQ_UINTEGER3 && type != RQ_INTEGER3 &&
01102 type != RQ_UINTEGER4 && type != RQ_INTEGER4 &&
01103 type != RQ_INTEGER8) {
01104 WARN_TYPE_OR_LENGTH(size)
01105 }
01106 break;
01107 #undef WARN_TYPE_OR_LENGTH
01108 case SQL_NUMERIC:
01109 case SQL_DECIMAL:
01110 case SQL_FLOAT:
01111 case SQL_REAL:
01112 case SQL_DOUBLE:
01113 if (!ast_rq_is_int(type) && type != RQ_FLOAT) {
01114 warn_type(col, type);
01115 }
01116 break;
01117 default:
01118 ast_log(LOG_WARNING, "Realtime table %s@%s: column type (%d) unrecognized for column '%s'\n", table, database, col->type, elm);
01119 }
01120 break;
01121 }
01122 }
01123 if (!col) {
01124 ast_log(LOG_WARNING, "Realtime table %s@%s requires column '%s', but that column does not exist!\n", table, database, elm);
01125 }
01126 }
01127 va_end(ap);
01128 AST_RWLIST_UNLOCK(&tableptr->columns);
01129 return 0;
01130 }
01131 #undef warn_length
01132 #undef warn_type
01133
01134 static int unload_odbc(const char *a, const char *b)
01135 {
01136 return ast_odbc_clear_cache(a, b);
01137 }
01138
01139 static struct ast_config_engine odbc_engine = {
01140 .name = "odbc",
01141 .load_func = config_odbc,
01142 .realtime_func = realtime_odbc,
01143 .realtime_multi_func = realtime_multi_odbc,
01144 .store_func = store_odbc,
01145 .destroy_func = destroy_odbc,
01146 .update_func = update_odbc,
01147 .update2_func = update2_odbc,
01148 .require_func = require_odbc,
01149 .unload_func = unload_odbc,
01150 };
01151
01152 static int unload_module (void)
01153 {
01154 ast_config_engine_deregister(&odbc_engine);
01155
01156 ast_verb(1, "res_config_odbc unloaded.\n");
01157 return 0;
01158 }
01159
01160 static int load_module (void)
01161 {
01162 ast_config_engine_register(&odbc_engine);
01163 ast_verb(1, "res_config_odbc loaded.\n");
01164 return 0;
01165 }
01166
01167 static int reload_module(void)
01168 {
01169 return 0;
01170 }
01171
01172 AST_MODULE_INFO(ASTERISK_GPL_KEY, AST_MODFLAG_LOAD_ORDER, "Realtime ODBC configuration",
01173 .load = load_module,
01174 .unload = unload_module,
01175 .reload = reload_module,
01176 .load_pri = AST_MODPRI_REALTIME_DRIVER,
01177 );