Connecting to Oracle DB via JDBC using QTP

[vishnuvpotty]

Hi All

I got this complicated question.

I tried to connect my DB via QTP. I used the below Script in QTP for connecting and querying DB. Iam gettign this below error in dialog box
"[Oracle][ODBC][Ora]ORA-12560: TNS:protocol adapter error
Line (22): "conn1.Open".

Note: First I used one host in connection string. Still I got the same error. I also set up "MYDB" datasource in "System DSN" tab on Control Panel -> Administrative Tools ->Data Sources (ODBC) for "Oracle in OraClient10g_home1" driver. I tested the connection after setting uphere and the connection was successful. I am able to connect to MYDB through TOAD application.

Please advice me how to correct the script.

****Starting of Script**

thisUser = username
thisPW = password
Environment("CURRENT_SERVICE_NAME&quot = "MYDB_APP.company.net"
sql = "select * from my_table';"
ConStr = "Driver={Oracle in OraClient10g_home1}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS = (PROTOCOL = TCP)" & _
"(host = abcn01p.company.net || host = def03v2.company.net)(PORT=1521))" & _
"(CONNECT_DATA=(SERVICE_NAME = " & Environment("CURRENT_SERVICE_NAME&quot & "))); uid=" & thisUser & ";pwd=" & thisPW & ";"

Set conn1 = CreateObject("ADODB.Connection&quot
Set thisObjRS = CreateObject("ADODB.recordset")
conn1.ConnectionString = ConStr
conn1.Open
Set thisObjRS = conn1.Execute(query,,adExecuteNoRecords)
varArray = thisObjRS.GetRows

******End of Script***

My TNS.Ora file is exactly same as below.

MYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(host = abcn01p.company.net)(port = 1521))
(ADDRESS = (PROTOCOL = TCP)(host = def03v2.company.net)(port = 1521))
)
(CONNECT_DATA =
(service_name = MYDB_APP.company.net)
)
(source_route = yes)
)

[Sivakumar Munganda]

Its better to contact with your DB admin, he will provid you the right details.

[vishnuvpotty]

Hi, Thanks for your quick reply.

However I checked with DB Admin and his point was since I am able to connect to My DB via TOAD / SQL TOOLS without facing any problem, he told its not because of TNS.Ora file which leaves me with one option ie, the connection string in QTP.

So I checked an alternative way like creating a dB check point. The connection was successful. But the string was bit lengthy. I replaced this string in my script. Now the connection opened successfully. But I got this below error.

"Command text was not set for the command object.
Line (30): "Set thisObjRS = conn1.Execute(query,,adExecuteNoRecords)". "

Can Someone please guide me how to first get rid of this above error and then how to correct my Connection string. First aim is to successfully connect to DB.

[Saket]

I am not able to understand your connection string, there are so many
connection string to connect to oracle should be -

Provider=msdaora;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;

the way I use in my script is

Code:

Set ConObj = CreateObject("ADODB.Connection")
ConObj.Provider = "MSDAORA.1"
ConObj.Properties("Data Source").Value = YourServerName
ConObj.Properties("Initial Catalog").Value = YourDatabase
ConObj.Properties("User ID").Value = YourUserName
ConObj.Properties("Password").Value = YourPassword
ConObj.Open

try this, hope it helps.