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.
Working with Cassandra DB
Java 8+ is required for working with Cassandra DB. This shouldn't be an issue because since version 4.0.7 the whole ATS framework requires Java 8.
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