Hi All,

can anyone tell me how to return the new value of an AutoInc field after an
insert sql statement. In Firebird I just use a "Returning" clause. How can i
do this in DBISAM?

Thanks,
Paul

Paul,

There are 2 ways: the SQL function LASTAUTOINC, that returns the last
autoinc value from a table, and TDBISAMTable.LastAutoincValue.
Be aware that in a multi user environment it's not safe to use the returned
values if you don't enclose the operation in a transaction as someone else
might change the value immediately after it has been returned.

--
Fernando Dias
[Team Elevate]

"Fernando Dias"  wrote in message
news:09DDB20F-7178-4443-A30C-A3C2F018FE35@news.elevatesoft.com...

There are 2 ways: the SQL function LASTAUTOINC, that returns the last
autoinc value from a table


Hi Fernando,

thanks for the answer. I'm not sure how to implement this. Do I do this in
an sql script? I saw an example in the help manual, but it uses the
LastAutoInc to insert into another table and doesn't show how to return the
value.

--------------------------------------------
Start Transaction;

Insert into "Invoices"
 (MemberId,FullyPaid,Memo)
Values
 (20, False, 'Fees for 2013');

Select LastAutoInc("Invoices") ???????????????;

Commit Flush;
----------------------------------------------

I don't know how to use the LastAutoInc function.

Thanks for any help,

Cheers,
Paul

Paul,

It depends on what you are wanting to do...
What do you want LastAutoinc value for and how are you inserting rows, are you using SQL or TDBISAMTable methods ?

--
Fernando Dias
[Team Elevate]

Em 23/01/2013 19:07, Paul Coshott escreveu:
>
>
> "Fernando Dias"  wrote in message news:09DDB20F-7178-4443-A30C-A3C2F018FE35@news.elevatesoft.com...
>
> There are 2 ways: the SQL function LASTAUTOINC, that returns the last
> autoinc value from a table
>
>
> Hi Fernando,
>
> thanks for the answer. I'm not sure how to implement this. Do I do this in an sql script? I saw an example in the help manual, but it uses the LastAutoInc to insert into another table and doesn't show how to return the value.
>
> --------------------------------------------
> Start Transaction;
>
> Insert into "Invoices"
>   (MemberId,FullyPaid,Memo)
> Values
>   (20, False, 'Fees for 2013');
>
> Select LastAutoInc("Invoices") ???????????????;
>
> Commit Flush;
> ----------------------------------------------
>
> I don't know how to use the LastAutoInc function.
>
> Thanks for any help,
>
> Cheers,
> Paul

Hi Fernando,

I am using SQL as in the example below. I am doing an Insert query to create
an invoice header, then I need to get the InvoiceNo (the AutoInc field) so I
can begin to add invoice detail lines (in a child table). Without the
InvoiceNo, I can't link the child records to the parent invoice record.

Thanks,
Paul


"Fernando Dias"  wrote in message
news:E6F08339-CB4B-4B75-86C8-9B006D5F9E5A@news.elevatesoft.com...

Paul,

It depends on what you are wanting to do...
What do you want LastAutoinc value for and how are you inserting rows, are
you using SQL or TDBISAMTable methods ?

--
Fernando Dias
[Team Elevate]

Em 23/01/2013 19:07, Paul Coshott escreveu:
>
>
> "Fernando Dias"  wrote in message
> news:09DDB20F-7178-4443-A30C-A3C2F018FE35@news.elevatesoft.com...
>
> There are 2 ways: the SQL function LASTAUTOINC, that returns the last
> autoinc value from a table
>
>
> Hi Fernando,
>
> thanks for the answer. I'm not sure how to implement this. Do I do this in
> an sql script? I saw an example in the help manual, but it uses the
> LastAutoInc to insert into another table and doesn't show how to return
> the value.
>
> --------------------------------------------
> Start Transaction;
>
> Insert into "Invoices"
>   (MemberId,FullyPaid,Memo)
> Values
>   (20, False, 'Fees for 2013');
>
> Select LastAutoInc("Invoices") ???????????????;
>
> Commit Flush;
> ----------------------------------------------
>
> I don't know how to use the LastAutoInc function.
>
> Thanks for any help,
>
> Cheers,
> Paul

Paul,

Ill assume that "InvoiceNumber" is the Autoinc field in your tables, here is an example:


START TRANSACTION ;

-- Inserting Header
INSERT INTO "Invoices"
  (MemberId,FullyPaid,Memo)
  VALUES
  (20, False, 'Fees for 2013');

-- Inserting 2 Lines
INSERT INTO "InvoicesLines"
  (InvoiceNumber, LineNumber, Item, Price)
  VALUES
  (LASTAUTOINC(Invoices), 1, 'Item 1', 5.00) ;

