Introduction

Currently ATS supports Oracle Database, MySQL, Microsoft SQL Server, PostgreSQL and Cassandra. With some effort, It is also possible to plug-in support for your own database type.

Basic example

Here is some example code:

    import com.axway.ats.action.dbaccess.DatabaseOperations;
    import com.axway.ats.action.dbaccess.model.DatabaseCell;
    import com.axway.ats.action.dbaccess.model.DatabaseRow;
    import com.axway.ats.harness.config.TestBox;

    @Test
    public void databaseOperations() {

        TestBox serverBox = new TestBox();
        serverBox.setHost( SERVER_IP );
        serverBox.setDbType( "MSSQL" );
        serverBox.setDbName( "DemoDatabase" );
        serverBox.setDbUser( USER_NAME );
        serverBox.setDbPass( USER_PASSWORD );

        // Make an instance of this class by providing the connection parameters
        DatabaseOperations dbOperations = new DatabaseOperations( serverBox );

        // fetch value from the DB
        String dbValue1 = dbOperations.getValue( "Table1", "column1", "id", "1" );
        log.info( "We fetched this db value: " + dbValue1 );

        // the next command will produce same result
        String dbValue2 = dbOperations.getValue( "SELECT column1 FROM Table1 WHERE id = 1" );
        log.info( "We fetched this db value: " + dbValue2 );

        // delete rows from Table1 that match the provided where clause
        dbOperations.delete( "Table1", "column1='ABC'" );

        // update the value of column1 where column2 has some particular value
        dbOperations.updateValue( "Table1", "column1", "new value", "column2", "where_value" );

        /* 
         * when you extract not just a single value, but many rows and columns
         * it is convenient to use the next example which shows the usage
         * of very simple classes called DatabaseRow and DatabaseCell
         */
        DatabaseRow[] dbRows = dbOperations.getDatabaseData( "SELECT column1, column2 FROM Table1" );
        log.info( "Found " + dbRows.length + " DB rows" );
        for( DatabaseRow dbRow : dbRows ) {
            DatabaseCell column1Cell = dbRow.getCell( "column1" );
            DatabaseCell column2Cell = dbRow.getCell( "column2" );

            log.info( "column1 is " + column1Cell.getValue() + " while column2 is " + column2Cell.getValue() );
        }
    }

Following are some specifics which are important when working with a particular database provider.

Providing DB connection parameters

Describing the database to work with needs a lot of parameters:

// Create a TestBox object describing your DB connection parameters
TestBox dbBox = new TestBox();
dbBox.setDbType( "MYSQL" );
dbBox.setHost( SERVER_IP );
dbBox.setDbName( "DemoDatabase" );
dbBox.setDbUser( USER_NAME );
dbBox.setDbPass( USER_PASSWORD );
	
// Make an instance of this class by providing the connection parameters
DatabaseOperations dbOperations = new DatabaseOperations( dbBox );

// Now you can use its methods

Set a custom port for any database type

If you do not specify a DB port, we will use the default one. Otherwise, here is how you can specify the right port number:

dbBox.setDbPort( "3310" );

Add additional connection parameters

You will see examples bellow with some more special configurations needed to work properly with a database. For such cases you can create a map which will contain data that will change the behavior of DatabaseOperations class.

import java.util.Map;
import java.util.HashMap;

// create a map for custom properties
Map<String, Object> customProperties = new HashMap<>();

// Make an instance of this class by providing the connection parameters and custom properties as well
DatabaseOperations dbOperations = new DatabaseOperations( dbBox, customProperties );

Use admin credentials

The TestBox class supports a DB user and password as well as Admin user and password. You set them by calling setDbUser, setDbPass, setAdminUser and setAdminPass.

By default when establishing connection the DatabaseOperations class will use the DB user and password. If you want to use the Admin user and password instead, then you need to add this custom property DbKeys.USE_ADMIN_CREDENTIALS with value "true"

import com.axway.ats.common.dbaccess.DbKeys;

