This is something that used to work earlier, but its not working as expected now. I have a TUniStoredProc with a SELECT stored procedure. An insert stored procedure is set on the SQLInsert property (using StoredProc Call Generator). There is a BeforeUpdateExecute event with code to add the parameter to get the ROW_ID auto-increment field after a new record is inserted and posted.
The problem: It works correctly only the first insert, returning the inserted ROW_ID correctly. After that for subsequent inserts, it returns 0. If the program is closed and started again, then again it works correctly for the first insert, and subsequent inserts return 0.
Code for the database objects and the program code follows:
SALE table:
Code: Select all
CREATE TABLE SALE
(
ROW_ID INTEGER NOT NULL,
SALE_DATE DATE NOT NULL,
TERMINAL_ID INTEGER NOT NULL,
GROSS_AMT NUMERIC( 12, 2) DEFAULT 0 NOT NULL,
VAT_AMT NUMERIC( 12, 2) DEFAULT 0 NOT NULL,
NET_AMT NUMERIC( 12, 2) DEFAULT 0 NOT NULL,
CONSTRAINT PK_SALE PRIMARY KEY (ROW_ID)
);
SET TERM ^^ ;
CREATE TRIGGER SALE_ID FOR SALE ACTIVE BEFORE INSERT POSITION 0 AS
begin
if ( (new.ROW_ID is null) or (new.ROW_ID = 0) )
then new.ROW_ID = gen_id(SALE_GEN, 1);
end ^^
SET TERM ; ^^
Code: Select all
SET TERM ^^ ;
CREATE PROCEDURE SALE_SEL (
IROW_ID Integer)
returns (
ROW_ID Integer,
SALE_DATE Date,
TERMINAL_ID Integer,
GROSS_AMT Numeric(12,2),
VAT_AMT Numeric(12,2),
NET_AMT Numeric(12,2))
AS
BEGIN
FOR SELECT
ROW_ID,
SALE_DATE,
TERMINAL_ID,
GROSS_AMT,
VAT_AMT,
NET_AMT
FROM SALE
WHERE (ROW_ID = :IROW_ID)
INTO
:ROW_ID,
:SALE_DATE,
:TERMINAL_ID,
:GROSS_AMT,
:VAT_AMT,
:NET_AMT
DO BEGIN
SUSPEND;
END
END ^^
SET TERM ; ^^
Code: Select all
SET TERM ^^ ;
CREATE PROCEDURE SALE_I (
SALE_DATE Date,
TERMINAL_ID Integer,
GROSS_AMT Numeric(12,2),
VAT_AMT Numeric(12,2),
NET_AMT Numeric(12,2))
returns (
ROW_ID Integer)
AS
BEGIN
INSERT INTO SALE (
SALE_DATE,
TERMINAL_ID,
GROSS_AMT,
VAT_AMT,
NET_AMT)
VALUES (
:SALE_DATE,
:TERMINAL_ID,
:GROSS_AMT,
:VAT_AMT,
:NET_AMT) RETURNING ROW_ID INTO :ROW_ID;
END ^^
SET TERM ; ^^
Code: Select all
object uspSALE_SEL: TUniStoredProc
StoredProcName = 'SALE_SEL'
SQL.Strings = (
'EXECUTE PROCEDURE SALE_SEL(:IROW_ID)')
SQLInsert.Strings = (
'EXECUTE PROCEDURE SALE_I(:SALE_DATE, :TERMINAL_ID, :GROSS_AMT, :' +
'VAT_AMT, :NET_AMT)')
Connection = unicon1
Options.StrictUpdate = False
Options.ReturnParams = True
BeforeUpdateExecute = uspSALE_SELBeforeUpdateExecute
Left = 64
Top = 296
ParamData = <
item
DataType = ftInteger
Name = 'IROW_ID'
ParamType = ptInput
Value = nil
end
item
DataType = ftInteger
Name = 'ROW_ID'
ParamType = ptOutput
Value = nil
end
item
DataType = ftDate
Name = 'SALE_DATE'
ParamType = ptOutput
Value = nil
end
item
DataType = ftInteger
Name = 'TERMINAL_ID'
ParamType = ptOutput
Value = nil
end
item
DataType = ftFloat
Name = 'GROSS_AMT'
ParamType = ptOutput
Value = nil
end
item
DataType = ftFloat
Name = 'VAT_AMT'
ParamType = ptOutput
Value = nil
end
item
DataType = ftFloat
Name = 'NET_AMT'
ParamType = ptOutput
Value = nil
end>
CommandStoredProcName = 'SALE_SEL'
object uspSALE_SELROW_ID: TIntegerField
FieldName = 'ROW_ID'
end
object uspSALE_SELSALE_DATE: TDateField
FieldName = 'SALE_DATE'
end
object uspSALE_SELTERMINAL_ID: TIntegerField
FieldName = 'TERMINAL_ID'
end
object uspSALE_SELGROSS_AMT: TFloatField
FieldName = 'GROSS_AMT'
end
object uspSALE_SELVAT_AMT: TFloatField
FieldName = 'VAT_AMT'
end
object uspSALE_SELNET_AMT: TFloatField
FieldName = 'NET_AMT'
end
end
Code: Select all
procedure Tdatamod.uspSALE_SELBeforeUpdateExecute(Sender: TDataSet; StatementTypes: TStatementTypes; Params: TDAParams);
var
pr: TUniParam;
begin
if (stInsert in StatementTypes) then
begin beep;
pr := TUniParam.Create(Params);
pr.Name := 'ROW_ID';
pr.DataType := ftInteger;
pr.ParamType := ptOutput;
Params.AddParam(pr);
end;
end;
Code: Select all
function TfmSale.SaveSale: Integer;
var
p: Integer;
begin
// :IROW_ID
// :SALE_DATE, :TERMINAL_ID, :GROSS_AMT, :VAT_AMT, :NET_AMT
datamod.uspSALE_SEL.Close;
datamod.uspSALE_SEL.ParamByName('IROW_ID').AsInteger := -1;
datamod.uspSALE_SEL.PrepareSQL(True);
datamod.uspSALE_SEL.Open;
datamod.uspSALE_SEL.Append;
datamod.uspSALE_SELSALE_DATE.AsDateTime := deSaleDt.Date;
datamod.uspSALE_SELTERMINAL_ID.AsInteger := datamod.FCurrentTerminalID;
datamod.uspSALE_SELGROSS_AMT.AsCurrency := FSaleTotal_Gross;
datamod.uspSALE_SELVAT_AMT.AsCurrency := FSaleTotal_VAT;
datamod.uspSALE_SELNET_AMT.AsCurrency := FSaleTotal_Net;
datamod.uspSALE_SEL.Post;
p := datamod.uspSALE_SELROW_ID.AsInteger;
ShowMessage(inttostr(p)); // <----- PROBLEM: Returns 0 when run the 2nd time..
cdsSale.First;
while not cdsSale.Eof do
begin
// :SALE_ID, :PRODUCT_ID, :QTY, :PRICE, :TOTAL
datamod.uspSALE_DTL_I.ParamByName('SALE_ID').AsInteger := p;
datamod.uspSALE_DTL_I.ParamByName('PRODUCT_ID').AsInteger := cdsSalePRODUCT_ID.AsInteger;
datamod.uspSALE_DTL_I.ParamByName('QTY').AsInteger := cdsSaleQTY.AsInteger;
datamod.uspSALE_DTL_I.ParamByName('PRICE').AsCurrency := cdsSalePRICE.AsCurrency;
datamod.uspSALE_DTL_I.ParamByName('TOTAL').AsCurrency := cdsSaleTOTAL.AsCurrency;
datamod.uspSALE_DTL_I.Prepared := True;
datamod.uspSALE_DTL_I.Execute;
cdsSale.Next;
end;
Result := p;
end;
