Thread: import_bytea function

import_bytea function

From
Stephen Davies
Date:
I am trying to use the import_bytea function described in various list posts
(PG version 9.3.14) in a jsp.

I get an error saying that only the super user can use server-side lo_import().

If I change the Java connection to use user postgres, the function works but I
would prefer not to do this.

Is there a better way to update a bytea column from an uploaded file (in this
case a small jpeg)?

Cheers and thanks,
Stephen


Re: import_bytea function

From
Thomas Kellerer
Date:
Stephen Davies schrieb am 07.10.2016 um 09:12:
> I am trying to use the import_bytea function described in various list posts (PG version 9.3.14) in a jsp.
>
> I get an error saying that only the super user can use server-side lo_import().
>
> If I change the Java connection to use user postgres, the function works but I would prefer not to do this.
>
> Is there a better way to update a bytea column from an uploaded file (in this case a small jpeg)?

Since you mention JSP, I assume you are using JDBC.

You can store the contents of a file in a bytea using plain JDBC no lo_import() required

    String sql = "insert into images (id, image_data) values (?,?)";
    Connection con = ....;
    File uploaded = new File("...");
    InputStream in = new FileInputStream(uploaded);
    PreparedStatement pstmt = con.prepareStatement(sql);
    pstmt.setInt(1, 42);
    pstmt.setBinaryStream(in, (int)uploaded.length());
    pstmt.executeUpdate();

This *only* works with bytea column, not with "large objects".

In production code you obviously need to close all resources and handle errors.
I left that out for simplicity.

Thomas



Re: import_bytea function

From
Stephen Davies
Date:
On 07/10/16 18:48, Thomas Kellerer wrote:
> Stephen Davies schrieb am 07.10.2016 um 09:12:
>> I am trying to use the import_bytea function described in various list posts (PG version 9.3.14) in a jsp.
>>
>> I get an error saying that only the super user can use server-side lo_import().
>>
>> If I change the Java connection to use user postgres, the function works but I would prefer not to do this.
>>
>> Is there a better way to update a bytea column from an uploaded file (in this case a small jpeg)?
>
> Since you mention JSP, I assume you are using JDBC.
>
> You can store the contents of a file in a bytea using plain JDBC no lo_import() required
>
>     String sql = "insert into images (id, image_data) values (?,?)";
>     Connection con = ....;
>     File uploaded = new File("...");
>     InputStream in = new FileInputStream(uploaded);
>     PreparedStatement pstmt = con.prepareStatement(sql);
>     pstmt.setInt(1, 42);
>     pstmt.setBinaryStream(in, (int)uploaded.length());
>     pstmt.executeUpdate();
>
> This *only* works with bytea column, not with "large objects".
>
> In production code you obviously need to close all resources and handle errors.
> I left that out for simplicity.
>
> Thomas
>
>
>
>
>
That looks reasonable but I need to update rather than insert and my similar
code with sql="update part set pic=? where id=3" did not work.

Cheers,
Stephen



Re: import_bytea function

From
Thomas Kellerer
Date:
Stephen Davies schrieb am 07.10.2016 um 10:46:
>> You can store the contents of a file in a bytea using plain JDBC no lo_import() required
>>
>>     String sql = "insert into images (id, image_data) values (?,?)";
>>     Connection con = ....;
>>     File uploaded = new File("...");
>>     InputStream in = new FileInputStream(uploaded);
>>     PreparedStatement pstmt = con.prepareStatement(sql);
>>     pstmt.setInt(1, 42);
>>     pstmt.setBinaryStream(in, (int)uploaded.length());
>>     pstmt.executeUpdate();
>>
>> This *only* works with bytea column, not with "large objects".
>>
>> In production code you obviously need to close all resources and handle errors.
>> I left that out for simplicity.

> That looks reasonable but I need to update rather than insert and my similar
> code with sql="update part set pic=? where id=3" did not work.

That *will* work (using that myself for updates as well).

What exactly is your problem? What was the error/exception?



Re: import_bytea function

