invoke sql functions with ado

[artu01]

Guys;
It isnt working this sentence and also i want to capture the result of the function in fwh
sql;

Code: Select all  Expand view

CREATE FUNCTION [dbo].[CalcSaldo]
(
  @Codigo varchar(8), @nMes int, @codusu char(2)
)
RETURNS int
AS
BEGIN
  -- Declare the return variable here
  DECLARE @nResul int

  -- Consigo el stock actual
  Set @nResul = (dbo.CalcIngAno(@Codigo, @nMes,@codusu) - dbo.CalcSalAno(@Codigo, @nMes, @codusu)) + (dbo.CalcIngMes(@Codigo, @nMes, @codusu) - dbo.calcSalMes(@Codigo, @nMes, @codusu))
  RETURN @nResul

END
GO
 

fwh code

Code: Select all  Expand view

oCon1:=AbreConexBD()

oCon1:Execute( "CALL CalcSaldo('T25CD', '01', '00' )" )


Function AbreConexBD()
  LOCAL cCString, oError //, oRS, lRS := .f.

  cCString := "Provider=SQLOLEDB;"
  cCString += "Server=PYSASERVER;"
  cCString += "Database=PysaBD;Uid=sa;Pwd=Pysa123456;"
  TRY
    oCon1 := CreateObject( "ADODB.Connection" )
    oCon1:Open( cCString )
  CATCH oError
     MsgStop( oError:Description )
     //RETURN
  END
Return Nil
 

this error results me

Code: Select all  Expand view

Error description: (DOS Error -2147352567) WINOLE/1007  Sintaxis incorrecta cerca de 'T25CD'. (0x80040E14): Microsoft OLE DB Provider for SQL Server
   Args:
     [   1] = C   CALL CalcSaldo('T25CD', '01', '00' )
 

sql:

[nageswaragunupudi]

Open recordset with sql as "SELECT functioname(param1,param2,...)"
Then,
result := oRs:Fields(0):Value

[artu01]

Thank master rao!
its working now

Code: Select all  Expand view

    cSql1:="SELECT dbo.CalcSaldo('T25CD' ,1, '00')"
    TRY
      oRsr := TOleAuto():New( "ADODB.RecordSet" )
      WITH OBJECT oRsr
        :ActiveConnection := oCon1
        :Source             := cSql1
        :CursorLocation     := adUseClient
        :CursorType         := adOpenStatic
        :LockType           := adLockOptimistic
        :Open()
      END
    CATCH oError
      MsgStop( oError:Description )
    END
    nResult :=oRsr:Fields(0):Value

 

[nageswaragunupudi]

With later versions of FWH

Code: Select all  Expand view

nResult := FW_AdoQueryResult( "SELECT dbo.CalcSaldo('T25CD' ,1, '00')", oCon1 )