The online community for software testing & quality assurance professionals
 
 
Calendar   Today's Topics
Sponsors:




Lost Password?

Home
BetaSoft
Blogs
Jobs
Training
News
Links
Downloads



Testing Tools >> HP Functional Testing / Mercury QuickTest Pro / QTP

Pages: 1 | 2 | >> (show all)
raja_reddy
Member


Reged: 02/27/07
Posts: 254
Help: Connect to oracle database from QTP script
      #366981 - 03/19/07 10:58 AM

Hi,

In my AUT, For one of the functional flow , I need to delete the data from front end and than need to verify the data in database (by looking in to one of the database table, as there is no way to verify this from front-end)

In current manual process , manual testers are using TOAD to query the required database table and verify data.

With automation, my thought process is to connect to database using VBScript Descriptive programming and retrive the dataset before and after the data deleted and verify. Is this correct approach?

If yes than can any one please share the sample code to connect to database (here its Oracle) using VBScript?

Many thanks.


Post Extras: Print Post   Remind Me!   Notify Moderator  
mwsrossoModerator
Veteran


Reged: 09/30/01
Posts: 4974
Loc: Doncaster, UK
Re: Help: Connect to oracle database from QTP script [Re: raja_reddy]
      #367010 - 03/19/07 12:52 PM

Search on ADODB

Mark Smith.


Post Extras: Print Post   Remind Me!   Notify Moderator  
cosed
Member


Reged: 03/18/07
Posts: 54
Re: Help: Connect to oracle database from QTP script [Re: mwsrosso]
      #367046 - 03/19/07 10:31 PM

You may try:

Dim rs,sq,pkey
set conn=createobject("adodb.connection")
set rs=createobject("adodb.recordset")
' Oracle client is required to be installed on your box
conn.open= "Provider=OraOLEDB.Oracle.1;Password=***;Persist Security Info=True;User ID=****;SERVER=****;Data Source=****;DBQ=****;"
sql="your sql"
rs.open sql,conn

rs.close
set rs=nothing

conn.close
set conn=nothing

Edited by cosed (03/19/07 11:29 PM)


Post Extras: Print Post   Remind Me!   Notify Moderator  
maxj
Active Member


Reged: 01/14/04
Posts: 825
Loc: UK
Re: Help: Connect to oracle database from QTP script [Re: cosed]
      #367150 - 03/20/07 08:37 AM

you could also use 'Connect.Open "Driver={Microsoft ODBC for Oracle};'

--------------------
I like what i know and I know what i like


Post Extras: Print Post   Remind Me!   Notify Moderator  
robbiewinston
Super Member


Reged: 03/06/06
Posts: 1554
Loc: Bristol, UK
Re: Help: Connect to oracle database from QTP script [Re: maxj]
      #367155 - 03/20/07 08:50 AM

There has been lots of posts about this in the last few weeks. It can defintely be done, I'm doing it!

Post Extras: Print Post   Remind Me!   Notify Moderator  
raja_reddy
Member


Reged: 02/27/07
Posts: 254
Re: Help: Connect to oracle database from QTP script [Re: robbiewinston]
      #369875 - 03/30/07 10:37 AM

Hi Friends,


I tried using various ways even by searching on google. but I could not succeed. I am getting the following error:

"
OraOLEDB (0x80004005)
ORA-12535: TNS:operation timed out
"

My connection string is as below:

objConn.Open "Provider=OraOLEDB.Oracle.1;Server=172.24.5.129;Data Source=<databasename>;User Id=admin;Password=passwd;"

Please help. It would be great if someone can post sample query string.

Thanks a ton in advance.


Post Extras: Print Post   Remind Me!   Notify Moderator  
raja_reddy
Member


Reged: 02/27/07
Posts: 254
Re: Help: Connect to oracle database from QTP script [Re: raja_reddy]
      #370169 - 04/02/07 06:39 AM

Someone please help..

I am not able to create TNS on my machine. Is there a way to do this without TNS name?

Will greatly appreciate your help and time.

Thanks a lot.

Edited by raja_reddy (04/02/07 06:41 AM)


Post Extras: Print Post   Remind Me!   Notify Moderator  
John Wolf
Member


Reged: 03/13/03
Posts: 124
Loc: Portland, OR
Re: Help: Connect to oracle database from QTP scri [Re: raja_reddy]
      #370240 - 04/02/07 09:23 AM

Quote:

Someone please help..

I am not able to create TNS on my machine. Is there a way to do this without TNS name?

Will greatly appreciate your help and time.

Thanks a lot.




Try this :

http://www.codeproject.com/vbscript/conn...;select=1273996


Post Extras: Print Post   Remind Me!   Notify Moderator  
raja_reddy
Member


