[fpc-pascal] How can retrive value from OUT parameter?
takubo at e-parcel.co.jp
Mon Apr 22 03:05:36 CEST 2013
(2013/04/20 23:06), Marcos Douglas wrote:
> On Sat, Apr 20, 2013 at 2:47 AM, Toru Takubo <takubo at e-parcel.co.jp> wrote:
>> (2013/04/19 21:22), Marcos Douglas wrote:
>>>> With analogical thinking, I expected that the following might work
>>>> for TSQLQuery in FPC:
>>>> r,i: Integer;
>>>> DBQ: TSQLQuery;
>>>> DBQ.SQL.Text:='IncValue :@Param';
>>>> 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
>>> 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 to abstract this. See the example:
>> 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.
> You right, sorry...
>> 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.
> (Thank you)
> Not so fast. I used your SP, without changes!
> The tip:
> For MSSQL, the TStatementType need to be [stExecProcedure]. The
> StrToStatementType method is protected so, you need to code a new
> class... or use Greyhound.
> Did you test the code as I sent before?
> Take this example:
> ...and modify it to use the code I sent before.
>> 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.
> I never tried to use a result set as output parameter before. But you
> can use another SP to call the "real SP" just for the application. ;-)
> Marcos Douglas
> fpc-pascal maillist - fpc-pascal at lists.freepascal.org
Thank you again for your advice. Now I understand what you mean.
Actually I made a descendant class of TSQLQuery, which override
StrToStatementType to force TStatementType on stExecPrcedure, but
it did not work then. Your suggestion is that in addition to it,
I should create a small sub-query to get OUTPUT parameter as result set.
I will try to proceed. On the other hand, I'm wondering if TSQLQuery
(or TMSSQLConnection) would natively support OUTPUT parameter and
More information about the fpc-pascal