INSERT INTO "InvoicesLines"
  (InvoiceNumber, LineNumber, Item, Price)
  VALUES
  (LASTAUTOINC(Invoices), 2, 'Item 2', 6.00) ;


COMMIT FLUSH;

--
Fernando Dias
[Team Elevate]

Hi Fernando,

thanks for the answer, but it's not what I need. I just really need to
insert the invoice record and return the InvoiceNo field which is the
AutoInc field. How can I get the new value?

Cheers,
Paul

"Fernando Dias"  wrote in message
news:95CB3A3D-634A-4650-9B71-3AE63C7D07CA@news.elevatesoft.com...

Paul,

Ill assume that "InvoiceNumber" is the Autoinc field in your tables, here is
an example:


START TRANSACTION ;

-- Inserting Header
INSERT INTO "Invoices"
  (MemberId,FullyPaid,Memo)
  VALUES
  (20, False, 'Fees for 2013');

-- Inserting 2 Lines
INSERT INTO "InvoicesLines"
  (InvoiceNumber, LineNumber, Item, Price)
  VALUES
  (LASTAUTOINC(Invoices), 1, 'Item 1', 5.00) ;

INSERT INTO "InvoicesLines"
  (InvoiceNumber, LineNumber, Item, Price)
  VALUES
  (LASTAUTOINC(Invoices), 2, 'Item 2', 6.00) ;


COMMIT FLUSH;

--
Fernando Dias
[Team Elevate]

Paul,

In that case I'd use the TDBISAMTable.LastAutoIncValue method or in case you only want to use sql:

INSERT INTO "Invoices"
    (MemberId,FullyPaid,Memo)
    VALUES
    (20, False, 'Fees for 2013');

SELECT LASTAUTOINC('Invoices') AS LastInvoiceNum FROM Invoices TOP 1 ;
or
SELECT MAX(InvoiceNumber) AS LastInvoiceNum FROM Invoices


However, no matter what method you prefer, be aware that this approach  is not reliable in a multi user environment because someone else might have inserted new invoices after your insert yours and just before your call to MAX or LASTAUTOINC or TDBISAMTable.LastAutoIncValue, unless you enclose all the operations inside a transaction.

--
Fernando Dias
[Team Elevate]

Paul,

Here is a complete example:

  DBISAMDatabase1.StartTransaction;
  try
    DBISAMQuery1.SQL.Add(
      'INSERT INTO Invoices (MemberId,FullyPaid,Memo) '+
      '  VALUES (20, False, ''Fees for 2013'' )'
    ) ;
    DBISAMQuery1.ExecSql;
    DBISAMQuery1.SQL.Clear;
    DBISAMQuery1.SQL.Add(
     'SELECT MAX(InvoiceNumber) AS LastNumber FROM Invoices'
    );
    DBISAMQuery1.Open;
    try
      //get the value and use it as needed...

      N := DBISAMQuery1.FieldByName('LastNumber').AsInteger;

    finally
      DBISAMQuery1.Close;
      DBISAMQuery1.SQL.Clear;
    end;
    DBISAMDatabase1.Commit;
  except
    DBISAMQuery1.Close;
    DBISAMDatabase1.Rollback;
    raise;
  end;

--
Fernando Dias
[Team Elevate]

Hi Fernando,

This is exactly what I was looking for. Thanks very much for your help. I
really appreciate it.

Cheers,
Paul

"Fernando Dias"  wrote in message
news:F2AD7E25-AA29-4EE1-8B1C-5F900C9CEE15@news.elevatesoft.com...

Paul,

Here is a complete example:

  DBISAMDatabase1.StartTransaction;
  try
    DBISAMQuery1.SQL.Add(
      'INSERT INTO Invoices (MemberId,FullyPaid,Memo) '+
      '  VALUES (20, False, ''Fees for 2013'' )'
    ) ;
    DBISAMQuery1.ExecSql;
    DBISAMQuery1.SQL.Clear;
    DBISAMQuery1.SQL.Add(
 'SELECT MAX(InvoiceNumber) AS LastNumber FROM Invoices'
    );
    DBISAMQuery1.Open;
    try
      //get the value and use it as needed...

      N := DBISAMQuery1.FieldByName('LastNumber').AsInteger;

    finally
      DBISAMQuery1.Close;
      DBISAMQuery1.SQL.Clear;
    end;
    DBISAMDatabase1.Commit;
  except
    DBISAMQuery1.Close;
    DBISAMDatabase1.Rollback;
    raise;
  end;

--
Fernando Dias
[Team Elevate]