Thread: JDBC + PostgreSQL + LargeObjects

JDBC + PostgreSQL + LargeObjects

From
Paulo Delgado
Date:
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?



Re: JDBC + PostgreSQL + LargeObjects

From
Barry Lind
Date:
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
>
>



Re: JDBC + PostgreSQL + LargeObjects

From
Paulo Delgado
Date:
Oh, i don't get an error message, instead, i see on the browser, the icon for a "not an image".
I tried to wget it, and the newly downloaded file does not match the size of the original image, and i cannot open it
withan image viewer. 
I used the same SQL sentence using the console (the terminal), and using lo_export, and the exported image is correctly
exported.

On Mon, 18 Feb 2002 09:44:43 -0800
Barry Lind <barry@xythos.com> wrote:

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

Re: JDBC + PostgreSQL + LargeObjects

From
Paulo Delgado
Date:
oh, i did "View source" and netscape crashed =) (don't have IEplore, linux box only)

On Tue, 19 Feb 2002 06:49:06 +1100
Justin Clift <justin@postgresql.org> wrote:

> Hi Paulo,
>
> From the browser window where you're getting the icon for "not an
> image", do a "View Source" of the .html page.
>
> The way in which web pages work, there's probably an error buried in the
> source code, and the browser isn't showing it to you.
>
> Doesn't always work, but commonly this is the case.
>
> Hope that's helpful.
>
> :-)
>
> Regards and best wishes,
>
> Justin Clift
>
>
> Paulo Delgado wrote:
> >
> > Oh, i don't get an error message, instead, i see on the browser, the icon for a "not an image".
> > I tried to wget it, and the newly downloaded file does not match the size of the original image, and i cannot open
itwith an image viewer. 
> > I used the same SQL sentence using the console (the terminal), and using lo_export, and the exported image is
correctlyexported. 
> >
> > On Mon, 18 Feb 2002 09:44:43 -0800
> > Barry Lind <barry@xythos.com> wrote:
> >
> > > 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
> > > >
> > > >
> > >
> > >
> > >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>
> --
> "My grandfather once told me that there are two kinds of people: those
> who work and those who take the credit. He told me to try to be in the
> first group; there was less competition there."
>    - Indira Gandhi
>

Re: JDBC + PostgreSQL + LargeObjects

From
Justin Clift
Date:
Hi Paulo,

From the browser window where you're getting the icon for "not an
image", do a "View Source" of the .html page.

The way in which web pages work, there's probably an error buried in the
source code, and the browser isn't showing it to you.

Doesn't always work, but commonly this is the case.

Hope that's helpful.

:-)

Regards and best wishes,

Justin Clift


Paulo Delgado wrote:
>
> Oh, i don't get an error message, instead, i see on the browser, the icon for a "not an image".
> I tried to wget it, and the newly downloaded file does not match the size of the original image, and i cannot open it
withan image viewer. 
> I used the same SQL sentence using the console (the terminal), and using lo_export, and the exported image is
correctlyexported. 
>
> On Mon, 18 Feb 2002 09:44:43 -0800
> Barry Lind <barry@xythos.com> wrote:
>
> > 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
> > >
> > >
> >
> >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi

Re: JDBC + PostgreSQL + LargeObjects

From
Paulo Delgado
Date:
Ok folks, now it works! thank you all!  (i'm using a servlet to avoid the white spaces)
Now i have another problem, when the servlet is writing the bytes to the outputstream, the cpu load increases to 100%,
iguess the problem is in the for() block, should i look for another way of doing this? or use perl? or what? 

check it out:

import javax.servlet.*;
import java.io.*;
import javax.servlet.http.*;
import java.sql.*;
import org.postgresql.largeobject.*;

public class show_coctel extends HttpServlet
{
    public void doGet(HttpServletRequest request , HttpServletResponse response) throws ServletException, IOException
    {
    ServletOutputStream out = response.getOutputStream();
    response.setContentType("image/jpeg");
    try
        {
        Class.forName("org.postgresql.Driver");
        }
    catch(ClassNotFoundException cnfex)
        {
        cnfex.printStackTrace();
        }
    try
        {
        Connection mycon;
        mycon= DriverManager.getConnection("jdbc:postgresql://localhost:5432/database", "username" , "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 cocteles 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++)
                {
                out.write(buf[i]);
                }
            // Close the object
            obj.close();
            }
            rs.close();
        }
        ps.close();
        mycon.close();
        }
    catch(SQLException sqex)
        {
        out.println(sqex.toString());
        }
    }
}

