TUniStoredProc ReturnParams not working

[stevel]

Using:
Delphi XE2 Enterprise Update 4 HotFix 1
UniDAC 5.5.11
Firebird 2.5.3

There is a TUniStoredProc with:
SQLInsert and SQLUpdate properties are set,
SQLInsert points to a Firebird stored procedure which returns a parameter,
BeforeUpdating event handler has code to add a new output parameter with the same name as in the database Insert stored procedure,
ReturnParams is set to True,
StrictUpdate is set to False.

However after an insert is done on the stored procedure dataset, the output parameter is not found in the list.

Method of doing the insert:
The TUniStoredProc is open in browse mode at runtime, then an Insert is called, field values are set and then Post is called.

The problem is that the returned output parameter is not found in the Params collection ie.

Code: Select all

 Params.FindParam('OROW_ID') = nil  //is true.. ! 

The TUniStoredProc SQL property (via Stored Procedure editor):

Code: Select all

EXECUTE PROCEDURE PATIENT_SEL(:IROW_ID)

The TUniStoredProc SQLInsert property:

Code: Select all

EXECUTE PROCEDURE PATIENT_I(:ROW_ID, :FNAME, :LNAME, :IDTYPE, :IDNO, 
:HOSPIDNO, :DOB, :GENDER, :REGDATE, :ADDR1, :ADDR2, :ADDR3, :CITY, 
:PROVINCE, :ADMINZONE, :LASTVSTDATE)

The Firebird Insert stored procedure:

Code: Select all

CREATE PROCEDURE PATIENT_I (
  ROW_ID Integer, 
  FNAME VarChar(80), 
  LNAME VarChar(80), 
  IDTYPE Integer, 
  IDNO VarChar(48), 
  HOSPIDNO VarChar(48), 
  DOB Date, 
  GENDER VarChar(1), 
  REGDATE Date, 
  ADDR1 VarChar(100), 
  ADDR2 VarChar(100), 
  ADDR3 VarChar(100), 
  CITY VarChar(100), 
  PROVINCE VarChar(100), 
  ADMINZONE VarChar(100), 
  LASTVSTDATE Timestamp)
 returns (
  OROW_ID Integer) AS 
BEGIN
  INSERT INTO PATIENT (
    FNAME,
    LNAME,
    IDTYPE,
    IDNO,
    HOSPIDNO,
    DOB,
    GENDER,
    REGDATE,
    ADDR1,
    ADDR2,
    ADDR3,
    CITY,
    PROVINCE,
    ADMINZONE,
    LASTVSTDATE)
  VALUES (
    :FNAME,
    :LNAME,
    :IDTYPE,
    :IDNO,
    :HOSPIDNO,
    :DOB,
    :GENDER,
    :REGDATE,
    :ADDR1,
    :ADDR2,
    :ADDR3,
    :CITY,
    :PROVINCE,
    :ADMINZONE,
    :LASTVSTDATE) RETURNING ROW_ID INTO :OROW_ID; 
  

The Firebird table DDL:

Code: Select all

CREATE TABLE PATIENT 
(
  ROW_ID                   INTEGER         NOT NULL,
  FNAME                    VARCHAR(    80)  COLLATE UTF8,
  LNAME                    VARCHAR(    80)  COLLATE UTF8,
  IDTYPE                   INTEGER,
  IDNO                     VARCHAR(    48)  COLLATE UTF8,
  HOSPIDNO                 VARCHAR(    48)  COLLATE UTF8,
  DOB                         DATE,
  GENDER                   VARCHAR(     1)  COLLATE UTF8,
  REGDATE                     DATE,
  ADDR1                    VARCHAR(   100)  COLLATE UTF8,
  ADDR2                    VARCHAR(   100)  COLLATE UTF8,
  ADDR3                    VARCHAR(   100)  COLLATE UTF8,
  CITY                     VARCHAR(   100)  COLLATE UTF8,
  PROVINCE                 VARCHAR(   100)  COLLATE UTF8,
  ADMINZONE                VARCHAR(   100)  COLLATE UTF8,
  LASTVSTDATE            TIMESTAMP,
 CONSTRAINT PK_PATIENT PRIMARY KEY (ROW_ID)
);

