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