Re: JDBC + PostgreSQL + LargeObjects

From
Marc Lavergne
Date:
I would just change this:

...
int i=0;
for(i=0; i<obj.size() ; i++)
{
   out.write(buf[i]);
}
...

to this:

...
out.write(buf);
out.flush();
out.close();
...

Writing it out 1 byte at a time would be way too inefficient and is
probably why your CPU pegs.

Cheers,

Marc


Paulo Delgado wrote:

> Ok folks, now it works! thank you all!  (i'm using a servlet to avoid the white spaces)
> Now i have another problem, when the servlet is writing the bytes to the outputstream, the cpu load increases to
100%,i guess the problem is in the for() block, should i look for another way of doing this? or use perl? or what? 
>
> check it out:
>
> import javax.servlet.*;
> import java.io.*;
> import javax.servlet.http.*;
> import java.sql.*;
> import org.postgresql.largeobject.*;
>
> public class show_coctel extends HttpServlet
> {
>     public void doGet(HttpServletRequest request , HttpServletResponse response) throws ServletException, IOException

>     {
>     ServletOutputStream out = response.getOutputStream();
>     response.setContentType("image/jpeg");
>     try
>         {
>         Class.forName("org.postgresql.Driver");
>         }
>     catch(ClassNotFoundException cnfex)
>         {
>         cnfex.printStackTrace();
>         }
>     try
>         {
>         Connection mycon;
>         mycon= DriverManager.getConnection("jdbc:postgresql://localhost:5432/database", "username" , "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 cocteles 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++)
>                 {
>                 out.write(buf[i]);
>                 }
>             // Close the object
>             obj.close();
>             }
>             rs.close();
>         }
>         ps.close();
>         mycon.close();
>         }
>     catch(SQLException sqex)
>         {
>         out.println(sqex.toString());
>         }
>     }
> }
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>


--
01010101010101010101010101010101010101010101010101

Marc P. Lavergne [wk:650-576-7978 hm:407-648-6996]
Senior Software Developer
Global Knowledge Management
Worldwide Support Technologies
Openwave Systems Inc.

--

"Anyone who slaps a 'this page is best viewed with
Browser X' label on a Web page appears to be
yearning for the bad old days, before the Web,
when you had very little chance of reading a
document written on another computer, another word
processor, or another network."
-Tim Berners-Lee (Technology Review, July 1996)

01010101010101010101010101010101010101010101010101


Re: JDBC + PostgreSQL + LargeObjects

From
Paulo Delgado
Date:
Thank you very  much! now it works perfectly! w00t!

On Mon, 18 Feb 2002 15:37:59 -0500
Marc Lavergne <mlavergne-pub@richlava.com> wrote:

> I would just change this:
>
> ...
> int i=0;
> for(i=0; i<obj.size() ; i++)
> {
>    out.write(buf[i]);
> }
> ...
>
> to this:
>
> ...
> out.write(buf);
> out.flush();
> out.close();
> ...
>
> Writing it out 1 byte at a time would be way too inefficient and is
> probably why your CPU pegs.
>
> Cheers,
>
> Marc
>
>
> Paulo Delgado wrote:
>
> > Ok folks, now it works! thank you all!  (i'm using a servlet to avoid the white spaces)
> > Now i have another problem, when the servlet is writing the bytes to the outputstream, the cpu load increases to
100%,i guess the problem is in the for() block, should i look for another way of doing this? or use perl? or what? 
> >
> > check it out:
> >
> > import javax.servlet.*;
> > import java.io.*;
> > import javax.servlet.http.*;
> > import java.sql.*;
> > import org.postgresql.largeobject.*;
> >
> > public class show_coctel extends HttpServlet
> > {
> >     public void doGet(HttpServletRequest request , HttpServletResponse response) throws ServletException,
IOException 
> >     {
> >     ServletOutputStream out = response.getOutputStream();
> >     response.setContentType("image/jpeg");
> >     try
> >         {
> >         Class.forName("org.postgresql.Driver");
> >         }
> >     catch(ClassNotFoundException cnfex)
> >         {
> >         cnfex.printStackTrace();
> >         }
> >     try
> >         {
> >         Connection mycon;
> >         mycon= DriverManager.getConnection("jdbc:postgresql://localhost:5432/database", "username" , "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 cocteles 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++)
> >                 {
> >                 out.write(buf[i]);
> >                 }
> >             // Close the object
> >             obj.close();
> >             }
> >             rs.close();
> >         }
> >         ps.close();
> >         mycon.close();
> >         }
> >     catch(SQLException sqex)
> >         {
> >         out.println(sqex.toString());
> >         }
> >     }
> > }
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> >
>
>
> --
> 01010101010101010101010101010101010101010101010101
>
> Marc P. Lavergne [wk:650-576-7978 hm:407-648-6996]
> Senior Software Developer
> Global Knowledge Management
> Worldwide Support Technologies
> Openwave Systems Inc.
>
> --
>
> "Anyone who slaps a 'this page is best viewed with
> Browser X' label on a Web page appears to be
> yearning for the bad old days, before the Web,
> when you had very little chance of reading a
> document written on another computer, another word
> processor, or another network."
> -Tim Berners-Lee (Technology Review, July 1996)
>
> 01010101010101010101010101010101010101010101010101
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Re: JDBC + PostgreSQL + LargeObjects

From
Barry Lind
Date:
Paulo,

Can you provide a test case that doesn't involve jsps?  One that just
writes a file out to the filesystem and shows that the file is being
corrupted.

thanks,
--Barry


Paulo Delgado wrote:
> Oh, i don't get an error message, instead, i see on the browser, the icon for a "not an image".
> I tried to wget it, and the newly downloaded file does not match the size of the original image, and i cannot open it
withan image viewer. 
> I used the same SQL sentence using the console (the terminal), and using lo_export, and the exported image is
correctlyexported. 
>
> On Mon, 18 Feb 2002 09:44:43 -0800
> Barry Lind <barry@xythos.com> wrote:
>
>
>>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
>>>
>>>
>>>
>>
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>



Re: JDBC + PostgreSQL + LargeObjects

From
Doug Fields
Date:
Your problem is very simple.

You cannot allow ANYTHING other than the image to go out via the JSP.

In your example given, You have a whole lot of whitespace going out.

I suggest you put, guarding each newline, <%-- at the end of the line and
--%> at the beginning of
the next - or just merge the tags, e.g.:

<%@ page import="java.io.*" %><%@
page import="java.sql.*" %><%@
page import="org.postgresql.largeobject.*" %><%
// rest of code...
%><%-- DO NOT END WITH A NEWLINE!!! --%>

Note that you can also combine multiple import statements into one with
code such as:

import="java.io.*,java.sql.*,org.postgresql.largeobject.*".

Cheers,

Doug



At 01:27 PM 2/18/2002, you wrote:
>Oh, i don't get an error message, instead, i see on the browser, the icon
>for a "not an image".
>I tried to wget it, and the newly downloaded file does not match the size
>of the original image, and i cannot open it with an image viewer.
>I used the same SQL sentence using the console (the terminal), and using
>lo_export, and the exported image is correctly exported.
>
>On Mon, 18 Feb 2002 09:44:43 -0800
>Barry Lind <barry@xythos.com> wrote:
>
> > 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
> > >
> > >
> >
> >
> >
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org