After inserting a value into a table with an auto_increment field type,
how do I retrieve the value for use in my program???

        I don't think it's implemented at all.

        -Craig

--
 Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
We're hiring: http://www.vertexsoftware.com/careerops.htm#sd
     Delphi/InterBase WebLog: http://delphi.weblogs.com

Try setting DatasetProvider option poAutoRefresh and i see folks have reported
that its not working correctly.
Another workaround would be to refresh the whole dataset and that will get  the
newly inserted auto increment values. This may be expensive in some cases where
you are dealing with large resultset or improper filtering in the SQL.

T.Ramesh.

--
This e-mail, and any attachments thereto, is intended only for use by
the addressee(s) named herein and may contain legally privileged and/or
confidential information.  If you are not the intended recipient of
this e-mail, you are hereby notified that any dissemination,
distribution or copying of this e-mail, and any attachments thereto, is
strictly prohibited.  If you have received this e-mail in error, please
immediately and permanently delete the original and any copy of any
e-mail and any printout thereof.

Accelerate your Linux? application development with Kylix(TM), the RAD
tool for Linux. Easily integrate web, desktop and database development
to quickly deliver fast Linux applications. Kylix is the only Linux
development tool that combines the world's fastest compiler with the
most productive visual design environment for GUI, web and database
programming. Order your copy now!
http://www.borland.com/kylix/

You can run this query to retrieve the value:

  select last_insert_id() from [anytablename] limit 1

'anytablename' can be any existing table in the database, not necessarily
the table you did the insert into. If you leave out the 'limit 1' you'll
get back multiple copies of the same answer, one row for each row in the
'anytablename'.

MySQL maintains a unique last id value for each established connection. So
you don't have to worry about other clients stepping on your id, but you do
have to watch out if your application is multi-threaded.

In Delphi 5 using the MySQL odbc driver the query was much cleaner, simply
'select last_insert_id()'. But dbexpress in Kylix 1 rejects this query.

I haven't tested any of this in Kylix 2 or Delphi 6 yet.

David

--
David Peoples  dav...@touringcyclist.com  http://www.touringcyclist.com
The Touring Cyclist, 11816 St. Charles Rock Rd, Bridgeton, MO 63044 USA
tel: 314-739-4648  fax: 314-739-4972

news:3c0ef28e_1@dnews...

    Thanks for the tip.  I'll give it a try!

I turned on detailed logging on my Mysql server and found that transactions
are being attempted, but every sql statement that is sent to the server is
sent as a seperate connection!!  This is causing the all kinds of problems.
The biggest for me, is that there is no way to even get the id of the last
record that was just inserted even from the server.  I did get it to work
with keepconnection=false, but I have to have keepconnection=true in order
for master detail to work.

I am using datasnap with and the provider is doing funky things with
dbexpress.

I am going to have to implement some other type of id generation routine to
get around this.  This often isn't an option.

I am going to try to do an insert, get last_insert_id() and delete where
id=last_insert_id() all in one statement and get my id's before calling the
applyupdates.  This is a pain, I have spent 3 days trying to make autoincs
work with dbexpress.

That works unless you are using datasnap.  Then it returns 0 all the time.
(Unless you only have one table connected to the provider and
keepconnection=false)

Why not use mySQL-function "LAST_INSERT_ID()" and you can get the
automatically created id directly after creation.

Thomas

Am 05.12.2001 21:10 Uhr schrieb "Ramesh Theivendran (BORLAND)" unter
<rtheivend...@borland.com> in 3C0E7F34.50E57...@borland.com:

because of a bug in either the datasnap provider or the dbexpress/Mysql
driver the value is being wiped out.  I Traced my Mysql logs and found that
a new connection to the db is occuring for every sql statement that is sent
to the server.  Since it is a new connection it can't give you the
last_insert_id because it may not of belonged to you.

Ross Davis - DataAnywhere.net wrote:

I found a workaround to this problem, but it's ugly. I've tested in Kylix 2
(trial version) using a TSqlQuery. Don't know yet if it works in a
TSqlClientDataset.

Arrange for this code to run just after the insertion query executes. The
code has to act on the _same_ object that ran the original insertion, in
this example called 'MyInsertQuery':
  //MyInsertQuery.SaveSQLCodeToSomewhere;
  MyInsertQuery.SQL.Clear;
  MyInsertQuery.SQL.Add('select last_insert_id()');
  MyInsertQuery.Open;
  ShowMessage('New ID: ' + MyInsertQuery.Fields[0].AsString);
  MyInsertQuery.Close;
  MyInsertQuery.RestoreSQLCodeFromSomewhere;
(I guess you could put this code in the 'AfteOpen' handler of
MyInsertQuery, if you add a trap to prevent it from recursing into
infinity. <g>)

David

--
David Peoples
The Touring Cyclist, 11816 St. Charles Rock Rd, Bridgeton, MO 63044
dav...@touringcyclist.com  http://www.touringcyclist.com

