1. Jeff Wayt
  2. PowerBuilder
  3. Monday, 1 July 2019 22:54 PM UTC

How do I update a blob in an Oracle table column dynamically?

I know about UPDATEBLOB in PowerBuilder imbedded statements, like

   UPDATEBLOB “tablename” set “blob_column_name” = :lblob;

This script uses SQLSA because the column name is known only at runtime. There is no UpdateBLOB statement in Oracle’s PL/SQL. So the statement becomes:

        ls_sql = "UPDATE tablename set " + ls_column_name + " = ?, update_timestamp = ? where header_id = ?";
        PREPARE SQLSA FROM :ls_sql;
        EXECUTE SQLSA USING :lb_data,:ldt_update,:al_header_id;

Trying to pass a row data to a string, convert to a blob, and pass as an argument-- it is seeing those tab characters and throwing the invalid hex value error. I suspect this worked in PB 12.6. I'd rather not use a CASE statement for each blob column.

Any ideas?

Miguel Leeuwe Accepted Answer Pending Moderation
  1. Tuesday, 2 July 2019 08:56 AM UTC
  2. PowerBuilder
  3. # 1

What I would try is the following:

Before calling the dynamic sql, insert your blob data value in a temporary fixed table (i.e. TEMP_BLOB) with a unique id, using first insert and then UpdateBlob.

Then modify your dynamic sql to NOT use :lb_data, but instead get the blob value from the TEMP_BLOB table.

In that case, maybe it would be even easier if you construct your sql statement and use EXECUTE IMMEDIATE.

HTH

Comment
  1. Chris Pollach @Appeon
  2. Tuesday, 2 July 2019 17:46 PM UTC
Hi Jeff;

I would concur with Miguel and suggest using the "EXECUTE IMMEDIATE" approach.

Regards ... Chris
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.