Micro Focus QTP (UFT) Forums
How to write a script or database connectivity to connect oracle databse - 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: UFT / QTP Beginners (https://www.learnqtp.com/forums/Forum-UFT-QTP-Beginners)
+--- Thread: How to write a script or database connectivity to connect oracle databse (/Thread-How-to-write-a-script-or-database-connectivity-to-connect-oracle-databse)

Pages: 1 2

How to write a script or database connectivity to connect oracle databse - upadhyay40 - 11-04-2009

Hello all,

How to write a script or database connectivity to connect oracle databse, can all please help me out to connect to the oracle database and retrieve data from database.

Thanks

RE: How to write a script or database connectivity to connect oracle databse - Saket - 11-05-2009

hi upadhyay40,

use this piece of code to connect to your oracle database, and execute the query
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
If ConObj.Errors.Count = 0 Then
    Set RecObj = CreateObject("ADODB.Recordset")
    RecObj.Open YourQuery, ConObj,adLockPessimistic
End if
your results for the query will be in recordset RecObj
let me know if there is any issue.

RE: How to write a script or database connectivity to connect oracle databse - upadhyay40 - 11-18-2009

Hi saket,

I got error "Item cannot be found in the collection corresponding to the requested name or ordinal" on line 4

My code is

Code:
Set ConObj = CreateObject("ADODB.Connection")
ConObj.Provider = "MSDAORA.1"
ConObj.Properties("Data Source").Value = "15.5.7.13"
ConObj.Properties("Initial Catalog").Value = "sid"
ConObj.Properties("User ID").Value = "User"
ConObj.Properties("Password").Value = "Pass"
ConObj.Open
If ConObj.Errors.Count = 0 Then
    Set RecObj = CreateObject("ADODB.Recordset")
    RecObj.Open "select * from USER", ConObj,adLockPessimistic
End if
Please let me know if i make any mistake, hoping for your cooperation

Thanks
Mahesh

RE: How to write a script or database connectivity to connect oracle databse - Saket - 11-18-2009

I think you have provided your host ip for data source value
you should give the 'Service Name' to connect to a oracle DB

RE: How to write a script or database connectivity to connect oracle databse - upadhyay40 - 11-18-2009

Hi Saket,

Please correct me if i am wrong, i was given my server Ip on data source row is this ok or wrong, and can you please let me explain from where i can get Service name, please take in consider.

Thanks
Mahesh

RE: How to write a script or database connectivity to connect oracle databse - Saket - 11-18-2009

Server IP for data source is wrong.
In oracle, usually the database resides on a server and it uses a service name (tnsname) to connect to the DB server. the service name or the tnsname should be there on both i.e server and the client machine as well. Use NetConfiguration Assistant to create a tnsname.

let me know if you need more help on this.

RE: How to write a script or database connectivity to connect oracle databse - v_selvam - 11-18-2009

Giveing server IP and is not a Wrong one.

If you connect oracle database, the service name should be configured in the 'tnsnames.ora' file. If you need more information on this, contact any developer or DBA who is using in oracle DB

RE: How to write a script or database connectivity to connect oracle databse - Saket - 11-18-2009

@ v_selvam - I was never able to connect to a oracle db with providing server IP. have you tried this ever?
would you like to share how to do this?

RE: How to write a script or database connectivity to connect oracle databse - upadhyay40 - 11-18-2009

Hi Saket,

Sorry i was not replying, actually i had discussion with my DB admin , he said for getiing tnsname i need to install oracle client on my end, so i am installing that now, so if i will get problem i will just let you know, take in consider

Thanks
Mahesh

RE: How to write a script or database connectivity to connect oracle databse - v_selvam - 11-24-2009

@Saket - When you connect oracle database, you need the oracle service name which you configured in the 'tnsnames.ora' file.

Here is the example for oracle service name configuration

Code:
ORA_Servicename =
(DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
   )
(CONNECT_DATA =
   (SERVICE_NAME = DataBaseName)
)
)


In the above code, 'ORA_Servicename' is service name. The connecting string is given below
ConnectionString = "User Id=scott;Password=tiger;Data Source=ORA_Servicename;"