Reged: 02/27/07
Posts: 254
Re: Help: Connect to oracle database from QTP scri [Re: John Wolf]
      #370599 - 04/03/07 11:03 AM

Hi John,

Thanks a ton for useful information I tried that option to connect to the Oracle database without using TNS and I ended up with the following error :

"Oracle]ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor"

If someone in this group have already done this , kindly send me the code snippet along with the details of configuration i need to do on my machine.

Will greatly appreciate any help. I am stuck up with this connectivity issue since last 3 days.

Please please please help me..

Thanks a ton in advance.

Edited by raja_reddy (04/03/07 11:04 AM)


Post Extras: Print Post   Remind Me!   Notify Moderator  
raja_reddy
Member


Reged: 02/27/07
Posts: 254
Re: Help: Connect to oracle database from QTP scri [Re: raja_reddy]
      #370789 - 04/04/07 05:53 AM

Friends,

I haven't got any success on this issue yet. I am working with DBA to resolve the Service Name issue (while creating TNS name i am getting error of Service name).

By the time I am requesting all QTP experts who have work with Oracle database through QTP script, kindly help me to resolve this issue.

Many thanks in advance.


Post Extras: Print Post   Remind Me!   Notify Moderator  
maxj
Active Member


Reged: 01/14/04
Posts: 825
Loc: UK
Re: Help: Connect to oracle database from QTP scri [Re: raja_reddy]
      #370835 - 04/04/07 07:59 AM

You need to have oracle client installed on your pc before you can make a connection to your db. Also check the username and password are correct and that you can actually access the database manually using a third party tool such as TOAD or PL/SQL Developer

HTH

--------------------
I like what i know and I know what i like


Post Extras: Print Post   Remind Me!   Notify Moderator  
raja_reddy
Member


Reged: 02/27/07
Posts: 254
Re: Help: Connect to oracle database from QTP scri [Re: maxj]
      #370939 - 04/04/07 01:00 PM

Thanks Maxj,

Actually the reason why I dont want to use TOAD or any third party tool is , I dont want my script to open these tools and run queries. Instead i want to execute the queries from within the script and verify the recordsets.

It would be great if someone can share their thoughts and sample code to connect to Oracle8i through VBScript.

Puting my question again:
1. What softwares/drivers i must install. for example OLEDB drivers etc.
2. With above softwares installed whats are sequence of steps (sample code) to connect to Oracle database. (its Oracle 8i)

Please help me if someone have already done this.

Thanks a ton for all your time and efforts.

Please help.

Edited by raja_reddy (04/04/07 01:02 PM)


Post Extras: Print Post   Remind Me!   Notify Moderator  
raja_reddy
Member


Reged: 02/27/07
Posts: 254
Re: Help: Connect to oracle database from QTP scri [Re: raja_reddy]
      #371428 - 04/06/07 07:49 AM

Any one who have tried this connectivity please help!!!!!!

Thanks a lot.


Post Extras: Print Post   Remind Me!   Notify Moderator  
indranil123
Member


Reged: 09/18/06
Posts: 83
Re: Help: Connect to oracle database from QTP scri [Re: raja_reddy]
      #371507 - 04/07/07 12:14 AM

Hi,

The first thing you should do "Check whether ORACLE client is installed in your machine or not". If not, install the correct ORACLE client version. If yes, try the following steps:

1>Go to Control Panel->Adminstrative Tools->Data Source ODBC->Click Add button and select "Oracle ODBC Driver" to set up the driver.

2> Once you select the "Oracle ODBC Driver" from the list, enter the data source name and the service name (as specified in TNSnames file).

Then try to search in some VBScript tutorial in google to connect to the database.


Post Extras: Print Post   Remind Me!   Notify Moderator  
sailaja007
Newbie


Reged: 12/11/06
Posts: 20
Re: Help: Connect to oracle database from QTP scri [Re: indranil123]
      #371523 - 04/07/07 07:12 AM

You can goto www.w3schools.com and click on ADO to learn ADODB connections. You can learn on Record set also in that tutorial.
ADODB connection is used to connect to DB's.

1) To get the DSN name, go to control panel/Administrative Tools and click on ODBC Data Sources(ODBC) then select the "system DSN" tab. You will see a list of system data sources. Note down the Data source name you want to connect to.

sample code:
'open data base connection. You can hard code the DSN, UID and PWD to connect to DB. Use DSN name you noted in 1)

ConnectionString = "DSN=" & DBAlias &";UID=" & UID & ";PWD=" & PWD & ";DBALIAS=" & DBAlias &";"

