Remote Database connection through QTP

[PrabhatN]

Hi All,

I am testing a Java Swing application which uses Oracle 10g as back-end.
I have Oracle client installed in my machine and I access database from a remote machine.

I wrote following snippet of codes and when ran it, I got some TNS:protocol adapter error(Attached is the screenshot of the error)

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Provider = "OraOLEDB.Oracle"

objConnection.open "server=XXXX;uid=YYYYYY;pwd=ZZZZZZ;database=AAAAA"

objRecordSet.Open "select ABC from RPQL",objConnection, adOpenStatic,adLockOptimistic
objRecordSet.MoveFirst
msgbox objRecordSet.RecordCount

I have cross-checked many times and confirmed that server,uid,pwd etc. are correct.

Can somebody help me solving this? Any suggestion is highly apprciated.

[KavitaPriyaCR]

Hi Prabhat,

Can you try with below connection string?

Code:

objConnection.Open "DRIVER={Oracle in OraClient10g_home1};SERVER=ORCL;UID=xyz;PWD=abc;DBQ=ORCL;DBA=W;APA=T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=Lo;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=Me;CSR=F;FWC=F;FBS=60000;TLO=O;"
AND comment the line:
objConnection.Provider = "OraOLEDB.Oracle"

Any one please correct me if am wrong.

[PrabhatN]

Hi Kavita,

It didn't work out as well.

It threw some error like "[Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve the connect identifier specified"

It's been months I am trying this. I could connect to SQL Server on another remote machine within minutes. I could not find what the issue here is.

[manishbhalshankar]

Hi Prabhat,

Here is a reference of connection strings.

[cdesserich]

Prabhat, as far as I know, through ODBC you have to have the "server" defined in your Oracle client's tnsnames.ora file. It can be found in a similar path to this:

C:\oracle\product\10.2.0\client\NETWORK\ADMIN\tnsnames.ora

Using a text editor, some code similar to the below will have to be added to the file and the correct values added for your system.

Code:

<<This would be the newly defined server name e.g. "TESTDB">> =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <<host name or IP of server>>)(PORT = 1521))
    (CONNECT_DATA =
      (SID = <<sid of server>>)
      (SERVER = DEDICATED)
    )
  )

The TNS code get worse if there is load balancing and such...