char writehandle[5][30];
char sql_read[2048];
char sql_write[2048];
+ char sql_insert[2048];
unsigned int flags;
int rowlimit;
struct ast_custom_function *acf;
static int resultcount = 0;
AST_THREADSTORAGE(sql_buf);
+AST_THREADSTORAGE(sql2_buf);
AST_THREADSTORAGE(coldata_buf);
AST_THREADSTORAGE(colnames_buf);
res = SQLAllocHandle (SQL_HANDLE_STMT, obj->con, &stmt);
if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
- ast_log(LOG_WARNING, "SQL Alloc Handle failed!\n");
+ ast_log(LOG_WARNING, "SQL Alloc Handle failed (%d)!\n", res);
return NULL;
}
res = SQLExecDirect(stmt, (unsigned char *)sql, SQL_NTS);
if ((res != SQL_SUCCESS) && (res != SQL_SUCCESS_WITH_INFO)) {
- ast_log(LOG_WARNING, "SQL Exec Direct failed![%s]\n", sql);
+ if (res == SQL_ERROR) {
+ int i;
+ SQLINTEGER nativeerror=0, numfields=0;
+ SQLSMALLINT diagbytes=0;
+ unsigned char state[10], diagnostic[256];
+
+ SQLGetDiagField(SQL_HANDLE_STMT, stmt, 1, SQL_DIAG_NUMBER, &numfields, SQL_IS_INTEGER, &diagbytes);
+ for (i = 0; i < numfields; i++) {
+ SQLGetDiagRec(SQL_HANDLE_STMT, stmt, i + 1, state, &nativeerror, diagnostic, sizeof(diagnostic), &diagbytes);
+ ast_log(LOG_WARNING, "SQL Execute returned an error %d: %s: %s (%d)\n", res, state, diagnostic, diagbytes);
+ if (i > 10) {
+ ast_log(LOG_WARNING, "Oh, that was good. There are really %d diagnostics?\n", (int)numfields);
+ break;
+ }
+ }
+ }
+
+ ast_log(LOG_WARNING, "SQL Exec Direct failed (%d)![%s]\n", res, sql);
SQLCloseCursor(stmt);
- SQLFreeHandle (SQL_HANDLE_STMT, stmt);
+ SQLFreeHandle(SQL_HANDLE_STMT, stmt);
return NULL;
}
SQLHSTMT stmt = NULL;
SQLLEN rows=0;
struct ast_str *buf = ast_str_thread_get(&sql_buf, 16);
+ struct ast_str *insertbuf = ast_str_thread_get(&sql2_buf, 16);
+ const char *status = "FAILURE";
if (!buf) {
return -1;
if (!query) {
ast_log(LOG_ERROR, "No such function '%s'\n", cmd);
AST_RWLIST_UNLOCK(&queries);
- ast_free(buf);
+ pbx_builtin_setvar_helper(chan, "ODBCSTATUS", status);
return -1;
}
ast_autoservice_start(chan);
ast_str_make_space(&buf, strlen(query->sql_write) * 2 + 300);
+ ast_str_make_space(&insertbuf, strlen(query->sql_insert) * 2 + 300);
/* Parse our arguments */
t = value ? ast_strdupa(value) : "";
AST_RWLIST_UNLOCK(&queries);
if (chan)
ast_autoservice_stop(chan);
- if (bogus_chan)
+ if (bogus_chan) {
ast_channel_free(chan);
- ast_free(buf);
+ } else {
+ pbx_builtin_setvar_helper(chan, "ODBCSTATUS", status);
+ }
return -1;
}
pbx_builtin_pushvar_helper(chan, "VALUE", value ? value : "");
pbx_substitute_variables_helper(chan, query->sql_write, buf->str, buf->len - 1);
+ pbx_substitute_variables_helper(chan, query->sql_insert, insertbuf->str, insertbuf->len - 1);
/* Restore prior values */
for (i = 0; i < args.argc; i++) {
if (obj)
stmt = ast_odbc_direct_execute(obj, generic_execute, buf);
}
- if (stmt)
+ if (stmt) {
+ status = "SUCCESS";
+ SQLRowCount(stmt, &rows);
break;
+ }
}
- AST_RWLIST_UNLOCK(&queries);
-
- if (stmt) {
- /* Rows affected */
- SQLRowCount(stmt, &rows);
+ if (stmt && rows == 0 && !ast_strlen_zero(insertbuf->str)) {
+ SQLCloseCursor(stmt);
+ SQLFreeHandle(SQL_HANDLE_STMT, stmt);
+ for (dsn = 0; dsn < 5; dsn++) {
+ if (!ast_strlen_zero(query->writehandle[dsn])) {
+ obj = ast_odbc_request_obj(query->writehandle[dsn], 0);
+ if (obj) {
+ stmt = ast_odbc_direct_execute(obj, generic_execute, insertbuf);
+ }
+ }
+ if (stmt) {
+ status = "FAILOVER";
+ SQLRowCount(stmt, &rows);
+ break;
+ }
+ }
}
+ AST_RWLIST_UNLOCK(&queries);
+
/* Output the affected rows, for all cases. In the event of failure, we
* flag this as -1 rows. Note that this is different from 0 affected rows
* which would be the case if we succeeded in our query, but the values did
* not change. */
snprintf(varname, sizeof(varname), "%d", (int)rows);
pbx_builtin_setvar_helper(chan, "ODBCROWS", varname);
+ pbx_builtin_setvar_helper(chan, "ODBCSTATUS", status);
if (stmt) {
SQLCloseCursor(stmt);
ast_autoservice_stop(chan);
if (bogus_chan)
ast_channel_free(chan);
- ast_free(buf);
return 0;
}
struct odbc_datastore *resultset = NULL;
struct odbc_datastore_row *row = NULL;
struct ast_str *sql = ast_str_thread_get(&sql_buf, 16);
+ const char *status = "FAILURE";
if (!sql) {
+ pbx_builtin_setvar_helper(chan, "ODBCSTATUS", status);
return -1;
}
ast_log(LOG_ERROR, "No such function '%s'\n", cmd);
AST_RWLIST_UNLOCK(&queries);
pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
- ast_free(sql);
+ pbx_builtin_setvar_helper(chan, "ODBCSTATUS", status);
return -1;
}
if (bogus_chan) {
ast_channel_free(chan);
}
- ast_free(sql);
return -1;
}
if (bogus_chan) {
ast_channel_free(chan);
}
- ast_free(sql);
return -1;
}
res1 = 0;
buf[0] = '\0';
ast_copy_string(rowcount, "0", sizeof(rowcount));
+ status = "NODATA";
} else {
ast_log(LOG_WARNING, "Error %d in FETCH [%s]\n", res, sql->str);
+ status = "FETCHERROR";
}
SQLCloseCursor(stmt);
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
ast_odbc_release_obj(obj);
pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
+ pbx_builtin_setvar_helper(chan, "ODBCSTATUS", status);
if (chan)
ast_autoservice_stop(chan);
if (bogus_chan)
ast_channel_free(chan);
- ast_free(sql);
return res1;
}
+ status = "SUCCESS";
+
for (y = 0; y < rowlimit; y++) {
for (x = 0; x < colcount; x++) {
int i;
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
ast_odbc_release_obj(obj);
pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
+ pbx_builtin_setvar_helper(chan, "ODBCSTATUS", "MEMERROR");
if (chan)
ast_autoservice_stop(chan);
if (bogus_chan)
ast_channel_free(chan);
- ast_free(sql);
return -1;
}
resultset = tmp;
row = ast_calloc(1, sizeof(*row) + buflen);
if (!row) {
ast_log(LOG_ERROR, "Unable to allocate space for more rows in this resultset.\n");
+ status = "MEMERROR";
goto end_acf_read;
}
strcpy((char *)row + sizeof(*row), buf);
end_acf_read:
snprintf(rowcount, sizeof(rowcount), "%d", y);
pbx_builtin_setvar_helper(chan, "ODBCROWS", rowcount);
+ pbx_builtin_setvar_helper(chan, "ODBCSTATUS", status);
pbx_builtin_setvar_helper(chan, "~ODBCFIELDS~", colnames->str);
if (resultset) {
int uid;
odbc_store = ast_datastore_alloc(&odbc_info, buf);
if (!odbc_store) {
ast_log(LOG_ERROR, "Rows retrieved, but unable to store it in the channel. Results fail.\n");
+ pbx_builtin_setvar_helper(chan, "ODBCSTATUS", "MEMERROR");
odbc_datastore_free(resultset);
SQLCloseCursor(stmt);
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
ast_autoservice_stop(chan);
if (bogus_chan)
ast_channel_free(chan);
- ast_free(sql);
return -1;
}
odbc_store->data = resultset;
ast_autoservice_stop(chan);
if (bogus_chan)
ast_channel_free(chan);
- ast_free(sql);
return 0;
}
return EINVAL;
}
+ if ((tmp = ast_variable_retrieve(cfg, catg, "insertsql"))) {
+ ast_copy_string((*query)->sql_insert, tmp, sizeof((*query)->sql_insert));
+ }
+
/* Allow escaping of embedded commas in fields to be turned off */
ast_set_flag((*query), OPT_ESCAPECOMMAS);
if ((tmp = ast_variable_retrieve(cfg, catg, "escapecommas"))) {
"substitution of the arguments into the query as specified by ${ARG1},\n"
"${ARG2}, ... ${ARGn}. When setting the function, the values are provided\n"
"either in whole as ${VALUE} or parsed as ${VAL1}, ${VAL2}, ... ${VALn}.\n"
- "\nRead:\n%s\n\nWrite:\n%s\n",
+ "%s"
+ "\nRead:\n%s\n\nWrite:\n%s\n%s%s%s",
+ ast_strlen_zero((*query)->sql_insert) ? "" :
+ "If the write query affects no rows, the insert query will be\n"
+ "performed.\n",
(*query)->sql_read,
- (*query)->sql_write);
+ (*query)->sql_write,
+ ast_strlen_zero((*query)->sql_insert) ? "" : "Insert:\n",
+ ast_strlen_zero((*query)->sql_insert) ? "" : (*query)->sql_insert,
+ ast_strlen_zero((*query)->sql_insert) ? "" : "\n");
} else if (!ast_strlen_zero((*query)->sql_read)) {
asprintf((char **)&((*query)->acf->desc),
"Runs the following query, as defined in func_odbc.conf, performing\n"
"substitution of the arguments into the query as specified by ${ARG1},\n"
"${ARG2}, ... ${ARGn}. The values are provided either in whole as\n"
"${VALUE} or parsed as ${VAL1}, ${VAL2}, ... ${VALn}.\n"
- "This function may only be set.\nSQL:\n%s\n",
- (*query)->sql_write);
+ "This function may only be set.\n%sSQL:\n%s\n%s%s%s",
+ ast_strlen_zero((*query)->sql_insert) ? "" :
+ "If the write query affects no rows, the insert query will be\n"
+ "performed.\n",
+ (*query)->sql_write,
+ ast_strlen_zero((*query)->sql_insert) ? "" : "Insert:\n",
+ ast_strlen_zero((*query)->sql_insert) ? "" : (*query)->sql_insert,
+ ast_strlen_zero((*query)->sql_insert) ? "" : "\n");
} else {
ast_free((char *)(*query)->acf->synopsis);
ast_free((char *)(*query)->acf->syntax);
ast_free((char *)(*query)->acf->name);
ast_free((*query)->acf);
ast_free(*query);
- ast_log(LOG_WARNING, "Section %s was found, but there was no SQL to execute. Ignoring.\n", catg);
+ ast_log(LOG_WARNING, "Section '%s' was found, but there was no SQL to execute. Ignoring.\n", catg);
return EINVAL;
}