Skip site navigation (1) Skip section navigation (2)

Re: BLOB handling compatibility with PostgreSQL > 7.4

From: Irina Sourikova <irina(at)bnl(dot)gov>
To: Ludek Finstrle <luf(at)pzkagis(dot)cz>
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: BLOB handling compatibility with PostgreSQL > 7.4
Date: 2005-11-30 19:16:12
Message-ID: 438DFA7C.8040804@bnl.gov (view raw or flat)
Thread:
Lists: pgsql-interfacespgsql-odbc
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
  <title></title>
</head>
<body text="#000000" bgcolor="#ffffff">
I compiled psqlodbc with --enable-unicode=no , didn't help.<br>
<br>
I'll try to give some details:<br>
<br>
On 2 postgres servers ( running 7.3.6 and 7.4.2 ) I did the following:<br>
<br>
create domain lo as oid;<br>
create table justlo(b lo);<br>
<br>
Then&nbsp; I inserted an entry from a text file via a program that uses
libodbc++ ( attached below ) <br>
insert into justlo values(lo_import('/usr/local/pgsql/text.txt'))<br>
<br>
The following 2 programs ( first uses libodbc++, second - plain ODBC )
run fine when connecting to postgres 7.3 and crash with postgres 7.4:<br>
/*<br>
&nbsp;odbc++ example<br>
*/<br>
#include &lt;sstream&gt;<br>
#include &lt;iostream&gt;<br>
#include &lt;string&gt;<br>
#include &lt;odbc++/connection.h&gt;<br>
#include &lt;odbc++/setup.h&gt;<br>
#include &lt;odbc++/types.h&gt;<br>
#include &lt;odbc++/errorhandler.h&gt;<br>
#include &lt;sql.h&gt;<br>
#include &lt;odbc++/drivermanager.h&gt;<br>
#include &lt;odbc++/resultset.h&gt;<br>
#include &lt;odbc++/resultsetmetadata.h&gt;<br>
#include &lt;odbc++/preparedstatement.h&gt;<br>
#include &lt;odbc++/databasemetadata.h&gt;<br>
#include &lt;fstream&gt;<br>
<br>
using namespace odbc;<br>
using namespace std;<br>
<br>
int main(int argc, char *argv[])<br>
{<br>
&nbsp; Connection* con = 0;<br>
&nbsp; Statement* stmt = 0;<br>
&nbsp; ResultSet* rs = 0;<br>
&nbsp; string query;<br>
&nbsp; unsigned int numcol;<br>
<br>
&nbsp; try<br>
&nbsp;&nbsp;&nbsp; {<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; con = DriverManager::getConnection("test74", "postgres", "");<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; //con = DriverManager::getConnection("test", "postgres", "");<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cout &lt;&lt; con-&gt;getMetaData()-&gt;getDriverVersion()
&lt;&lt; endl;<br>
&nbsp;&nbsp;&nbsp; }<br>
&nbsp; catch (SQLException&amp; e)<br>
&nbsp;&nbsp;&nbsp; {<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cout &lt;&lt; e.getMessage() &lt;&lt; endl;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return 1;<br>
&nbsp;&nbsp;&nbsp; }<br>
&nbsp; <br>
&nbsp; //on nuvi, postgreSQL 7.3.6<br>
&nbsp; query = "insert into justlo
values(lo_import('/usr/local/pgsql/text.txt'))";<br>
&nbsp; //on sql, postgreSQL 7.4.2<br>
&nbsp; //query = "insert into justlo
values(lo_import('/var/lib/pgsql/text.txt'))";<br>
<br>
&nbsp; stmt = con-&gt;createStatement();<br>
&nbsp; try{<br>
&nbsp;&nbsp;&nbsp; //stmt-&gt;executeUpdate(query.c_str());<br>
&nbsp; }<br>
&nbsp; catch (SQLException&amp; e)<br>
&nbsp;&nbsp;&nbsp; {<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cout &lt;&lt; e.getMessage() &lt;&lt; endl; <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return 1;<br>
&nbsp;&nbsp;&nbsp; }<br>
&nbsp;<br>
&nbsp; query = "select * from justlo";<br>
&nbsp; stmt = con-&gt;createStatement();<br>
&nbsp; try{<br>
&nbsp;&nbsp;&nbsp; rs = stmt-&gt;executeQuery(query.c_str());<br>
&nbsp; }<br>
&nbsp; catch (SQLException&amp; e)<br>
&nbsp;&nbsp;&nbsp; {<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; cout &lt;&lt; e.getMessage() &lt;&lt; endl; <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return 1;<br>
&nbsp;&nbsp;&nbsp; }<br>
&nbsp; <br>
&nbsp; char str[50];<br>
&nbsp; while( rs-&gt;next()){&nbsp;&nbsp;&nbsp; <br>
&nbsp;&nbsp;&nbsp; istream * ms = rs-&gt;getBinaryStream(1);<br>
&nbsp;&nbsp;&nbsp; ms-&gt;getline(str,50);<br>
&nbsp;&nbsp;&nbsp; cout &lt;&lt; str &lt;&lt; endl;<br>
&nbsp; }<br>
<br>
&nbsp; delete con;<br>
&nbsp; return 0;<br>
}<br>
<br>
========================<br>
/* odbc.c<br>
testing psqlodbc with postgreSQL 7.3.6 and 7.4.2<br>
*/<br>
#include &lt;stdlib.h&gt;<br>
#include &lt;stdio.h&gt;<br>
#include &lt;sql.h&gt;<br>
#include &lt;sqlext.h&gt;<br>
#include &lt;sqltypes.h&gt;<br>
<br>
SQLHENV&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;V_OD_Env;&nbsp;&nbsp;&nbsp;&nbsp; // Handle ODBC environment<br>
long&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;V_OD_erg;&nbsp;&nbsp;&nbsp;&nbsp; // result of functions<br>
SQLHDBC&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;V_OD_hdbc;&nbsp;&nbsp;&nbsp; // Handle connection<br>
char&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;V_OD_stat[10]; // Status SQL<br>
SQLINTEGER&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;V_OD_err,V_OD_rowanz,V_OD_id;<br>
SQLSMALLINT&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;V_OD_mlen;<br>
char&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; V_OD_msg[200],V_OD_buffer[200];<br>
SQLHSTMT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; V_OD_hstmt;&nbsp;&nbsp; // Handle for a statement<br>
SQLINTEGER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; V_OD_err,V_OD_id;<br>
char&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; V_OD_buffer[200];<br>
<br>
int main(int argc,char *argv[])<br>
{<br>
&nbsp; // 1. allocate Environment handle and register version <br>
&nbsp; V_OD_erg=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&amp;V_OD_Env);<br>
&nbsp; if ((V_OD_erg != SQL_SUCCESS) &amp;&amp; (V_OD_erg !=
SQL_SUCCESS_WITH_INFO))<br>
&nbsp;&nbsp;&nbsp; {<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; printf("Error AllocHandle\n");<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; exit(0);<br>
&nbsp;&nbsp;&nbsp; }<br>
&nbsp; V_OD_erg=SQLSetEnvAttr(V_OD_Env, SQL_ATTR_ODBC_VERSION, <br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;(void*)SQL_OV_ODBC3, 0); <br>
&nbsp; if ((V_OD_erg != SQL_SUCCESS) &amp;&amp; (V_OD_erg !=
SQL_SUCCESS_WITH_INFO))<br>
&nbsp;&nbsp;&nbsp; {<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; printf("Error SetEnv\n");<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; exit(0);<br>
&nbsp;&nbsp;&nbsp; }<br>
&nbsp; // 2. allocate connection handle, set timeout<br>
&nbsp; V_OD_erg = SQLAllocHandle(SQL_HANDLE_DBC, V_OD_Env, &amp;V_OD_hdbc); <br>
&nbsp; if ((V_OD_erg != SQL_SUCCESS) &amp;&amp; (V_OD_erg !=
SQL_SUCCESS_WITH_INFO))<br>
&nbsp;&nbsp;&nbsp; {<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; printf("Error AllocHDB %d\n",V_OD_erg);<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; exit(0);<br>
&nbsp;&nbsp;&nbsp; }<br>
&nbsp; SQLSetConnectAttr(V_OD_hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER *)5, 0);<br>
&nbsp; // 3. Connect to the datasource "test" <br>
&nbsp; V_OD_erg = SQLConnect(V_OD_hdbc, (SQLCHAR*) "test", SQL_NTS,<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; (SQLCHAR*) "postgres", SQL_NTS,<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; (SQLCHAR*) "", SQL_NTS);<br>
&nbsp; if ((V_OD_erg != SQL_SUCCESS) &amp;&amp; (V_OD_erg !=
SQL_SUCCESS_WITH_INFO))<br>
&nbsp;&nbsp;&nbsp; {<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; printf("Error SQLConnect %d\n",V_OD_erg);<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, <br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; V_OD_stat, &amp;V_OD_err,V_OD_msg,100,&amp;V_OD_mlen);<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; printf("%s (%d)\n",V_OD_msg,V_OD_err);<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SQLFreeHandle(SQL_HANDLE_DBC, V_OD_hdbc);<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; exit(0);<br>
&nbsp;&nbsp;&nbsp; }<br>
&nbsp; printf("Connected !\n");<br>
<br>
&nbsp; SQLRETURN retcode;<br>
&nbsp; SQLHSTMT hstmt;<br>
&nbsp; SQLCHAR&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BinaryPtr[50];<br>
&nbsp; SQLINTEGER&nbsp;&nbsp;&nbsp;&nbsp; BinaryLen;<br>
<br>
&nbsp; SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc, &amp;hstmt);<br>
<br>
&nbsp; retcode = SQLExecDirect(hstmt,"SELECT b&nbsp; FROM justlo",SQL_NTS);<br>
<br>
&nbsp; if (retcode == SQL_SUCCESS) {<br>
&nbsp;&nbsp;&nbsp; retcode = SQLFetch(hstmt);<br>
&nbsp;&nbsp;&nbsp; if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; printf(" error \n" );<br>
&nbsp;&nbsp;&nbsp; }<br>
&nbsp;&nbsp;&nbsp; if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SQLGetData(hstmt, 1, SQL_C_BINARY, BinaryPtr,
sizeof(BinaryPtr),&amp;BinaryLen);<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; printf( " %d, %s ",&nbsp; BinaryLen, BinaryPtr);<br>
&nbsp;&nbsp;&nbsp; }<br>
&nbsp; }<br>
&nbsp; else{<br>
&nbsp;&nbsp;&nbsp; printf(" error on select\n" );<br>
&nbsp; }<br>
}<br>
===================================================<br>
<br>
I tried to debug and put som code into odbc++, here is the difference
between 2 postgres versions:<br>
<br>
with 7.3, no problem:<br>
<br>
entering getBinaryStream<br>
DataHandler::getStream. cType_: -2&nbsp; sqlType -4<br>
<br>
with 7.4, segfault:<br>
<br>
entering getBinaryStream<br>
DataHandler::getStream. cType_: 4&nbsp; sqlType 4<br>
UNSUPPORTED_GET<br>
[libodbc++]: Could not get SQL type 4 (INTEGER), C type 4 (SQL_C_LONG)
as an stream<br>
<br>
Hope this helps.<br>
Thanks,<br>
Irina<br>
<br>
Ludek Finstrle wrote:<br>
<blockquote type="cite"
 cite="mid20051130094509(dot)GA6109(at)soptik(dot)pzkagis(dot)cz">
  <blockquote type="cite">
    <pre wrap="">We use unixODBC-2.2.11 and psqlodbc-08.01.0101.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