customProperties.put( DbKeys.USE_ADMIN_CREDENTIALS, "true" );

Oracle Database Specifics

This section deals with some Oracle DB specifics

Connection specifics

When working with an Oracle DB, you often need to provide some extra parameters in order to connect successfully. These are the SID and SERVICE NAME.

You can do it by providing some custom values:

import com.axway.ats.common.dbaccess.OracleKeys;

// Specify a SID
customProperties.put( OracleKeys.SID_KEY, "MySid" );

// Specify a Service name
customProperties.put( OracleKeys.SERVICE_NAME_KEY, "MyServiceName" );

Use secure connection to Oracle Database

// Specify the following property with value "true" to use secure connection
customProperties.put(OracleKeys.USE_SECURE_SOCKET, "true");
 
// Specify keystore file full path, type and password
customProperties.put(OracleKeys.KEY_STORE_FULL_PATH, <Keystore Full Path> );
customProperties.put(OracleKeys.KEY_STORE_TYPE, <Keystore Type> );
customProperties.put(OracleKeys.KEY_STORE_PASSWORD, <Keystore Password> );

Obtaining certificates directly from the database connection

If needed, you can use the following to obtain the appropriate certificate from your Oracle database server, then save it into a file and use for achieving secure connectivity.

import com.axway.ats.action.security.CertificateUtilities;

// next line will cause establishing TLS connection and returning the needed certificates
Certificate[] cerrificates = CertificateUtilities.getSecurityCertificates( DB_HOST, DB_PORT );

// now you can save the right certificate into a file
CertificateUtilities.createKeyStoreFile( cerrificates[0], <Keystore Full Path>, <Keystore Password>, <Keystore Type> );

// and now you can establish secure connection using DatabaseOperations as shown in an upper section

Problem with INSERT and UPDATE of long binary data

There is a common problem when trying to INSERT/UPDATE a long value in a binary column (BLOB, CLOB, ...) and the statement execution fails with an error like:

ORA-01704: string literal too long
Cause: The string literal is longer than 4000 characters.
Action: Use a string literal of at most 4000 characters. 
Longer values may only be entered using bind variables.

The workaround here is a block statement, where you have to assign the long value to a variable, defined with the same type as the column, and then use this variable in the INSERT/UPDATE statement:

DECLARE
  binValue myTable.blobColumn%type;
BEGIN
  binValue := to_blob('Some very long string value');
  UPDATE myTable SET blobColumn = binValue WHERE someKey = someCriteria;
END;

Microsoft SQL Server Specifics

This section deals with some Microsoft SQL (or Ms SQL) database specifics.

Secure connection with Ms SQL database

// add this property to enable security connection 
customProperties.put( DbKeys.USE_SECURE_SOCKET, "true" );

Use different JDBC driver for SQL Server

The Microsoft JDBC library must be present in the classpath. It could be retrieved from here. And there are also instructions how to add the dependency via Maven.

// add this property to use the mssql-jdbc driver
System.setProperty( "com.axway.automation.ats.logdbdriver", "MSSQL" );

Cassandra Specifics

Apache Cassandra can give you different kind of errors you are not used with.

Dependencies

Currently ATS is using the following Datastax driver version to work with Cassandra DB:

But the ATS is not directly depending on the Datastax driver dependencies. So you have to add them to your classpath.

Here is how to do it with Maven

  <dependency>
    <groupId>com.datastax.cassandra</groupId>
    <artifactId>cassandra-driver-core</artifactId>
    <version>3.2.0</version>
  </dependency>

Cassandra specific error messages

• "Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"
  The solution is the add ALLOW FILTERING token at the end of the SQL query. The options are:

  • Add the following custom property and we will append the token after each SELECT query

    customProperties.put( "ALLOW FILTERING", true );
    

  • Add yourself the needed token
