FoxPro app calling SQL Server stored procedure returning an OUTPUT parameter

Question

I have a Fox Pro Application in which I'm having trouble getting the results of the OUTPUT parameter

I am trying to call it like this

 cSQL = "Exec CustomerKeyChecker @ldccode = " + ProgramValidation.LdcCode + " , @result=0" 

Thisform.displaycnk = thisform.oSql.SQLExec(cSql)

Problem is This.displaycnk is always True ...

I can call my stored procedure from SSMS like this

DECLARE @return_value int, @result int

EXEC @return_value = [dbo].[CustomerKeyChecker]
                            @ldccode = N'psnh',
                            @result = @result OUTPUT

SELECT @result as N'@result'
SELECT 'Return Value' = @return_value

Results are either a 0 or 1

Stored procedure looks like this:

ALTER PROCEDURE [dbo].[CustomerKeyChecker]
    @ldccode VARCHAR(12),
    @result INT OUTPUT
 .... 
   RETURN @result

So with Fox Pro, what am I doing wrong?

Answer 1

Adding another sample (as another message to prevent crowd in previous):

TEXT TO mySampleSP noshow
create procedure #myCustomerSummary 
  @customerId varchar(10), @firstDate datetime output, @lastDate datetime output, @ordercount integer output
as
  set nocount on
  if exists (select * from customers where customerID = @customerID)
        begin 
            select 
                @firstDate = min(orderdate), 
                @lastDate = max(orderdate), 
                @ordercount = count(orderid)
            from orders
            where customerid = @customerId
            return 1
        end
  else
    return 0
ENDTEXT

handle = SQLSTRINGCONNECT("Driver={SQL native client};"+;
 "server=.\sqlexpress;database=Northwind;trusted_connection=yes")

SQLEXEC(m.handle,m.mySampleSP)

lcSQL  = "{?@m.return = Call #MyCustomerSummary(?m.customer,?@m.first,?@m.last,?@m.orders)}"

return = 0
customer = 'BONAP'
first = DATETIME()
last = DATETIME()
orders = 0

IF SQLEXEC(handle,m.lcSQL) < 0
    AERROR(aWHy)
    DISPLAY MEMORY LIKE aWhy
ELSE
    ? 'Success'
    ? m.return, m.first, m.last, m.orders
ENDIF
SQLDISCONNECT(0)

Answer 2

Your "return_value" is not really a return value but a select result. A real return value would be returned by a return statement and for an SP call is always an integer.

Making that note aside, if you had really a return value, then with ODBC it would look like:

cSQL = "{?@m.returnvalue = CALL CustomerKeyChecker(?ProgramValidation.LdcCode, ?@m.result)}" 
LOCAL returnvalue,result, lnConnectionHandle
returnvalue = 0
result = 0

lnConnectionHandle = SQLSTRINGCONNECT( ... )
SQLEXEC(m.lnConnectionHandle, m.cSQL)
SQLDISCONNECT(0)
? m.returnvalue, m.result

Since in your case it is not a return value but a select result, you can add the 3rd parameter as a result cursor and check its first column:

SQLEXEC(m.lnConnectionHandle, m.cSQL, 'crsRet')
select crsRet
browse

And these are samples to do check output parameters and return value using OLEDB (from one of my posts on UT):

Here is a series of ways to do it. First for the below sample this is stored procedure (create in Northwind database):

CREATE PROCEDURE usp_test 
 @country varchar(10), @customers int output AS 
 select * from customers where country = @country
 set @customers = @@rowcount
 if ( @customers > 10 )
    return 33
 else
    return 22

(to get the return value recordset needs to be closed)

#DEFINE adInteger   3
#DEFINE adCurrency  6
#DEFINE adDate      7
#DEFINE adBoolean   11
#DEFINE adChar      129
#DEFINE adNumeric   131
#DEFINE adVarChar   200
#DEFINE adDBTimeStamp   135
#DEFINE adParamInput    1
#DEFINE adParamOutput   2
#define adParamReturnValue 4
#DEFINE adCmdStoredProc 4

clear
Local cn As ADODB.Connection,;
  cmd As ADODB.Command,;
  rs As ADODB.Recordset
cn  = CreateObject('ADODB.Connection')
cmd = CreateObject('ADODB.Command')
rs  = CreateObject('ADODB.Recordset')

cn.ConnectionString = "Provider = SQLOLEDB;"+;
 "Server=servername;Database=Northwind;Trusted_Connection=yes"
cn.Open

cmd.ActiveConnection = cn

* Way 1
cmd.CommandText = "usp_test"
cmd.CommandType = 4 && adCmdStoredProc

* Set up parameters
retParm = cmd.CreateParameter("Return", adInteger, adParamReturnValue)
inParm = cmd.CreateParameter("Input", adVarChar, adParamInput,10)
outParm = cmd.CreateParameter("Output", adInteger, adParamOutput)

cmd.Parameters.Append(retParm)
cmd.Parameters.Append(inParm)
cmd.Parameters.Append(outParm)

lcCountry = Trim(InputBox("Enter country:"))
inParm.Value = m.lcCountry

* Way 2
*!* cmd.CommandText = "usp_test"
*!* cmd.CommandType = 4 && adCmdStoredProc

*!* * Set up parameters asking backend
*!* cmd.Parameters.Refresh
*!*   
*!* lcCountry = Trim(InputBox("Enter country:"))
*!* cmd.Parameters("@country").Value = m.lcCountry

* Way 3
*!* cmd.CommandText = "{? = Call usp_Test(?,?)}"
*!* cmd.CommandType = 1 && text

*!* * Set up parameters
*!* retParm = cmd.CreateParameter("Return", adInteger, adParamReturnValue)
*!* inParm  = cmd.CreateParameter("Input", adVarChar, adParamInput,10)
*!* outParm = cmd.CreateParameter("Output", adInteger, adParamOutput)

*!* cmd.Parameters.Append(retParm)
*!* cmd.Parameters.Append(inParm)
*!* cmd.Parameters.Append(outParm)
*!*   
*!* lcCountry = Trim(InputBox("Enter country:"))
*!* inParm.Value = m.lcCountry

* Execute command
rs = cmd.Execute
* Show result
DO While !rs.EOF
    ? rs.Fields(0).Value
    rs.MoveNext
enddo

* Need to close recordset before getting return 
* and output parameters.
rs.Close

* check return values - 1 output and 1 return code
* For Way 1 and Way 3
? "Return code: ", Cmd.Parameters("Return").Value && or Parameters(0)
? "Total rows: ", Cmd.Parameters("OutPut").Value && or Parameters(2)

* For Way 2
*!* ? "Return code: ", Cmd.Parameters(0).Value && or Parameters(0)
*!* ? "Total rows: ", Cmd.Parameters("@customers").Value && or Parameters(2)

cn.Close

Please note that return value is always the first parameter -Parameters(0)-. In way 1 and 3 parameters append order exactly matches SPs definition (positional) - Parameters(0)=return value, Parameters(1) = first parameter, Parameters(2) = second parameter and so on. Name of the parameters has no importance. In way 2 instead of appending parameters we 'ask' it to the stored procedure (note that this would cause a trip to server) and use named parameters (names match to that of SP parameter names) OR you might use ordinals - Parameters(0),Parameters(1)...