Oracle SQL: Insert into and returning

[Guest]

I work with D6 with Update2, component ODACnet 4.50.3.24 and Oracle 9i and I have the following problem:

In a table I have created a called field N_NUM_ORDEN that is upgraded with the value of a "sequence" that is "activated" when inserting a new record.

My question, is like I can pass the value of 'RETURNING' when I make "insert into.. " to a variable, to be able to show in screen the assigned number to N_NUM_ORDEN.

Thanks in advance.


The codes of the sequence and of the trigger they are:

Code: Select all

CREATE SEQUENCE "DESARROLLO"."TA_LIQ_NUM_ORDEN" INCREMENT BY 1 
    START WITH 5000 MAXVALUE 99999 MINVALUE 1 NOCYCLE 
    NOCACHE ORDER;

Code: Select all

CREATE OR REPLACE TRIGGER "DESARROLLO"."TA_LIQ_GENERAR_NUM_ORDEN" 
BEFORE INSERT OR UPDATE OF "NUM_ORDEN" ON "REGISTRO" 
FOR EACH ROW 
begin
   if :new.num_orden is null then
   select TA_LIQ_NUM_ORDEN.NextVal
   into :new.num_orden 
   from Dual;
   end if;
end;

Code: Select all

Procedure  TfLiq.grabaSQLliq;
begin
  fdm.OraSQL_liq.SQL.clear;
  fdm.OraSQL_liq.SQL.Text:=
   'INSERT INTO Desarrollo.registro'+#13+
   '(C_NUM_OFICINA, N_NUM_YEAR, N_NUM_ORDEN)'+#13+
   ' VALUES '+#13+
   '(:C_NUM_OFICINA, :N_NUM_YEAR, :N_NUM_ORDEN,'+#13+
   'RETURNING'+#13+  
   ' N_NUM_ORDEN'+#13+
   'INTO'+#13+
   ' :N_NUM_ORDEN';   // <= As to pass this data to a variable

    fdm.OraSQL_liq.ParamByName('C_NUM_OFICINA').AsString  := num_oficina.text;
    fdm.OraSQL_liq.ParamByName('N_NUM_YEAR').AsString     := Num_year.text;
    fdm.OraSQL_liq.ParamByName('N_NUM_ORDEN').AsString    := Num_Orden.Text; 

    fdm.OraSQL_liq.prepared;
    fdm.OraSQL_liq.execute;

End;

[Paul]

You cannot visualize parameter from RETURNING clause automatically. But you can use the one of the following. Use TOraDataSet.KeySequence:='DESARROLLO.TA_LIQ_NUM_ORDEN' and TOraDataSet.KeyFields properties to insert key values to the new inserted rows. Please use
TCustomDADataSet.RefreshOptions = [roAfterInsert] for refreshing key fields from database. Please see ODAC help for more details.

[Guest]

I have put in tOraQuery (tbLiq) the property KeySecuence := 'DESARROLLO.TA_LIQ_NUM_ORDEN',
Keyfields := 'n_num_orden' and Refreshoptions = [roAfterInsert]

But I don't see the form of reading the "n_num_orden" content when I execute "insert into.. " The whole process works me well, alone I need to obtain the "n_num_orden" to show it in the screen.

You could send me an example of how to make it using KeySecuence and KeyField

Thanks in advance.

[Paul]

In your code you can write

Code: Select all

begin 
  fdm.OraSQL_liq.SQL.clear; 
  fdm.OraSQL_liq.SQL.Text:= 
   'INSERT INTO Desarrollo.registro'+#13+ 
   '(C_NUM_OFICINA, N_NUM_YEAR, N_NUM_ORDEN)'+#13+ 
   ' VALUES '+#13+ 
   '(:C_NUM_OFICINA, :N_NUM_YEAR, :N_NUM_ORDEN,'+#13+ 
   'RETURNING'+#13+  
   ' N_NUM_ORDEN'+#13+ 
   'INTO'+#13+ 
   ' :N_NUM_ORDEN';   // <= As to pass this data to a variable 

    fdm.OraSQL_liq.ParamByName('C_NUM_OFICINA').AsString  := num_oficina.text; 
    fdm.OraSQL_liq.ParamByName('N_NUM_YEAR').AsString     := Num_year.text; 
    fdm.OraSQL_liq.ParamByName('N_NUM_ORDEN').ParamType := ptInputOutput;
    fdm.OraSQL_liq.ParamByName('N_NUM_ORDEN').AsString    := Num_Orden.Text; 

    fdm.OraSQL_liq.prepared; 
    fdm.OraSQL_liq.execute; 

    Num_Orden.Text := fdm.OraSQL_liq.ParamByName('N_NUM_ORDEN').AsString; 

End;

I misunderstand you a little. I proposed you schema to show results in TDBGrid.

Code: Select all

begin
  SmartQuery.SQL := 'SELECT r.rowid, r.* FROM Desarrollo.registro r';
  SmartQuery.KeySecuence := 'DESARROLLO.TA_LIQ_NUM_ORDEN';
  SmartQuery.Keyfields := 'n_num_orden';
  SmartQuery.Refreshoptions = [roAfterInsert];
  SmartQuery.Open;
  SmartQuery.Append;
  SmartQuery.FieldByName('C_NUM_OFICINA').AsString  := num_oficina.text; 
  SmartQuery.FieldByName('N_NUM_YEAR').AsString     := Num_year.text; 
  SmartQuery.FieldByName('N_NUM_ORDEN').AsString    := Num_Orden.Text; 
  SmartQuery.Post;
end;

"Insert.." SQL is generated by Post command.