From
Stephen Davies
Date:
On 07/10/16 19:24, Thomas Kellerer wrote:
> Stephen Davies schrieb am 07.10.2016 um 10:46:
>>> You can store the contents of a file in a bytea using plain JDBC no lo_import() required
>>>
>>>     String sql = "insert into images (id, image_data) values (?,?)";
>>>     Connection con = ....;
>>>     File uploaded = new File("...");
>>>     InputStream in = new FileInputStream(uploaded);
>>>     PreparedStatement pstmt = con.prepareStatement(sql);
>>>     pstmt.setInt(1, 42);
>>>     pstmt.setBinaryStream(in, (int)uploaded.length());
>>>     pstmt.executeUpdate();
>>>
>>> This *only* works with bytea column, not with "large objects".
>>>
>>> In production code you obviously need to close all resources and handle errors.
>>> I left that out for simplicity.
>
>> That looks reasonable but I need to update rather than insert and my similar
>> code with sql="update part set pic=? where id=3" did not work.
>
> That *will* work (using that myself for updates as well).
>
> What exactly is your problem? What was the error/exception?
>
>
>
>
>
I will have to regenerate that code to get the exact error message text but it
basically said that the parameter substitution was invalid.

A follow-up question.
Once the bytea column is populated, how best to display  the content in a web
page?

I have :

byte [] imgB;
ResultSet rs = st1.executeQuery("select pic from part where pno='" + p + "'");
       if(rs.next()){
           imgB = rs.getBytes(1);
           if (imgB != null){
             out.write("Content-type: image/jpeg");
             out.write("Content-length: " + (int)imgB.length);
             out.write(imgB.toString());
           }
       }

but this does not work.
The toString() looks wrong but removing it makes the write fail.

Cheers and thanks,
Stephen


--
=============================================================================
Stephen Davies Consulting P/L                             Phone: 08-8177 1595
Adelaide, South Australia.                                Mobile:040 304 0583


Re: import_bytea function

From
Stephen Davies
Date:
On 07/10/16 19:24, Thomas Kellerer wrote:
> Stephen Davies schrieb am 07.10.2016 um 10:46:
>>> You can store the contents of a file in a bytea using plain JDBC no lo_import() required
>>>
>>>     String sql = "insert into images (id, image_data) values (?,?)";
>>>     Connection con = ....;
>>>     File uploaded = new File("...");
>>>     InputStream in = new FileInputStream(uploaded);
>>>     PreparedStatement pstmt = con.prepareStatement(sql);
>>>     pstmt.setInt(1, 42);
>>>     pstmt.setBinaryStream(in, (int)uploaded.length());
>>>     pstmt.executeUpdate();
>>>
>>> This *only* works with bytea column, not with "large objects".
>>>
>>> In production code you obviously need to close all resources and handle errors.
>>> I left that out for simplicity.
>
>> That looks reasonable but I need to update rather than insert and my similar
>> code with sql="update part set pic=? where id=3" did not work.
>
> That *will* work (using that myself for updates as well).
>
> What exactly is your problem? What was the error/exception?
>
>
>
>
>
I tried the prepared statement approach again and this time it worked.
No idea what I did wrong last time.

However, my display code still does not work.

Cheers and thanks,
Stephen


Re: import_bytea function

From
Thomas Kellerer
Date:
Stephen Davies schrieb am 08.10.2016 um 02:57:
> A follow-up question.
> Once the bytea column is populated, how best to display  the content in a web page?
>
> I have :
>
> byte [] imgB;
> ResultSet rs = st1.executeQuery("select pic from part where pno='" + p + "'");
>       if(rs.next()){
>           imgB = rs.getBytes(1);
>           if (imgB != null){
>             out.write("Content-type: image/jpeg");
>             out.write("Content-length: " + (int)imgB.length);
>             out.write(imgB.toString());
>           }
>       }
>
> but this does not work.
> The toString() looks wrong but removing it makes the write fail.

What is "out"?

(And please use a PreparedStatement to run the SELECT for security reasons)


Re: import_bytea function

From
Jan de Visser
Date:

