(2013/04/19 21:22), Marcos Douglas wrote:
On Fri, Apr 19, 2013 at 4:26 AM, Toru Takubo <tak...@e-parcel.co.jp> wrote:
Hi Dmitry,
Thank you very much for your advice. I am sure your
code should work, but unfortunately I can not modify
the existing stored procedures.
I will explain what I have been trying so far.
Suppose you are using the following stored procedure,
----------------------------------------------
CREATE procedure [dbo].[IncValue]
@Param int OUTPUT
AS
SET NOCOUNT ON
set @Param = @Param + 1
return 100
----------------------------------------------
(snip)
With analogical thinking, I expected that the following might work
for TSQLQuery in FPC:
var
r,i: Integer;
DBQ: TSQLQuery;
begin
...
DBQ.SQL.Text:='IncValue :@Param';
DBQ.Params.CreateParam(ftInteger,'@RETURN_VALUE',ptResult);
DBQ.Params.CreateParam(ftInteger,'@Param',ptInputOutput).AsInteger:=3;
DBQ.ExecSQL;
r:=DBQ.ParamByName('@RETURN_VALUE').AsInteger; //got r=0, NG
i:=DBQ.ParamByName('@Param').AsInteger; //got i=3, unchanged NG
It does not work as I expected.
I read fpc db tutorials and googled, but I could not find the answer for
this specific issue. Would anyone have an idea which of the following
is the truth?
1. My program is wrong or incomplete.
2. TSQLQuery (or TMSSQLConnection) does not support OUTPUT parameter.
3. TSQLQuery should be able to handle OUTPUT parameter, but currently a bug
exists.
Hi Toru,
You can use SQLdb, MSSQL, StoredProcs and OUTPUT params, without
problems... but you have to change a little bit your code.
1- Use a TSQLScript
2- Following your example above, use this SQL:
declare @param int = :param
exec IncValue @param output
select @param as value
3- Call Open instead of ExecSQL
You can try Greyhound[1] to abstract this. See the example:
procedure TForm1.ToolButton9Click(Sender: TObject);
var
SC: TghSQLClient;
DS: TDataSet;
begin
DS := nil;
SC := TghSQLClient.Create(DM.coRemote);
try
SC.Script.Text := 'declare @param int = :param '#13
+ 'exec IncValue @param output '#13
+ 'select @param as value';
SC.Params['param'].AsInteger := 3;
SC.IsBatch := True;
SC.Open(DS);
FOwnerForm.MsgInfo(DS.FieldByName('value').AsString);
finally
DS.Free;
SC.Free;
end;
end;
[1] https://github.com/mdbs99/Greyhound
(please, see the dev-0.1.6 branch for new features and changes)
Marcos Douglas
_______________________________________________
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Hi Marcos,
As to TSQLScript, it does not seem to have "Open" method. It may be
for execute multiple SQLs which does not return result set.
And I checked your library. It looks simple and nice. But your
suggestion is based on the same idea shown by Dimitry in previous
mail in this thread, which require modification of existing stored
procedures. Unfortunately, I can't modify them.
One more thing is that some procedures return both result set and
value through OUTPUT parameter. In this case, if I would retrieve
OUTPUT parameter as a result set, I must handle multiple result sets.
Thank you and all lists,
Toru
_______________________________________________
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
