SQL_BLOB (-98) column type code falsely classified as string_t in odbc_result::column_types(nanodbc::result const& r)

[nattomi]

Th IBM Db2 driver sends the code -98 for BLOB column types. This should be captured by the BINARY block of the switch statement in odbc_result::column_types(nanodbc::result const& r), but unfortunately it falls through it and ends up captured by the default block:

    case SQL_BINARY:
    case SQL_VARBINARY:
    case SQL_LONGVARBINARY:
      types.push_back(raw_t);
      break;
    default:
      types.push_back(string_t);
      signal_unknown_field_type(type, r.column_name(i));
      break; 

The following patch solves the issue, but I don't want to submit it as a pull request because it also involves modifying nanodbc.

diff --git a/src/nanodbc/nanodbc.cpp b/src/nanodbc/nanodbc.cpp
index 846f038b..1e63bd40 100644
--- a/src/nanodbc/nanodbc.cpp
+++ b/src/nanodbc/nanodbc.cpp
@@ -93,6 +93,10 @@
 #ifndef SQL_SS_UDT
 #define SQL_SS_UDT (-151) // from sqlncli.h
 #endif
+// IBM Db2 specific SQL type for BLOB
+#ifndef SQL_BLOB
+#define SQL_BLOB (-98)
+#endif
 
 // Default to ODBC version defined by NANODBC_ODBC_VERSION if provided.
 #ifndef NANODBC_ODBC_VERSION
@@ -2977,6 +2981,7 @@ private:
             case SQL_VARBINARY:
             case SQL_LONGVARBINARY:
             case SQL_SS_UDT: // MSDN: Essentially, UDT is a varbinary type with additional metadata.
+           case SQL_BLOB:
                 col.ctype_ = SQL_C_BINARY;
                 col.blob_ = true;
                 col.clen_ = 0;
diff --git a/src/odbc_result.cpp b/src/odbc_result.cpp
index 14a8d2b5..7ee34a67 100644
--- a/src/odbc_result.cpp
+++ b/src/odbc_result.cpp
@@ -8,6 +8,10 @@
 #ifndef SQL_SS_TIME2
 #define SQL_SS_TIME2 (-154)
 #endif
+// IBM Db2 specific SQL type for BLOB
+#ifndef SQL_BLOB
+#define SQL_BLOB (-98)
+#endif
 namespace odbc {
 
 using odbc::utils::run_interruptible;
@@ -715,6 +719,7 @@ std::vector<r_type> odbc_result::column_types(nanodbc::result const& r) {
     case SQL_BINARY:
     case SQL_VARBINARY:
     case SQL_LONGVARBINARY:
+    case SQL_BLOB:
       types.push_back(raw_t);
       break;
     default:

The following R code can be used to generate a sample table with a 10MB BLOB column:

dbExecute(conn, "CREATE TABLE BLOB_TEST (ID INT, DATA BLOB(10485760))")

# Generate 10 MB of binary data (0xAA repeated)
raw_data <- as.raw(rep(0xAA, 10 * 1024 * 1024))

# Insert the data using parameterized query
dbExecute(conn, "INSERT INTO BLOB_TEST (ID, DATA) VALUES (?, ?)", 
          params = list(1L, list(raw_data)))

[detule]

Hey thanks for the report!

I was hoping to get to this, but ran out of time this weekend. I think I'll have a chance to circle back in the next couple of weeks. Will revert here.

[detule]

Hi there, had some time to look into this.

First - thanks for the detailed investigation and the work you did to diagnose on your own. That's great. If we are unable to mitigate using other means we may need to update nanodbc, but before we think about that can we try this:

It seems DB2 has a "long data compatibility mode", where the DB2 specific data types are translated as more standard ODBC data types. So "SQL_BLOB" ( -98 ) is reported as "SQL_LONGVARBINARY" (-4).

I am using a linux box. To enable this I needed to edit my db2dsdriver.cfg file. In particular, as in the link above, I added the parameters section:

...
<databases>
...
</databases>
<parameters>
     <parameter name="LongDataCompat" value="1"/>
</parameters>
</configuration>

Once I did that I saw the column type being reported as "-4" and handled appropriately:

> dbExecute(conn, "CREATE TABLE BLOB_TEST (ID INT, DATA BLOB(20M))")
> raw_data <- as.raw(rep(0xAA, 10 * 1024 * 1024))
> df <- data.frame(ID = 1L, DATA =blob::as_blob(raw_data))
> dbWriteTable(conn, "BLOB_TEST", df, append=TRUE)
> odbc::odbcConnectionColumns(conn, "BLOB_TEST")[, c("name", "field.type", "sql_data_type")]
   name                field.type sql_data_type
1   ID                   INTEGER             4
2 DATA LONG VARCHAR FOR BIT DATA            -4

[nattomi]

Hi, I was not able to make the driver pick up the LongDataCompat parameter when set in db2dsdriver.cfg. However, it does work when I add LongDataCompat=1 to my DSN config in db2cli.cfg. This is surely something I can work with for now. Thanks a lot for the suggestion!

[nattomi]

Note, however that setting LongDataCompat=1 only solves BLOB and CBLOB columns but not DBCLOB. With LongDataCompat=0:

> odbc::odbcConnectionColumns(conn, "TEST_LOBS")[, c("name", "field.type", "sql_data_type")]
       name field.type sql_data_type
1        ID    INTEGER             4
2   MY_BLOB       BLOB           -98
3   MY_CLOB       CLOB           -99
4 MY_DBCLOB     DBCLOB          -350

After setting LongDataCompat=1, DBCLOB gets mapped to -97:

> odbc::odbcConnectionColumns(conn, "TEST_LOBS")[, c("name", "field.type", "sql_data_type")]
       name                field.type sql_data_type
1        ID                   INTEGER             4
2   MY_BLOB LONG VARCHAR FOR BIT DATA            -4
3   MY_CLOB              LONG VARCHAR            -1
4 MY_DBCLOB           LONG VARGRAPHIC           -97

Code -97 is SQL_UNICODE_LONGVARCHAR according to the ODBC specification:

#define SQL_UNICODE_LONGVARCHAR                 (-97)

This is also not handled in nanodbc::auto_bind, it falls right into the default block:

            case SQL_BINARY:
            case SQL_VARBINARY:
            case SQL_LONGVARBINARY:
            case SQL_SS_UDT: // MSDN: Essentially, UDT is a varbinary type with additional metadata.
                col.ctype_ = SQL_C_BINARY;
                col.blob_ = true;
                col.clen_ = 0;
                break;
            default:
                col.ctype_ = sql_ctype<string_type>::value;
                col.clen_ = 128;
                break;

I know the original issue is about SQL_BLOB but this is closely related.