On 2016-10-08 2:36 AM, Stephen Davies wrote:
> On 07/10/16 19:24, Thomas Kellerer wrote:
>> Stephen Davies schrieb am 07.10.2016 um 10:46:
>>>> You can store the contents of a file in a bytea using plain JDBC no
>>>> lo_import() required
>>>>
>>>>     String sql = "insert into images (id, image_data) values (?,?)";
>>>>     Connection con = ....;
>>>>     File uploaded = new File("...");
>>>>     InputStream in = new FileInputStream(uploaded);
>>>>     PreparedStatement pstmt = con.prepareStatement(sql);
>>>>     pstmt.setInt(1, 42);
>>>>     pstmt.setBinaryStream(in, (int)uploaded.length());
>>>>     pstmt.executeUpdate();
>>>>
>>>> This *only* works with bytea column, not with "large objects".
>>>>
>>>> In production code you obviously need to close all resources and
>>>> handle errors.
>>>> I left that out for simplicity.
>>
>>> That looks reasonable but I need to update rather than insert and my
>>> similar
>>> code with sql="update part set pic=? where id=3" did not work.
>>
>> That *will* work (using that myself for updates as well).
>>
>> What exactly is your problem? What was the error/exception?
>>
>>
>>
>>
>>
> I tried the prepared statement approach again and this time it worked.
> No idea what I did wrong last time.
>
> However, my display code still does not work.

You need to stream the data. Working from memory here, and it's been a
long time, but it's something like

rs = conn.executeQuery("SELECT byeta_column FROM foo WHERE bar = ?");
Blob b = (Blob) rs.getObject(1);
InputStream is = b.getInputStream();
byte[1024] bytes;
while (is.read(bytes)) {
   System.out.print(String(bytes));
}

Something like this, modulo using PreparedStatements and proper use of
the byte[] buffer.

>
> Cheers and thanks,
> Stephen
>
>



Re: import_bytea function

From
Thomas Kellerer
Date:
Jan de Visser schrieb am 08.10.2016 um 16:11:
> You need to stream the data. Working from memory here, and it's been a long time, but it's something like
>
> rs = conn.executeQuery("SELECT byeta_column FROM foo WHERE bar = ?");
> Blob b = (Blob) rs.getObject(1);

No. getBytes() works fine with the JDBC driver.

The problem is calling toString() on it and sending that via print




Re: import_bytea function

From
Thomas Kellerer
Date:
Stephen Davies schrieb am 08.10.2016 um 02:57:
> I will have to regenerate that code to get the exact error message text but it basically said that the parameter
substitutionwas invalid. 
>
> A follow-up question.
> Once the bytea column is populated, how best to display  the content in a web page?
>
> I have :
>
> byte [] imgB;
> ResultSet rs = st1.executeQuery("select pic from part where pno='" + p + "'");
>       if(rs.next()){
>           imgB = rs.getBytes(1);
>           if (imgB != null){
>             out.write("Content-type: image/jpeg");
>             out.write("Content-length: " + (int)imgB.length);
>             out.write(imgB.toString());
>           }
>       }
>
> but this does not work.
> The toString() looks wrong but removing it makes the write fail.

Assuming you are doing this in a Servlet, you should be writing the binary data to the HttpServletResponse

Something like:

     ResultSet rs = st1.executeQuery("select pic from part where pno='" + p + "'");
     if (rs.next())
     {
        byte[] image = rs.getBytes(1);
        response.setContentType("image/jpeg");
        response.setIntHeader("Content-length", (int)image.length);
        response.getOutputStream().write(image);
     }

But this is getting quite off-topic now.


Re: import_bytea function (resolved)

From
Stephen Davies
Date:
On 08/10/16 17:16, Thomas Kellerer wrote:
> Stephen Davies schrieb am 08.10.2016 um 02:57:
>> A follow-up question.
>> Once the bytea column is populated, how best to display  the content in a
>> web page?
>>
>> I have :
>>
>> byte [] imgB;
>> ResultSet rs = st1.executeQuery("select pic from part where pno='" + p + "'");
>>       if(rs.next()){
>>           imgB = rs.getBytes(1);
>>           if (imgB != null){
>>             out.write("Content-type: image/jpeg");
>>             out.write("Content-length: " + (int)imgB.length);
>>             out.write(imgB.toString());
>>           }
>>       }
>>
>> but this does not work.
>> The toString() looks wrong but removing it makes the write fail.
>
> What is "out"?
>
> (And please use a PreparedStatement to run the SELECT for security reasons)
>
>
>
>

Thanks to all.
Both the update and display now work.


Cheers,
Stephen