Devart Forums

Posted: **Tue 04 Jan 2011 19:19**

Using: Firebird 2.5, Delphi 2010, Unidac 3.50.0.14

The problem:

I am using UniStoredProc to do a Select for data from a table.

I want to do editing also, so I set the SQLDelete, SQLInsert and SQLUpdate property values to the name of corresponding stored procedures for delete, insert and update.

I have a difficulty: After an insert is posted, I need the value of the new row id (which is generated by table BEFORE INSERT trigger). I need this value to be used for Master-Detail of other tables in the same form, so I would prefer if the stored procedure dataset's newly inserted rows field value could be updated with the new ROWID value. If this is not possible, at the very least I need to be able to store the new ROWID value in a variable.

StoredProcName property:

Code: Select all

StoredProcName := 'SPPERSON_S';

Select stored procedure :

Code: Select all

CREATE PROCEDURE SPPERSON_S
RETURNS(
 ROWID INTEGER,
 LNAME VARCHAR(64)
)
AS
BEGIN
  FOR
    SELECT
     ROWID, 
     LNAME
    FROM TBLPERSON
    INTO
      :ROWID,
      :LNAME
  DO
    BEGIN
      SUSPEND;
    END
END;

InsertSQL property:

Code: Select all

EXECUTE PROCEDURE SPPERSON_I(:LNAME);

Insert stored procedure:

Code: Select all

CREATE PROCEDURE SPPERSON_I(
  LNAME VARCHAR(64) CHARACTER SET UTF8 COLLATE UTF8,
 RETURNS(
  ROWID INTEGER)
AS
BEGIN
 INSERT INTO TBLPERSON  (
    LNAME
)
  VALUES (
    :LNAME) RETURNING ROWID  INTO :ROWID ;
     
END;

Now, after an insert, I want the new value from ROWID.

How to get this new value?

In AfterUpdateExecute event handler, Params property does not contain ROWID as a parameter; it only has LNAME.

How can I achieve this?

Posted: **Wed 05 Jan 2011 15:57**

Hello,

When you call a stored procedure with the Execute statement, you should manually add output parameters using the BeforeUpdateExecute event. Here is an example:

Code: Select all

procedure TMainForm.UniStoredProcBeforeUpdateExecute(Sender: TDataSet;
  StatementTypes: TStatementTypes; Params: TDAParams);
var
  pr: TUniParam;
begin
  pr := TUniParam.Create(Params);
  pr.Name := 'ROWID';
  pr.DataType := ftInteger;
  pr.ParamType := ptOutput;
  Params.AddParam(pr); 
end;

Also you should set the TUniStoredProc.ReturnParams property to True. When ReturnParams is True, OUT parameters of the SQLInsert and SQLUpdate statements are assigned to the corresponding fields.

Posted: **Wed 05 Jan 2011 15:58**

There is a problem in UniDAC 3.50.0.14 that will not allow to use this functionality. We have fixed this problem. This fix will be included in the next UniDAC build.

Posted: **Mon 22 Apr 2013 13:08**

Today I got a chance to test the solution presented, and confirm that it works.

The OUT parameter value of the INSERT statement is assigned to the field with the same name of the SELECT stored procedure.

Thanks.

Posted: **Mon 22 Apr 2013 15:27**

Thank you for your confirmation. If any other questions come up, please contact us.

Devart Forums

Using UniStoredProc with SQLInsert set to insert stored procedure, how to get new rowid?

Using UniStoredProc with SQLInsert set to insert stored procedure, how to get new rowid?

Re: Using UniStoredProc with SQLInsert set to insert stored procedure, how to get new rowid?

Re: Using UniStoredProc with SQLInsert set to insert stored procedure, how to get new rowid?