View previous topic :: View next topic |
Author |
Message |
Gryphyn
Joined: 20 Jan 2005 Posts: 431
|
Posted: Sun Aug 16, 2009 6:17 Post subject: |
|
|
Asparius wrote: | In multi-user environment maybe locking table for this query would prevent problems... |
All prior stuff has multi-user issues...including what I've written
when the select, delete & update are all separate SQLExec's there is the potential for things getting out-of-sync. phantom reads, missing entries etc.
Only by ensuring they are all in a single transaction, can you make sure of them (a stored procedure works) |
|
Back to top |
|
|
Baaleos
Joined: 02 Sep 2007 Posts: 830
|
Posted: Fri Aug 21, 2009 12:58 Post subject: Thx |
|
|
Sorry for the delay.
Managed to solve it as per your advice by making a Stored Proc.
Now, all nwn script has to do is
Code: |
string sSql = "CALL Renumber ()";
SQLDirectExecute(sSql); //or whatever the syntax is
|
Had some trouble writing the stored proc in phpmyadmin though.
Had to actually change the deliminator from ; symbol to @@ - apparently its a commonly known issue with Stored Proc's and phpmyadmin.
Thx for the help |
|
Back to top |
|
|
Gryphyn
Joined: 20 Jan 2005 Posts: 431
|
Posted: Fri Aug 21, 2009 13:21 Post subject: |
|
|
stored procs are the way to go...
In particular the common 'insert on error update' sequence can be done with a single NWScript request.
*'update, if affected rows = 0 insert' will usually get you better performance as no error/exception is caused. (simple for a stored proc) |
|
Back to top |
|
|
|