I don't exactly know how it's on linux. But which version of psqlodbc
do you use (unicode x ansi). Try the second type and let us know
if it helps.

  </pre>
  <blockquote type="cite">
    <pre wrap="">With postgres 7.3 lo type was mapped to SQL_C_BINARY and that's changed 
since postgres 7.4.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
That's changed to what type?

Luf

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
  </pre>
</blockquote>
<br>
<pre class="moz-signature" cols="72">-- 
Irina Sourikova
Brookhaven National Laboratory      phone: +1-631-344-3776
Physics Department, Bldg 510 C      fax:   +1-631-344-3253
Upton, NY 11973-5000                email: <a class="moz-txt-link-abbreviated" href="mailto:irina(at)bnl(dot)gov">irina(at)bnl(dot)gov</a>
</pre>
</body>
</html>



Attachment: unknown_filename
Description: text/html (10.0 KB)

In response to

Responses

pgsql-odbc by date

Next:From: Ludek FinstrleDate: 2005-12-01 13:04:06
Subject: Re: BLOB handling compatibility with PostgreSQL > 7.4
Previous:From: Dave PageDate: 2005-11-30 15:22:05
Subject: Re: Fixed server side prepare

pgsql-interfaces by date

Next:From: Ludek FinstrleDate: 2005-12-01 13:04:06
Subject: Re: BLOB handling compatibility with PostgreSQL > 7.4
Previous:From: Ludek FinstrleDate: 2005-11-30 09:45:09
Subject: Re: BLOB handling compatibility with PostgreSQL > 7.4

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group