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
>
>