| Subject | Re: AW: [firebird-support] difficult question |
|---|---|
| Author | Tupy... nambá |
| Post date | 2013-12-06T11:05:26Z |
Olaf,
Du sagst, I habe ein Form und ein SubForm !!!
May I ask you wich development enviroment are you using for the front-end application ? I see this kind of application building as having been built with MsAccess !!! Because I work with VB, C# and Delphi, and I found this only with the Office family !
If you have separated forms (sub
forms included) for both - parent and child table -, I see that you probably don´t have a only one transaction for both sql commands.... do you use explicit transaction in your application ?....
After you comment so:
>>2. Have you the same transaction for master detail
>Both tables are linked to the firebird database over the firebird odbc driver. After I insert the master
>record and klick into the detail-form, I get the generated Nr back from firebird (before I insert a detail
>data set). I’m sorry, I don’t know how the driver works in detail.
>Both tables are linked to the firebird database over the firebird odbc driver. After I insert the master
>record and klick into the detail-form, I get the generated Nr back from firebird (before I insert a detail
>data set). I’m sorry, I don’t know how the driver works in detail.
... you don´t talk explicitly something about this.
Making this sequence of operations doesn´t mean you have a common transaction. If you don´t explicitly start a transaction, you will not have a transaction for both commands.
Roberto Camargo,
aus Brasilien
On Friday, December 6, 2013 7:45 AM, checkmail <check_mail@...> wrote:
Hello @ll,
Today, I still could not reproduce the error again. What I can say, that I’m the only one in my test environment who creates records and the error also occurred there.
I can try to create a generator instead of the max-statement and I will see.
Of course, several people work at the same time (customer) in
the database, but locally I’m the only one and the problem already exists. I don’t know what exactly the error provoked. Currently I have create 100 records and every works. I’m confused.
Thanks a lot.
Best regards
Olaf
Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Im Auftrag von Svein Erling Tysvær
Gesendet: Donnerstag, 5. Dezember 2013 11:42
An: firebird-support@yahoogroups.com
Betreff: RE: [firebird-support] difficult question
Gesendet: Donnerstag, 5. Dezember 2013 11:42
An: firebird-support@yahoogroups.com
Betreff: RE: [firebird-support] difficult question
>>>I have a table with orders and a second table with orderpositions. The composite primary key from orders
>>>consists of jahr and nr, the orderpositions references with a foreign key to this table and its primary
>>>key is a continual ID.
>
>>>In my Frontend there is a form with the orders and a sub-form with the positions – the data-side of both
>>>forms is connected over jahr and nr. Now I can create a order, in main-form I select a supplier and
>>>select some articles in my sub-form. Ideally – it works fine. But in some case (notably if in the order
>>>before it is the same supplier) I select one or more parts to order and If the form will refresh, the
>>>order-positions from the order before are in the actually order! If I close the form and reopen it again,
>>>the last orderpositions and in the order before there are the from the last order. The same situation I
>>>have with a different odbc-driver and some other versions of Access. I’m confused and the problem exists
>>>for some years! One from 50 orders goes wrong in this way, the same problem exists in some other areas
>
>>1. How you generate pk keys
>Master: In this case I generate it with select max(nr)+1 from xxx where new.jahr… I know, not the best way.
>Detail: NEW.ID = GEN_ID(TBESTPOS_ID_GEN, 1); (generator)
>
>>2. Have you the same transaction for master detail
>Both tables are linked to the firebird database over the firebird odbc driver. After I insert the master
If this is the reason, the simple way to fix things would be to start using one or more generators for the master (you may have a separate generator for each jahr, or reset the generator each jahr if jahr is always the current year).
HTH,
Set
>>>consists of jahr and nr, the orderpositions references with a foreign key to this table and its primary
>>>key is a continual ID.
>
>>>In my Frontend there is a form with the orders and a sub-form with the positions – the data-side of both
>>>forms is connected over jahr and nr. Now I can create a order, in main-form I select a supplier and
>>>select some articles in my sub-form. Ideally – it works fine. But in some case (notably if in the order
>>>before it is the same supplier) I select one or more parts to order and If the form will refresh, the
>>>order-positions from the order before are in the actually order! If I close the form and reopen it again,
>>>the last orderpositions and in the order before there are the from the last order. The same situation I
>>>have with a different odbc-driver and some other versions of Access. I’m confused and the problem exists
>>>for some years! One from 50 orders goes wrong in this way, the same problem exists in some other areas
>
>>1. How you generate pk keys
>Master: In this case I generate it with select max(nr)+1 from xxx where new.jahr… I know, not the best way.
>Detail: NEW.ID = GEN_ID(TBESTPOS_ID_GEN, 1); (generator)
>
>>2. Have you the same transaction for master detail
>Both tables are linked to the firebird database over the firebird odbc driver. After I insert the master
for the master and succeed for the transaction with the detail records. Normally, this second user should get an error message, but it is of course possible to suppress that in your program. Hence you may end up with one master with ID 51 and detail 101, 102, 103, 104 and 105, whereas the other master isn't inserted at all.
>record and klick into the detail-form, I get the generated Nr back from firebird (before I insert a detail
>data set). I’m sorry, I don’t know how the driver works in detail.
Transactions are vital in Firebird, I don't use ODBC with Firebird and don't know how to control transactions in your environment. What I think can be a possible cause, is if the master and detail query are in separate transactions. Then I can imagine one user inserting a new master with, say, ID 51, and a few detail records (say ID 101, 102, 103). Then another user (before the transaction of the other master is committed) tries to insert another master, but since the other master isn't committed yet, this ID also is 51. This user also insert detail records (say ID 104, 105). Upon committing, the first user will succeed with his inserts, whereas the other user will get a lock conflict for the transaction
If this is the reason, the simple way to fix things would be to start using one or more generators for the master (you may have a separate generator for each jahr, or reset the generator each jahr if jahr is always the current year).
HTH,
Set