I'm trying to get the result of a firebird stored procedure but it always returns 0. The stored procedure gives the correct result when called directly in IBExpert. Is there something that I am doing wrong?
var
Statement: IDBStatement;
ResultSet: IDBResultSet;
Params : TObjectList< TDBParam >;
SQLCommand : string;
begin
Result := 0;
SQLCommand := 'SELECT UNIT_PRICE FROM CALCULATE_UNIT_PRICE(:COMMODITY_ID, :ACCOUNT_ID)';
//SQLCommand := 'execute procedure CALCULATE_UNIT_PRICE(:COMMODITY_ID, :ACCOUNT_ID) returning_values :UNIT_PRICE';
Params := TObjectList< TDBParam >.Create;
try
Params.Add( TDBParam.Create( 'COMMODITY_ID', ftInteger, ACommodityid ) );
Params.Add( TDBParam.Create( 'ACCOUNT_ID', ftInteger, AAccountId ) );
Statement := TXDataOperationContext.Current.GetManager.Connection.CreateStatement;
Statement.SetSQLCommand( SQLCommand );
ResultSet := Statement.ExecuteQuery;
while ResultSet.Next do
Result := ResultSet.GetFieldValue( 'UNIT_PRICE' );
finally
Params.Free;
end;
end;