| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Using bind variables with ADO in VB
I thought the .Prepared property of the command object was what you needed
to use bind vars from ADO.
Have a look at the following code, adapted from the ADO help entry for .Prepared. It purports to compare response times for prepared & unprepared versions of the same SQL command. In the couple of times I've run it, the prepared statement runs just a bit faster, for whatever that's worth. I'm not enough of a DBA to verify that it's using bind vars--but I bet one of you are.
The table the code inserts into is a single column table called Numbers.
' -------------------------------------------Public S u b PreparedX()
Dim Cnxn As ADODB.Connection Dim cmd1 As ADODB.Command Dim cmd2 As ADODB.Command
Dim strCnxn As String
Dim strCmd As String
Dim sngStart As Single
Dim sngEnd As Single
Dim sngNotPrepared As Single
Dim sngPrepared As Single
Dim intLoop As Integer
Dim strUserName As String
Dim strPwd As String
Dim strSID As String
strUserName = "scott"
strPwd = "tiger"
strSID = "devl"
' Open a connection
' strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial
Catalog=Pubs;User Id=sa;Password=; "
strCnxn = "Provider=MSDAORA.1;" _
& "User ID=" & strUserName & ";" _
& "Password=" & strPwd & ";" _
& "Data Source=" & strSID & ";" _
& "Persist Security Info=True"
Set Cnxn = New ADODB.Connection
' Create two command objects for the same
' command - one prepared and one not prepared
' strCmd = "SELECT title, type FROM Titles ORDER BY type"
' strCmd = "SELECT * FROM emp ORDER BY ename"
strCmd = "INSERT INTO numbers VALUES (?)"
Set cmd1 = New ADODB.Command
Set cmd1.ActiveConnection = Cnxn
' cmd1.CommandText = strCmd
Set cmd2 = New ADODB.Command
Set cmd2.ActiveConnection = Cnxn
cmd2.CommandText = strCmd
Set prm = cmd2.CreateParameter
With prm
.Name = "Val"
.Type = adNumeric
.Direction = adParamInput
' Set a timer, then execute the unprepared
' command 20 times
sngStart = Timer
For intLoop = 40 To 60
cmd1.CommandText = Replace(strCmd, "?", CStr(intLoop))
cmd1.Execute
' Reset the timer, then execute the prepared
' command 20 times
sngStart = Timer
For intLoop = 70 To 90
prm.Value = intLoop
cmd2.Execute
' Display performance results
MsgBox "Performance Results:" & vbCr & _
" Not Prepared: " & Format(sngNotPrepared, _
"##0.000") & " seconds" & vbCr & _
" Prepared: " & Format(sngPrepared, _
"##0.000") & " seconds"
' clean up
Cnxn.Close
Set Cnxn = Nothing
End Sub
' -------------------------------------------
(I ran this w/ADO 2.5, Net8 v.8.0.5, against an 8.0.6 db).
Cheers,
-Roy
Roy Pardee
Programmer/Analyst/DBA
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
Sent: Friday, June 06, 2003 9:00 AM
To: Multiple recipients of list ORACLE-L
I don't THINK so. To quote further "the parameters are not bound except
in the case of stored procedures...In the case of normal SQL, ADO
replaces the placeholder with Parameter.Value, so there is no binding on
the server side."
The way I read that, you can't use bind variables in VB using ADO.
Craig Healey
> -----Original Message----- > From: Stefan Jahnke [mailto:[EMAIL PROTECTED] > Sent: 06 June 2003 14:55 > To: Multiple recipients of list ORACLE-L > Subject: AW: Using bind variables with ADO in VB > > > Hi > > Question: Are you sure that the binding you're referring to (your book > excerpt) doesn't mean "early" bindig versus "late" binding in > VB using ADO ? > > Stefan > > > -----Original Message----- > Sent: Friday, June 06, 2003 12:35 PM > To: Multiple recipients of list ORACLE-L > > > Having read that bind variables will solve all our problems ;-) I've > tried to get the developers to use them. There response being: how? I > don't know much VB and they don't know what a bind variable is. So I > read with interest J Prem's question. Now knowing what to look for, I > read further, and found that "ADO also does not bind (compile > to p-code) > parameterized statements in Oracle" (VB Oracle 8 by Dov Trietsch) 8-( > Is it true? > Are bind variables that big a deal in VB code anyway? > We have a lot of badly written code, so are we better off > re-writing the > code without bothering about bind variables at the moment? > Am I worrying about nothing? > And no, it doesn't scale particularly well! > > Regards > > Craig Healey > ****************************************************************************
This email and any files transmitted with it are confidential and intended
solely
for the use of the individual or entity to whom they are addressed and may
contain
confidential and/or privileged material. Any review, retransmission,
dissemination
or other use of, or taking of any action in reliance upon, this information
by
persons or entities other than the intended recipient is prohibited.
Statements
and opinions expressed in this e-mail may not represent those of the
company.
If you have received this email in error please notify
[EMAIL PROTECTED]
This footnote also confirms that this email message has been swept by
MIMEsweeper
for the presence of computer viruses (www.mimesweeper.com)
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Healey 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 Mon Jun 09 2003 - 12:32:36 CDT
 |
 |