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@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