select failure

From: Jim Poe <jpoe_at_fulcrumit.com>
Date: 03 Aug 2001 22:20:40 GMT
Message-ID: <9kf83o$9ml@dispatch.concentric.net>

Oracle 8.1.7, D5, DOA components, MIDAS server

I have two datasets that are used to select and update two tables. These tables have primary keys that are populated with sequences on the before insert triggers. Because I am using MIDAS, I have an event AfterUpdateRecord. I am using this event to retrieve the primary keys of the just inserted record and update the record. The changes are then propagated down to the client.

My problem is that this works on one dataset, but not the other. I use separate query components to retrieve the primary keys. I assumed that the insert and query were taking place in the same transaction and so the insert would be visible from the query.

These are the results from the monitor:

• First Insert ***
  "01:06.996","00:00.001","Query.Execute (1 record processed) insert into
  ""FIT_DBA"".""RB_ITEM"" (""FOLDER_ID"", ""ITEM_NAME"", ""ITEM_SIZE"",
  ""ITEM_TYPE"", ""MODIFIED"", ""TEMPLATE"") values (:V0, :V1, :V2, :V3,
  :V4, EMPTY_BLOB()) RETURNING TEMPLATE INTO :V5 "

"01:06.996","00:00.010","LOB.Write(1313) "

• Successful query on first insert ***

"01:20.806","00:00.001","Query.Execute (1 record processed) select item_id
from rb_item where modified = :modified "
"01:27.295","00:00.001","Session.Commit "

• Second Insert - different table ***
  "01:40.444","00:00.001","Query.Execute (1 record processed) insert into
  ""FIT_DBA"".""RB_FOLDER"" (""FOLDER_NAME"", ""PARENT_ID"") values
  (:V0, :V1) "
• Failed query on second insert ***
  "01:47.975","00:00.001","Query.Execute (0 records processed) select
  folder_id from rb_folder where folder_name = :folder_name "

"02:12.440","00:00.001","Session.Commit "

The queries and parameters are correct.

Does the fact that the first insert is doing a LOB.Write have anything to do with the success of that query?

Thanks for any help.

--
Jim Poe (jpoe_at_fulcrumit.com)

--
Jim Poe (jpoe_at_fulcrumit.com)

Received on Fri Aug 03 2001 - 17:20:40 CDT