Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

How do I use Oracle bind variables using ADO in Visual Basic?

196 views
Skip to first unread message

Bob Burgess

unread,
May 8, 2002, 11:27:42 AM5/8/02
to
We are using VB through ADO & OLEDB with Oracle 8i. Right now, we're
just sending hard-coded SQLs (ie. SELECT x FROM y WHERE z = 5) to the
Execute method of the ADODB Connection object and getting the results
from the Recordset object.
We'd MUCH rather use bind variables in cases like this.

I'd rather not rewrite the database layer to use OO4O, if at all
possible. If there a way to use bind variables (ie. SELECT x FROM y
WHERE z = :v) in ADO?

Thanks for any input.

Bob Burgess
Montreal, Canada

tojo

unread,
May 8, 2002, 12:53:40 PM5/8/02
to
In article <df35b276.02050...@posting.google.com>,
bur...@sympatico.ca says...

> We are using VB through ADO & OLEDB with Oracle 8i. Right now, we're
> just sending hard-coded SQLs (ie. SELECT x FROM y WHERE z = 5) to the
> Execute method of the ADODB Connection object and getting the results
> from the Recordset object.
> We'd MUCH rather use bind variables in cases like this.
>
> I'd rather not rewrite the database layer to use OO4O, if at all
> possible. If there a way to use bind variables (ie. SELECT x FROM y
> WHERE z = :v) in ADO?
>

Just use ADO's parameters collection and a question mark (?) as a bind
variable in your SQL. Here's an example from the docs:


Dim objCon As NEW ADODB.Connection
Dim objCmd As NEW ADODB.Command
Dim prEmpno As NEW ADODB.Parameter
Dim prEname As NEW ADODB.Parameter
...
objCmd.ActiveConnection = objCon
objCmd.CommandType = adCmdText

' Create and append the parameters to the command object
Set prEmpno = objCmd.CreateParameter("prEmpno", adSmallInt,
adParamInput, ,8521)
' prEname is bound to a NVARCHAR column in the EMP table
Set prEname = objCmd.CreateParameter("prEname", adBSTR, adParamInput, ,
"Joe")
objCmd.Parameters.Append prEmpno
objCmd.Parameters.Append prEname

' Enabling the NDatatype property indicates to the provider
' that one or more of the bound parameters is of N datatype
objCmd.Properties("NDatatype") = TRUE

' Assume column ENAME in table EMP is of NVARCHAR type
objCmd.CommandText = "INSERT INTO EMP (EMPNO, ENAME) VALUES (?, ?)"

' Execute the SQL
objCmd.Execute

' It is a good idea to disable the property after execute as the same
command
' object may be used for a different SQL statement
objCmd.Properties("NDatatype") = FALSE


-- tojo

Niall Litchfield

unread,
May 9, 2002, 4:20:14 AM5/9/02
to
which reminds me of a question I've meant to ask a number of times. Is it
possible to use named parameters rather than the question mark notation in
ADO ?

so instead of

SELECT x FROM y WHERE z = ?

being passed you can actually pass something like

where z = namedparameter.

"tojo" <TomJor...@hotmail.com> wrote in message
news:MPG.174376807...@news.t-online.de...

Bob Burgess

unread,
May 9, 2002, 9:42:53 AM5/9/02
to
tojo <TomJor...@hotmail.com> wrote in message news:<MPG.174376807...@news.t-online.de>...
> In article <df35b276.02050...@posting.google.com>,
> bur...@sympatico.ca says...
> > I'd rather not rewrite the database layer to use OO4O, if at all
> > possible. If there a way to use bind variables (ie. SELECT x FROM y
> > WHERE z = :v) in ADO?
>
> Just use ADO's parameters collection and a question mark (?) as a bind
> variable in your SQL. Here's an example from the docs:
>
>
> Dim objCon As NEW ADODB.Connection
> Dim objCmd As NEW ADODB.Command
> Dim prEmpno As NEW ADODB.Parameter
> Dim prEname As NEW ADODB.Parameter
> ...
> objCmd.ActiveConnection = objCon
> objCmd.CommandType = adCmdText
>
> ' Create and append the parameters to the command object
-- snip --
> -- tojo


Thank you, Tom. After my post, I kept searching and found another
good example here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/iisref/html/psdk/asp/iiwadata.asp
(Scroll down to the heading "Improving Queries with the Command
Object".)

--Bob

tojo

unread,
May 11, 2002, 4:39:20 AM5/11/02
to
In article <3cda313f$0$237$ed9e...@reading.news.pipex.net>, n-
litch...@audit-commission.gov.uk says...

> which reminds me of a question I've meant to ask a number of times. Is it
> possible to use named parameters rather than the question mark notation in
> ADO ?
>

It's sad, but no. ADO replaces the question marks with param values in
the order you defined them. I think JDBC works the same way (but dont
quote me). OO4O is clearly superior in this area.

-- Tom

Jim Kennedy

unread,
May 11, 2002, 9:34:32 AM5/11/02
to
Yes, I believe JDBC does also. One place I used to work thought that was
silly and created a wrapper class so they could use named parameters. The
class handled the ? assignment so the developer could use a named parameter
for the bind variable. It sure made debugging etc. easier. Less chance of
error.
Jim
"tojo" <t...@hotmail.com> wrote in message
news:MPG.1746f71ed...@news.t-online.de...

Niall Litchfield

unread,
May 13, 2002, 4:27:54 AM5/13/02
to
thanks for the reply. I agree entirely, however our devs have standardized
on ADO.

I'm going to play a bit with.net and the Oracle client for that so I'll see
how that goes.


--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************


"tojo" <t...@hotmail.com> wrote in message
news:MPG.1746f71ed...@news.t-online.de...

0 new messages