Board index » cppbuilder » Oracle LOBs, Transactions, and TQuery. Ugh!!!!

Oracle LOBs, Transactions, and TQuery. Ugh!!!!


2004-01-23 07:15:06 AM
cppbuilder51
Hi everyone,
I am running C++ Builder 5.0 with BDE 5.01. My application contains a form with:
TDBGrid ->TDataSource ->TQuery ->BDE ->Oracle 8 table with five CLOB fields
A TDBNavigator component on the form allows user control over the insert/update/delete actions for the TDBGrid.
Borland specifies Oracle 8 LOBs must be manipulated within a transaction using TDatabase methods StartTransaction() and Commit() (bdn.borland.com/article/0,1410,19235,00.html).
Is setting BDE configuration "SQLPASSTHRU MODE" to "SHARED NOAUTOCOMMIT" a necessary step if I wish to use TDBNavigator with TQuery? If I put a call to method TDatabase.StartTransaction() in TQuery's "BeforePost" event handler, I get an error similar to, "already in transaction." :-( What am I doing wrong? It's a single table query with no bind variables, RequestLive == true, and CachedUpdates == true.
Also, Oracle suggests that this is the proper way to insert/update a CLOB value in the database:
INSERT INTO <tablename>( blobfieldname )
VALUES (empty_clob())
RETURNING blobfieldname
INTO :bindvariable;
This creates the CLOB locator and returns it into a local bind variable. To insert/update, the programmer then writes data to variable "bindvariable" and issues a Commit(). Would someone please let me know if this is the proper way to handle internal LOBs programmatically in C++ Builder? Any pitfalls I should watch out for?
Thanks for any help.
Sincerely,
Kevin
 
 

Re:Oracle LOBs, Transactions, and TQuery. Ugh!!!!

Success at last! :-) Rather than struggling through the long re-compiles, I created a test harness to experiment with the Oracle 8i LOB/BDE interactions and solved my problem:
Oracle 8i LOBs and BDE 5.0
Create:
TDBGrid
TDBNavigator
TDataSource
TQuery
TDatabase
TUpdateSQL
It goes like this:
TDBNavigator \
TDataSource ->TQuery ->TDatabase
TDBGrid /
Use the TQuery "Fields Editor" to add all the fields. Select all the CLOB fields and make them type "ftMemo."
Use the TwwDBGrid "Fields Editor" to assign TwwDBEdit components to the underlying CLOB fields.
Assign the TUpdateSQL to the TQuery, and then set TQuery CachedUpdates, RequestLive, and Active fields to "true."
Use the "TUpdateSQL Editor" to generate the Insert/Update/Delete Queries for the TUpdateSQL component.
The final step is to create TQuery "AfterPost" and "AfterDelete" event handlers to apply the cached updates to the database.
E.g.:
void __fastcall TMainForm::ApplyCachedUpdates(TDataSet *DataSet)
{
Database1->StartTransaction();
try
{
Query1->ApplyUpdates();
Database1->Commit(); // on success, commit the changes;
}
catch (...)
{
Database1->Rollback(); // on failure, undo the changes
throw; // throw the exception to prevent a call to CommitUpdates!
}
Query1->CommitUpdates();
}
void __fastcall TMainForm::Query1AfterPost(TDataSet *DataSet)
{
ApplyCachedUpdates(DataSet);
}
void __fastcall TMainForm::Query1AfterDelete(TDataSet *DataSet)
{
ApplyCachedUpdates(DataSet);
}
Note: Don't bother with the TDatabase->ApplyUpdate(...) method. It's more trouble than it's worth.
I believe this to be the best way to handle Oracle CLOBS.
Sincerely,
Kevin