JDBC + PostgreSQL + LargeObjects

From: Paulo Delgado <pdelgado(at)pasaportevip(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: JDBC + PostgreSQL + LargeObjects
Date: 2002-02-18 14:38:06
Message-ID: 20020218093806.73927364.pdelgado@pasaportevip.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

I'm writing a JSP which instead of generating HTML code, it sends an
image to the browser. The image is retrieved from the database, where it
is stored as a BLOB (an OID). I read the docs online, this is the URL:

http://developer.postgresql.org/docs/postgres/jdbc-binary-data.html

And this is the code (still not working)

<%@ page import="java.io.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="org.postgresql.largeobject.*" %>

<%
response.setContentType("image/jpeg");
out.close();
ServletOutputStream sos = response.getOutputStream();
try
{
Class.forName("org.postgresql.Driver");
}
catch(ClassNotFoundException cnfex)
{
cnfex.printStackTrace();
}
try
{
Connection mycon;
mycon=
DriverManager.getConnection("jdbc:postgresql://localhost:5432/database",
"userid" , "password");
mycon.setAutoCommit(false);

// Get the Large Object Manager to perform operations with
LargeObjectManager lobj =
((org.postgresql.Connection)mycon).getLargeObjectAPI();

PreparedStatement ps = mycon.prepareStatement("SELECT pic FROM
mytable WHERE month='"+request.getParameter("m")+"' AND
year="+request.getParameter("y"));
ResultSet rs = ps.executeQuery();
if (rs != null) {
while(rs.next()) {
//open the large object for reading
int oid = rs.getInt(1);
LargeObject obj = lobj.open(oid , LargeObjectManager.READ);

//read the data
byte buf[] = new byte[obj.size()];
obj.read(buf, 0, obj.size());

//do something with the data read here
response.setContentLength(obj.size());
int i=0;
for(i=0; i<obj.size() ; i++)
{
sos.write(buf[i]);
}
// Close the object
obj.close();
}
rs.close();
}
ps.close();
mycon.close();

}
catch(SQLException sqex)
{
out.println(sqex.toString());
}
%>

the table is created like this:

CREATE TABLE mytable(
pic oid,
month int2 NOT NULL,
year int2 NOT NULL,
PRIMARY KEY (month, year)
);

I've searched all over the docs, asked all over IRC, and still does not
work. any ideas?

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Bruce Momjian 2002-02-18 14:48:19 Re: Where is my patch ?
Previous Message Nicolas VERGER 2002-02-18 13:20:18 Where is my patch ?