Quick Links

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
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-interfaces pgsql-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. 
 
I'll try to give some details: 
 
On 2 postgres servers ( running 7.3.6 and 7.4.2 ) I did the following: 
 
create domain lo as oid; 
create table justlo(b lo); 
 
Then  I inserted an entry from a text file via a program that uses
libodbc++ ( attached below ) 
insert into justlo values(lo_import('/usr/local/pgsql/text.txt')) 
 
The following 2 programs ( first uses libodbc++, second - plain ODBC )
run fine when connecting to postgres 7.3 and crash with postgres 7.4: 
/* 
 odbc++ example 
*/ 
#include <sstream> 
#include <iostream> 
#include <string> 
#include <odbc++/connection.h> 
#include <odbc++/setup.h> 
#include <odbc++/types.h> 
#include <odbc++/errorhandler.h> 
#include <sql.h> 
#include <odbc++/drivermanager.h> 
#include <odbc++/resultset.h> 
#include <odbc++/resultsetmetadata.h> 
#include <odbc++/preparedstatement.h> 
#include <odbc++/databasemetadata.h> 
#include <fstream> 
 
using namespace odbc; 
using namespace std; 
 
int main(int argc, char *argv[]) 
{ 
  Connection* con = 0; 
  Statement* stmt = 0; 
  ResultSet* rs = 0; 
  string query; 
  unsigned int numcol; 
 
  try 
    { 
       con = DriverManager::getConnection("test74", "postgres", ""); 
       //con = DriverManager::getConnection("test", "postgres", ""); 
      cout << con->getMetaData()->getDriverVersion()
<< endl; 
    } 
  catch (SQLException& e) 
    { 
      cout << e.getMessage() << endl; 
      return 1; 
    } 
  
  //on nuvi, postgreSQL 7.3.6 
  query = "insert into justlo
values(lo_import('/usr/local/pgsql/text.txt'))"; 
  //on sql, postgreSQL 7.4.2 
  //query = "insert into justlo
values(lo_import('/var/lib/pgsql/text.txt'))"; 
 
  stmt = con->createStatement(); 
  try{ 
    //stmt->executeUpdate(query.c_str()); 
  } 
  catch (SQLException& e) 
    { 
      cout << e.getMessage() << endl; 
      return 1; 
    } 
  
  query = "select * from justlo"; 
  stmt = con->createStatement(); 
  try{ 
    rs = stmt->executeQuery(query.c_str()); 
  } 
  catch (SQLException& e) 
    { 
      cout << e.getMessage() << endl; 
      return 1; 
    } 
  
  char str[50]; 
  while( rs->next()){    
    istream * ms = rs->getBinaryStream(1); 
    ms->getline(str,50); 
    cout << str << endl; 
  } 
 
  delete con; 
  return 0; 
} 
 
======================== 
/* odbc.c 
testing psqlodbc with postgreSQL 7.3.6 and 7.4.2 
*/ 
#include <stdlib.h> 
#include <stdio.h> 
#include <sql.h> 
#include <sqlext.h> 
#include <sqltypes.h> 
 
SQLHENV             V_OD_Env;     // Handle ODBC environment 
long             V_OD_erg;     // result of functions 
SQLHDBC             V_OD_hdbc;    // Handle connection 
char             V_OD_stat[10]; // Status SQL 
SQLINTEGER         V_OD_err,V_OD_rowanz,V_OD_id; 
SQLSMALLINT         V_OD_mlen; 
char                     V_OD_msg[200],V_OD_buffer[200]; 
SQLHSTMT                 V_OD_hstmt;   // Handle for a statement 
SQLINTEGER               V_OD_err,V_OD_id; 
char                     V_OD_buffer[200]; 
 
int main(int argc,char *argv[]) 
{ 
  // 1. allocate Environment handle and register version 
  V_OD_erg=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&V_OD_Env); 
  if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg !=
SQL_SUCCESS_WITH_INFO)) 
    { 
      printf("Error AllocHandle\n"); 
      exit(0); 
    } 
  V_OD_erg=SQLSetEnvAttr(V_OD_Env, SQL_ATTR_ODBC_VERSION, 
             (void*)SQL_OV_ODBC3, 0); 
  if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg !=
SQL_SUCCESS_WITH_INFO)) 
    { 
      printf("Error SetEnv\n"); 
      SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); 
      exit(0); 
    } 
  // 2. allocate connection handle, set timeout 
  V_OD_erg = SQLAllocHandle(SQL_HANDLE_DBC, V_OD_Env, &V_OD_hdbc); 
  if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg !=
SQL_SUCCESS_WITH_INFO)) 
    { 
      printf("Error AllocHDB %d\n",V_OD_erg); 
      SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); 
      exit(0); 
    } 
  SQLSetConnectAttr(V_OD_hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER *)5, 0); 
  // 3. Connect to the datasource "test" 
  V_OD_erg = SQLConnect(V_OD_hdbc, (SQLCHAR*) "test", SQL_NTS, 
            (SQLCHAR*) "postgres", SQL_NTS, 
            (SQLCHAR*) "", SQL_NTS); 
  if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg !=
SQL_SUCCESS_WITH_INFO)) 
    { 
      printf("Error SQLConnect %d\n",V_OD_erg); 
      SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc,1, 
            V_OD_stat, &V_OD_err,V_OD_msg,100,&V_OD_mlen); 
      printf("%s (%d)\n",V_OD_msg,V_OD_err); 
      SQLFreeHandle(SQL_HANDLE_DBC, V_OD_hdbc); 
      SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env); 
      exit(0); 
    } 
  printf("Connected !\n"); 
 
  SQLRETURN retcode; 
  SQLHSTMT hstmt; 
  SQLCHAR       BinaryPtr[50]; 
  SQLINTEGER     BinaryLen; 
 
  SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc, &hstmt); 
 
  retcode = SQLExecDirect(hstmt,"SELECT b  FROM justlo",SQL_NTS); 
 
  if (retcode == SQL_SUCCESS) { 
    retcode = SQLFetch(hstmt); 
    if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) { 
      printf(" error \n" ); 
    } 
    if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){ 
      SQLGetData(hstmt, 1, SQL_C_BINARY, BinaryPtr,
sizeof(BinaryPtr),&BinaryLen); 
      printf( " %d, %s ",  BinaryLen, BinaryPtr); 
    } 
  } 
  else{ 
    printf(" error on select\n" ); 
  } 
} 
=================================================== 
 
I tried to debug and put som code into odbc++, here is the difference
between 2 postgres versions: 
 
with 7.3, no problem: 
 
entering getBinaryStream 
DataHandler::getStream. cType_: -2  sqlType -4 
 
with 7.4, segfault: 
 
entering getBinaryStream 
DataHandler::getStream. cType_: 4  sqlType 4 
UNSUPPORTED_GET 
[libodbc++]: Could not get SQL type 4 (INTEGER), C type 4 (SQL_C_LONG)
as an stream 
 
Hope this helps. 
Thanks, 
Irina 
 
Ludek Finstrle wrote: 
<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>
 
<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	Content-Type	Size
unknown_filename	text/html	10.0 KB

In response to

Re: BLOB handling compatibility with PostgreSQL > 7.4 at 2005-11-30 09:45:09 from Ludek Finstrle

Responses

Re: BLOB handling compatibility with PostgreSQL > 7.4 at 2005-12-01 13:04:06 from Ludek Finstrle

Browse pgsql-interfaces by date

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

Browse pgsql-odbc by date

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