Board index » delphi » How to retrieve info from an insert SQL statement

How to retrieve info from an insert SQL statement

Hi,

I want to insert a record with and the SQL insert statement. This work fine,
but I want to retrieve the value of an AutoIncrement field. I need this
value after the insertion of the new record. I use Access 97 with the BDE.
The following code works:

    wwTableUser.Append;

    wwTableUserfullName.AsString := user.fullName;
    wwTableUserLoginName.AsString := user.loginName;
    wwTableUserPassword.AsString := user.password;
    wwTableUserActive.AsBoolean := user.active;

    try
      wwTableUser.Post;
      result := SPCDataModule.wwTableUseruserId.AsInteger; // Return UserId
(autoincrement field)
    except
      raise EStorable.Create('Error:  post failed');
    end;

Do you know how retreive the value of the autoincrement field (userId) using
an SQL insert statement? Off cource I can search for the record just
inserted and then retrieve the value of the autoincrement field, but is
there also another way?

Thanks in advance.

Best regards,
 Arjan

 

Re:How to retrieve info from an insert SQL statement


<snip>
Quote

>Do you know how retreive the value of the autoincrement field (userId) using
>an SQL insert statement? Off cource I can search for the record just
>inserted and then retrieve the value of the autoincrement field, but is
>there also another way?

INSERT sql statements simply do not return any information. You will
have to use some other method. Personally, I prefer to use a separate
table with one record holding this type of counter value. Autoinc
fields are always a problem in a case like this because the value
isn't known until after the record is added.

If there is no need for multi-user operation, a SELECT
MAX(autoincfield) FROM table will get the value just created.
(Hopefully ;)
If you want to know more about the separate table method, let me know.

HTH,
Dan

Quote
>Thanks in advance.

>Best regards,
> Arjan

--
Dan Brennand
CMDC systems, inc.
Configuration Management and Document Control:
visit us at www.cmdcsystems.com
[SPAM block: change at to @ in my e-mail address]

Re:How to retrieve info from an insert SQL statement


Quote
On Thu, 8 Mar 2001 10:08:55 +0100, "Arjan" <n...@spam.com> wrote:
>Hi,

>I want to insert a record with and the SQL insert statement. This work fine,
>but I want to retrieve the value of an AutoIncrement field. I need this
>value after the insertion of the new record. I use Access 97 with the BDE.
>The following code works:

>    wwTableUser.Append;

>    wwTableUserfullName.AsString := user.fullName;
>    wwTableUserLoginName.AsString := user.loginName;
>    wwTableUserPassword.AsString := user.password;
>    wwTableUserActive.AsBoolean := user.active;

>    try
>      wwTableUser.Post;
>      result := SPCDataModule.wwTableUseruserId.AsInteger; // Return UserId
>(autoincrement field)
>    except
>      raise EStorable.Create('Error:  post failed');
>    end;

>Do you know how retreive the value of the autoincrement field (userId) using
>an SQL insert statement? Off cource I can search for the record just
>inserted and then retrieve the value of the autoincrement field, but is
>there also another way?

>Thanks in advance.

>Best regards,
> Arjan

With Oracle I use stored proc to insert rec. into table;
TABLE FIELDS XX1, XX2

CREATE OR REPLACE PROCEDURE InsertProc
        (vXx1 IN CHAR, vXx2 IN CHAR, vResult OUT CHAR)
AS
BEGIN
 INSERT INTO BrParc
 VALUES
   (vXx1,vXx2)
 RETURNING XX1 INTO vResult;
END;

So, call that proc from Delphi and it will return field from inserted
rec.

Hope that helps.

Re:How to retrieve info from an insert SQL statement


Dan,

Thanks for you answer. I can not use the search max method, because it is a
multi-user program. Therefore I would ask you to give me some more
information on using the separate table method you mentioned.

Thanks in advance

Best regards,
 Arjan

