01-06-2014 08:38 AM
I was recently assigned a new desktop machine [Windows 7 x64] and took this as an opportunity to upgrade from QTP 11.0 to UFT 11.5. I have common database connection code that I use to connect to several different RDBMs, and after switching to UFT 11.5, this code no longer works for oracle connections within UFT. I have confirmed that the same code works fine for DB2 and SQLServer connections (when used with the appropriate connection strings).
I have installed the Oracle 11g 32-bit ODAC, and the exact same code works just fine when I run it using the 32-bit version of cscript from the command line. It only fails when the script is being executed by UFT.
I have tried reinstalling the oracle driver, reinstalling UFT, granting full permissions for all users on the oracle_home folder, and I can't seem to get the oracle driver to work within UFT.
The code uses a TNS-less connection string (example below), but I even tried setting up a 32-bit system DSN and connecting using that. I get the same results (works from CMD line, fails in UFT).
strConnString = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOC
OL=TCP)(HOST=" &_ strServer & ")(PORT=" & strPort & ")))(CONNECT_DATA=(SID=" & strDataSource & ")(SERVER=DEDICATED)));User Id=" &_ strUserId & ";Password=" & strPassword
Here are the error details:
ERROR: Error Occurred!
Number (dec) : -2147467259
Number (hex) : 80004005
Description : Specified driver could not be loaded due to system error 1114: A dynamic link library (DLL) initialization routine failed. (Oracle in OraClient11g_home32, c:\oracle\product\11.2.0\client_32\SQORA32.DLL).
Source : Microsoft OLE DB Provider for ODBC Drivers
Note: The oracle_home path shown above is correct, and I have granted all permissions to the oracle_home folder.
Here is my code (though I think this is more of a setup/config issue):
'********* '** Author: Jason Self '** Date: 30 March 2012 '** Description: Connect to a database using a connection string '** '** Parameters: '** strConnectionString - Connection String used to identify the DB '** Samples: http://www.connectionstrings.com/ '** '** Returns: Object - the database connection object '********* Public Function ConnectToDatabase(strConnectionString) Dim objConn 'Create ADO objects Set objConn = CreateObject("ADODB.Connection") objConn.Open strConnectionString Set ConnectToDatabase = objConn End Function '********* '** Author: Jason Self '** Date: 30 March 2012 '** Description: Check a database connection '** '** Parameters: '** strConnectionString - Connection String used to identify the DB '** Samples: http://www.connectionstrings.com/ '** '** Returns: Boolean - Whether or not the connection was successful '********* Public Function CheckDBConnection(strConnectionString) On Error Resume Next ConnectToDatabase strConnectionString If Err.Number <> 0 Then DisplayCustomError "Error Occurred!" CheckDBConnection = False Else CheckDBConnection = True End If Err.clear End Function
Has anyone else seen this issue? Any ideas for how I can get around it?
01-08-2014 09:41 AM
I've had a similar problem using the MS for Oracle ODBC driver that comes with Windows 7. I installed the latest Oracle Drivers for my 11g DB and then used http://www.connectionstrings.com/oracle-provider-f
01-08-2014 10:46 AM
Thanks for the reply. I finally got it to work with the following procedure:
- I uninstalled all oracle drivers I had installed previously
- Rebooted my machine
- Downloaded the 64-bit and 32-bit Oracle 12c client install packages– look for the section near the bottom labeled Oracle Database 12c Release 1 Client (126.96.36.199.0) for Microsoft Windows (x64 and 32-bit)
- Installed 64-bit client first [installed to C:\oracle\product\12.1.0\client_64]
- Rebooted my machine [Note: I tried installing the 32-bit version immediately afterward, but I got a weird installer error. It worked fine after the reboot.]
- Installed 32-bit client [installed to C:\oracle\product\12.1.0\client_32]
- Rebooted again (because Windows)
I don't know what the magic sauce was here - maybe installing the full client instead of just the ODAC package did the trick.
The connection string format is shown in the original post. Here it is again, though...
strConnString = "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(" &_
"ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=" & strServer &_
")(PORT=" & strPort & ")))(CONNECT_DATA=(SID=" &_
strDataSource & ")(SERVER=DEDICATED)));User Id=" &_
strUserId & ";Password=" & strPassword