Re: Request for help on retrieving binary data from bytea column using ODBC calls

Hi Ganesh,

(2012/02/24 14:06), Ganesh Borse wrote:
> Dear All,
>
> I've a VC++ application which displays the various binary files (like
> jpgs, xls, zips) stored in PostgrelSQL DB in bytea column.
> Files are inserted into bytea column using hex encoding.

How did you insert the data concretely?

regards,
Hiroshi Inoue

> But, when I try to access this column by executing the query "select *
> from tablename;" from ODBC on Windows in this application, the buffer
> contains the same hex encoded data, but not the binary data.
>
> Application does the following steps to retrieve this binary data. This
> same code works well with other DBS, like Oracle, Sybase, DB2, MSSQL &
> so on.
> //---------------------------------------------------------------------------------------------------
> // prepare query:
> wchar_t *query = _T("select * from mytable;");
>     SQLRETURN ret = SQLPrepare(stmt, query, SQL_NTS);
>     if (!SQL_SUCCEEDED(ret))
>     {
>        TRACE("SybAse:SQLprepare failed.");
>        DbException::sqlErrorInfo(SQL_HANDLE_STMT, stmt);
>        throw DbException(std::string(""));
>     }
> // bind to column:
>     SQLBindCol(stmtHdl, columnNumber, SQL_C_BINARY,
>               m_colValue[0], m_len, &m_LenInd[0]);
> // Execute query:
> ret = SQLExecute(stmt);
> // fetch rows
> if (SQL_SUCCESS == ret || SQL_SUCCESS_WITH_INFO == ret)
> {
>     do {
>        ret = SQLFetchScroll(m_stmt, SQL_FETCH_NEXT, 0);
>        // handle data per row here...
>     } while(SQL_SUCCEEDED(ret));
> }
> //---------------------------------------------------------------------------------------------------
>
> Can you please help me know, how can I retrieve the data stored in bytea
> columns in binary format, so that those file can be handled appropriately?
>
> Thanks in advance for your help and guidance.
>
> Best Regards,
> Ganesh