res_config_odbc: Fix for nullable integer columns and keyfield existence check in...

This patch fixes setting nullable integer columns to NULL instead of an empty
string, which fails for PostgreSQL, for example. The current code is supposed
to do so, but the check is broken. The patch also allows the first column in
the list to be a nullable integer.

Also, the check for existence of a mandatory column checked for the first
column in the list instead of the key field lookup column. This patch fixes
that issue as well.

Finally, the compatibility option allow_empty_string_in_nontext, which was
added to previous revisions to allow for some database backends with certain
schemas to function, has been removed.

Review: https://reviewboard.asterisk.org/r/3335

ASTERISK-23459 #close
ASTERISK-23351 #close

(closes issue ASTERISK-23459)
Reported by: zvision
patches:
  res_config_odbc.diff uploaded by zvision (License 5755)

git-svn-id: https://origsvn.digium.com/svn/asterisk/trunk@411515 65c4cc65-6c06-0410-ace0-fbb531ad65f3

diff --git a/UPGRADE.txt b/UPGRADE.txt
index a24931d..b139fec 100644 (file)
--- a/UPGRADE.txt
+++ b/UPGRADE.txt
@@ -136,6 +136,10 @@ Configuration Files:
  - The manager.conf 'eventfilter' now takes an "extended" regular expression
    instead of a "basic" one.
 
+ODBC:
+- The compatibility setting, allow_empty_string_in_nontext, has been removed.
+  Empty column values will be stored as empty strings during realtime updates.
+
 Realtime Configuration:
  - WARNING: The database migration script that adds the 'extensions' table for
    realtime had to be modified due to an error when installing for MySQL.  The

diff --git a/res/res_config_odbc.c b/res/res_config_odbc.c
index 2735eea..5289f6d 100644 (file)
--- a/res/res_config_odbc.c
+++ b/res/res_config_odbc.c
@@ -69,6 +69,12 @@ static void decode_chunk(char *chunk)
        }
 }
 
+static inline int is_text(const struct odbc_cache_columns *column)
+{
+       return column->type == SQL_CHAR || column->type == SQL_VARCHAR || column->type == SQL_LONGVARCHAR
+               || column->type == SQL_WCHAR || column->type == SQL_WVARCHAR || column->type == SQL_WLONGVARCHAR;
+}
+
 static SQLHSTMT custom_prepare(struct odbc_obj *obj, void *data)
 {
        int res, x = 1, count = 0;
@@ -457,13 +463,13 @@ static int update_odbc(const char *database, const char *table, const char *keyf
        char sql[256];
        SQLLEN rowcount=0;
        const struct ast_variable *field = fields;
-       int res, count = 1;
+       int res, count = 0, paramcount = 0;
        struct custom_prepare_struct cps = { .sql = sql, .extra = lookup, .fields = fields, };
        struct odbc_cache_tables *tableptr;
        struct odbc_cache_columns *column = NULL;
        struct ast_flags connected_flag = { RES_ODBC_CONNECTED };
 
-       if (!table || !field) {
+       if (!table || !field || !keyfield) {
                return -1;
        }
 
@@ -478,27 +484,29 @@ static int update_odbc(const char *database, const char *table, const char *keyf
                return -1;
        }
 
-       if (tableptr && !ast_odbc_find_column(tableptr, field->name)) {
-               ast_log(LOG_WARNING, "Key field '%s' does not exist in table '%s@%s'.  Update will fail\n", field->name, table, database);
+       if (tableptr && !ast_odbc_find_column(tableptr, keyfield)) {
+               ast_log(LOG_WARNING, "Key field '%s' does not exist in table '%s@%s'.  Update will fail\n", keyfield, table, database);
        }
 
-       snprintf(sql, sizeof(sql), "UPDATE %s SET %s=?", table, field->name);
-       while ((field = field->next)) {
-               if ((tableptr && (column = ast_odbc_find_column(tableptr, field->name))) || count > 63) {
-                       /* NULL test for integer-based columns */
-                       if (ast_strlen_zero(field->name) && tableptr && column && column->nullable && count < 64 &&
-                               (column->type == SQL_INTEGER || column->type == SQL_BIGINT ||
-                                column->type == SQL_SMALLINT || column->type == SQL_TINYINT ||
-                                column->type == SQL_NUMERIC || column->type == SQL_DECIMAL)) {
-                               snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s=NULL", field->name);
+       snprintf(sql, sizeof(sql), "UPDATE %s SET ", table);
+       while (field) {
+               if ((tableptr && (column = ast_odbc_find_column(tableptr, field->name))) || count >= 64) {
+                       if (paramcount++) {
+                               snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", ");
+                       }
+                       /* NULL test for non-text columns */
+                       if (count < 64 && ast_strlen_zero(field->value) && column->nullable && !is_text(column)) {
+                               snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=NULL", field->name);
                                cps.skip |= (1LL << count);
                        } else {
-                               snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), ", %s=?", field->name);
+                               /* Value is not an empty string, or column is of text type, or we couldn't fit any more into cps.skip (count >= 64 ?!). */
+                               snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s=?", field->name);
                        }
                } else { /* the column does not exist in the table */
                        cps.skip |= (1LL << count);
                }
-               count++;
+               ++count;
+               field = field->next;
        }
        snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), " WHERE %s=?", keyfield);
        ast_odbc_release_table(tableptr);