Обсуждение: [Fwd: Re: [Fwd: Re: Problems with truncated BLOB]]
Mihael,
I'll include the attachments this time.
thanks,
--Barry
-------- Original Message --------
Subject: Re: [Fwd: Re: Problems with truncated BLOB]
Date: Mon, 26 Nov 2001 12:27:58 -0800
From: Barry Lind <barry@xythos.com>
To: miqster@gmx.net
CC: pgsql-jdbc@postgresql.org
References: <3C028151.8020301@xythos.com>
Mihael,
I have been able to reproduce your problem with the 7.1 drivers, but not
with the 7.2 drivers. It appears that this is bug is already fixed in
current sources.
Note however that the functionality around get/setBinaryStream() has
changed in 7.2. In 7.2 the default behavior is that binary data is
assumed to be in a column of type bytea. So you would create a table
as: create table blobs (filename text, data bytea) instead of using the
OID datatype as in 7.1 (create table blobs (filename text, data OID)).
For backward compatibility you can revert the driver back to the old
behavior of using LargeObjects (i.e. OIDs) by passing the compatible=7.1
parameter to the connection. (This can be done by addint
'?compatible=7.1' to the end of the jdbc connection url.
Attached are modified versions of the test case you sent that work for
me using the 7.2 drivers (but do show the bug using the 7.1 drivers).
The first (BlobTest.java) uses the new functionality and requires that
the table 'blobs' have a column named 'data' of type 'bytea'. The
second (BlobTest2.java) uses the backward compatible mode and requires
that the table 'blobs2' have a column named 'data' of type 'oid'.
thanks,
--Barry
Barry Lind wrote:
> Forwarding to the jdbc list in case someone else has time to look into
> this bug before I get around to it.
>
> thanks,
> --Barry
>
> -------- Original Message --------
> Subject: Re: [JDBC] Problems with truncated BLOB
> Date: 26 Nov 2001 17:37:05 +0100
> From: Mihael Vrbanec <miqster@gmx.net>
> To: Barry Lind <barry@xythos.com>
> References: <1006737949.367.2.camel@Caprice>
<3C01A2AA.2030902@xythos.com>
>
> Hi Barry,
>
> > Could you send a test case that demonstrates the problem? Ideally a
> > sample java program that will upload one of the problem files, and
then
> > another program that tries to download the file showing the
problem you
> > have having.
>
> I have attached a sample program that does both. It uses a table called
> blobs with two columns (filename, data). I experience the problem with
> all binary files I have tried (.doc, .png, .bmp, .tar.gz).
> HTML, and .txt do not have these problems.
>
> > Also, which version of the jdbc driver are you using? (the
version that
> > came with 7.1, or something newer)
>
> The jdbc-driver is the one shipped with 7.1.2/3 (i compiled the whole
> thing from source).
>
> many thanx in advance
>
> bxe Miq
>
>
> ------------------------------------------------------------------------
>
> import java.io.*;
> import java.util.*;
> import java.sql.*;
>
>
> public class BlobTest {
>
> private Connection con = null;
>
> public BlobTest() {
> try {
> Class.forName("org.postgresql.Driver");
> con = DriverManager.getConnection(
> "jdbc:postgresql:repository", "candle", "");
> if (con != null) {
> System.out.println("Connected to database system.\n");
> }
> } catch (SQLException e) {
> System.out.println(e.getMessage());
> System.out.println("Could not connect to database
system.\n");
> } catch (ClassNotFoundException e) {
> System.out.println("Class not found...:-(\n");
> }
> }
>
> private void store(String filename) {
> PreparedStatement ps = null;
> ResultSet r = null;
> File file = new File(filename);
> System.out.println("file.name:" + file.getName() +
"file.length:"+file.length());
> try {
> FileInputStream fis = new FileInputStream(file);
> con.setAutoCommit(false);
> ps = con.prepareStatement(
> "INSERT INTO blobs VALUES (?, ?)");
> ps.setString(1, filename);
> System.out.println("File.length(int): " + (int)
file.length());
> ps.setBinaryStream(2, fis, (int) file.length());
> ps.executeUpdate();
> ps.close();
> fis.close();
> con.commit();
> } catch (SQLException sqle) {
> System.err.println("Store content: " + sqle.getMessage());
> } catch (IOException ioe) {
> }
> }
>
> private void retrieve(String filename) {
> Statement s = null;
> ResultSet r = null;
> int byteSum = 0;
> int bytesRead = 0;
> byte[] buffer = new byte[8 * 1924];
> try {
> System.out.println("Trying to write: " +filename + "test");
> FileOutputStream out = new FileOutputStream(filename +
"test");
> con.setAutoCommit(false);
> s = con.createStatement();
> r = s.executeQuery("SELECT data FROM blobs WHERE filename
= '"
> + filename +"'");
> if (r != null) {
> while (r.next()) {
> System.out.println("We have a result!");
> InputStream is = r.getBinaryStream(1);
> while ((bytesRead = is.read(buffer)) != -1) {
> byteSum += bytesRead;
> out.write(buffer, 0, bytesRead);
> }
> is.close();
> }
> }
> out.close();
> System.out.println("bytes written: " + byteSum);
> con.commit();
> } catch (SQLException sqle) {
> System.err.println("Retrieve content: " + sqle.getMessage());
> } catch (Exception ioe) {
> System.err.println("Writing stuff: " + ioe.getMessage());
> }
> }
>
> public static void main(String[] args) {
> BlobTest bt = new BlobTest();
> bt.store(args[0]);
> bt.retrieve(args[0]);
> }
>
> }
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
import java.io.*;
import java.util.*;
import java.sql.*;
public class BlobTest {
private Connection con = null;
public BlobTest() {
try {
Class.forName("org.postgresql.Driver");
con = DriverManager.getConnection(
"jdbc:postgresql://localhost:5432/files", "blind", "");
if (con != null) {
System.out.println("Connected to database system.\n");
}
} catch (SQLException e) {
System.out.println(e.getMessage());
System.out.println("Could not connect to database system.\n");
} catch (ClassNotFoundException e) {
System.out.println("Class not found...:-(\n");
}
}
private void store(String filename) {
PreparedStatement ps = null;
ResultSet r = null;
File file = new File(filename);
System.out.println("file.name:" + file.getName() + "file.length:"+file.length());
try {
FileInputStream fis = new FileInputStream(file);
con.setAutoCommit(false);
ps = con.prepareStatement(
"INSERT INTO blobs VALUES (?, ?)");
ps.setString(1, filename);
System.out.println("File.length(int): " + (int) file.length());
ps.setBinaryStream(2, fis, (int) file.length());
ps.executeUpdate();
ps.close();
fis.close();
con.commit();
} catch (SQLException sqle) {
System.err.println("Store content: " + sqle.getMessage());
} catch (IOException ioe) {
}
}
private void retrieve(String filename) {
Statement s = null;
ResultSet r = null;
int byteSum = 0;
int bytesRead = 0;
byte[] buffer = new byte[8 * 1924];
try {
System.out.println("Trying to write: " +filename + "test");
FileOutputStream out = new FileOutputStream(filename + "test");
con.setAutoCommit(false);
s = con.createStatement();
r = s.executeQuery("SELECT data FROM blobs WHERE filename = '"
+ filename +"'");
if (r != null) {
while (r.next()) {
System.out.println("We have a result!");
InputStream is = r.getBinaryStream(1);
while ((bytesRead = is.read(buffer)) != -1) {
byteSum += bytesRead;
out.write(buffer, 0, bytesRead);
}
is.close();
}
}
out.close();
System.out.println("bytes written: " + byteSum);
con.commit();
} catch (SQLException sqle) {
System.err.println("Retrieve content: " + sqle.getMessage());
} catch (Exception ioe) {
System.err.println("Writing stuff: " + ioe.getMessage());
}
}
public static void main(String[] args) {
BlobTest bt = new BlobTest();
bt.store(args[0]);
bt.retrieve(args[0]);
}
}
import java.io.*;
import java.util.*;
import java.sql.*;
public class BlobTest2 {
private Connection con = null;
public BlobTest2() {
try {
Class.forName("org.postgresql.Driver");
con = DriverManager.getConnection(
"jdbc:postgresql://localhost:5432/files?compatible=7.1", "blind", "");
if (con != null) {
System.out.println("Connected to database system.\n");
}
} catch (SQLException e) {
System.out.println(e.getMessage());
System.out.println("Could not connect to database system.\n");
} catch (ClassNotFoundException e) {
System.out.println("Class not found...:-(\n");
}
}
private void store(String filename) {
PreparedStatement ps = null;
ResultSet r = null;
File file = new File(filename);
System.out.println("file.name:" + file.getName() + "file.length:"+file.length());
try {
FileInputStream fis = new FileInputStream(file);
con.setAutoCommit(false);
ps = con.prepareStatement(
"INSERT INTO blobs2 VALUES (?, ?)");
ps.setString(1, filename);
System.out.println("File.length(int): " + (int) file.length());
ps.setBinaryStream(2, fis, (int) file.length());
ps.executeUpdate();
ps.close();
fis.close();
con.commit();
} catch (SQLException sqle) {
System.err.println("Store content: " + sqle.getMessage());
} catch (IOException ioe) {
}
}
private void retrieve(String filename) {
Statement s = null;
ResultSet r = null;
int byteSum = 0;
int bytesRead = 0;
byte[] buffer = new byte[8 * 1924];
try {
System.out.println("Trying to write: " +filename + "test");
FileOutputStream out = new FileOutputStream(filename + "test");
con.setAutoCommit(false);
s = con.createStatement();
r = s.executeQuery("SELECT data FROM blobs2 WHERE filename = '"
+ filename +"'");
if (r != null) {
while (r.next()) {
System.out.println("We have a result!");
InputStream is = r.getBinaryStream(1);
while ((bytesRead = is.read(buffer)) != -1) {
byteSum += bytesRead;
out.write(buffer, 0, bytesRead);
}
is.close();
}
}
out.close();
System.out.println("bytes written: " + byteSum);
con.commit();
} catch (SQLException sqle) {
System.err.println("Retrieve content: " + sqle.getMessage());
} catch (Exception ioe) {
System.err.println("Writing stuff: " + ioe.getMessage());
}
}
public static void main(String[] args) {
BlobTest2 bt = new BlobTest2();
bt.store(args[0]);
bt.retrieve(args[0]);
}
}
Barry,
This program perfectly illustrates the bug for me (using both latest
drivers from website - uploaded 25/11 - and 7.1 drivers).
Here's some sample output:
[mike@frodo tmp]$ java BlobTest postgresql.jar
Connected to database system.
file.name: postgresql.jar file.length: 95059
File.length(int): 95059
Trying to write: postgresql.jartest
We have a result!
We have a result!
bytes written: 476
(used the postgresql.jar as a test binary file ;)).
It seems to work ok with text files, but not with binary files. I'd say
there's definitely a bug here somewhere.
Postgres DB version is 7.1.3, Linux 2.4, RH 7.2
-mike
On Tue, 2001-11-27 at 08:18, Barry Lind wrote:
> Mihael,
>
> I'll include the attachments this time.
>
> thanks,
> --Barry
>
> -------- Original Message --------
> Subject: Re: [Fwd: Re: Problems with truncated BLOB]
> Date: Mon, 26 Nov 2001 12:27:58 -0800
> From: Barry Lind <barry@xythos.com>
> To: miqster@gmx.net
> CC: pgsql-jdbc@postgresql.org
> References: <3C028151.8020301@xythos.com>
>
> Mihael,
>
> I have been able to reproduce your problem with the 7.1 drivers, but not
> with the 7.2 drivers. It appears that this is bug is already fixed in
> current sources.
>
> Note however that the functionality around get/setBinaryStream() has
> changed in 7.2. In 7.2 the default behavior is that binary data is
> assumed to be in a column of type bytea. So you would create a table
> as: create table blobs (filename text, data bytea) instead of using the
> OID datatype as in 7.1 (create table blobs (filename text, data OID)).
> For backward compatibility you can revert the driver back to the old
> behavior of using LargeObjects (i.e. OIDs) by passing the compatible=7.1
> parameter to the connection. (This can be done by addint
> '?compatible=7.1' to the end of the jdbc connection url.
>
> Attached are modified versions of the test case you sent that work for
> me using the 7.2 drivers (but do show the bug using the 7.1 drivers).
> The first (BlobTest.java) uses the new functionality and requires that
> the table 'blobs' have a column named 'data' of type 'bytea'. The
> second (BlobTest2.java) uses the backward compatible mode and requires
> that the table 'blobs2' have a column named 'data' of type 'oid'.
>
> thanks,
> --Barry
>
> Barry Lind wrote:
>
> > Forwarding to the jdbc list in case someone else has time to look into
> > this bug before I get around to it.
> >
> > thanks,
> > --Barry
> >
> > -------- Original Message --------
> > Subject: Re: [JDBC] Problems with truncated BLOB
> > Date: 26 Nov 2001 17:37:05 +0100
> > From: Mihael Vrbanec <miqster@gmx.net>
> > To: Barry Lind <barry@xythos.com>
> > References: <1006737949.367.2.camel@Caprice>
> <3C01A2AA.2030902@xythos.com>
> >
> > Hi Barry,
> >
> > > Could you send a test case that demonstrates the problem? Ideally a
> > > sample java program that will upload one of the problem files, and
> then
> > > another program that tries to download the file showing the
> problem you
> > > have having.
> >
> > I have attached a sample program that does both. It uses a table called
> > blobs with two columns (filename, data). I experience the problem with
> > all binary files I have tried (.doc, .png, .bmp, .tar.gz).
> > HTML, and .txt do not have these problems.
> >
> > > Also, which version of the jdbc driver are you using? (the
> version that
> > > came with 7.1, or something newer)
> >
> > The jdbc-driver is the one shipped with 7.1.2/3 (i compiled the whole
> > thing from source).
> >
> > many thanx in advance
> >
> > bxe Miq
> >
> >
> > ------------------------------------------------------------------------
> >
> > import java.io.*;
> > import java.util.*;
> > import java.sql.*;
> >
> >
> > public class BlobTest {
> >
> > private Connection con = null;
> >
> > public BlobTest() {
> > try {
> > Class.forName("org.postgresql.Driver");
> > con = DriverManager.getConnection(
> > "jdbc:postgresql:repository", "candle", "");
> > if (con != null) {
> > System.out.println("Connected to database system.\n");
> > }
> > } catch (SQLException e) {
> > System.out.println(e.getMessage());
> > System.out.println("Could not connect to database
> system.\n");
> > } catch (ClassNotFoundException e) {
> > System.out.println("Class not found...:-(\n");
> > }
> > }
> >
> > private void store(String filename) {
> > PreparedStatement ps = null;
> > ResultSet r = null;
> > File file = new File(filename);
> > System.out.println("file.name:" + file.getName() +
> "file.length:"+file.length());
> > try {
> > FileInputStream fis = new FileInputStream(file);
> > con.setAutoCommit(false);
> > ps = con.prepareStatement(
> > "INSERT INTO blobs VALUES (?, ?)");
> > ps.setString(1, filename);
> > System.out.println("File.length(int): " + (int)
> file.length());
> > ps.setBinaryStream(2, fis, (int) file.length());
> > ps.executeUpdate();
> > ps.close();
> > fis.close();
> > con.commit();
> > } catch (SQLException sqle) {
> > System.err.println("Store content: " + sqle.getMessage());
> > } catch (IOException ioe) {
> > }
> > }
> >
> > private void retrieve(String filename) {
> > Statement s = null;
> > ResultSet r = null;
> > int byteSum = 0;
> > int bytesRead = 0;
> > byte[] buffer = new byte[8 * 1924];
> > try {
> > System.out.println("Trying to write: " +filename + "test");
> > FileOutputStream out = new FileOutputStream(filename +
> "test");
> > con.setAutoCommit(false);
> > s = con.createStatement();
> > r = s.executeQuery("SELECT data FROM blobs WHERE filename
> = '"
> > + filename +"'");
> > if (r != null) {
> > while (r.next()) {
> > System.out.println("We have a result!");
> > InputStream is = r.getBinaryStream(1);
> > while ((bytesRead = is.read(buffer)) != -1) {
> > byteSum += bytesRead;
> > out.write(buffer, 0, bytesRead);
> > }
> > is.close();
> > }
> > }
> > out.close();
> > System.out.println("bytes written: " + byteSum);
> > con.commit();
> > } catch (SQLException sqle) {
> > System.err.println("Retrieve content: " + sqle.getMessage());
> > } catch (Exception ioe) {
> > System.err.println("Writing stuff: " + ioe.getMessage());
> > }
> > }
> >
> > public static void main(String[] args) {
> > BlobTest bt = new BlobTest();
> > bt.store(args[0]);
> > bt.retrieve(args[0]);
> > }
> >
> > }
> >
> >
> > ------------------------------------------------------------------------
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
>
> ----
>
> import java.io.*;
> import java.util.*;
> import java.sql.*;
>
>
> public class BlobTest {
>
> private Connection con = null;
>
> public BlobTest() {
> try {
> Class.forName("org.postgresql.Driver");
> con = DriverManager.getConnection(
> "jdbc:postgresql://localhost:5432/files", "blind", "");
> if (con != null) {
> System.out.println("Connected to database system.\n");
> }
> } catch (SQLException e) {
> System.out.println(e.getMessage());
> System.out.println("Could not connect to database system.\n");
> } catch (ClassNotFoundException e) {
> System.out.println("Class not found...:-(\n");
> }
> }
>
> private void store(String filename) {
> PreparedStatement ps = null;
> ResultSet r = null;
> File file = new File(filename);
> System.out.println("file.name:" + file.getName() + "file.length:"+file.length());
> try {
> FileInputStream fis = new FileInputStream(file);
> con.setAutoCommit(false);
> ps = con.prepareStatement(
> "INSERT INTO blobs VALUES (?, ?)");
> ps.setString(1, filename);
> System.out.println("File.length(int): " + (int) file.length());
> ps.setBinaryStream(2, fis, (int) file.length());
> ps.executeUpdate();
> ps.close();
> fis.close();
> con.commit();
> } catch (SQLException sqle) {
> System.err.println("Store content: " + sqle.getMessage());
> } catch (IOException ioe) {
> }
> }
>
> private void retrieve(String filename) {
> Statement s = null;
> ResultSet r = null;
> int byteSum = 0;
> int bytesRead = 0;
> byte[] buffer = new byte[8 * 1924];
> try {
> System.out.println("Trying to write: " +filename + "test");
> FileOutputStream out = new FileOutputStream(filename + "test");
> con.setAutoCommit(false);
> s = con.createStatement();
> r = s.executeQuery("SELECT data FROM blobs WHERE filename = '"
> + filename +"'");
> if (r != null) {
> while (r.next()) {
> System.out.println("We have a result!");
> InputStream is = r.getBinaryStream(1);
> while ((bytesRead = is.read(buffer)) != -1) {
> byteSum += bytesRead;
> out.write(buffer, 0, bytesRead);
> }
> is.close();
> }
> }
> out.close();
> System.out.println("bytes written: " + byteSum);
> con.commit();
> } catch (SQLException sqle) {
> System.err.println("Retrieve content: " + sqle.getMessage());
> } catch (Exception ioe) {
> System.err.println("Writing stuff: " + ioe.getMessage());
> }
> }
>
> public static void main(String[] args) {
> BlobTest bt = new BlobTest();
> bt.store(args[0]);
> bt.retrieve(args[0]);
> }
>
> }
> ----
>
> import java.io.*;
> import java.util.*;
> import java.sql.*;
>
>
> public class BlobTest2 {
>
> private Connection con = null;
>
> public BlobTest2() {
> try {
> Class.forName("org.postgresql.Driver");
> con = DriverManager.getConnection(
> "jdbc:postgresql://localhost:5432/files?compatible=7.1", "blind", "");
> if (con != null) {
> System.out.println("Connected to database system.\n");
> }
> } catch (SQLException e) {
> System.out.println(e.getMessage());
> System.out.println("Could not connect to database system.\n");
> } catch (ClassNotFoundException e) {
> System.out.println("Class not found...:-(\n");
> }
> }
>
> private void store(String filename) {
> PreparedStatement ps = null;
> ResultSet r = null;
> File file = new File(filename);
> System.out.println("file.name:" + file.getName() + "file.length:"+file.length());
> try {
> FileInputStream fis = new FileInputStream(file);
> con.setAutoCommit(false);
> ps = con.prepareStatement(
> "INSERT INTO blobs2 VALUES (?, ?)");
> ps.setString(1, filename);
> System.out.println("File.length(int): " + (int) file.length());
> ps.setBinaryStream(2, fis, (int) file.length());
> ps.executeUpdate();
> ps.close();
> fis.close();
> con.commit();
> } catch (SQLException sqle) {
> System.err.println("Store content: " + sqle.getMessage());
> } catch (IOException ioe) {
> }
> }
>
> private void retrieve(String filename) {
> Statement s = null;
> ResultSet r = null;
> int byteSum = 0;
> int bytesRead = 0;
> byte[] buffer = new byte[8 * 1924];
> try {
> System.out.println("Trying to write: " +filename + "test");
> FileOutputStream out = new FileOutputStream(filename + "test");
> con.setAutoCommit(false);
> s = con.createStatement();
> r = s.executeQuery("SELECT data FROM blobs2 WHERE filename = '"
> + filename +"'");
> if (r != null) {
> while (r.next()) {
> System.out.println("We have a result!");
> InputStream is = r.getBinaryStream(1);
> while ((bytesRead = is.read(buffer)) != -1) {
> byteSum += bytesRead;
> out.write(buffer, 0, bytesRead);
> }
> is.close();
> }
> }
> out.close();
> System.out.println("bytes written: " + byteSum);
> con.commit();
> } catch (SQLException sqle) {
> System.err.println("Retrieve content: " + sqle.getMessage());
> } catch (Exception ioe) {
> System.err.println("Writing stuff: " + ioe.getMessage());
> }
> }
>
> public static void main(String[] args) {
> BlobTest2 bt = new BlobTest2();
> bt.store(args[0]);
> bt.retrieve(args[0]);
> }
>
> }
> ----
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Cheers,
Mike
--
Mike Cannon-Brookes :: mike@atlassian.com
Atlassian :: http://www.atlassian.com
Supporting YOUR J2EE World
Mike,
When testing with the latest 7.2 drivers are you sure you are using the
latest drivers and not picking up an older version of the driver
somewhere else in your classpath? I can't reproduce the bug using the
latest drivers but can reproduce with 7.1 drivers (see below for my
successful results)
thanks,
--Barry
[blind@barry work]$ CLASSPATH=.:./postgresql.jar; export CLASSPATH
[blind@barry work]$ java BlobTest postgresql.jar
Connected to database system.
file.name:postgresql.jarfile.length:116228
File.length(int): 116228
Trying to write: postgresql.jartest
We have a result!
bytes written: 116228
[blind@barry work]$
The only thing different in my environment is that I am running 7.2b2 on
the server instead of 7.1.3. Otherwise I am also using RedHat 7.2 and
2.4 kernel.
Mike Cannon-Brookes wrote:
> Barry,
>
> This program perfectly illustrates the bug for me (using both latest
> drivers from website - uploaded 25/11 - and 7.1 drivers).
>
> Here's some sample output:
>
> [mike@frodo tmp]$ java BlobTest postgresql.jar
> Connected to database system.
>
> file.name: postgresql.jar file.length: 95059
> File.length(int): 95059
> Trying to write: postgresql.jartest
> We have a result!
> We have a result!
> bytes written: 476
>
> (used the postgresql.jar as a test binary file ;)).
>
> It seems to work ok with text files, but not with binary files. I'd say
> there's definitely a bug here somewhere.
>
> Postgres DB version is 7.1.3, Linux 2.4, RH 7.2
>
> -mike
>
> On Tue, 2001-11-27 at 08:18, Barry Lind wrote:
>
>>Mihael,
>>
>>I'll include the attachments this time.
>>
>>thanks,
>>--Barry
>>
>>-------- Original Message --------
>>Subject: Re: [Fwd: Re: Problems with truncated BLOB]
>>Date: Mon, 26 Nov 2001 12:27:58 -0800
>>From: Barry Lind <barry@xythos.com>
>>To: miqster@gmx.net
>>CC: pgsql-jdbc@postgresql.org
>>References: <3C028151.8020301@xythos.com>
>>
>>Mihael,
>>
>>I have been able to reproduce your problem with the 7.1 drivers, but not
>>with the 7.2 drivers. It appears that this is bug is already fixed in
>>current sources.
>>
>>Note however that the functionality around get/setBinaryStream() has
>>changed in 7.2. In 7.2 the default behavior is that binary data is
>>assumed to be in a column of type bytea. So you would create a table
>>as: create table blobs (filename text, data bytea) instead of using the
>>OID datatype as in 7.1 (create table blobs (filename text, data OID)).
>>For backward compatibility you can revert the driver back to the old
>>behavior of using LargeObjects (i.e. OIDs) by passing the compatible=7.1
>>parameter to the connection. (This can be done by addint
>>'?compatible=7.1' to the end of the jdbc connection url.
>>
>>Attached are modified versions of the test case you sent that work for
>>me using the 7.2 drivers (but do show the bug using the 7.1 drivers).
>>The first (BlobTest.java) uses the new functionality and requires that
>>the table 'blobs' have a column named 'data' of type 'bytea'. The
>>second (BlobTest2.java) uses the backward compatible mode and requires
>>that the table 'blobs2' have a column named 'data' of type 'oid'.
>>
>>thanks,
>>--Barry
>>
>>Barry Lind wrote:
>>
>> > Forwarding to the jdbc list in case someone else has time to look into
>> > this bug before I get around to it.
>> >
>> > thanks,
>> > --Barry
>> >
>> > -------- Original Message --------
>> > Subject: Re: [JDBC] Problems with truncated BLOB
>> > Date: 26 Nov 2001 17:37:05 +0100
>> > From: Mihael Vrbanec <miqster@gmx.net>
>> > To: Barry Lind <barry@xythos.com>
>> > References: <1006737949.367.2.camel@Caprice>
>><3C01A2AA.2030902@xythos.com>
>> >
>> > Hi Barry,
>> >
>> > > Could you send a test case that demonstrates the problem? Ideally a
>> > > sample java program that will upload one of the problem files, and
>>then
>> > > another program that tries to download the file showing the
>>problem you
>> > > have having.
>> >
>> > I have attached a sample program that does both. It uses a table called
>> > blobs with two columns (filename, data). I experience the problem with
>> > all binary files I have tried (.doc, .png, .bmp, .tar.gz).
>> > HTML, and .txt do not have these problems.
>> >
>> > > Also, which version of the jdbc driver are you using? (the
>>version that
>> > > came with 7.1, or something newer)
>> >
>> > The jdbc-driver is the one shipped with 7.1.2/3 (i compiled the whole
>> > thing from source).
>> >
>> > many thanx in advance
>> >
>> > bxe Miq
>> >
>> >
>> > ------------------------------------------------------------------------
>> >
>> > import java.io.*;
>> > import java.util.*;
>> > import java.sql.*;
>> >
>> >
>> > public class BlobTest {
>> >
>> > private Connection con = null;
>> >
>> > public BlobTest() {
>> > try {
>> > Class.forName("org.postgresql.Driver");
>> > con = DriverManager.getConnection(
>> > "jdbc:postgresql:repository", "candle", "");
>> > if (con != null) {
>> > System.out.println("Connected to database system.\n");
>> > }
>> > } catch (SQLException e) {
>> > System.out.println(e.getMessage());
>> > System.out.println("Could not connect to database
>>system.\n");
>> > } catch (ClassNotFoundException e) {
>> > System.out.println("Class not found...:-(\n");
>> > }
>> > }
>> >
>> > private void store(String filename) {
>> > PreparedStatement ps = null;
>> > ResultSet r = null;
>> > File file = new File(filename);
>> > System.out.println("file.name:" + file.getName() +
>>"file.length:"+file.length());
>> > try {
>> > FileInputStream fis = new FileInputStream(file);
>> > con.setAutoCommit(false);
>> > ps = con.prepareStatement(
>> > "INSERT INTO blobs VALUES (?, ?)");
>> > ps.setString(1, filename);
>> > System.out.println("File.length(int): " + (int)
>>file.length());
>> > ps.setBinaryStream(2, fis, (int) file.length());
>> > ps.executeUpdate();
>> > ps.close();
>> > fis.close();
>> > con.commit();
>> > } catch (SQLException sqle) {
>> > System.err.println("Store content: " + sqle.getMessage());
>> > } catch (IOException ioe) {
>> > }
>> > }
>> >
>> > private void retrieve(String filename) {
>> > Statement s = null;
>> > ResultSet r = null;
>> > int byteSum = 0;
>> > int bytesRead = 0;
>> > byte[] buffer = new byte[8 * 1924];
>> > try {
>> > System.out.println("Trying to write: " +filename + "test");
>> > FileOutputStream out = new FileOutputStream(filename +
>>"test");
>> > con.setAutoCommit(false);
>> > s = con.createStatement();
>> > r = s.executeQuery("SELECT data FROM blobs WHERE filename
>>= '"
>> > + filename +"'");
>> > if (r != null) {
>> > while (r.next()) {
>> > System.out.println("We have a result!");
>> > InputStream is = r.getBinaryStream(1);
>> > while ((bytesRead = is.read(buffer)) != -1) {
>> > byteSum += bytesRead;
>> > out.write(buffer, 0, bytesRead);
>> > }
>> > is.close();
>> > }
>> > }
>> > out.close();
>> > System.out.println("bytes written: " + byteSum);
>> > con.commit();
>> > } catch (SQLException sqle) {
>> > System.err.println("Retrieve content: " + sqle.getMessage());
>> > } catch (Exception ioe) {
>> > System.err.println("Writing stuff: " + ioe.getMessage());
>> > }
>> > }
>> >
>> > public static void main(String[] args) {
>> > BlobTest bt = new BlobTest();
>> > bt.store(args[0]);
>> > bt.retrieve(args[0]);
>> > }
>> >
>> > }
>> >
>> >
>> > ------------------------------------------------------------------------
>> >
>> >
>> > ---------------------------(end of broadcast)---------------------------
>> > TIP 6: Have you searched our list archives?
>> >
>> > http://archives.postgresql.org
>> >
>>
>>
>>----
>>
>>
>
>>import java.io.*;
>>import java.util.*;
>>import java.sql.*;
>>
>>
>>public class BlobTest {
>>
>> private Connection con = null;
>>
>> public BlobTest() {
>> try {
>> Class.forName("org.postgresql.Driver");
>> con = DriverManager.getConnection(
>> "jdbc:postgresql://localhost:5432/files", "blind", "");
>> if (con != null) {
>> System.out.println("Connected to database system.\n");
>> }
>> } catch (SQLException e) {
>> System.out.println(e.getMessage());
>> System.out.println("Could not connect to database system.\n");
>> } catch (ClassNotFoundException e) {
>> System.out.println("Class not found...:-(\n");
>> }
>> }
>>
>> private void store(String filename) {
>> PreparedStatement ps = null;
>> ResultSet r = null;
>> File file = new File(filename);
>> System.out.println("file.name:" + file.getName() + "file.length:"+file.length());
>> try {
>> FileInputStream fis = new FileInputStream(file);
>> con.setAutoCommit(false);
>> ps = con.prepareStatement(
>> "INSERT INTO blobs VALUES (?, ?)");
>> ps.setString(1, filename);
>> System.out.println("File.length(int): " + (int) file.length());
>> ps.setBinaryStream(2, fis, (int) file.length());
>> ps.executeUpdate();
>> ps.close();
>> fis.close();
>> con.commit();
>> } catch (SQLException sqle) {
>> System.err.println("Store content: " + sqle.getMessage());
>> } catch (IOException ioe) {
>> }
>> }
>>
>> private void retrieve(String filename) {
>> Statement s = null;
>> ResultSet r = null;
>> int byteSum = 0;
>> int bytesRead = 0;
>> byte[] buffer = new byte[8 * 1924];
>> try {
>> System.out.println("Trying to write: " +filename + "test");
>> FileOutputStream out = new FileOutputStream(filename + "test");
>> con.setAutoCommit(false);
>> s = con.createStatement();
>> r = s.executeQuery("SELECT data FROM blobs WHERE filename = '"
>> + filename +"'");
>> if (r != null) {
>> while (r.next()) {
>> System.out.println("We have a result!");
>> InputStream is = r.getBinaryStream(1);
>> while ((bytesRead = is.read(buffer)) != -1) {
>> byteSum += bytesRead;
>> out.write(buffer, 0, bytesRead);
>> }
>> is.close();
>> }
>> }
>> out.close();
>> System.out.println("bytes written: " + byteSum);
>> con.commit();
>> } catch (SQLException sqle) {
>> System.err.println("Retrieve content: " + sqle.getMessage());
>> } catch (Exception ioe) {
>> System.err.println("Writing stuff: " + ioe.getMessage());
>> }
>> }
>>
>> public static void main(String[] args) {
>> BlobTest bt = new BlobTest();
>> bt.store(args[0]);
>> bt.retrieve(args[0]);
>> }
>>
>>}
>>----
>>
>>
>
>>import java.io.*;
>>import java.util.*;
>>import java.sql.*;
>>
>>
>>public class BlobTest2 {
>>
>> private Connection con = null;
>>
>> public BlobTest2() {
>> try {
>> Class.forName("org.postgresql.Driver");
>> con = DriverManager.getConnection(
>> "jdbc:postgresql://localhost:5432/files?compatible=7.1", "blind", "");
>> if (con != null) {
>> System.out.println("Connected to database system.\n");
>> }
>> } catch (SQLException e) {
>> System.out.println(e.getMessage());
>> System.out.println("Could not connect to database system.\n");
>> } catch (ClassNotFoundException e) {
>> System.out.println("Class not found...:-(\n");
>> }
>> }
>>
>> private void store(String filename) {
>> PreparedStatement ps = null;
>> ResultSet r = null;
>> File file = new File(filename);
>> System.out.println("file.name:" + file.getName() + "file.length:"+file.length());
>> try {
>> FileInputStream fis = new FileInputStream(file);
>> con.setAutoCommit(false);
>> ps = con.prepareStatement(
>> "INSERT INTO blobs2 VALUES (?, ?)");
>> ps.setString(1, filename);
>> System.out.println("File.length(int): " + (int) file.length());
>> ps.setBinaryStream(2, fis, (int) file.length());
>> ps.executeUpdate();
>> ps.close();
>> fis.close();
>> con.commit();
>> } catch (SQLException sqle) {
>> System.err.println("Store content: " + sqle.getMessage());
>> } catch (IOException ioe) {
>> }
>> }
>>
>> private void retrieve(String filename) {
>> Statement s = null;
>> ResultSet r = null;
>> int byteSum = 0;
>> int bytesRead = 0;
>> byte[] buffer = new byte[8 * 1924];
>> try {
>> System.out.println("Trying to write: " +filename + "test");
>> FileOutputStream out = new FileOutputStream(filename + "test");
>> con.setAutoCommit(false);
>> s = con.createStatement();
>> r = s.executeQuery("SELECT data FROM blobs2 WHERE filename = '"
>> + filename +"'");
>> if (r != null) {
>> while (r.next()) {
>> System.out.println("We have a result!");
>> InputStream is = r.getBinaryStream(1);
>> while ((bytesRead = is.read(buffer)) != -1) {
>> byteSum += bytesRead;
>> out.write(buffer, 0, bytesRead);
>> }
>> is.close();
>> }
>> }
>> out.close();
>> System.out.println("bytes written: " + byteSum);
>> con.commit();
>> } catch (SQLException sqle) {
>> System.err.println("Retrieve content: " + sqle.getMessage());
>> } catch (Exception ioe) {
>> System.err.println("Writing stuff: " + ioe.getMessage());
>> }
>> }
>>
>> public static void main(String[] args) {
>> BlobTest2 bt = new BlobTest2();
>> bt.store(args[0]);
>> bt.retrieve(args[0]);
>> }
>>
>>}
>>----
>>
>>
>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>
Barry,
I'm definitely just using the new driver, here's my classpath:
/usr/java/jdk1.3.1/lib/tools.jar:.:/opt/orion/lib/jdbc7.2dev-1.2.jar
(downloaded the jdbc jar from jdbc.postgresql.org this morning)
Might I suggest it's something to do with bytea support from 7.1 to
7.2b3? ;)
-mike
On Tue, 2001-11-27 at 13:13, Barry Lind wrote:
> Mike,
>
> When testing with the latest 7.2 drivers are you sure you are using the
> latest drivers and not picking up an older version of the driver
> somewhere else in your classpath? I can't reproduce the bug using the
> latest drivers but can reproduce with 7.1 drivers (see below for my
> successful results)
>
> thanks,
> --Barry
>
> [blind@barry work]$ CLASSPATH=.:./postgresql.jar; export CLASSPATH
> [blind@barry work]$ java BlobTest postgresql.jar
> Connected to database system.
>
> file.name:postgresql.jarfile.length:116228
> File.length(int): 116228
> Trying to write: postgresql.jartest
> We have a result!
> bytes written: 116228
> [blind@barry work]$
>
> The only thing different in my environment is that I am running 7.2b2 on
> the server instead of 7.1.3. Otherwise I am also using RedHat 7.2 and
> 2.4 kernel.
>
>
>
>
>
>
> Mike Cannon-Brookes wrote:
>
> > Barry,
> >
> > This program perfectly illustrates the bug for me (using both latest
> > drivers from website - uploaded 25/11 - and 7.1 drivers).
> >
> > Here's some sample output:
> >
> > [mike@frodo tmp]$ java BlobTest postgresql.jar
> > Connected to database system.
> >
> > file.name: postgresql.jar file.length: 95059
> > File.length(int): 95059
> > Trying to write: postgresql.jartest
> > We have a result!
> > We have a result!
> > bytes written: 476
> >
> > (used the postgresql.jar as a test binary file ;)).
> >
> > It seems to work ok with text files, but not with binary files. I'd say
> > there's definitely a bug here somewhere.
> >
> > Postgres DB version is 7.1.3, Linux 2.4, RH 7.2
> >
> > -mike
> >
> > On Tue, 2001-11-27 at 08:18, Barry Lind wrote:
> >
> >>Mihael,
> >>
> >>I'll include the attachments this time.
> >>
> >>thanks,
> >>--Barry
> >>
> >>-------- Original Message --------
> >>Subject: Re: [Fwd: Re: Problems with truncated BLOB]
> >>Date: Mon, 26 Nov 2001 12:27:58 -0800
> >>From: Barry Lind <barry@xythos.com>
> >>To: miqster@gmx.net
> >>CC: pgsql-jdbc@postgresql.org
> >>References: <3C028151.8020301@xythos.com>
> >>
> >>Mihael,
> >>
> >>I have been able to reproduce your problem with the 7.1 drivers, but not
> >>with the 7.2 drivers. It appears that this is bug is already fixed in
> >>current sources.
> >>
> >>Note however that the functionality around get/setBinaryStream() has
> >>changed in 7.2. In 7.2 the default behavior is that binary data is
> >>assumed to be in a column of type bytea. So you would create a table
> >>as: create table blobs (filename text, data bytea) instead of using the
> >>OID datatype as in 7.1 (create table blobs (filename text, data OID)).
> >>For backward compatibility you can revert the driver back to the old
> >>behavior of using LargeObjects (i.e. OIDs) by passing the compatible=7.1
> >>parameter to the connection. (This can be done by addint
> >>'?compatible=7.1' to the end of the jdbc connection url.
> >>
> >>Attached are modified versions of the test case you sent that work for
> >>me using the 7.2 drivers (but do show the bug using the 7.1 drivers).
> >>The first (BlobTest.java) uses the new functionality and requires that
> >>the table 'blobs' have a column named 'data' of type 'bytea'. The
> >>second (BlobTest2.java) uses the backward compatible mode and requires
> >>that the table 'blobs2' have a column named 'data' of type 'oid'.
> >>
> >>thanks,
> >>--Barry
> >>
> >>Barry Lind wrote:
> >>
> >> > Forwarding to the jdbc list in case someone else has time to look into
> >> > this bug before I get around to it.
> >> >
> >> > thanks,
> >> > --Barry
> >> >
> >> > -------- Original Message --------
> >> > Subject: Re: [JDBC] Problems with truncated BLOB
> >> > Date: 26 Nov 2001 17:37:05 +0100
> >> > From: Mihael Vrbanec <miqster@gmx.net>
> >> > To: Barry Lind <barry@xythos.com>
> >> > References: <1006737949.367.2.camel@Caprice>
> >><3C01A2AA.2030902@xythos.com>
> >> >
> >> > Hi Barry,
> >> >
> >> > > Could you send a test case that demonstrates the problem? Ideally a
> >> > > sample java program that will upload one of the problem files, and
> >>then
> >> > > another program that tries to download the file showing the
> >>problem you
> >> > > have having.
> >> >
> >> > I have attached a sample program that does both. It uses a table called
> >> > blobs with two columns (filename, data). I experience the problem with
> >> > all binary files I have tried (.doc, .png, .bmp, .tar.gz).
> >> > HTML, and .txt do not have these problems.
> >> >
> >> > > Also, which version of the jdbc driver are you using? (the
> >>version that
> >> > > came with 7.1, or something newer)
> >> >
> >> > The jdbc-driver is the one shipped with 7.1.2/3 (i compiled the whole
> >> > thing from source).
> >> >
> >> > many thanx in advance
> >> >
> >> > bxe Miq
> >> >
> >> >
> >> > ------------------------------------------------------------------------
> >> >
> >> > import java.io.*;
> >> > import java.util.*;
> >> > import java.sql.*;
> >> >
> >> >
> >> > public class BlobTest {
> >> >
> >> > private Connection con = null;
> >> >
> >> > public BlobTest() {
> >> > try {
> >> > Class.forName("org.postgresql.Driver");
> >> > con = DriverManager.getConnection(
> >> > "jdbc:postgresql:repository", "candle", "");
> >> > if (con != null) {
> >> > System.out.println("Connected to database system.\n");
> >> > }
> >> > } catch (SQLException e) {
> >> > System.out.println(e.getMessage());
> >> > System.out.println("Could not connect to database
> >>system.\n");
> >> > } catch (ClassNotFoundException e) {
> >> > System.out.println("Class not found...:-(\n");
> >> > }
> >> > }
> >> >
> >> > private void store(String filename) {
> >> > PreparedStatement ps = null;
> >> > ResultSet r = null;
> >> > File file = new File(filename);
> >> > System.out.println("file.name:" + file.getName() +
> >>"file.length:"+file.length());
> >> > try {
> >> > FileInputStream fis = new FileInputStream(file);
> >> > con.setAutoCommit(false);
> >> > ps = con.prepareStatement(
> >> > "INSERT INTO blobs VALUES (?, ?)");
> >> > ps.setString(1, filename);
> >> > System.out.println("File.length(int): " + (int)
> >>file.length());
> >> > ps.setBinaryStream(2, fis, (int) file.length());
> >> > ps.executeUpdate();
> >> > ps.close();
> >> > fis.close();
> >> > con.commit();
> >> > } catch (SQLException sqle) {
> >> > System.err.println("Store content: " + sqle.getMessage());
> >> > } catch (IOException ioe) {
> >> > }
> >> > }
> >> >
> >> > private void retrieve(String filename) {
> >> > Statement s = null;
> >> > ResultSet r = null;
> >> > int byteSum = 0;
> >> > int bytesRead = 0;
> >> > byte[] buffer = new byte[8 * 1924];
> >> > try {
> >> > System.out.println("Trying to write: " +filename + "test");
> >> > FileOutputStream out = new FileOutputStream(filename +
> >>"test");
> >> > con.setAutoCommit(false);
> >> > s = con.createStatement();
> >> > r = s.executeQuery("SELECT data FROM blobs WHERE filename
> >>= '"
> >> > + filename +"'");
> >> > if (r != null) {
> >> > while (r.next()) {
> >> > System.out.println("We have a result!");
> >> > InputStream is = r.getBinaryStream(1);
> >> > while ((bytesRead = is.read(buffer)) != -1) {
> >> > byteSum += bytesRead;
> >> > out.write(buffer, 0, bytesRead);
> >> > }
> >> > is.close();
> >> > }
> >> > }
> >> > out.close();
> >> > System.out.println("bytes written: " + byteSum);
> >> > con.commit();
> >> > } catch (SQLException sqle) {
> >> > System.err.println("Retrieve content: " + sqle.getMessage());
> >> > } catch (Exception ioe) {
> >> > System.err.println("Writing stuff: " + ioe.getMessage());
> >> > }
> >> > }
> >> >
> >> > public static void main(String[] args) {
> >> > BlobTest bt = new BlobTest();
> >> > bt.store(args[0]);
> >> > bt.retrieve(args[0]);
> >> > }
> >> >
> >> > }
> >> >
> >> >
> >> > ------------------------------------------------------------------------
> >> >
> >> >
> >> > ---------------------------(end of broadcast)---------------------------
> >> > TIP 6: Have you searched our list archives?
> >> >
> >> > http://archives.postgresql.org
> >> >
> >>
> >>
> >>----
> >>
> >>
> >
> >>import java.io.*;
> >>import java.util.*;
> >>import java.sql.*;
> >>
> >>
> >>public class BlobTest {
> >>
> >> private Connection con = null;
> >>
> >> public BlobTest() {
> >> try {
> >> Class.forName("org.postgresql.Driver");
> >> con = DriverManager.getConnection(
> >> "jdbc:postgresql://localhost:5432/files", "blind", "");
> >> if (con != null) {
> >> System.out.println("Connected to database system.\n");
> >> }
> >> } catch (SQLException e) {
> >> System.out.println(e.getMessage());
> >> System.out.println("Could not connect to database system.\n");
> >> } catch (ClassNotFoundException e) {
> >> System.out.println("Class not found...:-(\n");
> >> }
> >> }
> >>
> >> private void store(String filename) {
> >> PreparedStatement ps = null;
> >> ResultSet r = null;
> >> File file = new File(filename);
> >> System.out.println("file.name:" + file.getName() + "file.length:"+file.length());
> >> try {
> >> FileInputStream fis = new FileInputStream(file);
> >> con.setAutoCommit(false);
> >> ps = con.prepareStatement(
> >> "INSERT INTO blobs VALUES (?, ?)");
> >> ps.setString(1, filename);
> >> System.out.println("File.length(int): " + (int) file.length());
> >> ps.setBinaryStream(2, fis, (int) file.length());
> >> ps.executeUpdate();
> >> ps.close();
> >> fis.close();
> >> con.commit();
> >> } catch (SQLException sqle) {
> >> System.err.println("Store content: " + sqle.getMessage());
> >> } catch (IOException ioe) {
> >> }
> >> }
> >>
> >> private void retrieve(String filename) {
> >> Statement s = null;
> >> ResultSet r = null;
> >> int byteSum = 0;
> >> int bytesRead = 0;
> >> byte[] buffer = new byte[8 * 1924];
> >> try {
> >> System.out.println("Trying to write: " +filename + "test");
> >> FileOutputStream out = new FileOutputStream(filename + "test");
> >> con.setAutoCommit(false);
> >> s = con.createStatement();
> >> r = s.executeQuery("SELECT data FROM blobs WHERE filename = '"
> >> + filename +"'");
> >> if (r != null) {
> >> while (r.next()) {
> >> System.out.println("We have a result!");
> >> InputStream is = r.getBinaryStream(1);
> >> while ((bytesRead = is.read(buffer)) != -1) {
> >> byteSum += bytesRead;
> >> out.write(buffer, 0, bytesRead);
> >> }
> >> is.close();
> >> }
> >> }
> >> out.close();
> >> System.out.println("bytes written: " + byteSum);
> >> con.commit();
> >> } catch (SQLException sqle) {
> >> System.err.println("Retrieve content: " + sqle.getMessage());
> >> } catch (Exception ioe) {
> >> System.err.println("Writing stuff: " + ioe.getMessage());
> >> }
> >> }
> >>
> >> public static void main(String[] args) {
> >> BlobTest bt = new BlobTest();
> >> bt.store(args[0]);
> >> bt.retrieve(args[0]);
> >> }
> >>
> >>}
> >>----
> >>
> >>
> >
> >>import java.io.*;
> >>import java.util.*;
> >>import java.sql.*;
> >>
> >>
> >>public class BlobTest2 {
> >>
> >> private Connection con = null;
> >>
> >> public BlobTest2() {
> >> try {
> >> Class.forName("org.postgresql.Driver");
> >> con = DriverManager.getConnection(
> >> "jdbc:postgresql://localhost:5432/files?compatible=7.1", "blind", "");
> >> if (con != null) {
> >> System.out.println("Connected to database system.\n");
> >> }
> >> } catch (SQLException e) {
> >> System.out.println(e.getMessage());
> >> System.out.println("Could not connect to database system.\n");
> >> } catch (ClassNotFoundException e) {
> >> System.out.println("Class not found...:-(\n");
> >> }
> >> }
> >>
> >> private void store(String filename) {
> >> PreparedStatement ps = null;
> >> ResultSet r = null;
> >> File file = new File(filename);
> >> System.out.println("file.name:" + file.getName() + "file.length:"+file.length());
> >> try {
> >> FileInputStream fis = new FileInputStream(file);
> >> con.setAutoCommit(false);
> >> ps = con.prepareStatement(
> >> "INSERT INTO blobs2 VALUES (?, ?)");
> >> ps.setString(1, filename);
> >> System.out.println("File.length(int): " + (int) file.length());
> >> ps.setBinaryStream(2, fis, (int) file.length());
> >> ps.executeUpdate();
> >> ps.close();
> >> fis.close();
> >> con.commit();
> >> } catch (SQLException sqle) {
> >> System.err.println("Store content: " + sqle.getMessage());
> >> } catch (IOException ioe) {
> >> }
> >> }
> >>
> >> private void retrieve(String filename) {
> >> Statement s = null;
> >> ResultSet r = null;
> >> int byteSum = 0;
> >> int bytesRead = 0;
> >> byte[] buffer = new byte[8 * 1924];
> >> try {
> >> System.out.println("Trying to write: " +filename + "test");
> >> FileOutputStream out = new FileOutputStream(filename + "test");
> >> con.setAutoCommit(false);
> >> s = con.createStatement();
> >> r = s.executeQuery("SELECT data FROM blobs2 WHERE filename = '"
> >> + filename +"'");
> >> if (r != null) {
> >> while (r.next()) {
> >> System.out.println("We have a result!");
> >> InputStream is = r.getBinaryStream(1);
> >> while ((bytesRead = is.read(buffer)) != -1) {
> >> byteSum += bytesRead;
> >> out.write(buffer, 0, bytesRead);
> >> }
> >> is.close();
> >> }
> >> }
> >> out.close();
> >> System.out.println("bytes written: " + byteSum);
> >> con.commit();
> >> } catch (SQLException sqle) {
> >> System.err.println("Retrieve content: " + sqle.getMessage());
> >> } catch (Exception ioe) {
> >> System.err.println("Writing stuff: " + ioe.getMessage());
> >> }
> >> }
> >>
> >> public static void main(String[] args) {
> >> BlobTest2 bt = new BlobTest2();
> >> bt.store(args[0]);
> >> bt.retrieve(args[0]);
> >> }
> >>
> >>}
> >>----
> >>
> >>
> >
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 2: you can get off all lists at once with the unregister command
> >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >>
>
>
--
Cheers,
Mike
--
Mike Cannon-Brookes :: mike@atlassian.com
Atlassian :: http://www.atlassian.com
Supporting YOUR J2EE World
Hi Mike, > I'm definitely just using the new driver, here's my classpath: > > /usr/java/jdk1.3.1/lib/tools.jar:.:/opt/orion/lib/jdbc7.2dev-1.2.jar > > (downloaded the jdbc jar from jdbc.postgresql.org this morning) > > Might I suggest it's something to do with bytea support from 7.1 to > 7.2b3? ;) Well, I tried it some minutes ago with the new development-driver (jdbc7.2dev-1.2.jar) and my 7.1.3 database (using additional "?compatible=7.1" to the connect-url) and the lod oid-style. It seemed to work. I will make some more tests, but so far I did not experience any problem... bxe Miq -- ** Mihael Vrbanec --- Uni Karlsruhe ** *** eMail: miqster@gmx.net *** ** Homepage: miqster.homeip.net/~miq/ **
Mike,
I am really at a loss to explain what is going on here. I just
resurected a 7.1.2 database and ran the test and it worked fine (for
both the bytea, and LargeObject versions using the 7.2 driver).
thanks,
--Barry
Mike Cannon-Brookes wrote:
> Barry,
>
> I'm definitely just using the new driver, here's my classpath:
>
> /usr/java/jdk1.3.1/lib/tools.jar:.:/opt/orion/lib/jdbc7.2dev-1.2.jar
>
> (downloaded the jdbc jar from jdbc.postgresql.org this morning)
>
> Might I suggest it's something to do with bytea support from 7.1 to
> 7.2b3? ;)
>
> -mike
>
> On Tue, 2001-11-27 at 13:13, Barry Lind wrote:
>
>>Mike,
>>
>>When testing with the latest 7.2 drivers are you sure you are using the
>>latest drivers and not picking up an older version of the driver
>>somewhere else in your classpath? I can't reproduce the bug using the
>>latest drivers but can reproduce with 7.1 drivers (see below for my
>>successful results)
>>
>>thanks,
>>--Barry
>>
>>[blind@barry work]$ CLASSPATH=.:./postgresql.jar; export CLASSPATH
>>[blind@barry work]$ java BlobTest postgresql.jar
>>Connected to database system.
>>
>>file.name:postgresql.jarfile.length:116228
>>File.length(int): 116228
>>Trying to write: postgresql.jartest
>>We have a result!
>>bytes written: 116228
>>[blind@barry work]$
>>
>>The only thing different in my environment is that I am running 7.2b2 on
>>the server instead of 7.1.3. Otherwise I am also using RedHat 7.2 and
>>2.4 kernel.
>>
>>
>>
>>
>>
>>
>>Mike Cannon-Brookes wrote:
>>
>>
>>>Barry,
>>>
>>>This program perfectly illustrates the bug for me (using both latest
>>>drivers from website - uploaded 25/11 - and 7.1 drivers).
>>>
>>>Here's some sample output:
>>>
>>>[mike@frodo tmp]$ java BlobTest postgresql.jar
>>>Connected to database system.
>>>
>>>file.name: postgresql.jar file.length: 95059
>>>File.length(int): 95059
>>>Trying to write: postgresql.jartest
>>>We have a result!
>>>We have a result!
>>>bytes written: 476
>>>
>>>(used the postgresql.jar as a test binary file ;)).
>>>
>>>It seems to work ok with text files, but not with binary files. I'd say
>>>there's definitely a bug here somewhere.
>>>
>>>Postgres DB version is 7.1.3, Linux 2.4, RH 7.2
>>>
>>>-mike
>>>
>>>On Tue, 2001-11-27 at 08:18, Barry Lind wrote:
>>>
>>>
>>>>Mihael,
>>>>
>>>>I'll include the attachments this time.
>>>>
>>>>thanks,
>>>>--Barry
>>>>
>>>>-------- Original Message --------
>>>>Subject: Re: [Fwd: Re: Problems with truncated BLOB]
>>>>Date: Mon, 26 Nov 2001 12:27:58 -0800
>>>>From: Barry Lind <barry@xythos.com>
>>>>To: miqster@gmx.net
>>>>CC: pgsql-jdbc@postgresql.org
>>>>References: <3C028151.8020301@xythos.com>
>>>>
>>>>Mihael,
>>>>
>>>>I have been able to reproduce your problem with the 7.1 drivers, but not
>>>>with the 7.2 drivers. It appears that this is bug is already fixed in
>>>>current sources.
>>>>
>>>>Note however that the functionality around get/setBinaryStream() has
>>>>changed in 7.2. In 7.2 the default behavior is that binary data is
>>>>assumed to be in a column of type bytea. So you would create a table
>>>>as: create table blobs (filename text, data bytea) instead of using the
>>>>OID datatype as in 7.1 (create table blobs (filename text, data OID)).
>>>>For backward compatibility you can revert the driver back to the old
>>>>behavior of using LargeObjects (i.e. OIDs) by passing the compatible=7.1
>>>>parameter to the connection. (This can be done by addint
>>>>'?compatible=7.1' to the end of the jdbc connection url.
>>>>
>>>>Attached are modified versions of the test case you sent that work for
>>>>me using the 7.2 drivers (but do show the bug using the 7.1 drivers).
>>>>The first (BlobTest.java) uses the new functionality and requires that
>>>>the table 'blobs' have a column named 'data' of type 'bytea'. The
>>>>second (BlobTest2.java) uses the backward compatible mode and requires
>>>>that the table 'blobs2' have a column named 'data' of type 'oid'.
>>>>
>>>>thanks,
>>>>--Barry
>>>>
>>>>Barry Lind wrote:
>>>>
>>>>
>>>>>Forwarding to the jdbc list in case someone else has time to look into
>>>>>this bug before I get around to it.
>>>>>
>>>>>thanks,
>>>>>--Barry
>>>>>
>>>>>-------- Original Message --------
>>>>>Subject: Re: [JDBC] Problems with truncated BLOB
>>>>>Date: 26 Nov 2001 17:37:05 +0100
>>>>>From: Mihael Vrbanec <miqster@gmx.net>
>>>>>To: Barry Lind <barry@xythos.com>
>>>>>References: <1006737949.367.2.camel@Caprice>
>>>>>
>>>><3C01A2AA.2030902@xythos.com>
>>>>
>>>>>Hi Barry,
>>>>>
>>>>> > Could you send a test case that demonstrates the problem? Ideally a
>>>>> > sample java program that will upload one of the problem files, and
>>>>>
>>>>then
>>>>
>>>>> > another program that tries to download the file showing the
>>>>>
>>>>problem you
>>>>
>>>>> > have having.
>>>>>
>>>>>I have attached a sample program that does both. It uses a table called
>>>>>blobs with two columns (filename, data). I experience the problem with
>>>>>all binary files I have tried (.doc, .png, .bmp, .tar.gz).
>>>>>HTML, and .txt do not have these problems.
>>>>>
>>>>> > Also, which version of the jdbc driver are you using? (the
>>>>>
>>>>version that
>>>>
>>>>> > came with 7.1, or something newer)
>>>>>
>>>>>The jdbc-driver is the one shipped with 7.1.2/3 (i compiled the whole
>>>>>thing from source).
>>>>>
>>>>>many thanx in advance
>>>>>
>>>>>bxe Miq
>>>>>
>>>>>
>>>>>------------------------------------------------------------------------
>>>>>
>>>>>import java.io.*;
>>>>>import java.util.*;
>>>>>import java.sql.*;
>>>>>
>>>>>
>>>>>public class BlobTest {
>>>>>
>>>>> private Connection con = null;
>>>>>
>>>>> public BlobTest() {
>>>>> try {
>>>>> Class.forName("org.postgresql.Driver");
>>>>> con = DriverManager.getConnection(
>>>>> "jdbc:postgresql:repository", "candle", "");
>>>>> if (con != null) {
>>>>> System.out.println("Connected to database system.\n");
>>>>> }
>>>>> } catch (SQLException e) {
>>>>> System.out.println(e.getMessage());
>>>>> System.out.println("Could not connect to database
>>>>>
>>>>system.\n");
>>>>
>>>>> } catch (ClassNotFoundException e) {
>>>>> System.out.println("Class not found...:-(\n");
>>>>> }
>>>>> }
>>>>>
>>>>> private void store(String filename) {
>>>>> PreparedStatement ps = null;
>>>>> ResultSet r = null;
>>>>> File file = new File(filename);
>>>>> System.out.println("file.name:" + file.getName() +
>>>>>
>>>>"file.length:"+file.length());
>>>>
>>>>> try {
>>>>> FileInputStream fis = new FileInputStream(file);
>>>>> con.setAutoCommit(false);
>>>>> ps = con.prepareStatement(
>>>>> "INSERT INTO blobs VALUES (?, ?)");
>>>>> ps.setString(1, filename);
>>>>> System.out.println("File.length(int): " + (int)
>>>>>
>>>>file.length());
>>>>
>>>>> ps.setBinaryStream(2, fis, (int) file.length());
>>>>> ps.executeUpdate();
>>>>> ps.close();
>>>>> fis.close();
>>>>> con.commit();
>>>>> } catch (SQLException sqle) {
>>>>> System.err.println("Store content: " + sqle.getMessage());
>>>>> } catch (IOException ioe) {
>>>>> }
>>>>> }
>>>>>
>>>>> private void retrieve(String filename) {
>>>>> Statement s = null;
>>>>> ResultSet r = null;
>>>>> int byteSum = 0;
>>>>> int bytesRead = 0;
>>>>> byte[] buffer = new byte[8 * 1924];
>>>>> try {
>>>>> System.out.println("Trying to write: " +filename + "test");
>>>>> FileOutputStream out = new FileOutputStream(filename +
>>>>>
>>>>"test");
>>>>
>>>>> con.setAutoCommit(false);
>>>>> s = con.createStatement();
>>>>> r = s.executeQuery("SELECT data FROM blobs WHERE filename
>>>>>
>>>>= '"
>>>>
>>>>> + filename +"'");
>>>>> if (r != null) {
>>>>> while (r.next()) {
>>>>> System.out.println("We have a result!");
>>>>> InputStream is = r.getBinaryStream(1);
>>>>> while ((bytesRead = is.read(buffer)) != -1) {
>>>>> byteSum += bytesRead;
>>>>> out.write(buffer, 0, bytesRead);
>>>>> }
>>>>> is.close();
>>>>> }
>>>>> }
>>>>> out.close();
>>>>> System.out.println("bytes written: " + byteSum);
>>>>> con.commit();
>>>>> } catch (SQLException sqle) {
>>>>> System.err.println("Retrieve content: " + sqle.getMessage());
>>>>> } catch (Exception ioe) {
>>>>> System.err.println("Writing stuff: " + ioe.getMessage());
>>>>> }
>>>>> }
>>>>>
>>>>> public static void main(String[] args) {
>>>>> BlobTest bt = new BlobTest();
>>>>> bt.store(args[0]);
>>>>> bt.retrieve(args[0]);
>>>>> }
>>>>>
>>>>>}
>>>>>
>>>>>
>>>>>------------------------------------------------------------------------
>>>>>
>>>>>
>>>>>---------------------------(end of broadcast)---------------------------
>>>>>TIP 6: Have you searched our list archives?
>>>>>
>>>>>http://archives.postgresql.org
>>>>>
>>>>>
>>>>
>>>>----
>>>>
>>>>
>>>>
>>>>import java.io.*;
>>>>import java.util.*;
>>>>import java.sql.*;
>>>>
>>>>
>>>>public class BlobTest {
>>>>
>>>> private Connection con = null;
>>>>
>>>> public BlobTest() {
>>>> try {
>>>> Class.forName("org.postgresql.Driver");
>>>> con = DriverManager.getConnection(
>>>> "jdbc:postgresql://localhost:5432/files", "blind", "");
>>>> if (con != null) {
>>>> System.out.println("Connected to database system.\n");
>>>> }
>>>> } catch (SQLException e) {
>>>> System.out.println(e.getMessage());
>>>> System.out.println("Could not connect to database system.\n");
>>>> } catch (ClassNotFoundException e) {
>>>> System.out.println("Class not found...:-(\n");
>>>> }
>>>> }
>>>>
>>>> private void store(String filename) {
>>>> PreparedStatement ps = null;
>>>> ResultSet r = null;
>>>> File file = new File(filename);
>>>> System.out.println("file.name:" + file.getName() + "file.length:"+file.length());
>>>> try {
>>>> FileInputStream fis = new FileInputStream(file);
>>>> con.setAutoCommit(false);
>>>> ps = con.prepareStatement(
>>>> "INSERT INTO blobs VALUES (?, ?)");
>>>> ps.setString(1, filename);
>>>> System.out.println("File.length(int): " + (int) file.length());
>>>> ps.setBinaryStream(2, fis, (int) file.length());
>>>> ps.executeUpdate();
>>>> ps.close();
>>>> fis.close();
>>>> con.commit();
>>>> } catch (SQLException sqle) {
>>>> System.err.println("Store content: " + sqle.getMessage());
>>>> } catch (IOException ioe) {
>>>> }
>>>> }
>>>>
>>>> private void retrieve(String filename) {
>>>> Statement s = null;
>>>> ResultSet r = null;
>>>> int byteSum = 0;
>>>> int bytesRead = 0;
>>>> byte[] buffer = new byte[8 * 1924];
>>>> try {
>>>> System.out.println("Trying to write: " +filename + "test");
>>>> FileOutputStream out = new FileOutputStream(filename + "test");
>>>> con.setAutoCommit(false);
>>>> s = con.createStatement();
>>>> r = s.executeQuery("SELECT data FROM blobs WHERE filename = '"
>>>> + filename +"'");
>>>> if (r != null) {
>>>> while (r.next()) {
>>>> System.out.println("We have a result!");
>>>> InputStream is = r.getBinaryStream(1);
>>>> while ((bytesRead = is.read(buffer)) != -1) {
>>>> byteSum += bytesRead;
>>>> out.write(buffer, 0, bytesRead);
>>>> }
>>>> is.close();
>>>> }
>>>> }
>>>> out.close();
>>>> System.out.println("bytes written: " + byteSum);
>>>> con.commit();
>>>> } catch (SQLException sqle) {
>>>> System.err.println("Retrieve content: " + sqle.getMessage());
>>>> } catch (Exception ioe) {
>>>> System.err.println("Writing stuff: " + ioe.getMessage());
>>>> }
>>>> }
>>>>
>>>> public static void main(String[] args) {
>>>> BlobTest bt = new BlobTest();
>>>> bt.store(args[0]);
>>>> bt.retrieve(args[0]);
>>>> }
>>>>
>>>>}
>>>>----
>>>>
>>>>
>>>>
>>>>import java.io.*;
>>>>import java.util.*;
>>>>import java.sql.*;
>>>>
>>>>
>>>>public class BlobTest2 {
>>>>
>>>> private Connection con = null;
>>>>
>>>> public BlobTest2() {
>>>> try {
>>>> Class.forName("org.postgresql.Driver");
>>>> con = DriverManager.getConnection(
>>>> "jdbc:postgresql://localhost:5432/files?compatible=7.1", "blind", "");
>>>> if (con != null) {
>>>> System.out.println("Connected to database system.\n");
>>>> }
>>>> } catch (SQLException e) {
>>>> System.out.println(e.getMessage());
>>>> System.out.println("Could not connect to database system.\n");
>>>> } catch (ClassNotFoundException e) {
>>>> System.out.println("Class not found...:-(\n");
>>>> }
>>>> }
>>>>
>>>> private void store(String filename) {
>>>> PreparedStatement ps = null;
>>>> ResultSet r = null;
>>>> File file = new File(filename);
>>>> System.out.println("file.name:" + file.getName() + "file.length:"+file.length());
>>>> try {
>>>> FileInputStream fis = new FileInputStream(file);
>>>> con.setAutoCommit(false);
>>>> ps = con.prepareStatement(
>>>> "INSERT INTO blobs2 VALUES (?, ?)");
>>>> ps.setString(1, filename);
>>>> System.out.println("File.length(int): " + (int) file.length());
>>>> ps.setBinaryStream(2, fis, (int) file.length());
>>>> ps.executeUpdate();
>>>> ps.close();
>>>> fis.close();
>>>> con.commit();
>>>> } catch (SQLException sqle) {
>>>> System.err.println("Store content: " + sqle.getMessage());
>>>> } catch (IOException ioe) {
>>>> }
>>>> }
>>>>
>>>> private void retrieve(String filename) {
>>>> Statement s = null;
>>>> ResultSet r = null;
>>>> int byteSum = 0;
>>>> int bytesRead = 0;
>>>> byte[] buffer = new byte[8 * 1924];
>>>> try {
>>>> System.out.println("Trying to write: " +filename + "test");
>>>> FileOutputStream out = new FileOutputStream(filename + "test");
>>>> con.setAutoCommit(false);
>>>> s = con.createStatement();
>>>> r = s.executeQuery("SELECT data FROM blobs2 WHERE filename = '"
>>>> + filename +"'");
>>>> if (r != null) {
>>>> while (r.next()) {
>>>> System.out.println("We have a result!");
>>>> InputStream is = r.getBinaryStream(1);
>>>> while ((bytesRead = is.read(buffer)) != -1) {
>>>> byteSum += bytesRead;
>>>> out.write(buffer, 0, bytesRead);
>>>> }
>>>> is.close();
>>>> }
>>>> }
>>>> out.close();
>>>> System.out.println("bytes written: " + byteSum);
>>>> con.commit();
>>>> } catch (SQLException sqle) {
>>>> System.err.println("Retrieve content: " + sqle.getMessage());
>>>> } catch (Exception ioe) {
>>>> System.err.println("Writing stuff: " + ioe.getMessage());
>>>> }
>>>> }
>>>>
>>>> public static void main(String[] args) {
>>>> BlobTest2 bt = new BlobTest2();
>>>> bt.store(args[0]);
>>>> bt.retrieve(args[0]);
>>>> }
>>>>
>>>>}
>>>>----
>>>>
>>>>
>>>>
>>>>---------------------------(end of broadcast)---------------------------
>>>>TIP 2: you can get off all lists at once with the unregister command
>>>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>>>
>>>>
>>
Mihael / Barry, I'm still struggling to make this work. So far: I've tried both scenarios 1) using a table with a bytea column (create table blobs(filename text, data bytea)) and the latest development driver 2) using a table with a traditional oid column (create table blobs2(filename text, data oid)) and the latest development driver, and appending ?compatible=7.1 to the driver URI In both scenarios uploading text files works perfectly. ONLY in the second scenario does uploading binary files work. In the first scenario they return truncated. I am using the BlobTest provided by Barry. My database version is 7.1.3 on RedHat 7.2 using Sun Linux JDK 1.3.1. Where can I go from here? What can I test? I really need to get this working - so I'm willing to try anything ;) Should I bother upgrading the whole DB to 7.2beta to see if it works? -mike Taking Mihael's advice I've managed to get the binary On Wed, 2001-11-28 at 00:51, Mihael Vrbanec wrote: > Hi Mike, > > > I'm definitely just using the new driver, here's my classpath: > > > > /usr/java/jdk1.3.1/lib/tools.jar:.:/opt/orion/lib/jdbc7.2dev-1.2.jar > > > > (downloaded the jdbc jar from jdbc.postgresql.org this morning) > > > > Might I suggest it's something to do with bytea support from 7.1 to > > 7.2b3? ;) > > Well, I tried it some minutes ago with the new development-driver > (jdbc7.2dev-1.2.jar) and my 7.1.3 database (using additional > "?compatible=7.1" to the connect-url) and the lod oid-style. It seemed > to work. I will make some more tests, but so far I did not experience > any problem... > > bxe Miq > -- > ** Mihael Vrbanec --- Uni Karlsruhe ** > *** eMail: miqster@gmx.net *** > ** Homepage: miqster.homeip.net/~miq/ ** > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- Cheers, Mike -- Mike Cannon-Brookes :: mike@atlassian.com Atlassian :: http://www.atlassian.com Supporting YOUR J2EE World