How to change VB code to use bind variables?

From: Miller, Jay <JayMiller_at_TDWaterhouse.com>
Date: Tue, 16 Jul 2002 13:08:23 -0800
Message-ID: <F001.004999AB.20020716130823@fatcity.com>

Hi,

In checking my v$SQLArea I recently discovered that there was an often executed SQL statement that was not using bind variables. I tracked it down to a VB front end and asked the development group to modify the code. They asked me how to do it.

Not being a VB programmer I said I had no idea but would try to find out. Here's an abbreviated version of the code. It's the sAcctId that seems to be causing the multiple executions. Anyone know how this can be rewritten to use bind variables? Can it be done from VB or do we need to transfer the code to PL/SQL on the back end?

Private Function GetSQL(ByVal sKey As String, _

                        ByVal sAcctId As String, _
                        Optional ByVal sAcctNum As String, _
                        Optional ByVal sWhereAnd As String)

Dim sConnect As String
Dim sSql As String
Dim oWsiLibData As WSILib.CDisplayData
Dim rsCustomer As ADODB.Recordset
Dim lCustId As Long
Dim sCustId As String

Select Case sKey
.......
.......
Case "accountinfo":

        sSql = "SELECT a.id, a.account_no, a.type, a.title1, a.title2, a.title3, " & _

"a.inventory_date date_opened, a.last_modified_date
last_change, " & _

"rc.description rebatecmsn, " & _
            sSql = sSql & " account_info.fetch_email(" & sAcctId & ") email "

            sSql = sSql & "FROM account a, status s, usa_wh_tax u, fund f, " & _

"stock_commission_codes sc, cmsn_rebate_codes rc,
account_kind ak "

            sSql = sSql & ", dual " 
            sSql = sSql & "WHERE a.status_id=s.id and
a.usa_tax_code=u.code(+) " & _


                    "and a.fund_id=f.id and " & _

                    "a.s_stk_cmsn_code=sc.code(+) and

a.cmsn_reb_code=rc.code(+) and a.kind_id=ak.id " & _


                    "and a.id =" & sAcctId


            sConnect = sConnectWSI

            GetSQL = Array(sConnect, sSql)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Miller, Jay
  INET: JayMiller_at_TDWaterhouse.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Jul 16 2002 - 16:08:23 CDT