TestComplete: Retrieving Raw Binary Data From Oracle Databases

From time to time our users ask whether TestComplete supports Oracle databases. The answer is yes. TestComplete supports Microsoft ADO and through it can connect and work with any database including the Oracle ones. We’d like to demonstrate this in this post. We will create a script code snippet that will retrieve data stored in the RAW (Raw Binary Data) format from an Oracle database and then post these this data to the test log (this example was suggested by one of our customers who had some difficulties with this).

The script code will connect to an Oracle database using the ADO functionality and then will retrieve data from this database by using an SQL query. After that, the data that was returned by the query will be converted to a hexadecimal format and posted to the test log.

The code doing this is below:

// [TestComplete JScript]

 
function OracleExample()

{

  var Qry;

  // Creates a query

  Qry = ADO.CreateADOQuery();

  // Specifies the connection string

  Qry.ConnectionString = "Provider=OraOLEDB.Oracle.1;" +

"Data Source=MYHOST.MYGROUP.COM"

  // Specifies the desired SQL expression

  Qry.SQL = "SELECT orders_id FROM orders WHERE order_no > 1179131";

  // Executes the query

  Qry.Open();

  // Processes results and insert data into the test log

  Qry.First();

  // Iterates through the data

  while (! Qry.EOF)

  {

    var res = "";

    var str = Qry.FieldByName("orders_id").AsString

    for (i = 0; i < 8; i++) // The field contains 8 bytes

    {

       // Transforms the value to the hexadecimal format

      var temp = aqString.Format( "%X", str.charCodeAt(i) );

      if (temp.length < 2 )

      {

        temp = "0" + temp;

      }

      res += temp;

    // Posts the data to the test log

    Log.Message(res);

    // Moves to the next data returned by the query

    Qry.Next();

  };

  // Closes the query

  Qry.Close();

}

Let me go through it and provide some explanation for the code parts.

 

First, we call TestComplete’s ADO.CreateADOQuery method to create an ADO Query object:

var Qry;
// Creates a query

Qry = ADO.CreateADOQuery();

 

Then we specify the query’s connection string and code and open the query:

// Specifies the connection string

Qry.ConnectionString = "Provider=OraOLEDB.Oracle.1;" +

"Data Source=MYHOST.MYGROUP.COM"
// Specifies the desired SQL expression

Qry.SQL = "SELECT orders_id FROM orders WHERE order_no > 1179131";
// Executes the query

Qry.Open();

If you have any problems with specifying a connection string for your database, please visit www.connectionstrings.com/oracle.

 

To process the data that was returned by the query, we iterate through it using the While loop:

// Processes results and insert data into the test log

Qry.First();
// Iterates through the data
while (! Qry.EOF)

{

  var res = "";

  var str = Qry.FieldByName("orders_id").AsString
  for (i = 0; i < 8; i++) // The field contains 8 bytes

  {

     // Transforms the value to the hexadecimal format

    var temp = aqString.Format( "%X", str.charCodeAt(i) );

    if (temp.length < 2)

    {

      temp = "0" + temp;

    }

    res += temp;

  }


  // Posts the data to the test log

  Log.Message(res);

  // Moves to the next data returned by the query

  Qry.Next();

};

We would like to draw your attention to the code highlighted above. It transforms RAW data to the hexadecimal format. The counter in the FOR loop iterates through the values from 0 to 7 as the integer field (orders_id) contains 8 bytes, that is, the field has the following definition:

CREATE TABLE ORDERS

(

  ORDERS_ID RAW(8),

  ...

)

So, in the loop TestComplete goes through the field’s bytes and converts them to a hexadecimal format using TestComplete’s aqString.Format() function.

 

After we converted all the data, we must not forget to close the query:

// Closes the query

Qry.Close();

 

That’s all. Hope you find the described solution useful when working with Oracle databases. For more information on working with databases from your TestComplete tests, see Working with Databases in the TestComplete documentation.


Close

Add a little SmartBear to your life

Stay on top of your Software game with the latest developer tips, best practices and news, delivered straight to your inbox

By submitting this form, you agree to our
Terms of Use and Privacy Policy

Thanks for Subscribing

Keep an eye on your inbox for more great content.

Continue Reading