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