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>
38 ASTERISK_FILE_VERSION(__FILE__
, "$Revision$")
45 #include "asterisk/file.h"
46 #include "asterisk/logger.h"
47 #include "asterisk/channel.h"
48 #include "asterisk/config.h"
49 #include "asterisk/options.h"
50 #include "asterisk/pbx.h"
51 #include "asterisk/module.h"
52 #include "asterisk/cli.h"
53 #include "asterisk/lock.h"
54 #include "asterisk/res_odbc.h"
58 AST_LIST_ENTRY(odbc_class
) list
;
64 unsigned int haspool
:1; /* Boolean - TDS databases need this */
65 unsigned int limit
:10; /* Gives a limit of 1023 maximum */
66 unsigned int count
:10; /* Running count of pooled connections */
67 unsigned int delme
:1; /* Purge the class */
68 AST_LIST_HEAD(, odbc_obj
) odbc_obj
;
71 AST_LIST_HEAD_STATIC(odbc_list
, odbc_class
);
73 static odbc_status
odbc_obj_connect(struct odbc_obj
*obj
);
74 static odbc_status
odbc_obj_disconnect(struct odbc_obj
*obj
);
75 static int odbc_register_class(struct odbc_class
*class, int connect
);
78 SQLHSTMT
ast_odbc_prepare_and_execute(struct odbc_obj
*obj
, SQLHSTMT (*prepare_cb
)(struct odbc_obj
*obj
, void *data
), void *data
)
80 int res
= 0, i
, attempt
;
81 SQLINTEGER nativeerror
=0, numfields
=0;
82 SQLSMALLINT diagbytes
=0;
83 unsigned char state
[10], diagnostic
[256];
86 for (attempt
= 0; attempt
< 2; attempt
++) {
87 /* This prepare callback may do more than just prepare -- it may also
88 * bind parameters, bind results, etc. The real key, here, is that
89 * when we disconnect, all handles become invalid for most databases.
90 * We must therefore redo everything when we establish a new
92 stmt
= prepare_cb(obj
, data
);
95 res
= SQLExecute(stmt
);
96 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
) && (res
!= SQL_NO_DATA
)) {
97 if (res
== SQL_ERROR
) {
98 SQLGetDiagField(SQL_HANDLE_STMT
, stmt
, 1, SQL_DIAG_NUMBER
, &numfields
, SQL_IS_INTEGER
, &diagbytes
);
99 for (i
=0; i
< numfields
+ 1; i
++) {
100 SQLGetDiagRec(SQL_HANDLE_STMT
, stmt
, i
+ 1, state
, &nativeerror
, diagnostic
, sizeof(diagnostic
), &diagbytes
);
101 ast_log(LOG_WARNING
, "SQL Execute returned an error %d: %s: %s (%d)\n", res
, state
, diagnostic
, diagbytes
);
103 ast_log(LOG_WARNING
, "Oh, that was good. There are really %d diagnostics?\n", (int)numfields
);
109 ast_log(LOG_WARNING
, "SQL Execute error %d! Attempting a reconnect...\n", res
);
110 SQLFreeHandle(SQL_HANDLE_STMT
, stmt
);
114 * While this isn't the best way to try to correct an error, this won't automatically
115 * fail when the statement handle invalidates.
117 /* XXX Actually, it might, if we're using a non-pooled connection. Possible race here. XXX */
118 odbc_obj_disconnect(obj
);
119 odbc_obj_connect(obj
);
124 ast_log(LOG_WARNING
, "SQL Prepare failed. Attempting a reconnect...\n");
125 odbc_obj_disconnect(obj
);
126 odbc_obj_connect(obj
);
133 int ast_odbc_smart_execute(struct odbc_obj
*obj
, SQLHSTMT stmt
)
136 SQLINTEGER nativeerror
=0, numfields
=0;
137 SQLSMALLINT diagbytes
=0;
138 unsigned char state
[10], diagnostic
[256];
140 res
= SQLExecute(stmt
);
141 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
) && (res
!= SQL_NO_DATA
)) {
142 if (res
== SQL_ERROR
) {
143 SQLGetDiagField(SQL_HANDLE_STMT
, stmt
, 1, SQL_DIAG_NUMBER
, &numfields
, SQL_IS_INTEGER
, &diagbytes
);
144 for (i
=0; i
< numfields
+ 1; i
++) {
145 SQLGetDiagRec(SQL_HANDLE_STMT
, stmt
, i
+ 1, state
, &nativeerror
, diagnostic
, sizeof(diagnostic
), &diagbytes
);
146 ast_log(LOG_WARNING
, "SQL Execute returned an error %d: %s: %s (%d)\n", res
, state
, diagnostic
, diagbytes
);
148 ast_log(LOG_WARNING
, "Oh, that was good. There are really %d diagnostics?\n", (int)numfields
);
154 /* This is a really bad method of trying to correct a dead connection. It
155 * only ever really worked with MySQL. It will not work with any other
156 * database, since most databases prepare their statements on the server,
157 * and if you disconnect, you invalidate the statement handle. Hence, if
158 * you disconnect, you're going to fail anyway, whether you try to execute
159 * a second time or not.
161 ast_log(LOG_WARNING
, "SQL Execute error %d! Attempting a reconnect...\n", res
);
162 ast_mutex_lock(&obj
->lock
);
164 ast_mutex_unlock(&obj
->lock
);
165 odbc_obj_disconnect(obj
);
166 odbc_obj_connect(obj
);
167 res
= SQLExecute(stmt
);
175 int ast_odbc_sanity_check(struct odbc_obj
*obj
)
177 char *test_sql
= "select 1";
182 res
= SQLAllocHandle(SQL_HANDLE_STMT
, obj
->con
, &stmt
);
183 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
186 res
= SQLPrepare(stmt
, (unsigned char *)test_sql
, SQL_NTS
);
187 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
190 res
= SQLExecute(stmt
);
191 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
196 SQLFreeHandle (SQL_HANDLE_STMT
, stmt
);
199 if (!obj
->up
) { /* Try to reconnect! */
200 ast_log(LOG_WARNING
, "Connection is down attempting to reconnect...\n");
201 odbc_obj_disconnect(obj
);
202 odbc_obj_connect(obj
);
207 static int load_odbc_config(void)
209 static char *cfg
= "res_odbc.conf";
210 struct ast_config
*config
;
211 struct ast_variable
*v
;
212 char *cat
, *dsn
, *username
, *password
;
213 int enabled
, pooling
, limit
;
214 int connect
= 0, res
= 0;
216 struct odbc_class
*new;
218 config
= ast_config_load(cfg
);
220 ast_log(LOG_WARNING
, "Unable to load config file res_odbc.conf\n");
223 for (cat
= ast_category_browse(config
, NULL
); cat
; cat
=ast_category_browse(config
, cat
)) {
224 if (!strcasecmp(cat
, "ENV")) {
225 for (v
= ast_variable_browse(config
, cat
); v
; v
= v
->next
) {
226 setenv(v
->name
, v
->value
, 1);
227 ast_log(LOG_NOTICE
, "Adding ENV var: %s=%s\n", v
->name
, v
->value
);
230 /* Reset all to defaults for each class of odbc connections */
231 dsn
= username
= password
= NULL
;
236 for (v
= ast_variable_browse(config
, cat
); v
; v
= v
->next
) {
237 if (!strcasecmp(v
->name
, "pooling")) {
238 if (ast_true(v
->value
))
240 } else if (!strcasecmp(v
->name
, "limit")) {
241 sscanf(v
->value
, "%d", &limit
);
242 if (ast_true(v
->value
) && !limit
) {
243 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
);
245 } else if (ast_false(v
->value
)) {
246 ast_log(LOG_WARNING
, "Limit should be a number, not a boolean: '%s'. Disabling ODBC class '%s'.\n", v
->value
, cat
);
250 } else if (!strcasecmp(v
->name
, "enabled")) {
251 enabled
= ast_true(v
->value
);
252 } else if (!strcasecmp(v
->name
, "pre-connect")) {
253 connect
= ast_true(v
->value
);
254 } else if (!strcasecmp(v
->name
, "dsn")) {
256 } else if (!strcasecmp(v
->name
, "username")) {
258 } else if (!strcasecmp(v
->name
, "password")) {
263 if (enabled
&& !ast_strlen_zero(dsn
)) {
264 new = ast_calloc(1, sizeof(*new));
272 ast_copy_string(new->name
, cat
, sizeof(new->name
));
274 ast_copy_string(new->dsn
, dsn
, sizeof(new->dsn
));
276 ast_copy_string(new->username
, username
, sizeof(new->username
));
278 ast_copy_string(new->password
, password
, sizeof(new->password
));
280 SQLAllocHandle(SQL_HANDLE_ENV
, SQL_NULL_HANDLE
, &new->env
);
281 res
= SQLSetEnvAttr(new->env
, SQL_ATTR_ODBC_VERSION
, (void *) SQL_OV_ODBC3
, 0);
283 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
284 ast_log(LOG_WARNING
, "res_odbc: Error SetEnv\n");
285 SQLFreeHandle(SQL_HANDLE_ENV
, new->env
);
290 new->haspool
= pooling
;
294 ast_log(LOG_WARNING
, "Pooling without also setting a limit is pointless. Changing limit from 0 to 5.\n");
299 odbc_register_class(new, connect
);
300 ast_log(LOG_NOTICE
, "Registered ODBC class '%s' dsn->[%s]\n", cat
, dsn
);
304 ast_config_destroy(config
);
308 static int odbc_show_command(int fd
, int argc
, char **argv
)
310 struct odbc_class
*class;
311 struct odbc_obj
*current
;
313 AST_LIST_LOCK(&odbc_list
);
314 AST_LIST_TRAVERSE(&odbc_list
, class, list
) {
315 if ((argc
== 2) || (argc
== 3 && !strcmp(argv
[2], "all")) || (!strcmp(argv
[2], class->name
))) {
317 ast_cli(fd
, "Name: %s\nDSN: %s\n", class->name
, class->dsn
);
319 if (class->haspool
) {
320 ast_cli(fd
, "Pooled: yes\nLimit: %d\nConnections in use: %d\n", class->limit
, class->count
);
322 AST_LIST_TRAVERSE(&(class->odbc_obj
), current
, list
) {
323 ast_cli(fd
, " Connection %d: %s", ++count
, current
->up
&& ast_odbc_sanity_check(current
) ? "connected" : "disconnected");
326 /* Should only ever be one of these */
327 AST_LIST_TRAVERSE(&(class->odbc_obj
), current
, list
) {
328 ast_cli(fd
, "Pooled: no\nConnected: %s\n", current
->up
&& ast_odbc_sanity_check(current
) ? "yes" : "no");
335 AST_LIST_UNLOCK(&odbc_list
);
340 static char show_usage
[] =
341 "Usage: odbc list [<class>]\n"
342 " List settings of a particular ODBC class.\n"
343 " or, if not specified, all classes.\n";
345 static struct ast_cli_entry cli_odbc_show_deprecated
= {
346 { "odbc", "show", NULL
},
347 odbc_show_command
, NULL
,
350 static struct ast_cli_entry cli_odbc
[] = {
351 { { "odbc", "list", NULL
},
352 odbc_show_command
, "List ODBC DSN(s)",
353 show_usage
, NULL
, &cli_odbc_show_deprecated
},
356 static int odbc_register_class(struct odbc_class
*class, int connect
)
358 struct odbc_obj
*obj
;
360 AST_LIST_LOCK(&odbc_list
);
361 AST_LIST_INSERT_HEAD(&odbc_list
, class, list
);
362 AST_LIST_UNLOCK(&odbc_list
);
365 /* Request and release builds a connection */
366 obj
= ast_odbc_request_obj(class->name
, 0);
367 ast_odbc_release_obj(obj
);
372 ast_log(LOG_WARNING
, "Attempted to register a NULL class?\n");
377 void ast_odbc_release_obj(struct odbc_obj
*obj
)
379 /* For pooled connections, this frees the connection to be
380 * reused. For non-pooled connections, it does nothing. */
384 struct odbc_obj
*ast_odbc_request_obj(const char *name
, int check
)
386 struct odbc_obj
*obj
= NULL
;
387 struct odbc_class
*class;
389 AST_LIST_LOCK(&odbc_list
);
390 AST_LIST_TRAVERSE(&odbc_list
, class, list
) {
391 if (!strcmp(class->name
, name
))
394 AST_LIST_UNLOCK(&odbc_list
);
399 AST_LIST_LOCK(&class->odbc_obj
);
400 if (class->haspool
) {
401 /* Recycle connections before building another */
402 AST_LIST_TRAVERSE(&class->odbc_obj
, obj
, list
) {
409 if (!obj
&& (class->count
< class->limit
)) {
411 obj
= ast_calloc(1, sizeof(*obj
));
413 AST_LIST_UNLOCK(&class->odbc_obj
);
416 ast_mutex_init(&obj
->lock
);
418 odbc_obj_connect(obj
);
419 AST_LIST_INSERT_TAIL(&class->odbc_obj
, obj
, list
);
422 /* Non-pooled connection: multiple modules can use the same connection. */
423 AST_LIST_TRAVERSE(&class->odbc_obj
, obj
, list
) {
424 /* Non-pooled connection: if there is an entry, return it */
429 /* No entry: build one */
430 obj
= ast_calloc(1, sizeof(*obj
));
432 AST_LIST_UNLOCK(&class->odbc_obj
);
435 ast_mutex_init(&obj
->lock
);
437 if (odbc_obj_connect(obj
) == ODBC_FAIL
) {
438 ast_log(LOG_WARNING
, "Failed to connect\n");
439 ast_mutex_destroy(&obj
->lock
);
442 AST_LIST_INSERT_HEAD(&class->odbc_obj
, obj
, list
);
446 AST_LIST_UNLOCK(&class->odbc_obj
);
449 ast_odbc_sanity_check(obj
);
454 static odbc_status
odbc_obj_disconnect(struct odbc_obj
*obj
)
457 ast_mutex_lock(&obj
->lock
);
459 res
= SQLDisconnect(obj
->con
);
461 if (res
== ODBC_SUCCESS
) {
462 ast_log(LOG_WARNING
, "res_odbc: disconnected %d from %s [%s]\n", res
, obj
->parent
->name
, obj
->parent
->dsn
);
464 ast_log(LOG_WARNING
, "res_odbc: %s [%s] already disconnected\n",
465 obj
->parent
->name
, obj
->parent
->dsn
);
468 ast_mutex_unlock(&obj
->lock
);
472 static odbc_status
odbc_obj_connect(struct odbc_obj
*obj
)
477 unsigned char msg
[200], stat
[10];
479 SQLINTEGER enable
= 1;
480 char *tracefile
= "/tmp/odbc.trace";
482 ast_mutex_lock(&obj
->lock
);
484 res
= SQLAllocHandle(SQL_HANDLE_DBC
, obj
->parent
->env
, &obj
->con
);
486 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
488 ast_log(LOG_WARNING
, "res_odbc: Error AllocHDB %d\n", res
);
489 SQLFreeHandle(SQL_HANDLE_ENV
, obj
->parent
->env
);
491 ast_mutex_unlock(&obj
->lock
);
494 SQLSetConnectAttr(obj
->con
, SQL_LOGIN_TIMEOUT
, (SQLPOINTER
*) 10, 0);
496 SQLSetConnectAttr(obj
->con
, SQL_ATTR_TRACE
, &enable
, SQL_IS_INTEGER
);
497 SQLSetConnectAttr(obj
->con
, SQL_ATTR_TRACEFILE
, tracefile
, strlen(tracefile
));
501 odbc_obj_disconnect(obj
);
502 ast_log(LOG_NOTICE
, "Re-connecting %s\n", obj
->parent
->name
);
504 ast_log(LOG_NOTICE
, "Connecting %s\n", obj
->parent
->name
);
507 res
= SQLConnect(obj
->con
,
508 (SQLCHAR
*) obj
->parent
->dsn
, SQL_NTS
,
509 (SQLCHAR
*) obj
->parent
->username
, SQL_NTS
,
510 (SQLCHAR
*) obj
->parent
->password
, SQL_NTS
);
512 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
513 SQLGetDiagRec(SQL_HANDLE_DBC
, obj
->con
, 1, stat
, &err
, msg
, 100, &mlen
);
514 ast_mutex_unlock(&obj
->lock
);
515 ast_log(LOG_WARNING
, "res_odbc: Error SQLConnect=%d errno=%d %s\n", res
, (int)err
, msg
);
518 ast_log(LOG_NOTICE
, "res_odbc: Connected to %s [%s]\n", obj
->parent
->name
, obj
->parent
->dsn
);
522 ast_mutex_unlock(&obj
->lock
);
526 static int reload(void)
528 static char *cfg
= "res_odbc.conf";
529 struct ast_config
*config
;
530 struct ast_variable
*v
;
531 char *cat
, *dsn
, *username
, *password
;
532 int enabled
, pooling
, limit
;
533 int connect
= 0, res
= 0;
535 struct odbc_class
*new, *class;
536 struct odbc_obj
*current
;
538 /* First, mark all to be purged */
539 AST_LIST_LOCK(&odbc_list
);
540 AST_LIST_TRAVERSE(&odbc_list
, class, list
) {
544 config
= ast_config_load(cfg
);
546 for (cat
= ast_category_browse(config
, NULL
); cat
; cat
=ast_category_browse(config
, cat
)) {
547 if (!strcasecmp(cat
, "ENV")) {
548 for (v
= ast_variable_browse(config
, cat
); v
; v
= v
->next
) {
549 setenv(v
->name
, v
->value
, 1);
550 ast_log(LOG_NOTICE
, "Adding ENV var: %s=%s\n", v
->name
, v
->value
);
553 /* Reset all to defaults for each class of odbc connections */
554 dsn
= username
= password
= NULL
;
559 for (v
= ast_variable_browse(config
, cat
); v
; v
= v
->next
) {
560 if (!strcasecmp(v
->name
, "pooling")) {
562 } else if (!strcasecmp(v
->name
, "limit")) {
563 sscanf(v
->value
, "%d", &limit
);
564 if (ast_true(v
->value
) && !limit
) {
565 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
);
567 } else if (ast_false(v
->value
)) {
568 ast_log(LOG_WARNING
, "Limit should be a number, not a boolean: '%s'. Disabling ODBC class '%s'.\n", v
->value
, cat
);
572 } else if (!strcasecmp(v
->name
, "enabled")) {
573 enabled
= ast_true(v
->value
);
574 } else if (!strcasecmp(v
->name
, "pre-connect")) {
575 connect
= ast_true(v
->value
);
576 } else if (!strcasecmp(v
->name
, "dsn")) {
578 } else if (!strcasecmp(v
->name
, "username")) {
580 } else if (!strcasecmp(v
->name
, "password")) {
585 if (enabled
&& !ast_strlen_zero(dsn
)) {
586 /* First, check the list to see if it already exists */
587 AST_LIST_TRAVERSE(&odbc_list
, class, list
) {
588 if (!strcmp(class->name
, cat
)) {
597 new = ast_calloc(1, sizeof(*new));
606 ast_copy_string(new->name
, cat
, sizeof(new->name
));
608 ast_copy_string(new->dsn
, dsn
, sizeof(new->dsn
));
610 ast_copy_string(new->username
, username
, sizeof(new->username
));
612 ast_copy_string(new->password
, password
, sizeof(new->password
));
615 SQLAllocHandle(SQL_HANDLE_ENV
, SQL_NULL_HANDLE
, &new->env
);
616 res
= SQLSetEnvAttr(new->env
, SQL_ATTR_ODBC_VERSION
, (void *) SQL_OV_ODBC3
, 0);
618 if ((res
!= SQL_SUCCESS
) && (res
!= SQL_SUCCESS_WITH_INFO
)) {
619 ast_log(LOG_WARNING
, "res_odbc: Error SetEnv\n");
620 SQLFreeHandle(SQL_HANDLE_ENV
, new->env
);
621 AST_LIST_UNLOCK(&odbc_list
);
627 new->haspool
= pooling
;
631 ast_log(LOG_WARNING
, "Pooling without also setting a limit is pointless. Changing limit from 0 to 5.\n");
637 ast_log(LOG_NOTICE
, "Refreshing ODBC class '%s' dsn->[%s]\n", cat
, dsn
);
639 odbc_register_class(new, connect
);
640 ast_log(LOG_NOTICE
, "Registered ODBC class '%s' dsn->[%s]\n", cat
, dsn
);
645 ast_config_destroy(config
);
648 /* Purge classes that we know can go away (pooled with 0, only) */
649 AST_LIST_TRAVERSE_SAFE_BEGIN(&odbc_list
, class, list
) {
650 if (class->delme
&& class->haspool
&& class->count
== 0) {
651 AST_LIST_TRAVERSE_SAFE_BEGIN(&(class->odbc_obj
), current
, list
) {
652 AST_LIST_REMOVE_CURRENT(&(class->odbc_obj
), list
);
653 odbc_obj_disconnect(current
);
654 ast_mutex_destroy(¤t
->lock
);
657 AST_LIST_TRAVERSE_SAFE_END
;
659 AST_LIST_REMOVE_CURRENT(&odbc_list
, list
);
663 AST_LIST_TRAVERSE_SAFE_END
;
664 AST_LIST_UNLOCK(&odbc_list
);
669 static int unload_module(void)
671 /* Prohibit unloading */
675 static int load_module(void)
677 if(load_odbc_config() == -1)
678 return AST_MODULE_LOAD_DECLINE
;
679 ast_cli_register_multiple(cli_odbc
, sizeof(cli_odbc
) / sizeof(struct ast_cli_entry
));
680 ast_log(LOG_NOTICE
, "res_odbc loaded.\n");
684 AST_MODULE_INFO(ASTERISK_GPL_KEY
, AST_MODFLAG_GLOBAL_SYMBOLS
, "ODBC Resource",
686 .unload
= unload_module
,