Micro Focus QTP (UFT) Forums
Connecting to Oracle DB via JDBC using QTP - Printable Version

+- Micro Focus QTP (UFT) Forums (https://www.learnqtp.com/forums)
+-- Forum: Micro Focus UFT (earlier known as QTP) (https://www.learnqtp.com/forums/Forum-Micro-Focus-UFT-earlier-known-as-QTP)
+--- Forum: VB Scripting/Descriptive Programming (https://www.learnqtp.com/forums/Forum-VB-Scripting-Descriptive-Programming)
+--- Thread: Connecting to Oracle DB via JDBC using QTP (/Thread-Connecting-to-Oracle-DB-via-JDBC-using-QTP)

Connecting to Oracle DB via JDBC using QTP - vishnuvpotty - 02-24-2010

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**

Code:
thisUser = username
thisPW = password
Environment("CURRENT_SERVICE_NAME") = "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") & "))); uid=" & thisUser & ";pwd=" & thisPW & ";"

Set conn1 = CreateObject("ADODB.Connection")
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.

Code:
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)
)


RE: Connecting to Oracle DB via JDBC using QTP - Sivakumar Munganda - 02-24-2010

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

RE: Connecting to Oracle DB via JDBC using QTP - vishnuvpotty - 02-24-2010

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):
Code:
"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. Smile

RE: Connecting to Oracle DB via JDBC using QTP - Saket - 02-25-2010

I am not able to understand your connection string, there are so many Wink Smile
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.