0

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.

  1. 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());

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

ADTC
  • 8,999
  • 5
  • 68
  • 93
  • Why do you use a `BLOB` to store a `String`? That is much better stored in a `CLOB` (`text` in Postgres) –  Sep 06 '13 at 09:35
  • See my answer here: http://stackoverflow.com/a/8349906/330315 and here http://stackoverflow.com/a/16462192/330315 for a DBMS independent solution –  Sep 06 '13 at 09: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 Sep 07 '13 at 09:24
  • 1
    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. –  Sep 07 '13 at 09:27

1 Answers1

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.

Kayaman
  • 72,141
  • 5
  • 83
  • 121
  • Thanks for your comments. It will be nice if you could provide some answers too for the questions here. – ADTC Sep 06 '13 at 06:52