Connect TestComplete to Oracle database using Oracle Provider for OLE DB

I will share a ready-made solution to connect to the Oracle database but this time using a free driver called "Oracle Provider for OLE DB".

Below you will find the installation process and code examples:

1. Install a driver compatible with your version of TestComplete x86/x64
  a. Download "Oracle Instant Client Basic Package"

    Link: https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html
  b. download "ODBC Package"
    Link: https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html

2. Unzip both downloaded folders to one location for example (C:\ininstantclient_19_3)

3. Install the ODBC driver by running the file "odbc_install.exe" as an administrator

4. Verify the installed ODBC driver is correct:
  a. in the "Run" field, type "ODBC" and select the version that is compatible with the installed one
  b. go to the "Drivers" tab.
  c. verify if there is an entry with the installed position in it

5. Validate the set environment variables:
  a. PATH: Add "C:\ininstantclient_19_3"
  b. TNS_ADMIN: add "'C:\instantclient_19_3"

6. Select the "Net Configuration Assistant" program from the available applications.

  a. in the application, select the "Local Net Service Name configuration" option

b. in the next window select "Add"
c. then enter the database name in the "Service Name" field

d. Select "TCP" from the list of protocols
e. in the next window fill in "Host name" data e.g. host.com.pl, port 1521 (leave standard)

f. we're doing a connection test
g. in the launch field enter "ODBC"
h. select a version that is compatible with the selected system

i. in the ODBC panel add a new item with the "Add" button
j. we are completing the data of the newly added item:
  - "Data Source Name': "Driver_x64", used in ConnectionStrings scripts
  - "TNS Service Name": in the list, you should have the item configured in "Net Configuration Assistant", select and select
  -"UserID": "DatabaseUser"

k. after the correct configuration of the item select the "Test Connection" button.

NOTE: THE VERSION OF THE DRIVER USED MUST MATCH THE VERSION OF TESTCOMPLETE RUNNING

Example of a valid JS for connecting TC to Oracle database:

var connectstring = "Data Source=DataSourceName;User Id=User;Password=Pass;";
// [1]
  var query = ADO.CreateADOQuery();
  query.ConnectionString = connectstring;
  query.SQL = 
  `
    SELECT mrp.firma_mrp(1) AAAA FROM Firma
  `;
  query.Open();
  query.First();
 
  var res = query.FieldByName("AAAA").Value;
 
//[2]
  var conn = ADO.CreateCommand();
  conn.ActiveConnection = connectstring;
  conn.CommandType = adCmdText;
  conn.CommandText = 
  `  
    select * from akcje_log;
  `;
  var RecSet = conn.Execute();
 
  // Process the results
  Log.AppendFolder("Opisy_Akcji_Z_Logow");
  RecSet.MoveFirst();
  while (! RecSet.EOF )
  {
    Log.Message(RecSet.Fields.Item("OPIS_AKCJI").Value);
    RecSet.MoveNext();
  }

Helpful links:

Doc SmartBear:

https://support.smartbear.com/testcomplete/docs/reference/program-objects/ado/index.html

https://support.smartbear.com/testcomplete/docs/testing-with/advanced/working-with-external-data-sources/databases/ado-components.html

https://support.smartbear.com/testcomplete/docs/reference/program-objects/ado/createcommand.html