Set cnME = CreateObject("ADODB.Connection")
cnME.ConnectionString = ConnectionString
SQLstatement = "insert statement"
cnME.Execute(SQLstatement)
cnME.close


Edited by sailaja007 (04/07/07 07:20 AM)


Post Extras: Print Post   Remind Me!   Notify Moderator  
robbiewinston
Super Member


Reged: 03/06/06
Posts: 1554
Loc: Bristol, UK
Re: Help: Connect to oracle database from QTP scri [Re: raja_reddy]
      #372108 - 04/10/07 10:25 AM

Quote:

Thanks Maxj,

Actually the reason why I dont want to use TOAD or any third party tool is , I dont want my script to open these tools and run queries. Instead i want to execute the queries from within the script and verify the recordsets.






I think what Maxj was trying to say is that if you can't connect manually (i.e. outside of QTP) via tools such as Toad, then there is no point in trying to do it through QTP. Get it working outside of QTP first.


Post Extras: Print Post   Remind Me!   Notify Moderator  
raja_reddy
Member


Reged: 02/27/07
Posts: 254
Re: Help: Connect to oracle database from QTP scri [Re: robbiewinston]
      #372817 - 04/12/07 09:38 AM

I can connect to the database using Oracle SQL developer but can not connect using my code. It gives following error :

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC driver for Oracle][Oracle]ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor

While connecting through Oracle SQL developer I dont need to provide 'Service Name'. While code requires service name I tried by providing SID in place of servicename but got the same result.

with window's DNS I am not sure how i can check the connectivity as 'Test Connection' button is removed in newer windows.

Any one can please suggest something?




Thanks a lot in advance.


Post Extras: Print Post   Remind Me!   Notify Moderator  
Tarun LalwaniModerator
Veteran


Reged: 07/21/05
Posts: 15329
Loc: Milwaukee, Wisconsin
Re: Help: Connect to oracle database from QTP scri [Re: raja_reddy]
      #372858 - 04/12/07 11:47 AM

You ate 30 mins of my work

Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=<UID>;Data Source=<SERVICENAME>;Password=<PASSWORD>;Extended Properties="Host=<HOSTSERVER>;Port=<PORT>;Service Name=<SERVICENAME>;"

--------------------
Regards,
Tarun
** First ever technical novel - And I thought I knew QTP! **
** Download QTP Unplugged 2nd Edition eBook for FREE **

KnowledgeInbox RSS


Post Extras: Print Post   Remind Me!   Notify Moderator  
raja_reddy
Member


Reged: 02/27/07
Posts: 254
Re: Help: Connect to oracle database from QTP scri [Re: Tarun Lalwani]
      #372861 - 04/12/07 12:00 PM

Hi,

Here is a connection string that worked fine (Tried and tested):

Dim strCon
strCon = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=hostname)(PORT=1526))" & _
"(CONNECT_DATA=(SID=sidname))); uid=report;pwd=report;

Thanks a ton Tarun for providing alternate connection string i will try that and if that will work you can send me your hourly rate!

Thanks a ton everyone for your time and efforts..

Happy testing.


Post Extras: Print Post   Remind Me!   Notify Moderator  
codewarrior0000007
Junior Member


Reged: 09/21/05
Posts: 264
Re: Help: Connect to oracle database from QTP scri [Re: raja_reddy]
      #374135 - 04/18/07 11:52 AM

Hi,

I've put all the details in the fields above and they are also present in my TNSName.ora file. But I keep getting the error message below:

"[Microsoft][ODBC driver for Oracle][Oracle]ORA-01017: invalid username/password; logon denied"

What I'm trying to do is connect to Oracle DB which hold QC's data. I can access QC using the same UID and PWD which is giving me error above. I've the correct and working UID and PWD.

Anyone has any ideas?

Thanks in advance.

--------------------
01. LR 11.1 (Patch 3)
02. Web(HTTP/HTML) URL method
03. N/A
04. N/A
05. LoadRunner 11.1
06. New
07. Subscription - 5000 Web and Multimedia
08. Current
09. Win Vista Enterprise SP2/Ubuntu Linux 10.04 LTS/Solaris
10. I haven't filed one.


Post Extras: Print Post   Remind Me!   Notify Moderator  
Pages: 1 | 2 | >> (show all)



Extra information
0 registered and 51 anonymous users are browsing this forum.

Moderator:  IanFraser, Rajkumar_Rajangam, thorwath, TReddy, AJ, Tarun Lalwani, mwsrosso 

Print Topic

Forum Permissions
      You cannot start new topics
      You cannot reply to topics
      HTML is disabled
      UBBCode is enabled

Rating:
Topic views: 39170

Rate this topic

Jump to

Contact Us | Privacy statement SQAForums

Powered by UBB.threads™ 6.5.5