PDA

View Full Version : SQLGetArgument - Help requried



Peter Bragg
15-Mar-2005, 06:21 AM
Whether or not this is the correct newsgroup I don't know ... but ....

Can anyone help me out with an example on how I can get the value of a
stored procedure parameter that is marked for output? I have looked at the
help in the 'embedded SQL User's Guide.pdf' document that comes with vdf10.1
but still am none the wiser!

I have a Stored Procedure. I can set all the required arguments and execute
it successfully .. but seem unable to retreive the value of the output
parameter. I have tried various odds n' sods, but basically do the
following:

Send SQLSetProcedureName of q <procedure_name>

Send SQLSetArgument of q 1 <value>
Send SQLSetArgument of q 2 <value>

etc.

Send SQLSetArgument of q 19 <value>

Send SQLCall of q

If I then do something like

Get SQLGetArgument of q 1 to sValue, I can see the 'input' parameter value
for the 1st parameter ok.
The Output parameter is the 20th defined parameter, but coding "Get
SQLGetArgument of q 20 to sValue" simply returns a blank string.

I read in the help that the output params maybe returned in a different
results set, but I have tried various pieces of code calling "Get
SQLNextResultSet of q to iRetVal" and then using both SQLGetArgument AND
SQLGetArguments but have had no joy.

Clearly I am missing something obvious. But what? (VDF10.1 MSSQL 2000)

Peter Bragg

Ben Weijers
16-Mar-2005, 03:04 AM
I have tested this using the following procedure:

CREATE PROCEDURE [dbo].[TestOutarg]
@ArgOut int OUT
AS
set nocount on
select @ArgOut = count(*) from debtor
GO

and the following VDF code:

Procedure OnClick
Handle hoSQL
Handle hdbc
Handle hstmt
Integer iArgOut

Get Create U_cSQLHandleManager To hoSQL
If (hoSQL <> 0)Begin
Get SQLConnect Of hoSQL "MSSQLDRV"
"SERVER=(local);Trusted_Connection=yes;DATABASE=Geo rgeConvTest" To hdbc
If (hdbc <> 0) Begin
Get SQLOpen Of hdbc To hstmt
If (hstmt <> 0) Begin
Send SQLSetProcedurename Of hstmt "TestOutArg"
Send SQLCall Of hstmt
Get SQLGetArgument Of hstmt 1 To iArgOut
Showln "Argout = " iArgOut
End
Send SQLDisconnect Of hdbc
End
Send Destroy Of hoSQL
End
End_Procedure // OnClick

This works. I hope it helps you. BTW, you must set nocount in your stored
procedure.

Regards,

Ben Weijers
Data Access Worldwide

Peter Bragg
16-Mar-2005, 04:25 AM
Thank you Ben - "Set NoCount On". This was exactly all it needed. My Vdf
code was ok, but the SP was simply missing this line.

One little point though, I've just spent an entire working day on this - may
be it might be worth adding this little gem to the "embedded SQL User's
Guide.pdf" help document. There is no reference to "NoCount" in the help as
far as I can see, yet from experience I would say it is a rather important
setting!

Thanks again,

Peter Bragg


"Ben Weijers" <ben.weijers@dataaccess.nl> wrote in message
news:tE0Ea7fKFHA.4924@dacmail.dataaccess.com...
>
> I have tested this using the following procedure:
>
> CREATE PROCEDURE [dbo].[TestOutarg]
> @ArgOut int OUT
> AS
> set nocount on
> select @ArgOut = count(*) from debtor
> GO
>
> and the following VDF code:
>
> Procedure OnClick
> Handle hoSQL
> Handle hdbc
> Handle hstmt
> Integer iArgOut
>
> Get Create U_cSQLHandleManager To hoSQL
> If (hoSQL <> 0)Begin
> Get SQLConnect Of hoSQL "MSSQLDRV"
> "SERVER=(local);Trusted_Connection=yes;DATABASE=Geo rgeConvTest" To hdbc
> If (hdbc <> 0) Begin
> Get SQLOpen Of hdbc To hstmt
> If (hstmt <> 0) Begin
> Send SQLSetProcedurename Of hstmt "TestOutArg"
> Send SQLCall Of hstmt
> Get SQLGetArgument Of hstmt 1 To iArgOut
> Showln "Argout = " iArgOut
> End
> Send SQLDisconnect Of hdbc
> End
> Send Destroy Of hoSQL
> End
> End_Procedure // OnClick
>
> This works. I hope it helps you. BTW, you must set nocount in your stored
> procedure.
>
> Regards,
>
> Ben Weijers
> Data Access Worldwide
>
>

Peter Bragg
16-Mar-2005, 04:34 AM
..... quick amendment ... when I refer to not seeing "NoCount" referenced in
"Embedded SQL User's Guide", I do in fact mean the "MSSQL CK User's Guide".

Peter Bragg


"Ben Weijers" <ben.weijers@dataaccess.nl> wrote in message
news:tE0Ea7fKFHA.4924@dacmail.dataaccess.com...
>
> I have tested this using the following procedure:
>
> CREATE PROCEDURE [dbo].[TestOutarg]
> @ArgOut int OUT
> AS
> set nocount on
> select @ArgOut = count(*) from debtor
> GO
>
> and the following VDF code:
>
> Procedure OnClick
> Handle hoSQL
> Handle hdbc
> Handle hstmt
> Integer iArgOut
>
> Get Create U_cSQLHandleManager To hoSQL
> If (hoSQL <> 0)Begin
> Get SQLConnect Of hoSQL "MSSQLDRV"
> "SERVER=(local);Trusted_Connection=yes;DATABASE=Geo rgeConvTest" To hdbc
> If (hdbc <> 0) Begin
> Get SQLOpen Of hdbc To hstmt
> If (hstmt <> 0) Begin
> Send SQLSetProcedurename Of hstmt "TestOutArg"
> Send SQLCall Of hstmt
> Get SQLGetArgument Of hstmt 1 To iArgOut
> Showln "Argout = " iArgOut
> End
> Send SQLDisconnect Of hdbc
> End
> Send Destroy Of hoSQL
> End
> End_Procedure // OnClick
>
> This works. I hope it helps you. BTW, you must set nocount in your stored
> procedure.
>
> Regards,
>
> Ben Weijers
> Data Access Worldwide
>
>