Connecting to oracle DB with QTP

[rajendra649]

Hi All,

In my application i want to connect oracle database to QTP .I tried with below code but failed to establish connection. For this, I have created data source name by following steps :
Control Panel...>Administrative Tools...>Data Sources (ODBC) ...>select system DSN...>
Click on Add...>Select Driver “Microsoft ODBC For Oracle” Database
And C:\oracle_10.2.0_client_1 software has been installed in my machine. I have tried many ways even I am getting following errors with respective to the following codes:
Case1:

Code:

set strcon=createobject("ADODB.Connection")
                set Recordset=createobject("ADODB.Recordset")
                strcon.Open "Provider=OraOLEDB.Oracle"
                strcon.Open "DRIVER={Microsoft ODBC for Oracle};UID=username;PWD=password;SERVER=servername;DBQ=DBQname;"
Error message: Provider cannot be found. It may not be properly installed.
                Line (5): "strcon.Open "Provider=OraOLEDB.Oracle"".

Case2:

Code:

set strcon=createobject("ADODB.Connection")
                set Recordset=createobject("ADODB.Recordset")
                strcon.Open "Provider=msdaora;DRIVER=Microsoft ODBC for Oracle;User Id=username;Password=password;SERVER=servername;"

Error Message: ORA-12560: TNS:protocol adapter error
Case3:

Code:

set strcon=createobject("ADODB.Connection")
                set Recordset=createobject("ADODB.Recordset")
strcon.Open "Provider=msdaora;Data Source=MyOracleDB;User Id=username;Password=password;SERVER=servername;"

Error message: ORA-12154: TNS:could not resolve the connect identifier specified

please help me to resolve this problem

Thanks and Regards
Raju

[PrabhatN]

Hi Rajendra,

Try the following code and let me know how it goes:

Code:

Dim strConn
strConn = "Driver= {Microsoft ODBC for Oracle}; " &_
                  "ConnectString=(DESCRIPTION=" &_
                  "(ADDRESS=(PROTOCOL=TCP)" &_
                  "(HOST=host name) (PORT=port no))" &_
                  "(CONNECT_DATA=(SERVICE_NAME=service name)));uid=user id; pwd=password;"

Dim obConnect
Dim obRecset

Set obConnect =CreateObject("ADODB.Connection")
Set obRecset = CreateObject("ADODB.Recordset")

obConnect.Open strConn

Dim queryStr
queryStr = "select * from TABLE" // your query

Set obRecset = obConnect.Execute(queryStr)

obRecset.MoveFirst

GetDBField = obRecset.Fields(0).Value

Replace host name, port, service name, uid and password with your original values.

[Anita2020]

Hello,

Small world. Thats actually my code or a derivative of code I wrote. I recognize the naming conventions. Anyway...

It could be a few things.

-Do you have oracle client installed on the machine?
-Can you connect to the schema WPSQA manually using Toad or SQL plus?
-If both of the above or true, then open the tnsnames.ora file in your oracle installation. Search for WPSQA in that file. Make sure the service name, host, ports, user name, and password all match what is in the tnsnames file. You dont need the tnsnames for this code to work, but the connection info does need to be correct.

[quote]
thisUser = "loginid"
thisPW = "pwd"
Environment("CURRENT_SERVICE_NAME") = "WPSQA"
sql = "select * from tablename;"
ConStr = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=db-qa)(PORT=1521))" & _
"(CONNECT_DATA=(SERVICE_NAME = " & Environment("CURRENT_SERVICE_NAME") & "))); uid=" & thisUser & ";pwd=" & thisPW & ";"

Set conn1 = CreateObject("ADODB.Connection")
Set thisObjRS = CreateObject("ADODB.recordset")

conn1.ConnectionString = ConStr
conn1.Open

Set thisObjRS = conn1.Execute(sql,,adExecuteNoRecords)

varArray = thisObjRS.GetRows


But I get an error on the conn1.Open line. See error message below. Any ideas?

[Microsoft][ODBC driver for Oracle][Oracle]ORA-12154: TNS:could not resolve service name

Line (35): "conn1.Open".

Thanks & Regards,
Anita