PostgreSQL equivalent of oracle.sql.BLOB in Java

Question

I have the following code using oracle.sql.BLOB

BLOB b = BLOB.createTemporary(conn, false, BLOB.DURATION_SESSION);
//conn is a PostgreSQL connection (java.sql.Connection object)
b.setBytes(1, someString.getBytes());
ps.setBlob(++i, b); //ps is a PreparedStatement

Obviously it wouldn't work because createTemporary expects an Oracle connection.

What is the equivalent way of achieving this using a Postgres connection? I understand the Postgres equivalent of Blob is ByteA. The target column is a bytea column. Can I just do the following? Or is there a proper way of achieving the same effect?
```
ps.setBytes(++i, someString.getBytes());
```
Also, how do I make the Oracle-specific code DB-vendor-independent? (avoiding the use of oracle.sql.BLOB even if it is an Oracle connection)

Why do you use a BLOB to store a String? That is much better stored in a CLOB (text in Postgres) — user330315
– user330315, Commented Sep 6, 2013 at 9:35
See my answer here: stackoverflow.com/a/8349906/330315 and here stackoverflow.com/a/16462192/330315 for a DBMS independent solution — user330315
– user330315, Commented Sep 6, 2013 at 9:36
@a_horse_with_no_name It's not my code. There are reasons the programmers used it for this instance. We don't do it for every string. Thanks for the links. Can I ask, how is setBinaryStream better than setBytes? would setBytes be enough here? — ADTC
– ADTC, Commented Sep 7, 2013 at 9:24
From my experience, setBinaryStream() works with all JDBC drivers, whereas some drivers do not support setBytes(). If you know exactly which drivers you'll be using and those support setBytes() as well, there is nothing wrong with it. — user330315
– user330315, Commented Sep 7, 2013 at 9:27

Kayaman · Accepted Answer · 2013-09-06 05:20:10Z

1

If you do use setBytes() (I can't remember if there was another psql or non-psql specific blob method), do remember to add a character encoding to your someString.getBytes().

As for making your code db-independent, stop using the db specific classes. You can go a long way with just the java.sql.* classes. It should only be a special case when you have to dig at the actual implementing driver classes.

answered Sep 6, 2013 at 5:20

Kayaman

73.8k6 gold badges91 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ADTC Over a year ago

Thanks for your comments. It will be nice if you could provide some answers too for the questions here.

Collectives™ on Stack Overflow

PostgreSQL equivalent of oracle.sql.BLOB in Java

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related