Returning last ID after insert
-
- Posts: 3
- Joined: Tue 17 Jan 2017 16:17
Returning last ID after insert
Hi,
I'm creating a software for order management.
I have 2 tables: one for the command headers and one for the detail of the commands.
When I validate my order, I insert all of my information in the header, but I need to have the id of my header table to insert it into my table detail.
How can I retrieve this id with the dac after an insert ?
Thanks
I'm creating a software for order management.
I have 2 tables: one for the command headers and one for the detail of the commands.
When I validate my order, I insert all of my information in the header, but I need to have the id of my header table to insert it into my table detail.
How can I retrieve this id with the dac after an insert ?
Thanks
Re: Returning last ID after insert
Hello,
To return the value retrieved in the sequence to DataSet, you should set the ReturnParams option to True (PgQuery1.Options.ReturnParams := True), and use the following construction:
To return the value retrieved in the sequence to DataSet, you should set the ReturnParams option to True (PgQuery1.Options.ReturnParams := True), and use the following construction:
Code: Select all
INSERT INTO t_autoinc (f_txt) VALUES (:f_txt) returning f_id
Code: Select all
CREATE TABLE public.t_autoinc
(
f_id integer NOT NULL DEFAULT nextval('t_autoinc_f_id_seq'::regclass),
f_txt character varying(20),
CONSTRAINT pk_t_autoinc PRIMARY KEY (f_id)
)
Code: Select all
PgQuery1.SQL.Text := 'select * from t_autoinc';
PgQuery1.SQLInsert.Text := 'INSERT INTO t_autoinc (f_txt) VALUES (:f_txt) returning f_id';
PgQuery1.Options.ReturnParams := True;
PgQuery1.Open;
PgQuery1.Append;
PgQuery1.Fields[1].AsString := 'test';
PgQuery1.Post;
ShowMessage(PgQuery1.Fields[0].AsString);
-
- Posts: 3
- Joined: Tue 17 Jan 2017 16:17
Re: Returning last ID after insert
Is it possible to do the same with a TPgTable component because currently I don't use a query to make an insert but directly a table with append and post ?
Re: Returning last ID after insert
Yes, PgTable also allows to get the inserted id. In the example above it is enough to change PgQuery into PgTable.
-
- Posts: 3
- Joined: Tue 17 Jan 2017 16:17
Re: Returning last ID after insert
Here is my code, TPrepE is my table (and I code in C ++)
I have activated ReturnParams option to True but I can not get the ID by following the example.
The table points to a view with a trigger
Code: Select all
this->TPrepE->Open();
this->TPrepE->Append();
this->TPrepE->FieldValues["id_client"] = (int)this->TClients->FieldValues["id_client"];
this->TPrepE->FieldValues["id_medecin"] = (int)this->TMedecin->FieldValues["id_medecin"];
this->TPrepE->FieldValues["id_patient"] = (int)this->TPatient->FieldValues["id_patient"];
this->TPrepE->FieldValues["id_formegalenique"] = (int)this->TForme->FieldValues["id_fg"];
this->TPrepE->FieldValues["dateperemtion"] = this->E_Forme_DatePeremp->Date;
this->TPrepE->FieldValues["nbserie"] = this->E_Forme_Serie->Text;
this->TPrepE->FieldValues["voieadmin"] = 0;
this->TPrepE->Post();
// return id //
this->TPrepE->Close()
The table points to a view with a trigger
Re: Returning last ID after insert
Besides the ReturnParams property you need to set the Inser query correctly, a full sample is provided below:
Code: Select all
IF NOT EXISTS public.t_autoinc f_id integer NOT NULL DEFAULT nextval('t_autoinc_f_id_seq'::regclass), f_txt character varying(20), CONSTRAINT pk_t_autoinc PRIMARY KEY (f_id))
Code: Select all
TPgConnection *PgConnection = new TPgConnection(NULL);
TPgTable *PgTable = new TPgTable(NULL);
PgConnection->ConnectString = "...";
PgConnection->Connect();
PgTable->Connection = PgConnection;
PgTable->TableName = "t_autoinc";
PgTable->Options->ReturnParams = True;
PgTable->SQLInsert->Text = "INSERT INTO t_autoinc (f_txt) VALUES (:f_txt) returning f_id";
PgTable->Open();
PgTable->Append();
PgTable->FieldByName("f_txt")->AsString = "test";
PgTable->Post();
ShowMessage(PgTable->FieldByName("f_id")->AsString);
PgTable->Close();