CODEWITHSUNDEEP
javajdbcfirebird
dougband
dougband

Reputation: 1

Insert and restore BLOB values SubType = Text Field Firebird Java

I'm building a project, and I came across a problem with a Blob field I created in the database Firebird. This field would be referring to a field observations, which I do not want to limit the amount of text which the user will enter.

But I have a problem and do not know how I save and read this field.

I'm using JDBC to use the insert prepareStatement stmt stmt.set ... - For the blob do not know how to do and also do not know how to convert the String value for the field

Upvotes: 0

Views: 4208

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 109090

You can use PreparedStatement.setString() and ResultSet.getString() and the Firebird driver will convert it to/from a blob for you if you are using a BLOB SUB_TYPE 1 (aka BLOB SUB_TYPE TEXT). You do need to ensure that your connection characterset is the same as the blob character set, otherwise you could get incorrect characterset conversions.

Other options are to explicitly create a Clob (using Connection.createClob()) and set that on the statement, or to use the setCharacterStream method.

Upvotes: 1

kentcdodds
kentcdodds

Reputation: 29071

To convert a string to blob, I found this example:

//Convert String to Blob
String data = “hello world”;
java.sql.Blob blob = org.hibernate.Hibernate.createBlob(data.getBytes());

//Convert Blob to String
byte[] bdata = blob.getBytes(1, (int)blob.length());
String data1 = new String(bdata);

Take a look at this example setBlob for a prepared statement. Here's a piece. From their example it looks like you can call setBlob on a PreparedStatement

java.sql.Blob blob = null;
try {
  conn = getConnection();
  // prepare blob object from an existing binary column
  pstmt = conn.prepareStatement("select photo from my_pictures where id = ?");
  pstmt.setString(1, "0001");
  rs = pstmt.executeQuery();
  rs.next();
  blob = rs.getBlob(1);

  // prepare SQL query for inserting a new row using setBlob()
  String query = "insert into blob_table(id, blob_column) values(?, ?)";
  // begin transaction
  conn.setAutoCommit(false);

  pstmt = conn.prepareStatement(query);
  pstmt.setString(1, "0002");
  pstmt.setBlob(2, blob);

  int rowCount = pstmt.executeUpdate();
  System.out.println("rowCount=" + rowCount);
  // end transaction
  conn.commit();

Upvotes: 0

Related Questions