2 * Asterisk -- An open source telephony toolkit.
4 * Copyright (C) 1999 - 2005, Digium, Inc.
6 * Mark Spencer <markster@digium.com>
8 * res_odbc.c <ODBC resource manager>
9 * Copyright (C) 2004 - 2005 Anthony Minessale II <anthmct@yahoo.com>
11 * See http://www.asterisk.org for more information about
12 * the Asterisk project. Please do not directly contact
13 * any of the maintainers of this project for assistance;
14 * the project provides a web site, mailing lists and IRC
15 * channels for your use.
17 * This program is free software, distributed under the terms of
18 * the GNU General Public License Version 2. See the LICENSE file
19 * at the top of the source tree.
24 * \brief ODBC resource manager
26 * \author Mark Spencer <markster@digium.com>
27 * \author Anthony Minessale II <anthmct@yahoo.com>
29 * \arg See also: \ref cdr_odbc
33 <depend>unixodbc</depend>
39 ASTERISK_FILE_VERSION(__FILE__
, "$Revision$")
46 #include "asterisk/file.h"
47 #include "asterisk/logger.h"
48 #include "asterisk/channel.h"
49 #include "asterisk/config.h"
50 #include "asterisk/options.h"
51 #include "asterisk/pbx.h"
52 #include "asterisk/module.h"
53 #include "asterisk/cli.h"
54 #include "asterisk/lock.h"
55 #include "asterisk/res_odbc.h"
59 AST_LIST_ENTRY(odbc_class
) list
;
65 unsigned int haspool
:1; /* Boolean - TDS databases need this */
66 unsigned int limit
:10; /* Gives a limit of 1023 maximum */
67 unsigned int count
:10; /* Running count of pooled connections */
68 unsigned int delme
:1; /* Purge the class */
69 AST_LIST_HEAD(, odbc_obj
) odbc_obj
;
72 AST_LIST_HEAD_STATIC(odbc_list
, odbc_class
);
74 static odbc_status
odbc_obj_connect(struct odbc_obj
*obj
);
75 static odbc_status
odbc_obj_disconnect(struct odbc_obj
*obj
);
76 static int odbc_register_class(struct odbc_class
*class, int connect
);
79 SQLHSTMT
ast_odbc_prepare_and_execute(struct odbc_obj
*obj
, SQLHSTMT (*prepare_cb
)(struct odbc_obj
*obj
, void *data
), void *data
)
81 int res
= 0, i
, attempt
;
82 SQLINTEGER nativeerror
=0, numfields
=0;
83 SQLSMALLINT diagbytes
=0;
84 unsigned char state
[10], diagnostic
[256];
87 for (attempt
= 0; attempt
< 2; attempt
++) {
88 /* This prepare callback may do more than just prepare -- it may also
89 * bind parameters, bind results, etc. The real key, here, is that
90 * when we disconnect, all handles become invalid for most databases.
91 * We must therefore redo everything when we establish a new
93 stmt
= prepare_cb(obj
, data
);
96 res
= SQLExecute(stmt
);
97 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
) && (res
!= SQL_NO_DATA
)) {
98 if (res
== SQL_ERROR
) {
99 SQLGetDiagField(SQL_HANDLE_STMT
, stmt
, 1, SQL_DIAG_NUMBER
, &numfields
, SQL_IS_INTEGER
, &diagbytes
);
100 for (i
= 0; i
< numfields
; i
++) {
101 SQLGetDiagRec(SQL_HANDLE_STMT
, stmt
, i
+ 1, state
, &nativeerror
, diagnostic
, sizeof(diagnostic
), &diagbytes
);
102 ast_log(LOG_WARNING
, "SQL Execute returned an error %d: %s: %s (%d)\n", res
, state
, diagnostic
, diagbytes
);
104 ast_log(LOG_WARNING
, "Oh, that was good. There are really %d diagnostics?\n", (int)numfields
);
110 ast_log(LOG_WARNING
, "SQL Execute error %d! Attempting a reconnect...\n", res
);
111 SQLFreeHandle(SQL_HANDLE_STMT
, stmt
);
116 * While this isn't the best way to try to correct an error, this won't automatically
117 * fail when the statement handle invalidates.
119 /* XXX Actually, it might, if we're using a non-pooled connection. Possible race here. XXX */
120 odbc_obj_disconnect(obj
);
121 odbc_obj_connect(obj
);
126 ast_log(LOG_WARNING
, "SQL Prepare failed. Attempting a reconnect...\n");
127 odbc_obj_disconnect(obj
);
128 odbc_obj_connect(obj
);
135 int ast_odbc_smart_execute(struct odbc_obj
*obj
, SQLHSTMT stmt
)
138 SQLINTEGER nativeerror
=0, numfields
=0;
139 SQLSMALLINT diagbytes
=0;
140 unsigned char state
[10], diagnostic
[256];
142 res
= SQLExecute(stmt
);
143 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
) && (res
!= SQL_NO_DATA
)) {
144 if (res
== SQL_ERROR
) {
145 SQLGetDiagField(SQL_HANDLE_STMT
, stmt
, 1, SQL_DIAG_NUMBER
, &numfields
, SQL_IS_INTEGER
, &diagbytes
);
146 for (i
= 0; i
< numfields
; i
++) {
147 SQLGetDiagRec(SQL_HANDLE_STMT
, stmt
, i
+ 1, state
, &nativeerror
, diagnostic
, sizeof(diagnostic
), &diagbytes
);
148 ast_log(LOG_WARNING
, "SQL Execute returned an error %d: %s: %s (%d)\n", res
, state
, diagnostic
, diagbytes
);
150 ast_log(LOG_WARNING
, "Oh, that was good. There are really %d diagnostics?\n", (int)numfields
);
156 /* This is a really bad method of trying to correct a dead connection. It
157 * only ever really worked with MySQL. It will not work with any other
158 * database, since most databases prepare their statements on the server,
159 * and if you disconnect, you invalidate the statement handle. Hence, if
160 * you disconnect, you're going to fail anyway, whether you try to execute
161 * a second time or not.
163 ast_log(LOG_WARNING
, "SQL Execute error %d! Attempting a reconnect...\n", res
);
164 ast_mutex_lock(&obj
->lock
);
166 ast_mutex_unlock(&obj
->lock
);
167 odbc_obj_disconnect(obj
);
168 odbc_obj_connect(obj
);
169 res
= SQLExecute(stmt
);
177 int ast_odbc_sanity_check(struct odbc_obj
*obj
)
179 char *test_sql
= "select 1";
184 res
= SQLAllocHandle(SQL_HANDLE_STMT
, obj
->con
, &stmt
);
185 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
188 res
= SQLPrepare(stmt
, (unsigned char *)test_sql
, SQL_NTS
);
189 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
192 res
= SQLExecute(stmt
);
193 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
198 SQLFreeHandle (SQL_HANDLE_STMT
, stmt
);
201 if (!obj
->up
) { /* Try to reconnect! */
202 ast_log(LOG_WARNING
, "Connection is down attempting to reconnect...\n");
203 odbc_obj_disconnect(obj
);
204 odbc_obj_connect(obj
);
209 static int load_odbc_config(void)
211 static char *cfg
= "res_odbc.conf";
212 struct ast_config
*config
;
213 struct ast_variable
*v
;
214 char *cat
, *dsn
, *username
, *password
;
215 int enabled
, pooling
, limit
;
216 int connect
= 0, res
= 0;
218 struct odbc_class
*new;
220 config
= ast_config_load(cfg
);
222 ast_log(LOG_WARNING
, "Unable to load config file res_odbc.conf\n");
225 for (cat
= ast_category_browse(config
, NULL
); cat
; cat
=ast_category_browse(config
, cat
)) {
226 if (!strcasecmp(cat
, "ENV")) {
227 for (v
= ast_variable_browse(config
, cat
); v
; v
= v
->next
) {
228 setenv(v
->name
, v
->value
, 1);
229 ast_log(LOG_NOTICE
, "Adding ENV var: %s=%s\n", v
->name
, v
->value
);
232 /* Reset all to defaults for each class of odbc connections */
233 dsn
= username
= password
= NULL
;
238 for (v
= ast_variable_browse(config
, cat
); v
; v
= v
->next
) {
239 if (!strcasecmp(v
->name
, "pooling")) {
240 if (ast_true(v
->value
))
242 } else if (!strcasecmp(v
->name
, "limit")) {
243 sscanf(v
->value
, "%d", &limit
);
244 if (ast_true(v
->value
) && !limit
) {
245 ast_log(LOG_WARNING
, "Limit should be a number, not a boolean: '%s'. Setting limit to 1023 for ODBC class '%s'.\n", v
->value
, cat
);
247 } else if (ast_false(v
->value
)) {
248 ast_log(LOG_WARNING
, "Limit should be a number, not a boolean: '%s'. Disabling ODBC class '%s'.\n", v
->value
, cat
);
252 } else if (!strcasecmp(v
->name
, "enabled")) {
253 enabled
= ast_true(v
->value
);
254 } else if (!strcasecmp(v
->name
, "pre-connect")) {
255 connect
= ast_true(v
->value
);
256 } else if (!strcasecmp(v
->name
, "dsn")) {
258 } else if (!strcasecmp(v
->name
, "username")) {
260 } else if (!strcasecmp(v
->name
, "password")) {
265 if (enabled
&& !ast_strlen_zero(dsn
)) {
266 new = ast_calloc(1, sizeof(*new));
274 ast_copy_string(new->name
, cat
, sizeof(new->name
));
276 ast_copy_string(new->dsn
, dsn
, sizeof(new->dsn
));
278 ast_copy_string(new->username
, username
, sizeof(new->username
));
280 ast_copy_string(new->password
, password
, sizeof(new->password
));
282 SQLAllocHandle(SQL_HANDLE_ENV
, SQL_NULL_HANDLE
, &new->env
);
283 res
= SQLSetEnvAttr(new->env
, SQL_ATTR_ODBC_VERSION
, (void *) SQL_OV_ODBC3
, 0);
285 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
286 ast_log(LOG_WARNING
, "res_odbc: Error SetEnv\n");
287 SQLFreeHandle(SQL_HANDLE_ENV
, new->env
);
292 new->haspool
= pooling
;
296 ast_log(LOG_WARNING
, "Pooling without also setting a limit is pointless. Changing limit from 0 to 5.\n");
301 odbc_register_class(new, connect
);
302 ast_log(LOG_NOTICE
, "Registered ODBC class '%s' dsn->[%s]\n", cat
, dsn
);
306 ast_config_destroy(config
);
310 static int odbc_show_command(int fd
, int argc
, char **argv
)
312 struct odbc_class
*class;
313 struct odbc_obj
*current
;
315 AST_LIST_LOCK(&odbc_list
);
316 AST_LIST_TRAVERSE(&odbc_list
, class, list
) {
317 if ((argc
== 2) || (argc
== 3 && !strcmp(argv
[2], "all")) || (!strcmp(argv
[2], class->name
))) {
319 ast_cli(fd
, "Name: %s\nDSN: %s\n", class->name
, class->dsn
);
321 if (class->haspool
) {
322 ast_cli(fd
, "Pooled: yes\nLimit: %d\nConnections in use: %d\n", class->limit
, class->count
);
324 AST_LIST_TRAVERSE(&(class->odbc_obj
), current
, list
) {
325 ast_cli(fd
, " Connection %d: %s\n", ++count
, current
->up
&& ast_odbc_sanity_check(current
) ? "connected" : "disconnected");
328 /* Should only ever be one of these */
329 AST_LIST_TRAVERSE(&(class->odbc_obj
), current
, list
) {
330 ast_cli(fd
, "Pooled: no\nConnected: %s\n", current
->up
&& ast_odbc_sanity_check(current
) ? "yes" : "no");
337 AST_LIST_UNLOCK(&odbc_list
);
342 static char show_usage
[] =
343 "Usage: odbc show [<class>]\n"
344 " List settings of a particular ODBC class.\n"
345 " or, if not specified, all classes.\n";
347 static struct ast_cli_entry cli_odbc
[] = {
348 { { "odbc", "show", NULL
},
349 odbc_show_command
, "List ODBC DSN(s)",
353 static int odbc_register_class(struct odbc_class
*class, int connect
)
355 struct odbc_obj
*obj
;
357 AST_LIST_LOCK(&odbc_list
);
358 AST_LIST_INSERT_HEAD(&odbc_list
, class, list
);
359 AST_LIST_UNLOCK(&odbc_list
);
362 /* Request and release builds a connection */
363 obj
= ast_odbc_request_obj(class->name
, 0);
365 ast_odbc_release_obj(obj
);
370 ast_log(LOG_WARNING
, "Attempted to register a NULL class?\n");
375 void ast_odbc_release_obj(struct odbc_obj
*obj
)
377 /* For pooled connections, this frees the connection to be
378 * reused. For non-pooled connections, it does nothing. */
382 struct odbc_obj
*ast_odbc_request_obj(const char *name
, int check
)
384 struct odbc_obj
*obj
= NULL
;
385 struct odbc_class
*class;
387 AST_LIST_LOCK(&odbc_list
);
388 AST_LIST_TRAVERSE(&odbc_list
, class, list
) {
389 if (!strcmp(class->name
, name
))
392 AST_LIST_UNLOCK(&odbc_list
);
397 AST_LIST_LOCK(&class->odbc_obj
);
398 if (class->haspool
) {
399 /* Recycle connections before building another */
400 AST_LIST_TRAVERSE(&class->odbc_obj
, obj
, list
) {
407 if (!obj
&& (class->count
< class->limit
)) {
409 obj
= ast_calloc(1, sizeof(*obj
));
411 AST_LIST_UNLOCK(&class->odbc_obj
);
414 ast_mutex_init(&obj
->lock
);
416 if (odbc_obj_connect(obj
) == ODBC_FAIL
) {
417 ast_log(LOG_WARNING
, "Failed to connect to %s\n", name
);
418 ast_mutex_destroy(&obj
->lock
);
424 AST_LIST_INSERT_TAIL(&class->odbc_obj
, obj
, list
);
428 /* Non-pooled connection: multiple modules can use the same connection. */
429 AST_LIST_TRAVERSE(&class->odbc_obj
, obj
, list
) {
430 /* Non-pooled connection: if there is an entry, return it */
435 /* No entry: build one */
436 obj
= ast_calloc(1, sizeof(*obj
));
438 AST_LIST_UNLOCK(&class->odbc_obj
);
441 ast_mutex_init(&obj
->lock
);
443 if (odbc_obj_connect(obj
) == ODBC_FAIL
) {
444 ast_log(LOG_WARNING
, "Failed to connect to %s\n", name
);
445 ast_mutex_destroy(&obj
->lock
);
449 AST_LIST_INSERT_HEAD(&class->odbc_obj
, obj
, list
);
453 AST_LIST_UNLOCK(&class->odbc_obj
);
456 ast_odbc_sanity_check(obj
);
461 static odbc_status
odbc_obj_disconnect(struct odbc_obj
*obj
)
464 ast_mutex_lock(&obj
->lock
);
466 res
= SQLDisconnect(obj
->con
);
468 if (res
== ODBC_SUCCESS
) {
469 ast_log(LOG_WARNING
, "res_odbc: disconnected %d from %s [%s]\n", res
, obj
->parent
->name
, obj
->parent
->dsn
);
471 ast_log(LOG_WARNING
, "res_odbc: %s [%s] already disconnected\n",
472 obj
->parent
->name
, obj
->parent
->dsn
);
475 ast_mutex_unlock(&obj
->lock
);
479 static odbc_status
odbc_obj_connect(struct odbc_obj
*obj
)
484 unsigned char msg
[200], stat
[10];
486 SQLINTEGER enable
= 1;
487 char *tracefile
= "/tmp/odbc.trace";
489 ast_mutex_lock(&obj
->lock
);
491 res
= SQLAllocHandle(SQL_HANDLE_DBC
, obj
->parent
->env
, &obj
->con
);
493 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
494 ast_log(LOG_WARNING
, "res_odbc: Error AllocHDB %d\n", res
);
495 ast_mutex_unlock(&obj
->lock
);
498 SQLSetConnectAttr(obj
->con
, SQL_LOGIN_TIMEOUT
, (SQLPOINTER
*) 10, 0);
500 SQLSetConnectAttr(obj
->con
, SQL_ATTR_TRACE
, &enable
, SQL_IS_INTEGER
);
501 SQLSetConnectAttr(obj
->con
, SQL_ATTR_TRACEFILE
, tracefile
, strlen(tracefile
));
505 odbc_obj_disconnect(obj
);
506 ast_log(LOG_NOTICE
, "Re-connecting %s\n", obj
->parent
->name
);
508 ast_log(LOG_NOTICE
, "Connecting %s\n", obj
->parent
->name
);
511 res
= SQLConnect(obj
->con
,
512 (SQLCHAR
*) obj
->parent
->dsn
, SQL_NTS
,
513 (SQLCHAR
*) obj
->parent
->username
, SQL_NTS
,
514 (SQLCHAR
*) obj
->parent
->password
, SQL_NTS
);
516 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
517 SQLGetDiagRec(SQL_HANDLE_DBC
, obj
->con
, 1, stat
, &err
, msg
, 100, &mlen
);
518 ast_mutex_unlock(&obj
->lock
);
519 ast_log(LOG_WARNING
, "res_odbc: Error SQLConnect=%d errno=%d %s\n", res
, (int)err
, msg
);
522 ast_log(LOG_NOTICE
, "res_odbc: Connected to %s [%s]\n", obj
->parent
->name
, obj
->parent
->dsn
);
526 ast_mutex_unlock(&obj
->lock
);
530 static int reload(void)
532 static char *cfg
= "res_odbc.conf";
533 struct ast_config
*config
;
534 struct ast_variable
*v
;
535 char *cat
, *dsn
, *username
, *password
;
536 int enabled
, pooling
, limit
;
537 int connect
= 0, res
= 0;
539 struct odbc_class
*new, *class;
540 struct odbc_obj
*current
;
542 /* First, mark all to be purged */
543 AST_LIST_LOCK(&odbc_list
);
544 AST_LIST_TRAVERSE(&odbc_list
, class, list
) {
548 config
= ast_config_load(cfg
);
550 for (cat
= ast_category_browse(config
, NULL
); cat
; cat
=ast_category_browse(config
, cat
)) {
551 if (!strcasecmp(cat
, "ENV")) {
552 for (v
= ast_variable_browse(config
, cat
); v
; v
= v
->next
) {
553 setenv(v
->name
, v
->value
, 1);
554 ast_log(LOG_NOTICE
, "Adding ENV var: %s=%s\n", v
->name
, v
->value
);
557 /* Reset all to defaults for each class of odbc connections */
558 dsn
= username
= password
= NULL
;
563 for (v
= ast_variable_browse(config
, cat
); v
; v
= v
->next
) {
564 if (!strcasecmp(v
->name
, "pooling")) {
566 } else if (!strcasecmp(v
->name
, "limit")) {
567 sscanf(v
->value
, "%d", &limit
);
568 if (ast_true(v
->value
) && !limit
) {
569 ast_log(LOG_WARNING
, "Limit should be a number, not a boolean: '%s'. Setting limit to 1023 for ODBC class '%s'.\n", v
->value
, cat
);
571 } else if (ast_false(v
->value
)) {
572 ast_log(LOG_WARNING
, "Limit should be a number, not a boolean: '%s'. Disabling ODBC class '%s'.\n", v
->value
, cat
);
576 } else if (!strcasecmp(v
->name
, "enabled")) {
577 enabled
= ast_true(v
->value
);
578 } else if (!strcasecmp(v
->name
, "pre-connect")) {
579 connect
= ast_true(v
->value
);
580 } else if (!strcasecmp(v
->name
, "dsn")) {
582 } else if (!strcasecmp(v
->name
, "username")) {
584 } else if (!strcasecmp(v
->name
, "password")) {
589 if (enabled
&& !ast_strlen_zero(dsn
)) {
590 /* First, check the list to see if it already exists */
591 AST_LIST_TRAVERSE(&odbc_list
, class, list
) {
592 if (!strcmp(class->name
, cat
)) {
601 new = ast_calloc(1, sizeof(*new));
610 ast_copy_string(new->name
, cat
, sizeof(new->name
));
612 ast_copy_string(new->dsn
, dsn
, sizeof(new->dsn
));
614 ast_copy_string(new->username
, username
, sizeof(new->username
));
616 ast_copy_string(new->password
, password
, sizeof(new->password
));
619 SQLAllocHandle(SQL_HANDLE_ENV
, SQL_NULL_HANDLE
, &new->env
);
620 res
= SQLSetEnvAttr(new->env
, SQL_ATTR_ODBC_VERSION
, (void *) SQL_OV_ODBC3
, 0);
622 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
623 ast_log(LOG_WARNING
, "res_odbc: Error SetEnv\n");
624 SQLFreeHandle(SQL_HANDLE_ENV
, new->env
);
625 AST_LIST_UNLOCK(&odbc_list
);
631 new->haspool
= pooling
;
635 ast_log(LOG_WARNING
, "Pooling without also setting a limit is pointless. Changing limit from 0 to 5.\n");
641 ast_log(LOG_NOTICE
, "Refreshing ODBC class '%s' dsn->[%s]\n", cat
, dsn
);
643 odbc_register_class(new, connect
);
644 ast_log(LOG_NOTICE
, "Registered ODBC class '%s' dsn->[%s]\n", cat
, dsn
);
649 ast_config_destroy(config
);
652 /* Purge classes that we know can go away (pooled with 0, only) */
653 AST_LIST_TRAVERSE_SAFE_BEGIN(&odbc_list
, class, list
) {
654 if (class->delme
&& class->haspool
&& class->count
== 0) {
655 AST_LIST_TRAVERSE_SAFE_BEGIN(&(class->odbc_obj
), current
, list
) {
656 AST_LIST_REMOVE_CURRENT(&(class->odbc_obj
), list
);
657 odbc_obj_disconnect(current
);
658 ast_mutex_destroy(¤t
->lock
);
661 AST_LIST_TRAVERSE_SAFE_END
;
663 AST_LIST_REMOVE_CURRENT(&odbc_list
, list
);
667 AST_LIST_TRAVERSE_SAFE_END
;
668 AST_LIST_UNLOCK(&odbc_list
);
673 static int unload_module(void)
675 /* Prohibit unloading */
679 static int load_module(void)
681 if(load_odbc_config() == -1)
682 return AST_MODULE_LOAD_DECLINE
;
683 ast_cli_register_multiple(cli_odbc
, sizeof(cli_odbc
) / sizeof(struct ast_cli_entry
));
684 ast_log(LOG_NOTICE
, "res_odbc loaded.\n");
688 AST_MODULE_INFO(ASTERISK_GPL_KEY
, AST_MODFLAG_GLOBAL_SYMBOLS
, "ODBC Resource",
690 .unload
= unload_module
,