Re: import_bytea function

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