Problem with my multy-client program

[zapalm]

Hello

My program using 20 users at the same time and I got match errors. Many of that errors I don’t understand – like this “translate(0) …”. Can somebody advise me what I have to do with my problem? I think I have to do these things:
1) Before creating new record in table “A” I have to refresh this table and all tables that have relationship with this table.
2) Also I have to block table “A” when record is saving to avoid duplicated primary keys, but I don’t know how.
Can somebody advise me please?

[mdaems]

zapalm,

Your question isn't very clear.
But as far as I understand you seem to have trouble with duplicate primary keys.
My first idea would be : don't assign them yourself, use autoincrement fields. The you can just leave the field empty on insert and mysql assigns the next one available.
If you really want to assign a key yourself : get it from a service that's guaranteed to serve every values only once. (Different ways to implement this exist, like a counter table accessed by a function that increments and commits, ...) Don't start doing 'select ma(id)+1 from...'. It's terribly slow and requires locking for the complete transaction.

It's not clear if you are using data aware components to insert, or if you're just sending plain queries. Are you working with master-detail datasources in your program?

Mark

[zapalm]

Mark, thank you for the answer
yes, i have a problem with duplicating primary keys. i setting a new key value manually because i need to use this value later. when i using autoincrement field for generating a new value, than i don't known what is the new value, because in one time may be several attempts to create new record and the code (for example):

Code: Select all

tbBook.AppendRecord(...)
tbBook.last;
id:= tbBook.fieldbyname('id');

may be wrong, because in "id" i may got another value and if i will use this value later than i will get an error somewhere.

i have other big problems. i have much errors and i think that these errors rises because every client program don't have actually data. i think to solve these problems i need perform commit and refresh procedures every time when i work with tables that have relationships with another tables.

i using this code to append a new record:

Code: Select all

dm.tbBook.AppendRecord([...]);

to update a record i using this code:

Code: Select all

if tbBook.locate('id', 3) then
begin
tbBook.edit;
tbBook.FieldByName('id').AsInteger:= 1;
...
tbBook.post;
end;

i using TZQuery component to delete a record.

i have database schema with more then 30 tables with relations. i using only these zeos components: TZQuery, TZTable, TZConnection. i don't working with master-detail datasources.
i have mysql 5.5 and zeoslib 7.0

i need advises

[zapalm]

i setting a new key value manually because i need to use this value later. when i using autoincrement field for generating a new value, than i don't known what is the new value, because in one time may be several attempts to create new record

i am may be wrong...
is this code right for multy-client program? :

Code: Select all

tbBook.AppendRecord([
nil, // primary key 'id' of autoincrement generation value
...

]) ;
id:= tbBook.fieldbyname('id');

[zapalm]

i found a problem

Code: Select all

// when edit 
...
ZConnection.StartTransaction;
try
 dm.tbBook.Edit;
 dm.tbBook.FieldByName('id').AsInteger:= 10; // change key to 10, but this value already presents in the table and it will be rises an exception 
 ...
 dm.tbBook.Post;
 except on E: Exception do
 begin
 ZConnection.Rollback;
 end;
end;
ZConnection.Commit;

after this code will not work append or edit procedures, because will be rises the exception that appeared previously (duplicated keys)...
whats wrong with code?

[zapalm]

i suppose that my code is not right:

Code: Select all

// when edit 
 ... 
 ZConnection.StartTransaction; 
 try 
  dm.tbBook.Edit; 
  dm.tbBook.FieldByName('id').AsInteger:= 10; // change key to 10, but this value already presents in the table and it will be rises an exception 
  ... 
  dm.tbBook.Post; 
  except on E: Exception do 
  begin 
  ZConnection.Rollback; 
  end; 
 end; 
 ZConnection.Commit;

i think this code is right:

Code: Select all

 try 
  dm.tbBook.Edit; 
  dm.tbBook.FieldByName('id').AsInteger:= 10; // change key to 10, but this value already presents in the table and it will be rises an exception 
  ... 
  dm.tbBook.Post; 
  except on E: Exception do 
  begin 
  dm.tbBook.CancelUpdates; 
  end; 
 end; 
dm.tbBook.ApplyUpdates;
dm.tbBook.CommitUpdates;

it work fine, but i don't can say with confidence that this code will also work fine when my program will using many clients at the same time...

[mdaems]

is this code right for multy-client program? :

Code: Select all

tbBook.AppendRecord([
nil, // primary key 'id' of autoincrement generation value
...

]) ;
id:= tbBook.fieldbyname('id');

Well this code would probably be right, when you post this record to the database before getting the id back.
So, doing

Code: Select all

 tbBook.Post;

before

Code: Select all

id:= tbBook.fieldbyname('id');

Did you try that?
I used it before in one of my projects.
See http://maaltijd.svn.sourceforge.net/vie ... iew=markup at lines 580-585 (Sorry for the code in dutch )

An alternative is what I did here :
http://salco32.svn.sourceforge.net/view ... iew=markup -> lines 850-855.
There I use a separate query to get the last inserted id. This is safe as this method only retrieves the id's inserted by your own database session.

Mark