Thread: 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
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@cse.unsw.edu.au> >To: pgsql-odbc@postgresql.org >CC: unixodbc-dev@easysoft.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@postgresql.org _________________________________________________________________ Find a cheaper internet access deal - choose one to suit you. http://www.msn.co.uk/internetaccess
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@cse.unsw.edu.au> > >To: pgsql-odbc@postgresql.org > >CC: unixodbc-dev@easysoft.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@postgresql.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