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

Even through ODBC?

I thought PostgreSQL's ODBC layer should have some mechanism of converting
type SQL_C_BINARY to and from bytea.

If that's the case, how should I put across the unixodbc layer?

Any suggestions?

I mean for SQLBindParam and BindCol, which data type should I specify?

Also, when I fetch the data out of the DBMS, do I have to convert the escapped
characters back into their original form?

thanks in advance.

Bill

On Mon, Jun 02, 2003 at 09:22:12AM +0000, Nisha Joseph wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html

--
Bill Shui
--------------------------------------------------
Certified research space-monkey.
School of CSE, UNSW