Primary keys 2

[Faded]

Using Delphi 5, I am trying to make an application that uses an ODBC connection to a MS SQL Server DB which contains three tables. After reading about the flaws of using auto-increment primary keys, I am trying to increment the key within the application, but when I try to post to the database I always get a "cannot insert duplicate values into primary key." even though it is a new entry into the DB with no value in the key field.

What I am doing is querying the key field with "SELECT max(pkGuestID) FROM Guest" to get the highest value in the DB, then I write that to a variable and add one to it then try to write it back to the db with the new record. Is this the right thinking? Is there a better way to do this?

Thanks, Faded

 

[McMerfy]

Hmmm...
>>...even though it is a new entry into the DB with no value in the key field.
>>...then try to write it back to the db with the new record.
These two sentenses are contradicting each other. Tell us WHAT exactly you are doing.

--- markus.

 

[Faded]

Marcus,

Sorry, you are right. When I try to enter a new record with no value in the pkGuestID field, I get the error "field 'pkGuestID' must have a value. etc..." and when I enter a value in that field, I get the "Duplicate value" error.

I have also tried to set the pkGuestID to "identity" on the SQL Server and trying to enter a value in the field gives me an obvious "Key violation, cannot insert explicit value into identity...", but when I enter no value shouldn't the SQL Server just add an incremented value into that field for me? It doesn't, I get the "must have a value" error.

I think right now I'd be happy with any solution, auto-number or not. Any ideas?

Thanks, Faded

 

[Faded]

Wow, While messing with the "identity" thing, I changed something(?) and now am able to write to the database a new record when I enter a value for the pk that is not used. So that's a major step forward, but my code to automatically increment the value is not working. Here's what I have:

procedure TFormMain.btnSaveClick(Sender: TObject);
var
  maxGuestID: Integer; {variable used for pk}

begin

  maxGuestID := dbeMaxGuestID.Field.Value; {dbeMaxGuestID gets the current max value from pk column}
  maxGuestID := maxGuestID + 1; {add 1 to it}

  {??? unsure of this.  Am I calling the right method here?}
  {DataMain is the Data Module, dsGuest is the Data Source, the table I am using is tblGuest.}
  {Should I be using that here?}
  with DataMain.dsGuest.DataSet do
  begin
    Insert;
    {write values to the database}
    FieldByName('pkGuestID').AsInteger := maxGuestID;
    FieldByName('firstName').AsString := dbeFirstName.Text;
    FieldByName('lastName').AsString := dbeLastName.Text;
    {etc...}
    Post;
  end;

end;

If there is a better way, I'd love to hear it.

Cheers, Faded

 

[svanels]

The best way to insert the field is using a stored procedure on your database server. This is a compiled sql statement which is executed by SQL-server, every time a new record is inserted.
Check your sql-server documentation or go to

http://www.databases.about.com/library/weekly/aa042201a.htm

for some more info

Regards Steven van Els
SAvanEls@cq-link.sr

 

[Faded]

Thanks Steven,

So I have created a procedure:

CREATE PROCEDURE spMaxGuestID
AS
SELECT max(pkGuestID)
FROM Guest

and dropped a TStoredProc (named spMaxGuestID) and a TDataSource (dsMaxGuestID) on the DataModule. Now, in the code, right now on a button click event, I put the following:

DataMain.spMaxGuestID.ExecProc;

which seems to execute the stored procedure. Now I need to store the result in a variable, so I created one (currentMax of type integer) but can't seem to get the sp result into the variable. How can I do this?

Thanks, Faded

 

[svanels]

I am travelling right now but at the end of the week, I can send a small example, but you are already on the right track. Steven van Els
SAvanEls@cq-link.sr

 

[McMerfy]

Change you stored procedure to something that return a value, for example:

CREATE PROCEDURE spMaxGuestID
RETURNS (
  MaxID INTEGER)
AS
BEGIN
  SELECT max(pkGuestID)
  FROM Guest
  INTO :MaxID;
END

That's actually an Interbase sytnax, but i think there shouldn't be many differences with MS SQL.
Once you have a stored procedure like that in your application use TStoredProc, bound to you stored proc on a server, to execute it (actually you did it right ).

var
  aMaxID : Integer;
begin
  DataMain.spMaxGuestID.ExecProc;
  aMaxID := DataMain.spMaxGuestID.ParamByName('MAXID').asInteger;
...
end;

By the way: it's not a good thing to generate new record IDs by selecting a maximal value. Read about generator or sequence or whatever they called in MS SQL.

--- markus

 

[Faded]

Thanks McMerfy!

Sorry for the delayed response, I've been working on something else.

Well, I got it figured out in a way that I think is similar to what you suggested.

I created a new table called NextID and put the table name and "next pk id" in it, then created a stored procedure that takes the table name as a parameter and returns the next id to the caller and then increments the id. I'm not sure this is the best way to go about it, but it works well so far.

Thanks for your help.
Faded

 

[svanels]

That approach is valid for things like paradox and access, but SQL-server must have things called generators and triggers that take care of autonumbering fields.

A generator is an internal sequence number mechanism that takes place when an event (trigger) like inserting a new record is generated.

Regards Steven van Els
SAvanEls@cq-link.sr

 

[svanels]

This is the Interbase SQL sintaxe, but it should be similar to other RDBMS like MS-SQL, ORACLE etc.

Imagine I have a table and I need an unique ID_Number. I do this by creating a generator, and the first number must start at 101

sintaxe:

CREATE GENERATOR ID_NUMBER_GEN;
SET GENERATOR ID_NUMBER_GEN TO 100

After that I create a trigger for mytable that fires every time when a new record is inserted. MyTable has an integer key field called ID.

sintaxe:

CREATE TRIGGER NUMBER_INSERT FOR MYTABLE BEFORE INSERT POSITION 0 AS BEGIN
NEW.ID = GEN_ID(ID_NUMBER_GEN,1);
END

The advantage of this method is that SQL-Server will take care of generating the number, regardless wich SQL-tool, or front end program is used.

Another example is when you delete a customer from your customers database, you want to delete also the sales of this customer.

create trigger SALEDelete on CUSTOMER for DELETE
as
BEGIN
DELETE from SALE
where CustomerNumber = (SELECT CustomerNumber FROM deleted)
END

above is MS-SQL server sintaxe, that is similar to interbase

Regards Steven van Els
SAvanEls@cq-link.sr