On Fri, Apr 19, 2013 at 9:22 AM, Marcos Douglas <m...@delfire.net> 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
>> ----------------------------------------------
>>
>>
>> The original program uses TADOStoredProc in Delphi 7,
>> which is very easy to handle stored procedure.
>> How to use it is like this:
>>
>> var
>> r,i: Integer;
>> DBP: TADOStoredProc;
>> begin
>> ...
>> DBP.ProcedureName:='IncValue';
>> DBP.Parameters.Refresh;
>> DBP.Parameters.ParamValues['@Param']:=3; //set @Param as 3
>> DBP.ExecProc;
>> r:=DBP.Parameters.ParamValues['@RETURN_VALUE']; //got r=100, OK!
>> i:=DBP.Parameters.ParamValues['@Param']; //got i=4, incremented OK!
>>
>>
>>
>> If you don't have TADOStored, still you can do the equivalent thing
>> by using TADOQuery. (a little tricky, though...)
>>
>> var
>> r,i: Integer;
>> DBQ: TADOQuery;
>> begin
>> ...
>> DBQ.SQL.Text:='IncValue';
>>
>> DBQ.Parameters.CreateParameter('@RETURN_VALUE',ftInteger,pdReturnValue,0,0);
>> DBQ.Parameters.CreateParameter('@Param',ftInteger,pdInputOutput,0,3);
>> //set @Param as 3
>> DBQ.Parameters.Command.CommandType:=cmdStoredProc;
>> DBQ.ExecSQL;
>> r:=DBQ.Parameters.ParamValues['@RETURN_VALUE']; //got r=100, OK!
>> i:=DBQ.Parameters.ParamValues['@Param']; //got i=4, incremented OK!
>>
>>
>>
>> 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
Sorry, I did not explained these:
DM.coRemote = DM is a DataModule and coRemote is a instance of
TghSQLConnector class.
FOwnerForm.MsgInfo = change this for ShowMessage function.
Best regards,
Marcos Douglas
_______________________________________________
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
