Re: BLOB handling compatibility with PostgreSQL > 7.4

I compiled psqlodbc with --enable-unicode=no , didn't help.<br /><br /> I'll try to give some details:<br /><br /> On
2postgres servers ( running 7.3.6 and 7.4.2 ) I did the following:<br /><br /> create domain lo as oid;<br /> create
tablejustlo(b lo);<br /><br /> Then  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 (
firstuses libodbc++, second - plain ODBC ) run fine when connecting to postgres 7.3 and crash with postgres 7.4:<br />
/*<br/>  odbc++ example<br /> */<br /> #include <sstream><br /> #include <iostream><br /> #include
<string><br/> #include <odbc++/connection.h><br /> #include <odbc++/setup.h><br /> #include
<odbc++/types.h><br/> #include <odbc++/errorhandler.h><br /> #include <sql.h><br /> #include
<odbc++/drivermanager.h><br/> #include <odbc++/resultset.h><br /> #include
<odbc++/resultsetmetadata.h><br/> #include <odbc++/preparedstatement.h><br /> #include
<odbc++/databasemetadata.h><br/> #include <fstream><br /><br /> using namespace odbc;<br /> using namespace
std;<br/><br /> int main(int argc, char *argv[])<br /> {<br />   Connection* con = 0;<br />   Statement* stmt = 0;<br
/>  ResultSet* rs = 0;<br />   string query;<br />   unsigned int numcol;<br /><br />   try<br />     {<br />       
con= DriverManager::getConnection("test74", "postgres", "");<br />        //con = DriverManager::getConnection("test",
"postgres","");<br />       cout << con->getMetaData()->getDriverVersion() << endl;<br />     }<br />
 catch (SQLException& e)<br />     {<br />       cout << e.getMessage() << endl;<br />       return
1;<br/>     }<br />   <br />   //on nuvi, postgreSQL 7.3.6<br />   query = "insert into justlo
values(lo_import('/usr/local/pgsql/text.txt'))";<br/>   //on sql, postgreSQL 7.4.2<br />   //query = "insert into
justlovalues(lo_import('/var/lib/pgsql/text.txt'))";<br /><br />   stmt = con->createStatement();<br />   try{<br />
   //stmt->executeUpdate(query.c_str());<br />   }<br />   catch (SQLException& e)<br />     {<br />       cout
<<e.getMessage() << endl; <br />       return 1;<br />     }<br />  <br />   query = "select * from
justlo";<br/>   stmt = con->createStatement();<br />   try{<br />     rs = stmt->executeQuery(query.c_str());<br
/>  }<br />   catch (SQLException& e)<br />     {<br />       cout << e.getMessage() << endl; <br />
     return 1;<br />     }<br />   <br />   char str[50];<br />   while( rs->next()){    <br />     istream * ms =
rs->getBinaryStream(1);<br/>     ms->getline(str,50);<br />     cout << str << endl;<br />   }<br
/><br/>   delete con;<br />   return 0;<br /> }<br /><br /> ========================<br /> /* odbc.c<br /> testing
psqlodbcwith postgreSQL 7.3.6 and 7.4.2<br /> */<br /> #include <stdlib.h><br /> #include <stdio.h><br />
#include<sql.h><br /> #include <sqlext.h><br /> #include <sqltypes.h><br /><br /> SQLHENV           
 V_OD_Env;    // Handle ODBC environment<br /> long             V_OD_erg;     // result of functions<br /> SQLHDBC   
        V_OD_hdbc;    // Handle connection<br /> char             V_OD_stat[10]; // Status SQL<br /> SQLINTEGER       
 V_OD_err,V_OD_rowanz,V_OD_id;<br/> SQLSMALLINT         V_OD_mlen;<br /> char                    
