Re: ADODB.Connection object not returning correct ConnectonString prop
- From: "Ralph" <nt_consulting64@xxxxxxxxx>
- Date: Fri, 26 Jun 2009 22:30:27 -0500
"Eliz" <Eliz@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:822B738C-7977-42A4-87DE-D79C13E396CC@xxxxxxxxxxxxxxxx
I have a Windows Server 2008 box with Oracle 10.2.0.4 (32bit) driversnot
installed.
I have a small script that
1. creates an ADO connection object,
2. sets the ConnectionString to a VALID ODBC Connectsion (using the
{DRIVER=} parameter),
3. Opens the connection (successfully)
4. Returns the Conn.ConnectionString property.
The connectionstring property returns the MSDASQL provider BUT, it does
return the rest of the Extended Properties data needed to be a valid
connection string.
Has anyone seen this or know of a fix?
I've attached the script below. (It happens using both a .vbs or a .js)
Run the script using the wscript.exe in the SYSWOW64 folder.
' ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Option Explicit
Dim conn
Dim connStr
connStr = "DRIVER={Oracle in
OraClient10gWin2K8x86};DBQ=ORCL;UID=SCOTT;PWD=TIGER;"
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = connStr
MsgBox conn.ConnectionString
conn.Open connStr
'Should NOT return "Provider=MSDASQL.1;"
MsgBox "Connection Opened"
MsgBox conn.ConnectionString
MsgBox "Done."
[Warning! A partially (or even fully) unsatisfactory answer follows.]
I'm not positive but a guess is that ADO is using the MSDASQL Provider to
provide OLE DB support for ODBC.
All data access is through a layer of components. In your current situation:
The ADO data access library as a client has to use an OLE DB service.
A provider that provides OLE DB service for the ADO client and talk to an
ODBC Driver. (MSDASQL)
The ODBC Driver in turn provides an ODBC interface for the client (in this
case MSDASQL), and supports talking to an Oracle ODBC service which can talk
to the Natvie Oracle OCI.
ie, you have a stack that looks something like this ...
ADO Data Access Library
OLE DB for ODBC Provider
ODBC Driver
ODBC for Oracle OCI engine
Oracle Engine
This kind of setup is notorious for confusing the 'H' out of programmers and
components. Behaviors range from unsupported ADODB methods, to intermittent
connnection problems, to spurious ugly but benign 'errors', to mix 'n match
issues (ProviderA works with DriverB and DriverC, but not with DriverA which
works with ProviderB, to ...) I'm not surprised that your query returned
'erronous' information. For as far as ADO is concerned - MSDASQL *IS* the
provider.
The solution? DO NOT USE ODBC with ADO!
There is no good reason to do so. All it does is add layers the opportunity
for problems.
Use the MS or Oracle OLE DB Provider. (I'd go with Oracle's)
Then your stack will look like this...
ADO Data Access Library
OLE DB for Oracle OCI Provider
Oracle Engine
Everyone is happy. Things run faster. Trees sing, the sun shines, squirrels
dance, and everyone gets to go home at 5pm.
hth
-ralph
.
- Follow-Ups:
- References:
- Prev by Date: ADODB.Connection object not returning correct ConnectonString prop
- Next by Date: Re: ADODB.Connection object not returning correct ConnectonString prop
- Previous by thread: ADODB.Connection object not returning correct ConnectonString prop
- Next by thread: Re: ADODB.Connection object not returning correct ConnectonString prop
- Index(es):
Relevant Pages
|
