topic-37565

Posts: 400

Joined: Fri May 11, 2007 08:20 PM

invoke sql functions with ado
Posted: Wed Jul 31, 2019 04:51 PM

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

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 (fw): Select all Collapse

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 (fw): Select all Collapse

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:

fwh 17.12, harbour 3.2.0, pelles C, bcc7, Ms-Sql

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: invoke sql functions with ado
Posted: Thu Aug 01, 2019 12:39 AM

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

Regards

G. N. Rao.

Hyderabad, India

artu01

Posts: 400

Joined: Fri May 11, 2007 08:20 PM

Re: invoke sql functions with ado
Posted: Fri Aug 02, 2019 12:24 AM

Thank master rao!
its working now

Code (fw): Select all Collapse

    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

fwh 17.12, harbour 3.2.0, pelles C, bcc7, Ms-Sql

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: invoke sql functions with ado
Posted: Fri Aug 02, 2019 02:58 AM

With later versions of FWH

Code (fw): Select all Collapse

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

Regards

G. N. Rao.

Hyderabad, India

FiveTech Support Forums

Continue the discussion