Hi,
I used the steps same as outlined below:
xxd -p /home/user/myimage.png | tr -d '\n' > /tmp/image.hex
DELETE FROM hexdump; -- hexdump table is: -- CREATE TABLE hexdump (hex text);
COPY hexdump FROM '/tmp/image.hex';
UPDATE users SET image= (SELECT decode(hex, 'hex') FROM hexdump LIMIT 1) where id=15489
Is this the correct way of inserting binary data into bytea column?
When I fetch a small part of this column, I get output as below:
select substring(smallblob,0,64) from longdata where blobi d=1;
\xd0cf11e0a1b11ae1000000000000000000000000000000003b000300feff09000600000000000 00000000000010000006a0000000000000000100000670000 (1 row)
Thanks and Regards.
-----------------------------
On Fri, Feb 24, 2012 at 5:06 PM, Hiroshi Inoue
<inoue@tpf.co.jp> wrote:
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.
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