Quote
"Dan Brennand" <D...@CMDCsystems.com> wrote in message

news:3aa80485.451322004@news.dimensional.com...
Quote
> <snip>

> >Do you know how retreive the value of the autoincrement field (userId)
using
> >an SQL insert statement? Off cource I can search for the record just
> >inserted and then retrieve the value of the autoincrement field, but is
> >there also another way?

> INSERT sql statements simply do not return any information. You will
> have to use some other method. Personally, I prefer to use a separate
> table with one record holding this type of counter value. Autoinc
> fields are always a problem in a case like this because the value
> isn't known until after the record is added.

> If there is no need for multi-user operation, a SELECT
> MAX(autoincfield) FROM table will get the value just created.
> (Hopefully ;)
> If you want to know more about the separate table method, let me know.

> HTH,
> Dan
> >Thanks in advance.

> >Best regards,
> > Arjan

> --
> Dan Brennand
> CMDC systems, inc.
> Configuration Management and Document Control:
> visit us at www.cmdcsystems.com
> [SPAM block: change at to @ in my e-mail address]

Re:How to retrieve info from an insert SQL statement


Quote
On Fri, 9 Mar 2001 09:47:28 +0100, "Arjan" <n...@spam.com> wrote:
>Dan,

>Thanks for you answer. I can not use the search max method, because it is a
>multi-user program. Therefore I would ask you to give me some more
>information on using the separate table method you mentioned.

>Thanks in advance

>Best regards,
> Arjan

Here is the general method: When one user needs the next counter
value: lock the table, read the current value, increment it, then
return the new value to the calling procedure, then unlock the table.
Each user should call for a new number in a try-except block to allow
for simultaneous calls.
Note: the following is for Paradox tables, which are locked
automatically by the BDE for a Live Query. For other DBs, be sure to
understand how the Counter table will be locked. You may have to set
an explicit lock in your code, and then release it later.

1. create a table called 'Counter.db' [I'm assuming Paradox for
illustration only]. Give it an integer field for each different
counter you need, for example 'OrderNo' and 'CustNo'. Open the table
and set each field to your starting values; e.g. 1000. Use Database
Desktop, or Paradox, or ?? to make this table.

2. drop a query on a datamodule, say dm1, named qCounter. Set the SQL
property to SELECT * FROM "Counter.db"
RequestLive = true

3. put the following function in dm1:

function Tdm1.NextNum(typ: string): integer;
var
  nn: integer;
begin
  with qNumber do
  begin
    close;
    try
    open;   // this locks the record to other users
    edit;
    nn := FieldByName(typ).AsInteger;  // read current max value
    inc(nn);  // add one to it
    FieldByName(typ).AsInteger := nn;  // write the new value
    post;    // store the new value in the database
    result := nn;  // send back new value
    except
    result := -1;  // send back error code
    end;   // end of try-except block
   close;  // release the lock on the table
  end;
end;

4. Typical calling procedure:

procedure TfrmOrders.cmdNewClick(Sender: TObject);
var newnum, N: integer;  { if newnum is needed elsewhere, make it a
form variable}
begin
  N := 1;
  repeat
  begin  
    newnum := dm1.NextNum('OrderNo');  // gets next order number
    inc(N);
  end;
  until (newnum <> -1) or (N = 5);  // tries 5 times to get a number
  if newnum = -1 then showmessage('Could not get new Order Number');
  qcvp.AppendRecord([newnum, date]);  { add a new record to the
dataset in use by the order entry form. This example fills in the 1st
two field values, including the just generated order number}
{<do other things if necessary>}
end;
--
Dan Brennand
CMDC systems, inc.
Configuration Management and Document Control:
visit us at www.cmdcsystems.com
[SPAM block: change at to @ in my e-mail address]

Re:How to retrieve info from an insert SQL statement


Dan,

Thanks for you explanation on using the separate table method. This will
help me a lot!

Best regards,
 Arjan

Other Threads