Problem inserting vector of uchars into BLOB field

The Oate · #1 (**permalink**) 03-08-11, 12:56

I have an array of unsigned chars that I need to insert into a field of a table that is defined as a BLOB(30000). The following is the insert statement I've been trying to use along with the bind parameter call for the field in question.

Code:

    char *insertStmt =
        "INSERT INTO TESTTABLE (CATEGORY, TOPIC, DATA) VALUES(?, ?, CAST(? AS BLOB(30000)))";

Code:

    SQLBindParameter(handleStmt, 3, SQL_PARAM_INPUT, SQL_C_BINARY,
                     SQL_BLOB, (SQLUINTEGER)30000, 0, ptr, (SQLINTEGER)30000, &sqlNts);

ptr in the above code is defined as unsigned char ptr[30000];

The issue I have with this is if a NULL character occurs anywhere in this array, then all characters occurring after the initial NULL are being truncated from what's getting inserted into the database. When I do a select * on this table, the field for the BLOB always only has the bytes that occurred before the first byte representing the NULL character.

Is there a way to do this such that I can get the full array of bytes stored in to the BLOB and not have this issue with bytes that would represent the NULL character?

DB2 version - 9.1.0.9, Fix Pack - 9, DB2 type - ESE

The Oate · #2 (**permalink**) 03-08-11, 17:02

So as a temporary work around, I've resorted to using the following code to create a string with the integer representation of each byte in a space delimited format.

Code:

    stringstream ss;
    for (unsigned int z=0; z<30000; z++, ptr++) 
    {
        ss << (int)(*ptr) << " ";
    }

This, however, has the drawback that the string created is worst case 4 times larger than the original 30000 byte array (if all the bytes had a value of 0xff for example, then I get "255 255 255 etc."). I think this is probably ok, but if it's possible, I'd like to store this without having to change the size of the BLOB field in the table. Does anyone know how to get around this issue with the NULL character?

stolze · #3 (**permalink**) 03-09-11, 03:01

The question is what's the value of the "sqlNts" variable (passed as last parameter) when the statement is executed? The variable must contain the actual length of the data to be inserted. If it is SQL_NTS instead, then you tell DB2 that the data is a "Null-Terminated String", which tells DB2 to cut off everything after the first 0x00 byte.

Internet Services

Web Development

Digital Marketing

Consumer Tech