import_bytea function

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general