Delphi / SQL Server / Transactions & Table locking 1

[awarnica]

Hope someone can clarify something for me, here is my situation:

I am coding in Delphi an application that connects to a SQL Server database.

The application creates boxes on a production floor.

To get the next box number, I need to:

a-Retreive the last box number.
b-Increment this number, if it exceeds 6 digits (1,000,000), set it back to 10,000.
c-Save this new number back to the database.

The issue is that many users could be hitting this setup table and I need to lock the table while I do my incrementation, then release it after I write back to the database.

I am unsure of how to do this, it seems I will need multiple SQL statements.

The code as follows might clear up what I am trying to do, but it of course has no tablelocking / releasing.

strSQL := 'SELECT [Last Entry No] FROM WFSetup';
if odbcStmt.ExecDirect(strSQL) then begin
if odbcStmt.Fetch then begin
dTemp := odbcStmt.GetDataAsDouble('Last Entry No');
Result := dTemp;
dTemp := dTemp + 1;
if dTemp > 1000000 then
dTemp := 100000;
strSQL := 'UPDATE WFSetup' SET [Last Entry No] = ' + FloatToStr(dTemp);
odbcStmt2 := odbcConn.NewStatement;
if not odbcStmt2.ExecDirect(strSQL) then
...

I should point out that in my case SQL Server is being used as a back end for a Navision database, so I cannot use any SQL trigger functionality.

I know that I have to put in the locking somewhere but am unsure on how to do so.

Any help would be appreciated.

Andrew

 

[LucieLastic]

hi

In my database I have a table that contains the last ids of primary columns. So instead of doing a select MAx() which could take time, I just read the value for the table and update it. Even with this idea there is still risk of clashes but I think it's less because there isn't such a delay actually getting the last id

eg My id table comprises of:-

tablename lastID
COMPANY 2530
USER 35
etc

My function :-

function TBaseMain.GetNewID(TableName : string): integer;
begin
with TQuery.Create(self) do
begin
try
DatabaseName := ODBCds;
RequestLive := true;
SQL.Add('SELECT lastID FROM ID_Generator');
SQL.Add('WHERE TableName = '''+TableName+'''');
Open;
if IsEmpty then
Result := NO_ID
else
begin
try
Result := Fields[0].AsInteger;
Edit;
Inc(Result);
Fields[0].AsInteger := Result;
Post;
except
Result := NO_ID;
end;
end;
Close;
finally
Free;
end;//Try-Finally
end;//with
end;

Hope this helps
lou

 

[awarnica]

That does make sense, that is what I'm doing currently, but I am worried about the gap. The customer has a slow network and I am trying to assure no primary key duplication.

I was hoping to find a way to maybe combine this all into one SQL statement, like a select / increment / modify all in one maybe? Or one statement to lock, another to select, another to modify and a final one to release the lock (via a commit i would guess)?

 

[LucieLastic]

hi, Sorry, yes I have repeated your message really, I was a bit blinkered yesterday.

Anyway, you may be able to do your select and insert as a cursor or Stored Procedure but it's beyond me. May I suggest you put your sql request on the SQL Server forum and someone there should be able to give you a solution.

cheers
lou

 

[awarnica]

I am now working with a SQL statement and code as follows:

strSQL := 'UPDATE [WFSetup]' +
'SET [LastNo] = ' +
'(SELECT [LastNo] AS dLastNo ' +
'FROM [WFSetup]) + 1';

if odbcStmt.ExecDirect(strSQL) then
begin
if odbcStmt.Fetch then //<---
begin
dTemp := odbcStmt.GetDataAsDouble('dLastNo');

I error out on the fetch, the statement has executed so I cannot fetch as I would normally. If the fetch is removed I don't have access to the dLastNo variable.

BTW the update does execute and increment, I just cant seem to retrieve the variable.

Thanks in advance anyone!

Andrew

 

[Zathras]

Here is the general procedure I prefer (in pseudocode):

Use a table of next numbers (NEXTNUMBERS):
At least two columns: ID and NEXTNUM

Retrieve the NEXTNUM for a given ID

Increment the NEXTNUM in code.

UPDATE NEXTNUMBERS SET NEXTNUM=NEXTNUM+1
 WHERE ID= [CurrentID] AND NEXTNUM= [Original NEXTNUM]

If the update fails, then someone else got there before you -- try again.

 

[awarnica]

Thanks to all, I fooled around and found a SQL statement that selects and updates all in one:

strSQL := 'SELECT [LastNo] as dLastNo ' +
'FROM [WFSetup] ' +
'UPDATE [WFSetup] ' +
'SET [LastNo] = ' +
'(SELECT [LastNo] ' +
'FROM [WFSetup]) + 1';

if odbcStmt.ExecDirect(strSQL) then
begin
if odbcStmt.Fetch then
begin
dTemp := odbcStmt.GetDataAsDouble('dLastNo');


And even better than that I found it on Friday morning, so I don't have to ponder it all weekend!

I appreciate the help everybody.

Andrew

 

[LucieLastic]

hi awarnica

What an excellent solution! I've nicked it, hope that's ok

lou

 

[awarnica]

Yea for sure, thanks for the ideas.

&quot;If it is stupid but works, then it isn't stupid&quot;