Re: JDBC + PostgreSQL + LargeObjects

Paulo,

If you could mention what error you are getting and what version of the
code you are using that would be helpful.

--Barry

Paulo Delgado wrote:
> 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?
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>