ADO and bind variables (was RE: Performance improvement required :-))

From: Pardee, Roy E <roy.e.pardee_at_lmco.com>
Date: Fri, 13 Jun 2003 11:22:31 -0700
Message-ID: <F001.005B18ED.20030613111019@fatcity.com>

This is interesting--if I use ADO with the ODBC provider (as the code does below), I get the same results. But if I use just ADO (that is, ms' OLE DB provider for oracle (MSDAORA.1)) then I don't get bind vars.

(I'm doing INSERTs in my code, not SELECTs).

I wonder if oracle's native OLE DB provider works any differently--I would bet that it does...

Cheers,

-Roy

Roy Pardee
Programmer/Analyst/DBA
SWFPAC Lockheed Martin IT
Extension 8487

-----Original Message-----
Sent: Friday, June 13, 2003 11:05 AM
To: Multiple recipients of list ORACLE-L

I'm sure you can. You should see it in an ODBC trace log, or you can use trace events on the database. Here's a really simplistic test I did to verify it. I ran this VB code that executes a really dumb query that could not have come from anywhere else - SELECT DUMMY FROM DUAL WHERE DUMMY = 'X' but passed 'X' as a bind variable

Private Sub Form_Load()

    Dim conn1 As New ADODB.Connection
    Dim cmd1 As New ADODB.Command
    Dim rs1 As New ADODB.Recordset
    Dim STRSQLSTRING As String
    Dim param1 As New Parameter

    strConnect = "UID=produser;PWD=prodpass;DSN=WAREHOUSE;"     STRSQLSTRING = "SELECT DUMMY FROM DUAL WHERE DUMMY = ?"          With conn1

        .ConnectionTimeout = 0
        .CommandTimeout = 0
        .CursorLocation = adUseClient
        .Mode = adModeRead
        .Open strConnect

    End With
    If Err.Number Then

      MsgBox Err.Number
      Exit Sub

    End If
    With cmd1

        .ActiveConnection = conn1
        .CommandText = STRSQLSTRING
        .CommandType = adCmdText
        Set param1 = .CreateParameter("DummyValue", adChar,
adParamInput, 1, "X")
        param1.Value = "X"
        .Parameters.Append param1
        Set rs1 = .Execute

    End With

    MsgBox rs1.Fields("DUMMY")

End Sub

Afterward, executed this on the database -

SQL> select sql_text from v$sqlarea where sql_text like 'SELECT DUMMY %';

SQL_TEXT

---

---

SELECT DUMMY FROM DUAL WHERE DUMMY = :1 It shows the parameter was definitely passed as a bind variable.

Check out this document on Metalink -
Retrieving Record Set from Stored Proc Using ADO and VB (SCR 782)

It appears to have an example of passing a cursor back to a recordset, though I've never tried it.

HTH. Beth

-----Original Message-----
Sent: Friday, June 13, 2003 1:20 PM
To: Multiple recipients of list ORACLE-L  

>
> Why can't you use bind variables? I thought using .Parameters method
> (property?) of ADODB.Command would use bind variables.
I thought it didn't. Any way of checking (other than to get the developers to try it?

>
> What function, and where can't you use it?
>

Stored Procedure type function (i.e. user-written) called from VB. 'Cos it's a Stored Procedure it will use bind variables, but you can't return a result set to VB.

Craig

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Seefelt, Beth
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Pardee, Roy E
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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 Fri Jun 13 2003 - 13:22:31 CDT