VB + SQL + Automation Calls

VB + SQL + Automation Calls

Post by dalr.. » Fri, 01 Oct 1999 04:00:00



I'm trying to implement a function in Visual Basic for Applciations
that uses Pmt and Rate to calculate APRs.  So I created a COM object
that does just that.  I can instantiate and call it from ASP, but I
can't seem to get it to work as an automation call in SQL.  We're
running a query that needs to calculate and select loans based upon
APR, and doing this calculation outside of SQL is proving to be a
serious performance penalty.  So I looked up what I needed to do, and
as far as I can tell I'm doing everything properly, but it just won't
work.  Here is the object, and some SQL code that calls it.  Notice the
only function that works in SQL is the testReturnCode(), however all
these functions work in ASP.

Any help is greatly appreciated!
Bryan

-- BEGIN CODE --

Public Function calcArmAPR(LoanAmount As Double, Interest As Double,
Points As Double, Costs As Double, Term As Integer, Years As Integer,
Index As Double, Margin As Double) As Double
    Dim newRate As Double
    Dim newAmount As Double
    Dim origPayment As Double
    Dim newPayment As Double
    Dim APR As Double

    If Not (IsNumeric(LoanAmount) And IsNumeric(Interest) And IsNumeric
(Points) And IsNumeric(Term) And IsNumeric(Years) And IsNumeric(Index)
And IsNumeric(Margin)) Then
        calcArmAPR = 0
        Exit Function
    End If

    newRate = Interest / 1200

    newAmount = LoanAmount - Costs - LoanAmount * (Points / 100)

    origPayment = Pmt(newRate, Term, -1 * LoanAmount, 0, 0)
    newPayment = Pmt((Index + Margin) / 1200, Term, -1 * LoanAmount, 0,
0)

    APR = Rate(Term, (((origPayment * Years * 12) + (newPayment * (Term
- (Years * 12)))) / 360), -1 * newAmount, 0, 1, newRate)

    calcArmAPR = APR * 1200
End Function

Public Function testReturnCode() As Integer
    testReturnCode = 77
End Function

Public Function testByRef(ByRef Val As Integer) As Integer
    Val = 1
    testByRef = 0
End Function

Public Function testCalculation(ByRef A As Integer, ByRef B As Integer,
ByRef Result As Integer) As Integer
    Result = A + B
    testCalculation = 0
End Function

And finally the SQL code:
DECLARE










SET NOCOUNT ON







        RETURN


1.0, 1000.0, 360, 7, 5.5, 2.0

        BEGIN


        END
ELSE




        BEGIN


        END
ELSE



        BEGIN


        END
ELSE



        BEGIN


        END
ELSE



        BEGIN


        END
ELSE


Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

VB + SQL + Automation Calls

Post by Michael Vlad » Sun, 19 Dec 1999 04:00:00


Hi Bryan,

What if you try to avoid all the hassle by packing the VB's function return
value into a String.
Then, in your T-SQL code you unpack it into Float and thats it.

Here is what I mean:

VB:
Public Function calcArmAPR (..) as String  ''''in place of  Double
 .
Dim sDoubl2Str$
sDouble2String = CStr (<Your Double value>)
calcArmAPR = sDouble2string
.

T-SQL:
.


1.0, 1000.0, 360, 7, 5.5, 2.0
. . . .

storage


Yes, there is some overhead involved, but you are now safe from any
conversion uncertainties and complexities- you are dealing with
plain-vanilla  zeroterminated string to which BSTR is coerced.

Michael.


> I'm trying to implement a function in Visual Basic for Applciations
> that uses Pmt and Rate to calculate APRs.  So I created a COM object
> that does just that.  I can instantiate and call it from ASP, but I
> can't seem to get it to work as an automation call in SQL.  We're
> running a query that needs to calculate and select loans based upon
> APR, and doing this calculation outside of SQL is proving to be a
> serious performance penalty.  So I looked up what I needed to do, and
> as far as I can tell I'm doing everything properly, but it just won't
> work.  Here is the object, and some SQL code that calls it.  Notice the
> only function that works in SQL is the testReturnCode(), however all
> these functions work in ASP.

> Any help is greatly appreciated!
> Bryan

> -- BEGIN CODE --

> Public Function calcArmAPR(LoanAmount As Double, Interest As Double,
> Points As Double, Costs As Double, Term As Integer, Years As Integer,
> Index As Double, Margin As Double) As Double
>     Dim newRate As Double
>     Dim newAmount As Double
>     Dim origPayment As Double
>     Dim newPayment As Double
>     Dim APR As Double

>     If Not (IsNumeric(LoanAmount) And IsNumeric(Interest) And IsNumeric
> (Points) And IsNumeric(Term) And IsNumeric(Years) And IsNumeric(Index)
> And IsNumeric(Margin)) Then
>         calcArmAPR = 0
>         Exit Function
>     End If

>     newRate = Interest / 1200

>     newAmount = LoanAmount - Costs - LoanAmount * (Points / 100)

>     origPayment = Pmt(newRate, Term, -1 * LoanAmount, 0, 0)
>     newPayment = Pmt((Index + Margin) / 1200, Term, -1 * LoanAmount, 0,
> 0)

>     APR = Rate(Term, (((origPayment * Years * 12) + (newPayment * (Term
> - (Years * 12)))) / 360), -1 * newAmount, 0, 1, newRate)

>     calcArmAPR = APR * 1200
> End Function

> Public Function testReturnCode() As Integer
>     testReturnCode = 77
> End Function

> Public Function testByRef(ByRef Val As Integer) As Integer
>     Val = 1
>     testByRef = 0
> End Function

> Public Function testCalculation(ByRef A As Integer, ByRef B As Integer,
> ByRef Result As Integer) As Integer
>     Result = A + B
>     testCalculation = 0
> End Function

> And finally the SQL code:
> DECLARE










> SET NOCOUNT ON







>         RETURN


> 1.0, 1000.0, 360, 7, 5.5, 2.0

>         BEGIN


>         END
> ELSE




>         BEGIN


>         END
> ELSE




>         BEGIN


>         END
> ELSE



>         BEGIN


>         END
> ELSE



>         BEGIN


>         END
> ELSE


> Sent via Deja.com http://www.deja.com/
> Before you buy.