Locking Tables

[tcot]

sql.add('insert into PSIRA.LAB_DETAIL ');
sql.add('(SAMPLE_NBR, PROPERTY ) values ');
sql.add('('''+sampNbr+''',''Visual'') ');
query1.Execute ;

end; // if not EOF
end; // with Query1
// Add commit code here
with query1 do begin
close;
sql.clear;
sql.add('Commit');
end;
query1.Execute;

Why EXACTLY do I need to do the commit? I was using the DOA3.2 with Delphi 3 and their seemed to be NO problem. We upgraded from Oracle 7.3 to Oracle 8.05, installed the new DOA3.3 and I had to add the code about the commit to get things to work..... I am missing the boat on what is going on with the inserts/commits/ etc.... can you please help me understand?

Or maybe what I did will not help my locking problems!!!

Please help me get a clue!

------------------
Lisa Cottrell

 

[tcot]

Since I've sent this message, it seems the commit did NOT fix the locking issue! Any ideas why the table is locked until the user closes the program? The user must close the whole Delphi program.... not just the 1 screen!

 

[tcot]

Again...... all queries have been closed, when does the actual "post" happen? HELP!!!!

 

[Marco Kalter]

If you didn't need to commit in version 3.2, then you probably had set the TOracleSession.AutoCommit to True. This property has been removed in 3.3 (though you can still set it at run time, but I wouldn't advise it).

Anyway, if you commit your transaction, then there can be no more locks for this session. Instead of executing a commit statement in a query, you can alternatively use TOracleSession.Commit. If you are still experiencing locks, then there must be something more to the problem than just this piece of code, either through additional sessions or additional update queries.

------------------
Marco Kalter
Allround Automations

 

[tcot]

Marco,
This system has thousands of updates and inserts (along with deletes!) If I was to open all programs(screen code) I could still be doing this in September! Is there anything else I can do?

 

[Marco Kalter]

Sure, you can still set the AutoCommit property to True at run-time.

------------------
Marco Kalter
Allround Automations

 

[tcot]

How would you set it at run time?

 

[Marco Kalter]

Somewhere in your code (MainForm or DataModule.OnCreate sounds like a good place) you can write:

OracleSession.AutoCommit := True;

------------------
Marco Kalter
Allround Automations