I keep putting my foot in my mouth -- 'AfterOpen' doesn't get called after
a TSQLQuery.ExecSQL, just after TSQLQuery.Open.

Below is code for a component that saves the last auto_increment value. It
has a new public property called 'LastInsertID' and works by overriding the
ExecSQL function.

  --- Code starts here ---

unit MySQLAutoIncQuery;

interface

uses
  SysUtils, Types, Classes, QGraphics, QControls, QForms, QDialogs, DB,
  SqlExpr;

type
  TMySQLAutoIncQuery = class(TSQLQuery)
  private
    FLastInsertID: Integer;
    FCommandList: TStringList;
  public
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
    function ExecSQL(ExecDirect: Boolean = False): Integer; override;
    property LastInsertID: Integer read FLastInsertID;
  end;

procedure Register;

implementation

constructor TMySQLAutoIncQuery.Create(AOwner: TComponent);
begin
  Inherited Create(AOwner);
  FCommandList := TStringlist.Create;
  FLastInsertID := -1;
end;

destructor TMySQLAutoIncQuery.Destroy;
begin
  FCommandList.Free;
  Inherited Destroy;
end;

function TMySQLAutoIncQuery.ExecSQL(ExecDirect: Boolean = False): Integer;
begin
  Result := Inherited ExecSQL(ExecDirect);
  Close;
  FCommandList.Clear;
  FCommandList.AddStrings(SQL);
  SQL.Clear;
  SQL.Add('select last_insert_id()');
  Open;
  FLastInsertId := Fields[0].AsInteger;
  Close;
  SQL.Clear;
  SQL.AddStrings(FCommandList);
end;

procedure Register;
begin
  RegisterComponents('DAP', [TMySQLAutoIncQuery]);
end;

end.

  --- Code ends here ---

--
David Peoples
The Touring Cyclist, 11816 St. Charles Rock Rd, Bridgeton, MO 63044
dav...@touringcyclist.com  http://www.touringcyclist.com

because of the numerous bugs in datasnap with dbexpress, I had to create
these functions and set the record id's of new records in the
beforeupdaterecord.  (Sample below) Once the bugs have been fixed I will be
able to check the last_insert_id in the afterupdaterecord function instead
of before

Note: I use a similar routine to what Dan Miser suggested for this where new
records added on the client side are set with unique negative numbers.

These routines work great and with poPropigatechaanges=true the updates go
right back to the client just like they should.

// AUV = After Update Value
Function TEraDataServer.auv(fieldbeingupdated : Tfield) : Variant;
begin
  if varisempty(Fieldbeingupdated.NewValue) then
    auv := fieldbeingupdated.oldvalue
  else
    auv := fieldbeingupdated.newvalue;
end;

function TEraDataServer.getid(table,field:string) : integer;
var
   SQL: string;
begin
  try
    Updatekeyconnection.Open;
    SQL := 'insert into ' + table + ' (' + field + ') values (null)';
    UpdateKeyConnection.Executedirect(SQL);
    SQL := 'delete from ' + table + ' where ' + field + '=last_insert_id()';
    UpdateKeyConnection.Executedirect(SQL);
    genidquery.Close;
    genidquery.Open;  // select last_insert_id() as id
    result := genidqueryid.AsInteger;
  except
    on E: Exception do raise exception.Create('Error Generating ID on table
: '+table+ ' Error : '+e.message);
  end;
  genidquery.Close;
  updatekeyconnection.Close;
end;

Function TEraDataServer.CheckID(var Deltads : TCustomClientDataset;
    tablename, idfield: String ) : Integer;
begin
  result :=auv(deltads.FieldByName(idfield));
  if result < 0 then // want new id
    result := getid(tablename,idfield);
  DeltaDS.FieldByName(idfield).newValue := result;
end;

Function TEraDataServer.CheckMasterID(var Deltads : TCustomClientDataset;
    MasterIDField: String ; LastMasterID : Integer) : Integer;
begin
  result :=auv(deltads.FieldByName(MasterIDField));
  if result < 0 then // Want the last Master ID Value
    result := LastMasterID;
  DeltaDS.FieldByName(MasterIDField).newValue := result;
end;

procedure TEraDataServer.master_ProviderBeforeUpdateRecord(Sender: TObject;
  SourceDS: TDataSet; DeltaDS: TCustomClientDataSet;
  UpdateKind: TUpdateKind; var Applied: Boolean);
begin
  if (SourceDS = master_SQLDS) then
  begin
    master_id := checkid(DeltaDS,'master','master_id');
  end;

  if SourceDS = detail_SQLDS then
  begin
    detail_id := checkid(DeltaDS,'detail','detail_id');
    master_id := checkmasterid(DeltaDs,'master_id',master_id);
  end;

  if SourceDS = sub_detail_SQLDS then
  begin
    sub_detail_id := checkid(DeltaDS,'sub_detail','sub_detail_id');
    detail_id := checkmasterid(DeltaDs,'detail_id',detail_id);
  end;

end;