Using SA12 proxy table with oracle table and blob

[Former Member]

To synchronize a table with a blob column, I created a remote server against a Oracle 11 db and created a proxy table in SA12 against the table in Oracle.

Running sql in SA12, updates and inserts of blob values works from Oracle to Sybase

Example:

update table1 set blob_column = (select blob_column from proxy_table1 where id = 1) where id = 1;

However, from Sybase to Oracle I have issues with large blobs

update proxy_table1 set blob_column = (select blob_column from table1 where id = 1) where id = 1;

Do anyone have any experience with passing blob fields between SA12 and Oracle 11 ?

Errormessage: Update operation attempted on non-updatable remote query.

[VolkerBarth]

As you talk about "synchronization" - with SQL Anywhere, that usually means MobiLink (or SQL Remote) technology. But you're "just" refering to remote data access (aka proxy tables), right?

[Former Member]

Your assumption is spot on 🙂

[VolkerBarth · Accepted solution]

Cf. this FAQ dealing with the same error message and explaining its cause.

If you have to update the remote side (i.e. an insert won't work), then you might try to use a variable to hold the SA blob value and use that variable to update the remote side, such as (untested)

begin
  declare myBlob long varbinary;
  set myBlob = (select blob_column from table1 where id = 1);
  update proxy_table1 set blob_column = myBlob where id = 1;
end;

[Former Member · Accepted solution]

Thank you! Works like a charm!!