help - major problems after I changed CommitRetaining to Commit etc
Joe Sansalone
I also changed "FIBTransaction.Active := True" to
"FIBTransaction.StartTransaction"
because after making the first changed I got errors stating that an explicit
StartTransaction
was needed.
The code below is a sample of the way my code querys the database - whether
Select,
Update or Insert. I use the same IBSQL and IBTransaction object per thread
.. I simply
change the SQL statement to get what I need.
Anyone know why the code below would produce tons of errors like:
19 Transaction is active
-901 invalid database handle (no active connection)
etc. (help!)
begin
try
FIBSQL.SQL.Clear;
FIBSQL.SQL.Append('Select DimValue from DN where SpId = :spid and
CompanyID= :companyid ' + 'and UserID = :userID and dimName = :dimname and
DN= :dn' );
FIBTransaction.StartTransaction;
FIBSQL.ParamByName('spid').AsString := user.comp.SpID;
FIBSQL.ParamByName('companyid').AsString := user.comp.CoID;
FIBSQL.ParamByName('userid').AsString := user.UID;
FIBSQL.ParamByName('dimname').AsString := aDimName;
FIBSQL.ParamByName('dn').AsString := DN;
FIBSQL.ExecQuery;
FIBSQL.Close;
FIBTransaction.Commit;
except
on E:EIBError do
begin
// Log IB database error
end;
on E:Exception do
begin
// log general exception
end;
end; // try/except
end;
![Bill Todd [TeamB]'s profile photo](http://lh3.googleusercontent.com/a-/ALV-UjWCsgxmldb_86qScxWLohQRsXwmDfg_NwjwQAFKFefVLRVoLw=s40-c)
Bill Todd [TeamB]
Expxress components. You should post your questions in the FIB
newsgroup. If you want to post in a CodeGear newsgroup even though FIB
is not a CodeGear product post to the database.sqlservers group.
--
Bill Todd (TeamB)
Joe Sansalone
I didn't realize that there were FIB components.
I'm using TIBTransaction and TIBSQL component types.
"Bill Todd [TeamB]" <n...@no.com> wrote in message
news:4890...@newsgroups.borland.com...
Ömür Ölmez
then StartTransaction'
* Yo should not commit after a select statement. Instead, you can commit
before the select. committing before select is meaningless. Actually means,
committing previous select call.
So, you can simply use this code :
begin
with FIBTransaction do
if InTransaction then
Commit;
FIBQuery. . . .
FIBQuery.Exec; //here FIB.StartTransaction is called automatically if
it is not in transaction
end;
"Joe Sansalone" <j...@project1.ca> wrote in message
Joe Sansalone
"Ömür Ölmez" <om...@olmez.net> wrote in message
news:489075bd$1...@newsgroups.borland.com...
Craig Stuntz [TeamB]
The problem is that you are not explicitly, consistently managing
transaction state. Changing Transaction. Active := True to
Transaction.StartTransaction has only one effect: your application will
throw an exception if the transaction is already active. That is
generally what you want, because it catches cases where you have failed
to commit the transaction elsewhere, but it has no effect on the
"expecting explicit transaction start" error, because that error means
that you failed to start the transaction at all, via Active or
StartTransaction.
Change your code so that, in every case where you execute a query, you
explicitly start and commit the transaction, and, further, you protect
the call to Commit in a try/finally, this:
FIBTransaction.StartTransaction;
try
FIBSQL.ParamByName('spid').AsString := user.comp.SpID;
FIBSQL.ParamByName('companyid').AsString := user.comp.CoID;
FIBSQL.ParamByName('userid').AsString := user.UID;
FIBSQL.ParamByName('dimname').AsString := aDimName;
FIBSQL.ParamByName('dn').AsString := DN;
FIBSQL.ExecQuery;
FIBSQL.Close;
finally
FIBTransaction.Commit;
end;
This ensures that, even if the SQL statement raises an exception
(e.g., the user entered data which violates a constraint), but the
transaction will still be ended with a Commit. This prevents you from
accidentally leaving the transaction open, which should be your goal.
--
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
IB 6 versions prior to 6.0.1.6 are pre-release and may corrupt
your DBs! Open Edition users, get 6.0.1.6 from http://mers.com
Craig Stuntz [TeamB]
> * You can change the line 'StartTransaction' with code 'if not
> InTransaction then StartTransaction'
This is bad advice. It hides problems caused by improper transaction
control elsewhere. You should try to make your coding mistakes
obvious, not bury them.
* Yo should not commit after a select statement.
This is completely wrong. Committing is always the best way to end a
transaction when you do not need to rollback a successful statement.
Indeed, InterBase will convert a rollback to a commit whenever it can.
--
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Everything You Need to Know About InterBase Character Sets:
http://blogs.teamb.com/craigstuntz/articles/403.aspx
Bill Todd [TeamB]
> FIBTransaction is the actual object name not the type.
> I didn't realize that there were FIB components.
Coincidences do happen.<g> See my reply to your original post.
--
Bill Todd (TeamB)
Bill Todd [TeamB]
> I use the same IBSQL and IBTransaction object per thread
Are you saying that you use a single IBDatabase, IBSQL and
IBTransaction component for multiple threads?
--
Bill Todd (TeamB)
Craig Stuntz [TeamB]
> Thanks so much!
Do not take these suggestions; it's horrible advice. See my post
elsewhere.
--
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
How to ask questions the smart way:
http://www.catb.org/~esr/faqs/smart-questions.html
Joe Sansalone
where it failed correctly. I was going to put another Commit in the
exception section.
Joe
"Craig Stuntz [TeamB]" <craig_...@nospam.please [a.k.a. acm.org]> wrote
in message news:48907698$1...@newsgroups.borland.com...
Craig Stuntz [TeamB]
> I use one IBSQL and one IBTransaction per thread.
> And there are 2 database components that are global ... all
> Transactions share these 2 database connections.
In IB < 7.5, connections are not thread-safe. >= 7.5, connections are
thread-safe, but serialized = no concurrency. TIBDatabase may not be
thread-safe.
--
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Please read and follow Borland's rules for the user of their
server: http://support.borland.com/entry.jspa?externalID=293
Joe Sansalone
Do you think there's an issue with using Delphi 6 (IBX)?
Joe
"Craig Stuntz [TeamB]" <craig_...@nospam.please [a.k.a. acm.org]> wrote
in message news:48908350$1...@newsgroups.borland.com...
Joe Sansalone
And there are 2 database components that are global ... all Transactions
share these 2 database connections.
"Bill Todd [TeamB]" <n...@no.com> wrote in message
news:48907788$1...@newsgroups.borland.com...
Craig Stuntz [TeamB]
> Do you think there's an issue with using Delphi 6 (IBX)?
Probably. TIBDatabase was never designed to be thread-safe.
--
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Ömür Ölmez
> elsewhere.
>> * You can change the line 'StartTransaction' with code 'if not
>> InTransaction then StartTransaction'
>This is bad advice. It hides problems caused by improper transaction
>control elsewhere. You should try to make your coding mistakes
>obvious, not bury them.
But, in Sansalone's situation, it is a next step to develope program's
structe. He was in a trouble. When you are saying better program and better
bug election, he was saying his program does not work ! With the bad advice,
Sansalone can show a finished work to his boss and find a chance to develop
his programming skills. I am sure he can noticed some other details in time.
At least We both know he looks at newsgroups regularly.
>* Yo should not commit after a select statement.
>This is completely wrong. Committing is always the best way to end a
>transaction when you do not need to rollback a successful statement.
>Indeed, InterBase will convert a rollback to a commit whenever it can.
You should not commit after a select statement.
Is this true or false ? It depends. It depends on when ? Connect a dbgrid to
a datasource and also connect it to query, transaction and database. Okay
now, execute a select and then call Transaction.Commit. This is what I try
to avoid Sansalone to do. You will see an empty grid. You know why.
I also know what you mean when you say I have to commit a transaction. But
When the form is destroyed (IBTransaction is on form) or program terminates
(IBTransaction is on datamodule), it already Commits.
By the way, I want to thank to you and the other people in TeamB who helps
us. With your advices, your tones of answers, taking your hours, you are
doing really good job and a lot people take benefits from.
Ömür
Craig Stuntz [TeamB]
> But, in Sansalone's situation, it is a next step to develope
> program's structe. He was in a trouble. When you are saying better
> program and better bug election, he was saying his program does not
> work !
The correct solution to this problem is to manage transactions
correctly, not to defensively steer around bugs until you've produced
spaghetti which doesn't seem to raise an exception in *one* specific
use case.
> You should not commit after a select statement.
>
> Is this true or false ?
I said this before, and I will say it again: you should *always*
commit unless there is a successfully-executed statement you wish to
rollback. You should not rollback in any other case, and you should
always explicitly and a transaction that you start. Hence, you should
always commit after a SELECT unless, in the same transaction, you
successfully executed DML which you now wish to undo.
If, in a transaction, you do a SELECT, and only a SELECT, there is
*never* a reason to rollback.
> By the way, I want to thank to you and the other people in TeamB who
> helps us. With your advices, your tones of answers, taking your
> hours, you are doing really good job and a lot people take benefits
> from.
You're welcome.
--
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Want to help make Delphi and InterBase better? Use QC!
http://qc.borland.com -- Vote for important issues
Bill Todd [TeamB]
> You should not commit after a select statement.
>
> Is this true or false ? It depends. It depends on when ? Connect a
> dbgrid to a datasource and also connect it to query, transaction and
> database. Okay now, execute a select and then call
> Transaction.Commit. This is what I try to avoid Sansalone to do. You
> will see an empty grid. You know why.
>
> I also know what you mean when you say I have to commit a
> transaction. But When the form is destroyed (IBTransaction is on
> form) or program terminates (IBTransaction is on datamodule), it
> already Commits.
In addition to Craig's comments let me point out a possible problem
with your suggestion to commit when a form is destroyed or when the
program terminates. I single form may be open for hours. An application
may be open all day. Both the OIT and the OAT will be frozen at the
oldest active transaction. If the OIT does not advance garbage
collection stops. In addition, all transactions from the OIT up to the
next transaction must be tracked on the transaction inventory pages
(TIP). In a high transaction volume environment where a large number of
updates occur this can cause the size of the TIP to become very large
and can cause a large number of record versions to accumulate. The
worst case I have encountered on a production server was a table with
1,600 rows that had 863,000 versions. One row had ove 57,000 versions.
Needless to say, performance when querying this table was very poor.
The point is that you need to design your transaction handling so that
transactions will be committed often enough to prevent the problems
described above. How do you prevent the user from doing something that
starts a transaction and displays some records then walking away for
hours and leaving the transaction active? The easy way is to always use
ClientDataSets to display data in the U/I.
--
Bill Todd (TeamB)
Joe Sansalone
Joe
"Bill Todd [TeamB]" <n...@no.com> wrote in message
news:4890be06$1...@newsgroups.borland.com...
Joe Sansalone
another
connection recently changed but didn't commit? In other words, if it can't
Commit, should
I have something in the code that does a rollback?
Should I put all of the below code within a try/except and then rollback in
the case of
the Commit throwing an exception?
If the Commit fails (and no rollback) then StartTransaction will throw an
exception on the next call because there's still an active transaction,
right?
The code below is the template for a successful transaction? Was that the
assumption?
The reason I ask all this is that I did change my code BUT I got all sorts
of exceptions.
However, I was using Delphi 6 ... tonight I'll be testing the app with
D2007 - same code.
I wasn't sure if all the exceptions plus loss of db connection was because
of Delphi 6 ...
so to be safe, I'm going with D2007.
"Craig Stuntz [TeamB]" <craig_...@nospam.please [a.k.a. acm.org]> wrote
in message news:48907698$1...@newsgroups.borland.com...
Bill Todd [TeamB]
> What will the Commit within the finally do if the SQL updated
> something that another connection recently changed but didn't commit?
> In other words, if it can't Commit, should I have something in the
> code that does a rollback?
The commit in the finally block assumes that you are executing a SELECT
statement. If you are executing a statement that changes data then you
should call Rollback in the finally block.
>
> Should I put all of the below code within a try/except and then
> rollback in the case of the Commit throwing an exception?
Yes.
>
> If the Commit fails (and no rollback) then StartTransaction will
> throw an exception on the next call because there's still an active
> transaction, right?
Right.
>
> The code below is the template for a successful transaction? Was
> that the assumption?
Yes. The recipe is:
Start a transaction.
Execute some SQL
If the SQL fails and data may have been changed roll back.
If the SQL fails and data could not have been changed (i.e. the SQL was
a SELECT) commit.
If the SQL succeeds commit.
You must make sure that you end every transaction that you start.
>
> The reason I ask all this is that I did change my code BUT I got all
> sorts of exceptions. However, I was using Delphi 6 ... tonight I'll
> be testing the app with D2007 - same code. I wasn't sure if all the
> exceptions plus loss of db connection was because of Delphi 6 ... so
> to be safe, I'm going with D2007.
>
It is not likely that the Delphi version will make a difference.
--
Bill Todd (TeamB)
Joe Sansalone
I had a feeling rollback cases weren't taken care of.
I assumed the code I inherited was based on good assumptions. I was wrong!
Joe
"Bill Todd [TeamB]" <n...@no.com> wrote in message
news:48925754$1...@newsgroups.borland.com...
Joe Sansalone
my company.
The code you (Bill) and Craig gave me was obviously based on correct
assumptions
and a solid understanding of Interbase.
Thanks again!
"Joe Sansalone" <j...@project1.ca> wrote in message
news:4892765f$1...@newsgroups.borland.com...
Ömür Ölmez
Start a transaction.
Execute some SQL
If the SQL fails and data may have been changed roll back.
If the SQL fails and data could not have been changed (i.e. the SQL was a
SELECT) ROLL BACK
If the SQL succeeds commit.
I ask because it makes simplify things. Especially in a data operation which
has both select sqls and insert/update/delete sqls.
Ex:
start a transaction
try
select sql
insert/update/delete sql
commit transaction
except
rollback transaction //if select fails roll back
raise
end
Select sql does not change database. So what is the difference between
commit and roll back for select sql ?
Regards
Ömür Ölmez
"Bill Todd [TeamB]" <n...@no.com> wrote in message
news:48925754$1...@newsgroups.borland.com...
Jeff Overcash (TeamB)
> Can we also use this ?
>
> Start a transaction.
> Execute some SQL
> If the SQL fails and data may have been changed roll back.
> If the SQL fails and data could not have been changed (i.e. the SQL was a
> SELECT) ROLL BACK
> If the SQL succeeds commit.
>
> I ask because it makes simplify things. Especially in a data operation which
> has both select sqls and insert/update/delete sqls.
> Ex:
>
> start a transaction
> try
> select sql
> insert/update/delete sql
> commit transaction
> except
> rollback transaction //if select fails roll back
> raise
> end
>
> Select sql does not change database. So what is the difference between
> commit and roll back for select sql ?
Rollbacks are actually harder to do on the engine than a commit. Actually
whenever possible the engine actually determines if it is safe to turn a
rollback into a commit before actually doing a rollback. Also note that if you
only do one insert/update/delete statement and it fails, you can commit the
transaction because no changes were made to the database, you only need to
rollback when you are doing 2 or more and one of them succeeds but another one
fails.
--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)
Craig Stuntz [TeamB]
> If the SQL fails and data may have been changed roll back.
If a single statement fails (for example, an UPDATE fails because the
new values violate a constraint), then no data has been changed. You
should commit, not rollback.
If one statement succeeds and a second statement fails, and you need
both statements to succeed or fail as a group, then you should rollback.
> If the SQL fails and data could not have been changed (i.e. the SQL
> was a SELECT) ROLL BACK
No. In this case, you should commit.
--
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Joe Sansalone
called it could throw an exception, right?
Should we check if InTransaction before calling StartTransaction?
(especially since the StartTransaction is not within the try/except)
"Jeff Overcash (TeamB)" <jeffov...@mindspring.com> wrote in message
news:4892d2ac$1...@newsgroups.borland.com...
Joe Sansalone
even though
some SQL statements failed.
"Craig Stuntz [TeamB]" <craig_...@nospam.please [a.k.a. acm.org]> wrote
in message news:4892fbf2$1...@newsgroups.borland.com...
Craig Stuntz [TeamB]
> So Commit really means commit all successful changes of this
> transaction, even though some SQL statements failed.
Every statement that you run has an implicit savepoint. When a
statement fails, changes made by the partial execution of the statement
are rolled back to the implicit savepoint which was started when the
statement began. Hence, when a statement fails with an error, you can
commit, since there are no changes saved after the error has been
raised.
The implicit savepoint works just like the explicit savepoints that
you can write manually.
--
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Joe Sansalone
succeeds,
the second fails and the 3rd succeeds ... will a Commit after the 3rd
statement change
the data to reflect SQL statements 1 and 3?
"Craig Stuntz [TeamB]" <craig_...@nospam.please [a.k.a. acm.org]> wrote
in message news:489320f6$1...@newsgroups.borland.com...
Craig Stuntz [TeamB]
> If there are 3 different SQL updates within a transaction, and the
> first succeeds, the second fails and the 3rd succeeds ... will a
> Commit after the 3rd statement change the data to reflect SQL
> statements 1 and 3?
Yes.
--
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
All the great TeamB service you've come to expect plus (New!)
Irish Tin Whistle tips: http://learningtowhistle.blogspot.com
Bill Todd [TeamB]
> Should the Commit somehow fail, the next time StartTransaction is
> called it could throw an exception, right?
Since committing a transaction consists of changing one bit on the TIP
it is difficult to imagine how it could fail unless the server crashes
or the network connection is lost.
>
> Should we check if InTransaction before calling StartTransaction?
> (especially since the StartTransaction is not within the try/except)
It will not hurt anything.
--
Bill Todd (TeamB)
Joe Sansalone
connection)
It works with a CommitRetaining but not with a Commit! I have no idea why.
These are some of the errors in my log file (all Interbase exceptions):
35 unknown SQL Data type (0)
-501 invalid request handle
-901 connection lost to database
-501 invalid request handle
etc etc
-904 invalid database handle (no active connection)
etc etc
19 Transaction is active
Another time:
-901 invalid BLOB handle
-901 invalid transaction handle (expecting explicit transaction start)
35 unknown SQL Data type
19 Transaction is active
Do these errors suggest something to you? Any clue?
ALL the transactions follow this template:
try
setting up the statement using IBSQL
StartTransaction
try
IBSQL.ParamByName(...) setting up the values
IBSQL.ExecQuery
(get values etc)
IBSQL.Close
finally
Commit
end
except
on E:IBError do ....
on E:Exception do ....
end
Craig Stuntz [TeamB]
> try
> setting up the statement using IBSQL
> StartTransaction
Are you sure you have the right trans here?
Try:
MyIBSQL.Transaction.StartTransaction
--
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Joe Sansalone
with
the IBSQL.
Why would it work with CommitRetaining but not Commit?
I'll try what you said ....
"Craig Stuntz [TeamB]" <craig_...@nospam.please [a.k.a. acm.org]> wrote
in message news:48934a9c$1...@newsgroups.borland.com...
Craig Stuntz [TeamB]
> Why would it work with CommitRetaining but not Commit?
Because CommitRetaining leaves the transaction open. Hence, if you,
in one section of code, for get to it and a certain transaction, and
leave it open, you will not get an error when you use a query connected
to that transaction but have failed to explicitly start the transaction.
--
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Joe Sansalone
CommitRetaining was
changed to Commit.
There are some 60 threads, each with their own transaction and IBSQL object.
They share 2 database connections. The even threads share database1 and the
odd threads share database2.
BUT, I assign each database object a default transaction.
database1 gets the transaction object in thread 1
database2 gets the transaction object in thread 2
WRONG!
it should be database1 gets the even one (which is transaction 2) and
database2 gets the odd.
Because of that, there were major problems.
Craig pointed the way when he questioned whether IBSQL was using the right
transaction.
Merci - Thanks.
"Craig Stuntz [TeamB]" <craig_...@nospam.please [a.k.a. acm.org]> wrote
in message news:48934a9c$1...@newsgroups.borland.com...
Craig Stuntz [TeamB]
> Does a database object even need a default transaction assigned to it?
No, not necessarily. A transaction needs a default database, though.
--
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Joe Sansalone
"Craig Stuntz [TeamB]" <craig_...@nospam.please [a.k.a. acm.org]> wrote
in message news:489376aa$1...@newsgroups.borland.com...
> Joe Sansalone wrote:
>
>> Merci - Thanks.
>
> De rien.
>
> --
> Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
> Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
> Borland newsgroup denizen Sergio González has a new CD of
> Irish music out, and it's good: http://tinyurl.com/7hgfr
Craig Stuntz [TeamB]
> Merci - Thanks.
De rien.
--
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz