| Subject | Re: AW: AW: [firebird-support] difficult question |
|---|---|
| Author | Tupy... nambá |
| Post date | 2013-12-06T16:07:25Z |
Olaf,
I glaube ich have eine Ahnhung von was passiert.
Wenn man Access brauch, wenn du bewegst von eine Linie (record) zu eine
andere, Access macht sofort commit. Das heisst, wenn du clickst an eine Linie, die letze wo der Focus früher war wir sofort commited. Dan, man verliert der Kontrol der Sequenz wie die Id´s generiert werden. Dan, wen der haupt Form wieder geladen wird, der Data in der Subform werden geladet als sie commited wurden.
Mach ein paar Versuchen mit Insert, wo du in der Subform clicks geben wird, und versuch zu alles schliessen (als du normalerweise machst). Und dann ladet die letzten eingefügten Daten, und sag mir wie die Daten
in der Subform geordnet sind, ob als du wünscht oder als du früher clicks gegeben hast.
Viel erfolgt,
Roberto.
(entschuldigung dass Ich ein paar Fehler auf Deutsch mache...)
On Friday, December 6, 2013 8:44 AM, checkmail <check_mail@...> wrote:
..sorry, Roberto of course
One thing again, firebird realized in every case the ID-Generation, main-table at the time select max(nr) – will I change – and in detail-table with a generator.
Hello Robert,
yes, I use Microsoft Access and I have test it with different versions of it. The tables are linked over odbc (Firebird ODBC 32 Bit, only select dialect 3, quoted identifiers and safe thread) and some commands (not used in this case /forms) realized with ado-db.
I have separately forms, a main and a subform. In both there is a standard recordsource to the linked tables. I don’t start the transactions manually, odbc is my friend.
Thanks.
In German Language:
Hallo Robert,
ich verwende ebenfalls Microsoft Access und habe von 2002 bis 2013 alle Versionen getestet, in allen Versionen konnte ich das Problem beobachten. Leider lässt es sich nicht dingfest machen, nur so viel weiß ich, dass es vor allem dann auftritt, wenn im vorherigen Datensatz der selbe Kunde ausgewählt wurde. Dann passiert es, dass die soeben eingetragenen Detaildatensätze auf einmal Zuwachs bekommen von eben dem vorherigen Vorgang. Schließt man das Formular und öffnet es erneut, findet man im
letzten Datensatz keine Detaildatensätze im Unterformular mehr vor, statt dessen sind alle im vorherigen Vorgang abgelegt, die original eingetragenen und auch die vom letzten Vorgang. Das nervt natürlich die Angestellten, auch wenn es nur einige Male in der Woche vorkommt. Formular und Unterformular sind ganz normal angebunden, einfache Datenquelle, Tabellenabfrage. Die Tabellen sind alle verknüpft – zu Firebird. Auch dort versuchte ich schon diverse Versionen, das brachte jedoch auch nichts. Selbst den Treiber tauschte ich schon, keine Veränderung.
Die genaue Vorgehensweise:
Im Hauptformular wird ein Kunde ausgewählt, einige andere Parameter ebenfalls. Zu dem Zeitpunkt steht die Vergabe der fortlaufenden Nummer noch aus. Sobald man z. B. in das Unterformular reinklickt, wird die Nummer übertragen und
im Formular angezeigt. Derzeitig eben noch in Firebird über einen Trigger vergeben, select max(nr).. Das kann ich noch mit einem Generator realisieren, kein Problem.
Im Unterformular wählt man dann einige Artikel aus, Mengen usw. Und nun öffnet der Bearbeiter meist den Bericht um ihn auszudrucken. Sobald die Daten refresht werden, sieht man das Theater dass eben Positionen im UFO vorhanden sind die dort gar nicht ausgewählt waren. Löscht man diese jetzt beispielsweise heraus, verschwinden sie auch aus dem vorherigen Vorgang. Wie schon erwähnt, auch wenn nach dem Datensatz von Kunde A ein anderer eingefügt wurde, dennoch wird ein Vorgang in den neuen verschmolzen in welchem der Kunde übereinstimmt.
Danke für deine Hilfe, viele
Grüße nach Brasilien.
Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Im Auftrag von Tupy... nambá
Gesendet: Freitag, 6. Dezember 2013 12:05
An: firebird-support@yahoogroups.com
Betreff: Re: AW: [firebird-support] difficult question
Gesendet: Freitag, 6. Dezember 2013 12:05
An: firebird-support@yahoogroups.com
Betreff: Re: AW: [firebird-support] difficult question
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
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
>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 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.
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
>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 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.
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