the proper way of inserting and retrieving odbc data.

Hi,

I am having difficulty inserting or retrieving data through
unixodbc using postgresql.

I am sure that the config was correct, since I was able to insert
and retrieve data to and from postgresql using other datatypes.

However, when I tried to insert a binary data, and then try to
fetch the data, I could never get it right.


The following is a snippet of the code that do the insertion and deletion.

the table is created using:

create table test1 (id integer primary key, data bytea not null);


bool
EConnection::insert_blob(unsigned int id, const std::string &filename)
{
    std::string command("INSERT INTO test1 (id, data) VALUES (?, ?)");

    SQLHSTMT   sql_stmt;   // Handle for a statement
    SQLINTEGER b_ind = SQL_NTS,
               id_ind = 0,
               sql_error = 0;
    SQLRETURN  res;

    res = SQLAllocStmt(sql_conn, &sql_stmt);
    if (res != SQL_SUCCESS && res != SQL_SUCCESS_WITH_INFO) {
        return false;
    }

    SQLCHAR data[102400];
    FILE *f = fopen(filename.c_str(), "r");
    if (!f)
        return false;
    size_t size = 0;

    size = fread(data, 1, 102400, f);
    fclose(f);

    res = SQLPrepare(sql_stmt,
            (SQLCHAR *)(command.c_str()), SQL_NTS);

    if (res != SQL_SUCCESS && res != SQL_SUCCESS_WITH_INFO) {
        return false;
    }

    SQLBindParam(sql_stmt, 1, SQL_C_ULONG, SQL_INTEGER, SIZEOF_LONG, 0,
                 (void *) &id, &id_ind);

    SQLBindParam(sql_stmt, 2, SQL_C_BINARY, SQL_BINARY, size, 0,
                 (void *) data, &b_ind);

    res = SQLExecute(sql_stmt);
    if (res != SQL_SUCCESS && res != SQL_SUCCESS_WITH_INFO) {
        return false;
    }


    SQLFreeHandle(SQL_HANDLE_STMT, sql_stmt);

    return true;
}

When I called this function, it appears to work.

however, when I tried to retrieve the binary data
and store it in a separate file and compare it to the original
data, the diff tells me tha they're different.

bool
EConnection::get_blob(unsigned int id)
{
    std::string command("SELECT data from test1 where id = ?");
    SQLHSTMT sql_stmt;   // Handle for a statement
    SQLRETURN res;
    SQLINTEGER sql_error = 0, id_ind = 0;

    SQLCHAR data[29403];

    res = SQLAllocStmt(sql_conn, &sql_stmt);
    if (res != SQL_SUCCESS && res != SQL_SUCCESS_WITH_INFO) {
        return false;
    }

    res = SQLPrepare(sql_stmt, (SQLCHAR *) command.c_str(), SQL_NTS);

    if (res != SQL_SUCCESS && res != SQL_SUCCESS_WITH_INFO) {
        return false;
    }

    SQLBindParam(sql_stmt, 1, SQL_C_ULONG, SQL_INTEGER, SIZEOF_LONG,
                 0, (void *) &id, &id_ind);

    SQLBindCol(sql_stmt,1,SQL_C_BINARY, data, sizeof(data), &sql_error);

    assert(SQLFetch(sql_stmt) != SQL_NO_DATA);
    FILE *f = fopen("output.jpg", "w");
    fwrite(data, 1, 29403, f);
    fclose(f);

    SQLFreeHandle(SQL_HANDLE_STMT, sql_stmt);
    return true;
}



I'm running out of ideas as to why this is happening. Anyhelp will
be great.


Thanks in advance.

--
Bill Shui