The TUniStoredProc BeforeUpdateExecute event Delphi code:

Code: Select all

procedure Tdatamod.uspPATIENT_SELBeforeUpdateExecute(Sender: TDataSet; StatementTypes: TStatementTypes;
  Params: TDAParams);
begin
  if stInsert in StatementTypes then
  begin
    Params.Add;
    Params[Params.Count - 1].Name := 'OROW_ID';
    Params[Params.Count - 1].ParamType := ptOutput;
    Params[Params.Count - 1].DataType := ftInteger;
  end;
end;

I would be glad if you could tell me how to resolve the error (or investigate if its a bug).

Thanks in advance.


--
Steve Faleiro

[ViktorV]

To investigate the problem, a script for creating the PATIENT_SEL procedure is missing.
Please write it, or better, please send a small sample to viktorv*devart*com to demonstrate the issue, including a script to create database objects.

[stevel]

Hi ViktorV,

Procedure PATIENT_SEL in the Firebird database:

Code: Select all

CREATE PROCEDURE PATIENT_SEL (
  IROW_ID Integer)
 returns (
  ROW_ID Integer, 
  FNAME VarChar(80), 
  LNAME VarChar(80), 
  IDTYPE Integer, 
  IDNO VarChar(48), 
  HOSPIDNO VarChar(48), 
  DOB Date, 
  GENDER VarChar(1), 
  REGDATE Date, 
  ADDR1 VarChar(100), 
  ADDR2 VarChar(100), 
  ADDR3 VarChar(100), 
  CITY VarChar(100), 
  PROVINCE VarChar(100), 
  ADMINZONE VarChar(100), 
  LASTVSTDATE Timestamp)
AS
BEGIN
  FOR SELECT 
    ROW_ID,
    FNAME,
    LNAME,
    IDTYPE,
    IDNO,
    HOSPIDNO,
    DOB,
    GENDER,
    REGDATE,
    ADDR1,
    ADDR2,
    ADDR3,
    CITY,
    PROVINCE,
    ADMINZONE,
    LASTVSTDATE
  FROM PATIENT  
  WHERE (ROW_ID = :IROW_ID)
  INTO 
    :ROW_ID,
    :FNAME,
    :LNAME,
    :IDTYPE,
    :IDNO,
    :HOSPIDNO,
    :DOB,
    :GENDER,
    :REGDATE,
    :ADDR1,
    :ADDR2,
    :ADDR3,
    :CITY,
    :PROVINCE,
    :ADMINZONE,
    :LASTVSTDATE
  DO BEGIN
       SUSPEND;
     END
END

--
Steve Faleiro

[ViktorV]

The same question has already been discussed on our forum. Follow the link http://forums.devart.com/viewtopic.php?t=19905 for details.

[stevel]

My configuration is the same as in that post but it is not working.

Did you see whether I did anything wrong in my code / configuration?

[ViktorV]

Unfortunately, we could not reproduce the issue.
Please send a small sample to viktorv*devart*com to demonstrate the issue, including a script to create database objects

[stevel]

Hi ViktorV,

I found a way to resolve the issue for my purpose.

Use the AfterUpdateExecute event handler to assign the output parameter value to the primary key field.

Code follows:

Code: Select all

procedure TForm1.uspPATIENT_SELAfterUpdateExecute(Sender: TDataSet; StatementTypes: TStatementTypes; Params: TDAParams);
begin
  if stInsert in StatementTypes then
  begin
    Sender.FieldByName('ROW_ID').AsInteger := Params.ParamByName('OROW_ID').AsInteger;
  end;

end;

It works perfectly! Thanks for your assistance in helping me to resolve the issue.


--
Steve Faleiro

[ViktorV]

It is good to see that the problem has been solved. If any questions concerning our products come up, please contact us.