| Subject | Re: [IBO] "with lock" SQL function |
|---|---|
| Author | Helen Borrie |
| Post date | 2008-11-28T00:19:11Z |
At 09:37 AM 28/11/2008, you wrote:
The other way it is silly is in throwing a static SQL statement at it. Set the KeyLinks and provide the SQL statement once and only once, with parameters, viz. Select * from TheActualTableName where TheActualPKColumn = :theDesiredPK. In its BeforeOpen, assign the value of the PK from the other dataset to the parameter.
PessimisticLocking is a property of a DATASET, not a transaction. If you make a dataset ReadOnly, what do you hope to achieve by setting its PessLocking to true?
On the read/write dataset, PessLocking is not certain to work if the KeyLinks are missing or wrong. That routine has to be able to target the exact record and do 'UPDATE THIS_TABLE SET PK=PK WHERE PK=<The current PK value>. In other words, don't rely on the exact record being found via the DB_Key.
All the same, I'd want to open the DFM in text mode and see whether there are any BDE artifacts lurking in there, like a TDatabase, or a TIBODatabase that was substituted in at conversion time and then overlooked.
Check all the statement objects to be certain that all are pointing to the correct IB_Connection and the default transaction. As long as you are certain that the IB_Connection and the IBOTransaction *are* the combo that is actually being used by the application, you should be able to see the IBOTransaction in the statement object's IB_Transaction property. You could then select it; but if it is indeed the default transaction then, next time you look, it will display as <default>.
Helen
>Hi Helen--Hmmm....What does "didn't work" mean? What happens?
>
>As always, thanks for the prompt reply!
>
>It didn't work.
>I'll supply a few more details:This is at least TWO WAYS SILLY. WITH LOCK is OK if you are just operating on a set of one and only one record. But don't use both PessimisticLocking and WITH LOCK. Use one *or* the other. If you use WITH LOCK, you defeat IBO's PessimisticLocking mechanism, anyway.
>
>DataModule has 2 persistent queries. 1 is for the read-only display
>for the grid, another is what I use to put the user into "edit" mode.
>
>The read-only query is straight ahead..."select blahblahblah".
>
>The edit-query is setup like this:
>
> dmMain.dbInterbase.DefaultTransaction.StartTransaction;
> qe.active := False;
> qe.SQL.clear;
> qe.SQL.add('Select T.* from '+sTableName + ' T');
> qe.SQL.add('where T.UniqueKey ='+quotedstr(sUK));
>//v5.02K to prevent multiple concurrent edit to same record
>//v5.02K did work? qe.SQL.add('with lock');
> qe.Active := True;
> qe.Edit;
The other way it is silly is in throwing a static SQL statement at it. Set the KeyLinks and provide the SQL statement once and only once, with parameters, viz. Select * from TheActualTableName where TheActualPKColumn = :theDesiredPK. In its BeforeOpen, assign the value of the PK from the other dataset to the parameter.
>In the properties in dmMain, I set .PessimisticLocking := TrueTotal confusion, in other words. A transaction cannot be both ReadOnly and ReadWrite at the same time. RequestLive makes no sense for a read-only dataset.
>and .RequestLive := True for both (although the grid display query
>never puts it into edit mode).
PessimisticLocking is a property of a DATASET, not a transaction. If you make a dataset ReadOnly, what do you hope to achieve by setting its PessLocking to true?
On the read/write dataset, PessLocking is not certain to work if the KeyLinks are missing or wrong. That routine has to be able to target the exact record and do 'UPDATE THIS_TABLE SET PK=PK WHERE PK=<The current PK value>. In other words, don't rely on the exact record being found via the DB_Key.
>If 1 FB application clicks "edit" so the above query puts the recordTwo different connections from two users cannot share a common transaction context. An IB_Transaction instance is owned by the connection.
>into edit mode, the other user cannot resolve a display query using
>the grid-display tIBOQuery. If i execute the "select blahblahblah"
>for display only and .open it, I get the same "lock conflict on no
>wait transaction deadlock".
>
>Both have their .IB_Transaction = (blank) (if i click it it
>changes to "<default>", they seem to be the same?)
>There is a tIBOTransaction on the dmMain. .AutoCommit =It is a valid model, if you do it right.
>True; .ReadOnly = False; .Isolation = tiCommitted.
>Rest I believe are the default properties.
>
>I also have an tIB_Session and tIB_Connection on the dmMain.
>
>tIB.Connection.DefaultTransaction = IBOTransaction (ie. the
>transaction component on dmMain). The rest of the properties look
>default / standard.
>
>This is a converted BDE application and it's a very old application
>so I cannot change the way there are 2 queries...1 for edit, 1 for
>display (if that matters).
All the same, I'd want to open the DFM in text mode and see whether there are any BDE artifacts lurking in there, like a TDatabase, or a TIBODatabase that was substituted in at conversion time and then overlooked.
Check all the statement objects to be certain that all are pointing to the correct IB_Connection and the default transaction. As long as you are certain that the IB_Connection and the IBOTransaction *are* the combo that is actually being used by the application, you should be able to see the IBOTransaction in the statement object's IB_Transaction property. You could then select it; but if it is indeed the default transaction then, next time you look, it will display as <default>.
>help please.Dunno what further clues I could give. I'm fairly confused by the various snippets of problem description here. Maybe it's the weather.
Helen