TSQLQuery RowsAffected not working with Blobs (on OraDirect)

gb · Post by gb » Mon 13 Apr 2009 11:51

I'm using

  qr->SQL->Text = "UPDATE SomeTable SET SomeBinary=empty_blob() WHERE SomeKey=2 "
    "RETURNING SomeBinary INTO :SomeBinary";

where such record (SomeKey==2) does not exist on the table.

The issue is that after calling qr->ExecSQL(), qr->RowsAffected is equal to 1 instead of 0, therefore it fails to flag a miss update.

Is there any workaround?

Thank you.

[Edit: Update of topic description about OraDirect and Blobs)

Plash · Post by **Plash** » Tue 14 Apr 2009 06:51

Please specify whether this problem occurs only with the Direct driver. There is a known problem with RowsAffected in the Direct mode.

gb · Post by gb » Tue 14 Apr 2009 07:47

Yes, you're right. It was using ORADirect. Is there a workaround?

Plash · Post by **Plash** » Wed 15 Apr 2009 07:07

Currently we don't know a way for getting the correct RowsAffected value for such query. The only workaround is using Oracle client with the DevartOracle driver.

gb · Post by gb » Wed 15 Apr 2009 09:12

Plash wrote:Currently we don't know a way for getting the correct RowsAffected value for such query. The only workaround is using Oracle client with the DevartOracle driver.

It's unfortunate to hear that, but I hope that a fix may still come.

OraDirect is one of the main selling points of this driver. Zero (client database drivers) deployment, is a dream to any installer.

Another remarkable feature was (with Source) to embed it on the EXE, which does not work quite straight with C++ (but to be fair, can work after some hacking).

Besides this two issues, DevArt dbx driver is quite good and atm provides the *only* workaround to the AsSQLTimeStamp codegear issue.

Regards.

PS: Some background info on the reason of *this* issue. 99% of the times, some blob record will be updated, the other 1% is only the first time when there isn't any yet. Therefore, to cut on the roundtrip to check if it exists before an update, RowsAffected provided a pretty nice way to only trigger an "additional" insert when required (update failed).

gb · Post by gb » Thu 23 Apr 2009 10:11

Hi!

Would it be possible to let me know, if this issue is still being researched for a possible fix? True, only a week has gone but maybe (hopefully) there is some news regarding a fix expectation.

From the reply "Currently we don't know (...)" I couldn't infer if it was flagged as something not fixable (in any time soon) and therefore no point in waiting any longer to start patching and rewriting over one hundred places that relied on the expected and proper behavior of RowsAffected on a miss update.

Regards,

gb

Plash · Post by **Plash** » Fri 24 Apr 2009 06:46

We have stoped to search for a fix. So this bug will not be fixed in the nearest future.

gb · Post by gb » Fri 24 Apr 2009 08:56

Thank you for the reply.

Adding a note on the documentation (readme?) about such shortcoming on OraDirect mode may turn to be quite useful for unaware users. That much can be done.

Regards,

gb

Plash · Post by **Plash** » Tue 05 May 2009 10:53

We'll add this information to the ReadMe.html file.