V_OD_msg[200],V_OD_buffer[200];<br/> SQLHSTMT                 V_OD_hstmt;   // Handle for a statement<br />
SQLINTEGER              V_OD_err,V_OD_id;<br /> char                     V_OD_buffer[200];<br /><br /> int main(int
argc,char*argv[])<br /> {<br />   // 1. allocate Environment handle and register version <br />  
V_OD_erg=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&V_OD_Env);<br/>   if ((V_OD_erg != SQL_SUCCESS) &&
(V_OD_erg!= SQL_SUCCESS_WITH_INFO))<br />     {<br />       printf("Error AllocHandle\n");<br />       exit(0);<br />
   }<br />   V_OD_erg=SQLSetEnvAttr(V_OD_Env, SQL_ATTR_ODBC_VERSION, <br />              (void*)SQL_OV_ODBC3, 0); <br
/>  if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))<br />     {<br />       printf("Error
SetEnv\n");<br/>       SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);<br />       exit(0);<br />     }<br />   // 2. allocate
connectionhandle, set timeout<br />   V_OD_erg = SQLAllocHandle(SQL_HANDLE_DBC, V_OD_Env, &V_OD_hdbc); <br />   if
((V_OD_erg!= SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))<br />     {<br />       printf("Error
AllocHDB%d\n",V_OD_erg);<br />       SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);<br />       exit(0);<br />     }<br />  
SQLSetConnectAttr(V_OD_hdbc,SQL_LOGIN_TIMEOUT, (SQLPOINTER *)5, 0);<br />   // 3. Connect to the datasource "test" <br
/>  V_OD_erg = SQLConnect(V_OD_hdbc, (SQLCHAR*) "test", SQL_NTS,<br />             (SQLCHAR*) "postgres", SQL_NTS,<br
/>            (SQLCHAR*) "", SQL_NTS);<br />   if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg !=
SQL_SUCCESS_WITH_INFO))<br/>     {<br />       printf("Error SQLConnect %d\n",V_OD_erg);<br />      
SQLGetDiagRec(SQL_HANDLE_DBC,V_OD_hdbc,1, <br />             V_OD_stat, &V_OD_err,V_OD_msg,100,&V_OD_mlen);<br
/>      printf("%s (%d)\n",V_OD_msg,V_OD_err);<br />       SQLFreeHandle(SQL_HANDLE_DBC, V_OD_hdbc);<br />      
SQLFreeHandle(SQL_HANDLE_ENV,V_OD_Env);<br />       exit(0);<br />     }<br />   printf("Connected !\n");<br /><br />  
SQLRETURNretcode;<br />   SQLHSTMT hstmt;<br />   SQLCHAR       BinaryPtr[50];<br />   SQLINTEGER     BinaryLen;<br
/><br/>   SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc, &hstmt);<br /><br />   retcode = SQLExecDirect(hstmt,"SELECT
b FROM justlo",SQL_NTS);<br /><br />   if (retcode == SQL_SUCCESS) {<br />     retcode = SQLFetch(hstmt);<br />     if
(retcode== SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {<br />       printf(" error \n" );<br />     }<br />     if
(retcode== SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){<br />       SQLGetData(hstmt, 1, SQL_C_BINARY, BinaryPtr,
sizeof(BinaryPtr),&BinaryLen);<br/>       printf( " %d, %s ",  BinaryLen, BinaryPtr);<br />     }<br />   }<br />  
else{<br/>     printf(" error on select\n" );<br />   }<br /> }<br />
===================================================<br/><br /> I tried to debug and put som code into odbc++, here is
thedifference between 2 postgres versions:<br /><br /> with 7.3, no problem:<br /><br /> entering getBinaryStream<br />
DataHandler::getStream.cType_: -2  sqlType -4<br /><br /> with 7.4, segfault:<br /><br /> entering getBinaryStream<br
/>DataHandler::getStream. cType_: 4  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
Finstrlewrote:<br /><blockquote cite="mid20051130094509.GA6109@soptik.pzkagis.cz" type="cite"><blockquote
type="cite"><prewrap="">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@bnl.gov">irina@bnl.gov</a>
</pre>