Thread: import_bytea function
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
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
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
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?
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
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
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)
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
>
>
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
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.
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