Returning last ID after insert

[SebastienD]

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

[AlexP]

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:

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);

[SebastienD]

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 ?

[AlexP]

Yes, PgTable also allows to get the inserted id. In the example above it is enough to change PgQuery into PgTable.

[SebastienD]

Here is my code, TPrepE is my table (and I code in C ++)

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()

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

[AlexP]

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();