(2013/04/20 23:06), Marcos Douglas wrote:
On Sat, Apr 20, 2013 at 2:47 AM, Toru Takubo <tak...@e-parcel.co.jp> wrote:
(2013/04/19 21:22), Marcos Douglas wrote:
(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:
(snip)
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:
https://github.com/mdbs99/Greyhound/blob/master/src/gh_sqldblib.pas#L115
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:
https://github.com/mdbs99/Greyhound/blob/master/examples/mssql/t1.pas
...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. ;-)
Regards,
Marcos Douglas
_______________________________________________
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
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
RETURN_VALUE...
Toru
_______________________________________________
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
