Forum Discussion

jmancor's avatar
jmancor
Occasional Contributor
14 years ago

Oracle connection

Hey everyone!



I've been surfing either the internet and some TC7 books but somehow nobody has ever written something about this feature.

How can i connect to an Oracle database in order to retrieve data to be compared in my TC script?

I'm currently working on a Script that tests functionality inside my app but i really need to get some data from the DB and compare it. Everything is cool except for the oracle connection. Is there any way to do this without having to set a dsn?

Windows gets really buggy when comes to work with Oracle ODBC anyway... if there's any hint it certainly would be appreciated.



Here's an example of what i'm trying to achieve :




DDT.ADODriver("Driver={Microsoft ODBC for Oracle};Server=161.83.80.6;Uid=jmc;Pwd=secure;");


Notice the extreme complexity of my password.!



Best regards and thx in advance.



Jmc

11 Replies

Sort By
  • AlexeyKolosov's avatar
    AlexeyKolosov
    Staff
    14 years ago

    Hello Carlos,


    Try to use the following connection strings within the DDT.ADODriver method call:




    DDT.ADODriver("Driver=Microsoft ODBC for Oracle;Server=161.83.80.6:1521;Uid=jmc;Pwd=secure;", "TABLE"); 

    // or

    DDT.ADODriver("Provider=MSDAORA.1;Password=secure;User ID=jmc;Data Source=161.83.80.6:1521;Persist Security Info=True", "TABLE");


    Note that the method requires a table name as its second parameter. Please see the DDT.ADODriver article on our Support Portal for details.


    Does this help?

  • jmancor's avatar
    jmancor
    Occasional Contributor
    14 years ago
    Yes indeed!



    Thank you so much i'll try these methods out. Hopefully everything is
    going to be fine. One of the main reasons was the fact that i could not
    successfully set up a ODBC with either Oracle or Windows Drivers. I do
    not know why. Fortunately i found a driver made by "Progress" which
    allowed me to connect with "DataDirect Oracle Wire protocol". Connection
    successfully Tested made me realize that the only further step would be
    to find the Right connection String.

    Gonna try this and let you guys know.

    Thank you so much for this it really helps...seriously guys both of you!





    Best Regards,



    Cmc
  • jmancor's avatar
    jmancor
    Occasional Contributor
    14 years ago
    No luck...



    The first one throws an exception that says : DataSource name not found and no Default driver specified



    Any hint?.. How comes there is no example available to test this.... TestComplete examples only show demos with CSV driver :S!!



    Well i'll keep trying!



    Thanks again!
  • jmancor's avatar
    jmancor
    Occasional Contributor
    14 years ago
    Hey Guys,



    We finally made it! After hours and almost days of working on this stuff!! (just kidding)

    Is there any test developer position on Automated Qa?? kidding again!

    Alright,



    First we needed to download the driver. For some crazy reason we could not made this with Oracle nor Miscrosoft drivers. We had to download Progress DataDirect ODBC driver. Really cool!

    After reading loads of websites (I was actually wondering why Test Complete does not come with a nice Oracle connection example) we finally came up with the simpliest and most useful connection query... here's the example and once again thank you so much guys for your help.

    Cheers !

    Jmc



    Example :



    function connect(){

      var connectstring = "Data Source=dsnOracle;User ID=user;Password=password"; //3 params only!!!

      var patientlist = ADO["CreateADOQuery"]();

      patientlist["ConnectionString"] = connectstring;

      patientlist["SQL"] = "Select pat_lastname FROM pat_data";

      patientlist["Open"]();

      patientlist["First"]();

      res = patientlist["FieldByName"]("pat_lastname")["Value"];

      BuiltIn["ShowMessage"](res);

    }  
    • ASB's avatar
      ASB
      New Member
      10 years ago

      Hey.. Can you help me figure how to use a jdbc url in the snipped u provided?

  • mazhar555's avatar
    mazhar555
    Contributor
    14 years ago
    Hi, i am facing an exception "[Microsoft][ODBS Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed" with the following script



    Sub TestProc


      Set oraConnection = ADO.CreateADOConnection


      oraConnection.ConnectionString = _

      "Driver = Oracle in Home1; Data Source=JANUSDRIVER; User Id=userID;Password=userPassword;" 

      'Also used the following string

      ' "Data Source=JANUSDRIVER; User Id=userID;Password=userPassword;" 

      ' Suppress the login dialog box

      'oraConnection.LoginPrompt = False

      oraConnection.Open


      oraConnection.Close

    End Sub



    i have also used DDT.ADOConnection but facing the same problem

  • mazhar555's avatar
    mazhar555
    Contributor
    14 years ago
    Hi somehow i am able to create a oracle conection but now i am facing problem while executing query



    Sub TestProc

      Set AConnection = CreateObject("ADODB.Connection")

      AConnection.ConnectionString = _

      "Provider=MSDAORA.1; User Id=user;Password=pasword;Data Source=INTRA"

      AConnection.Open


      query =  "Select * from prj_project Where PRJProJECTID_NB = 7301"

      Set RecSet = Aconnection.Execute(query)

      RecSet.MoveFirst

      While Not RecSet.EOF

        Log.Message "Hello"

        RecSet.MoveNext

      Wend

      AConnection.Close

    End Sub



    Prompts me Data Type not supported.

  • Dmitry_Nikolaev's avatar
    Dmitry_Nikolaev
    Staff
    14 years ago

    Hello Muhammad,





    Please make sure that you have specified your query correctly in the Execute method.


  • Veereshmuttagi's avatar
    Veereshmuttagi
    13 years ago
    Hi All,



    I have a solution for the oracle connection, if anyone is installed TestComplete tool in Windows7 64 bit OS



    1> Install TestComplete tool in any drives, but make sure the path does not contain any Special characters(e.g "C:\Program Files (x86)\AutomatedQA\TestComplete")

    2> Install Oracle10g 32 bit client on the same machine

    3> To Create the DSN to connect Oracle DB open the 32  bit ODBC administrator

    "C:\Windows\SysWOW64\odbcad32.exe"

    4> Use following connection string to open the connection to Oracle DB

    ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;User ID=Username;Password=pwd;Data Source=DSN_Name"