Database Specifics
Learn how the main databases and their JDBC drivers differ, and how to write tests that behave as expected with each of them.
Understand why databases differ
JDBC standardizes the API, not the behavior: drivers differ in how they convert values, return generated keys, fetch rows, or handle transactions. This page lists the differences that matter for load tests, as observed with:
| Database | Version | Driver |
|---|---|---|
| PostgreSQL | 18 | org.postgresql:postgresql 42.7 |
| MySQL | 9.7 | com.mysql:mysql-connector-j 26.10 |
| MariaDB | 12.3 | org.mariadb.jdbc:mariadb-java-client 4.0 |
| SQL Server | 2022 | com.microsoft.sqlserver:mssql-jdbc 13.6 |
| Oracle Database | 23 | com.oracle.database.jdbc:ojdbc11 23.26 |
Other versions may behave differently.
Bind parameters
Databases don’t all accept a String for a non-text column.
Use typed parameters with param(value, jdbcType), so that Gatling converts the values the same way whatever the database.
| Database | Untyped String for a numeric or date column | Untyped NULL |
|---|---|---|
| PostgreSQL | Rejected | Accepted |
| MySQL, MariaDB | Converted by the server | Accepted |
| SQL Server | Converted by the server | Rejected for DATE and DATETIME2 columns |
| Oracle Database | Depends on the session’s NLS settings, derived from the JVM locale, for decimals and dates | Accepted |
On SQL Server, bind NULLs for date and time columns with a type, such as param(null, JDBCType.DATE).
Read values
Checks extract the values the driver returns with getObject, so their Java type depends on the driver:
| Database | Notable Java types |
|---|---|
| PostgreSQL | As expected from the SQL types. |
| MySQL | Integer literals are Long, DATETIME is java.time.LocalDateTime, TRUE is the integer 1. |
| MariaDB | Integer literals are Integer, DATETIME is java.sql.Timestamp, TRUE is the integer 1. |
| SQL Server | TINYINT is Short, MONEY is a BigDecimal with a scale of 4, DATETIMEOFFSET is the driver’s microsoft.sql.DateTimeOffset. |
| Oracle Database | Every NUMBER is a BigDecimal without trailing zeros, DATE is java.sql.Timestamp, TIMESTAMP is the driver’s oracle.sql.TIMESTAMP. |
With Oracle Database, compare numbers with BigDecimal values, and set the oracle.jdbc.J2EE13Compliant property to true to read TIMESTAMP columns as java.sql.Timestamp.
Column labels also differ: PostgreSQL turns unquoted names into lower case, Oracle Database into upper case, and the others keep them as written.
Gatling matches column names ignoring case, so column("email") works everywhere.
On SQL Server, an expression without alias, such as SELECT 1 + 1, has an empty label: use an alias, or column(1).
Return generated keys
Without column names, drivers don’t return the same columns:
| Database | returnGeneratedKeys() returns |
|---|---|
| PostgreSQL | The generated columns, with their names. |
| MySQL | The key, labeled GENERATED_KEY, as a BigInteger. |
| MariaDB | The key, labeled insert_id, as a BigInteger. |
| SQL Server | The key, labeled GENERATED_KEYS, as a BigDecimal. |
| Oracle Database | The ROWID of the rows, not their key. |
Use generatedKey without argument to read the first column whatever its label.
With Oracle Database, use returnGeneratedKeys("ID") to get the actual key.
On SQL Server, a batch with returnGeneratedKeys fails, although the driver inserts the rows: don’t combine them.
Run batches
All drivers report one update count per row by default.
Some driver options that rewrite batches into multi-row statements make the driver report Statement.SUCCESS_NO_INFO instead, so updateCount.count works, but updateCount.findAll fails:
- PostgreSQL with
reWriteBatchedInserts=true - MySQL with
rewriteBatchedStatements=true
MariaDB’s driver ignores rewriteBatchedStatements.
Call stored procedures
All drivers support OUT and INOUT parameters with callableStatement.
- PostgreSQL: cursors are closed at the end of the transaction, so read
refcursorvalues inside a transaction. - Oracle Database:
SYS_REFCURSORvalues, as OUT parameters or columns, can be read outside of a transaction. - MySQL, MariaDB: a
CALLproduces the result sets of the procedure, then an update count for its last statement. - SQL Server: without
SET NOCOUNT ONin the procedure, triggers and each statement produce their own update count.
Configure transactions
| Database | Isolation levels | readOnly |
|---|---|---|
| PostgreSQL | All | Enforced |
| MySQL, MariaDB | All | Enforced |
| SQL Server | All standard levels | Ignored by the driver |
| Oracle Database | TRANSACTION_READ_COMMITTED and TRANSACTION_SERIALIZABLE only, others fail on begin |
Ignored by the driver |
- SQL Server: the
SNAPSHOTisolation level isn’t a standard JDBC level, sotransactionIsolationcan’t set it. Don’t runSET TRANSACTION ISOLATION LEVEL SNAPSHOTeither: it would stay on the connection for the next virtual users. Consider theREAD_COMMITTED_SNAPSHOTdatabase option instead. To make connections read-only, addApplicationIntent=ReadOnlyto the URL. - Oracle Database: to make a transaction read-only, run
SET TRANSACTION READ ONLYas its first statement.
Configure the fetch size
Drivers handle fetchSize differently, especially outside of a transaction, in auto-commit mode:
| Database | Behavior |
|---|---|
| PostgreSQL | Ignored in auto-commit mode: the driver reads the whole result. It only fetches by batches inside a transaction. |
| MySQL | Ignored: the driver reads the whole result, unless you add useCursorFetch=true to the URL. |
| MariaDB | Honored, including in auto-commit mode. |
| SQL Server | Depends on the driver’s selectMethod and responseBuffering settings. |
| Oracle Database | Adapted by the driver when not set. A small explicit value, such as 10, multiplies the round trips: leave it unset, or set it large. |
Understand error messages
SQL Server’s and Oracle Database’s drivers translate their messages into the language of the JVM’s locale.
Checks and assertions on error messages should rely on error codes, such as ORA-01722, rather than on text.