Re: import_bytea function

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