Mega Code Archive
No ,Youre not crazy Sql Server does not have generators
<![CDATA[
Title: No ,Youre not crazy Sql Server does not have generators.
Question: Used with Oracle or Interbase.
Just forget Microsoft Sql Server does not have generators.
Answer:
Hi,
If youre used with Oracle or Interbase and suddenly based on the first
Article of the Murphy law one customer of yours come and say.
I want this system to run in a Microsoft Sql Server.
First of all i expect you not to be using IBX or other architecture that just
Dont let you make the move(This week i convert a whole system From Interbase
Into SQL Server,for my happiness im using DbExpress ,based on the articles of guys
Like Cary Jensen,Bill Tod,Dr.Bob,Eric Harmon and others.
Second if you want to use DbExpress for Oracle or M$ SQL Server,just forget
D7 DbExpress original drivers,They have very annoying bugs at this moment, and
Are much slower than Core labsones.
But coming back to the main subject of this article.
Sql server does not have Generators(Interbase) or Sequences(Oracle) instead of that
They have of they call Identity Columns a kind of autoincrement fields.
I myself think them to be totally useless ,specially if you like me is a fan of Optmistic Locking,and use ClientDatasets a lot.
I just cant image doing a master-detail unit without having generators.
And the method that im going to show you is not from my authory and to be
Honest I didnt like it and I wont recommend it for you to use it in a bank or in airline company, because youre problably end up with an enourmous Bottle neck, But untill Microsoft open its yes its the best you can do.
========================================================
THE TABLE.
The first thing is to create this table in the SQL SERVER
Table name- MYGENERATORS
Fields - GEN_NAME char(20)
- ID integer
THE STORED PROCEDURE
Create procedure gen_id ( @GenName varchar(30))
as
begin
declare @ID integer
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION SP_GEN_ID
select @ID = ID from MYGENERATORS where GEN_NAME =@GenName
IF @@ERROR 0 GOTO LBL_ERROR
update mygenerators set ID = ID + 1 where GEN_NAME=@GenName
IF @@ERROR 0 GOTO LBL_ERROR
COMMIT TRANSACTION SP_GEN_ID
return @ID
LBL_ERROR:
ROLLBACK TRANSACTION SP_GEN_ID
return 0
end
GO
THE DELPHI CODE
SpSequencias is a Stored Procedure.
Set the StoreProcName to gen_id
In this example im using a client dataset with persistent fields but of course
You have to adapt it to our logic.
begin
with spsequencias do
begin {Start of with Stored proc do}
try {Start of try...except block}
ParamByName('GenName').AsString := 'ULTCATEGORIAEST';
ExecProc;
DMClient.CLDSCategoriaEstCTG_ID.AsInteger:=ParamByName('Result').AsInteger;
Close;
except
on e: EDatabaseError do
begin
Close;
DMClient.CLDSCategoriaEstCTG_ID.AsInteger:=-1;
ShowMessage(Na error occurred in your database,Try again);
DBE_CTG_NOME.SetFocus;
end;
end; {End of try...except block}
end; {End of with Stored proc do}
Youre probably find some articles in The Net similar to this one,but with different Contents,I prefer to be hard with MicroSoft since i think they must respect us the Customers and give us GENERATORS,if someone dont like them dont use them.
But since many programmers like me want to create Code that runs on several RDMS
by know we have to use this Clipper-times approach because if you dont do so your code will be Microsoft Sql Server incompatible
Special thanks to Kevin Frevert and Philippe who helped me very much.
Regards,
Marcello Dias
]]>