Re: the proper way of inserting and retrieving odbc data.

Postgres escapes certain characters while storing binary data in the bytea
type. YOu need to take in to account this also while retrieving and storing
and would have to write extra code to handle this

http://developer.postgresql.org/docs/postgres/datatype-binary.html

Nisha

>From: Bill Shui <wshui(at)cse(dot)unsw(dot)edu(dot)au>
>To: pgsql-odbc(at)postgresql(dot)org
>CC: unixodbc-dev(at)easysoft(dot)com
>Subject: [ODBC] the proper way of inserting and retrieving odbc data.
>Date: Mon, 2 Jun 2003 13:23:54 +1000
>
>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
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

_________________________________________________________________
Find a cheaper internet access deal - choose one to suit you.
http://www.msn.co.uk/internetaccess