Used with Oracle or Interbase.
Just forget Microsoft Sql Server does not have generators.

Hi,

If you´re 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
Don´t let you make the move(This week i convert a whole system From Interbase
Into SQL Server,for my happiness i´m 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 labs´ones.

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 can´t image doing a master-detail unit without having generators.
And the method that i´m going to show you is not from my authory and to be
Honest I didn´t like it and I won´t recommend it for you to use it in a bank or in airline company, because you´re 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 i´m 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}


You´re 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 don´t like them don´t 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 don´t do so your code will be Microsoft Sql Server  incompatible

Special thank´s to Kevin Frevert and Philippe who helped me very much.


Regards,
Marcello Dias