• "No indexed columns present in by-columns clause with Equal operator" - Change your where clause.
• "Non PRIMARY KEY type found in where clause" - Change your where clause.
• "Invalid STRING constant (a14f0f51-7ff2-43fe-b922-8c36ddbbd91f) for id of type uuid" - In the where clauses, some data types (like UUID) must not be quoted while others (like TEXT) must be quoted.

PostgreSQL Specifics

Naming conventions

By default, in SQL queries, PostgreSQL converts identifiers like table and column names to lower case.
In order to preserve the case of the letters for the identifiers, you must additionally enclose them in double quotes. So if the table name is Persons ( intentionally created case-sensitive) , then you should pass "Persons" in the query, in quotes. The guideline is to either always use lower case, or always use case-sensitive names enclosed in double quotes.
For more details you may check PostgreSQL documentation here: https://www.postgresql.org/docs/current/sql-syntax-lexical.html, Section 4.1.1. Identifiers and Key Words, last paragraph.

MySQL Specifics

JDBC driver support

Starting with ATS version 4.0.7, MySQL has support for at least two JDBC driver versions, namely 8.0.xx (currently is 8.0.21) and 5.1.xx (validated with 5.1.42).
This is done transparently to the user (regardless of the different classes used) by checking which version is available in the classpath. First is the check for 8.0.xx case, then, if no such dependency is found, for 5.1.xx.

Most of the time, you will not be able to differentiate which version is used, because both of them are supported equally and no difference in their behaviour was found.

The only change might be in the performance of the two drivers. This might be related only to PERFormance testing against MySQL servers.

Timezone support

If you are using the 8.xx.xx JDBC version, MySQL JDBC driver requires to know the timezone of the server it is connecting to. This leads to a situation where the returned timezone name is not IANA named, e.g. instead of Europe/Sofia, it is returned as EEST ( Eastert European Summer Time )

This causes the JVM to throw an exception about missing timezone or timezone name, that references more than one actual timezone.

ATS automatically tries to find the best possible match, but if some of your SQL types (Date, Timezone, etc) starts to look quirky, you can see the server time zone and set the appropriate value.

This is done by passing a custom property to the MySQL database connection. Here is a little example:

...
Map<String, Object> custProps = new HashMap<String, Object>();
custProps.put(DbKeys.SERVER_TIMEZONE, "GMT+2"); // UTC works only without offset, so if you want UTC-3 just replace UTC with GMT and you are good to go
// pass the custom properties where needed
...

The symptoms of this type of problems are such logs (Warning and a possible exception after it):

WARNING mysql.DbConnMySQL: No server timezone specified. This can lead to an exception!

INFO           mysql.DbConnMySQL: MySQL datasource class will be 'com.mysql.cj.jdbc.MysqlConnectionPoolDataSource'. Begin datasource configuration

ERROR       EXCEPTION

   USER message: axway.ats: [TestNG]: TEST FAILED

   CALL STACK:com.axway.ats.action.exceptions.DatabaseOperationsException: Error getting value from DB with query xxxxx

  at com.axway.ats.action.dbaccess.DatabaseOperations.getValue(DatabaseOperations.java:328)

  ...

  at org.testng.TestNG.runSuites(TestNG.java:1133)

  ...

Caused by: com.axway.ats.core.dbaccess.exceptions.DbException: Error while configuring MySQL's data source instance

  at com.axway.ats.core.dbaccess.mysql.DbConnMySQL.createDataSource(DbConnMySQL.java:285)

  at com.axway.ats.core.dbaccess.mysql.DbConnMySQL.getDataSource(DbConnMySQL.java:160)

  at com.axway.ats.core.dbaccess.ConnectionPool.getConnection(ConnectionPool.java:66)

  ...

Caused by: java.text.ParseException: Unparseable date: "00:00:00 FLE Standard Time 2020"

  at java.base/java.text.DateFormat.parse(DateFormat.java:395)

  at com.axway.ats.core.dbaccess.mysql.DbConnMySQL.createDataSource(DbConnMySQL.java:258)

  ...

  34 more

Back to parent page

Go to Table of Contents