connect oracle to QTP

[sherinvg]

hi,
in my application i want to connect oracle database to QTP and execute the query.i tried with this code but failed to establish connection.

what all add-ins should be installed to connect database to QTP?

Code:

set conn=Createobject("ADODB.Connection")
Srvname="Driver={Microsoft ODBC for Oracle}:Server=DBserver,UID=abcd,Password=xyzz"
conn.open Srvname
Set Rec = CreateObject("ADODB.Recordset")
SQL="select PR_CODE  from SCHED"
Rec.open SQL ,conn

thanks

[priyankmg]

Try this:

Code:

Srvname="Driver={Microsoft ODBC for Oracle};Server=DBserver;UID=abcd;Password=xyzz;"

[sherinvg]

NO,It will not work if we use semicolon at the end .The other script works now .
Thanks for your reply.
Regards,
Sherin G

[idolast]

Hi,

I also have some problems, setting up a database connection to an oracle server.
I googlet a lot, but I just can't get it done...
My code is like this:

Code:

set conn=Createobject("ADODB.Connection")
Srvname="Driver={Microsoft ODBC for Oracle}:Server=xyz.intranet.com,UID=abcd,Password=xyzz"
conn.open Srvname
...

After the conn.open, the script stops having an error.

The errror I get with this code is:
"[Microsoft][ODBC Driver Manager][Oracle]ORA-12504: TNS: listener was not given the SID in CONNECT_DATA"

I'm a bit confused about the connection-string, because I did not find a way to specify the connection-port and the database used for this connection. I tried around with adding "SID=myDB" or "Data Source=myDB" and "Port=123". But it did not work...
With adding "SID=myDB", the error-message is:
"[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".

I have installed OracleXEClient on my computer, but as I said, the db is running on a another server in the intranet. My PC on which also QTP is running, has Windows XP as OS.

Many solutions I found in the internet meant to specify the connection in the tnsnames.ora-file. But, honestly, I can't find such a file.

Is there a way to get the oracle-connection running without specifying some tnsnames.ora-file? Do I have to install some other version of oracle?

[somisays]

Hi,
You can do like this.
1)Create a Data Source name by following these steps.
Control Panel...>Administrative Tools...>Data Sources(ODBC)
Click on Add...>Select Driver Microsoft ODBC For Oracle Database
and follow the screens and then use the following code.
2)Paste the following Code in the QTP Expert View Window

Connect to the flight 32 database

Code:

Set Conn = CreateObject("ADODB.Connection")
Set Rs = CreateObject("ADODB.recordset")

Conn.Open("DSN=dsnname")

[idolast]

Hi Somisays,

Thanks for the Reply.
I tried to do what you posted, but the problem was again, that I do not know what to do about this TNS-stuff...
When adding a Data Source with the Oracle driver, a TNS-Service Name is required. Again I googled for this, but ... everybody just tells to add something to the file tnsnames.ora...
As I already mentioned above, I do not have any .ora-file on my system. I think I need something more to install, but I have no idea what?
I made some connections to mySQL, Access and MSSQL databases in the past, but never to oracle. So I appreciate any help about what I need to add to my system in order to make it work.

Thanks!

[somisays]

Hi,
Have you installed oracle or not.If so Which version?
Would you able to connect to SQLPlus or not?

[idolast]

Hi,

I have OracleXE installed on my system. The database, I try to connect to is on a different server.

I'm also not able to connect with SQLPlus. It gets the same error.
Do I need to install another version of Oracle?

Thanks in advance!

[idolast]

Hi,
Ok I resolved my problem.
I had installed OracleXEClient instead of OracleXE. The client does not have any tnsnames.ora-files ...
With OracleXE, the solution you gave me with adding a Data Source works fine. :-)
Thank you very much!