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.<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 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>
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 justlo
values(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 psqlodbc with 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 connection handle, 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>
SQLRETURN retcode;<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 the difference
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 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 | Content-Type | Size |
---|---|---|
unknown_filename | text/html | 10.0 KB |
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 |
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 |