VB + SQL + Automation Calls 
Author Message
 VB + SQL + Automation Calls

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.*-*-*.com/
Before you buy.



Mon, 18 Mar 2002 03:00:00 GMT  
 VB + SQL + Automation Calls
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.

Quote:

> 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.



Wed, 05 Jun 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Automation Error during Remote Procedure Call (RPC) using DCOM and Automation Manager

2. SQL procedure call from VB with PL/SQL table (with two fields) as agrument

3. Automation error (-2147217911) SQL-DMO / VB

4. Read query SQL through VB call

5. vb.net calls sql server 2000 user defined function

6. Problems with SQL Insert command called from VB

7. How to call a SP on SQL Server 7 from VB

8. Creating views with an SQL call within VB

9. VB with BeginTrans using native ODBCAPI calls to SQL Server 6.0

10. Tracing VB/QDBC SQL CALLS??

11. Calling Oracle SQL Stored Procedures from VB

12. Calling a VB component from a SQL Server trigger

 

 
Powered by phpBB® Forum Software