| Chapter 12. Compatibility With Other DBMS |
Chapter 11. System Management |  | Chapter 13. Properties

## Chapter 12. Compatibility With Other DBMS

### Fred Toussi

The HSQL Development Group

$Revision: 3096 $

Copyright 2010-2015 Fred Toussi. Permission is granted to distribute this document without any alteration under the terms of the HSQLDB license. Additional permission is granted to the HSQL Development Group to distribute this document with or without alterations under the terms of the HSQLDB license.

2015-06-29 22:26:11-0400

## Compatibility Overview

HyperSQL is used more than any other database engine for application testing and development targeted at other databases. Over the years, this usage resulted in developers finding and reporting many obscure bugs in HyperSQL, which have all been fixed. HyperSQL 2 has been written to the SQL Standard and avoids the traps caused by superficial imitations of some other RDBMS.

HyperSQL has many property settings that relax conformance to the Standard in order to allow compatibility with other RDBMS, without breaking the core integrity of the database. These properties are modified with SET DATABASE SQL statements described in the SQL Conformance Settings section of this chapter

The SQL Standard has existed since 1989 and has been expanded over the years in several revisions. Also, the X-Open specification has defined a number of SQL functions which are implemented by most RDBMS.

Each RDBMS supports additional functions that are not covered by the standard. Some RDBMS use non-standard syntax for some operations. Fortunately, most popular RDBMS products have introduced better compatibility with the Standard in their recent versions, but there are still some portability issues. HyperSQL overcomes the potability issues using these strategies

- An extensive set of functions cover the SQL Standard, X-Open, and most of the useful functions that other RDBMS support.

- Database properties, which can be specified on the URL or as SQL statements, relax conformance to the Standard in order to allow non-standard comparisons and assignments allowed by other RDBMS.

- Specific SQL syntax compatibility modes allow syntax and type names that are supported by some popular RDBMS.

- User-defined types and functions, including aggregate functions, allow any type or function that is supported by some RDBMS to be defined and used.

Support for compatibility with other RDBMS has been extended with each version of HyperSQL.

### PostgreSQL Compatibility

PostgreSQL is fairly compatible with the Standard, but uses some non-standard features.

- Use `<set database sql syntax PGS statement>` to enable the PostgreSQL's non-standard features. References to SERIAL, BIGSERIAL and TEXT data types, as well as sequence functions, are translated into HyperSQL equivalents.

- Use MVCC if your application is multi-user.

- PostgreSQL functions are generally supported.

- For identity columns, PostgreSQL uses a non-standard linkage with an external identity sequence. In most cases, this can be converted to `GENERATED BY DEFAULT AS IDENTITY`. In those cases where the identity sequence needs to be shared by multiple tables, you can use a new HyperSQL 2.2 feature `GENERATED BY DEFAULT AS SEQUENCE <sequence name>`, which is the equivalent of the PostgreSQL implementation.

- In CREATE TABLE statements, the SERIAL and BIGSERIAL types are translated into INTEGER or BIGINT, with `GENERATED BY DEFAULT AS IDENTITY`. Usage of DEFAULT NEXTVAL(<sequence name>) is supported so long as the `<sequence name>` refers to an existing sequence. This usage is translated into `GENERATED BY DEFAULT AS SEQUENCE <sequence name>`.

- In SELECT and other statements, the `NEXTVAL(<sequence name>)` and `LASTVAL()` functions are supported and translated into HyperSQL's `NEXT VALUE FOR <sequence name>` and `IDENTITY()` expressions.

- PostgreSQL uses a non-standard expression, `SELECT 'A Test String'` to return a single row table. The standard form is `VALUES('A Test String')`. In PGS syntax mode, this type of SELECT is supported.

- HyperSQL supports SQL Standard ARRAY types. PostgreSQL also supports this, but not entirely according to the Standard.

- SQL routines are portable, but some syntax elements are different and require changes.

- You may need to use `SET DATABASE SQL TDC { DELETE | UPDATE } FALSE` statements, as PostgreSQL does not enforce the subtle rules of the Standard for foreign key cascading deletes and updates.

### MySQL Compatibility

The latest versions of MySQL have introduced greater Standard compatibility but some of these features have to be turned on via properties. You should therefore check the current Standard compatibility settings of your MySQL database and use the available HyperSQL properties to achieve closer results. If you avoid the few anti-Standard features of MySQL, you can port your databases to HyperSQL.

Using HyperSQL during development and testing of MySQL apps helps to avoid data integrity issues that MySQL may ignore.

HyperSQL does not have the following non-standard limitations of MySQL.

- With HyperSQL, an UPDATE statement can update UNIQUE and PRIMARY KEY columns of a table without causing an exception due to temporary violation of constraints. These constraints are checked at the end of execution, therefore there is no need for an ORDER BY clause in an UPDATE statement.

- MySQL foreign key constraints are not enforced by the default MyISAM engine. Be aware of the possibility of data being rejected by HyperSQL due to these constraints.

- With HyperSQL INSERT or UPDATE statements either succeed or fail due to constraint violation. MySQL has the non-standard IGNORE override to ignore violations and alter the data, which is not accepted by HyperSQL.

- Unlike MySQL, HyperSQL allows you to modify a table with an INSERT, UPDATE or DELETE statement which selects from the same table in a subquery.

Follow the guidelines below for converting MySQL databases and applications.

- Use `<set database sql syntax MYS statement>` to enable support for AUTO_INCREMENT and TEXT data types and several other types. These type definitions are translated into HyperSQL equivalents.

- Use MVCC with `<set database transaction control statement>` if your application is multi-user.

- Avoid storing invalid values, for example invalid dates such as '0000-00-00' or '2001-00-00' which are rejected by HyperSQL.

- Avoid the MySQL feature that trims spaces at the end of CHAR values.

- In MySQL, a database is the same as a schema. In HyperSQL several schemas can exist in the same database and accessed transparently. In addition a HyperSQL server supports multiple separate databases.

- In MySQL, older, non-standard, forms of database object name case-sensitivity make is difficult to port applications. Use the latest form which encloses case-sensitive names in double quotes.

- MySQL functions are generally supported, including GROUP_CONCAT.

- For fine control over type conversion, check the settings for `<set database sql convert truncate statement>`

- If you use concatenation of possibly NULL values in your select statements, you may need to change the setting with the `<set database sql concat nulls statement>`

- MySQL supports most SQL Standard types (except INTERVAL types), as well as non-standard types, which are also supported by HyperSQL. Supported types include SMALLINT, INT, BIGINT, DOUBLE, FLOAT, DECIMAL, NUMERIC, VARCHAR, CHAR, BINARY, VARBINARY, BLOB, DATE, TIMESTAMP (all Standard SQL) and TINYINT, DATETIME (non Standard). UNSIGNED types are converted to signed.

- MySQL uses a non-standard expression, `SELECT 'A Test String'` to return a single row table. The standard form is `VALUES('A Test String')`. In MYS syntax mode, this type of SELECT is supported.

- Indexes defined inside CREATE TABLE statements are accepted and created. The index names must be unique within the schema.

- HyperSQL translates INSERT IGNORE, REPLACE and ON DUPLICATE KEY UPDATE variations of INSERT into predictable and error-free operations.

  When INSERT IGNORE is used, if any of the inserted rows would violate a PRIMARY KEY or UNIQUE constraint, that row is not inserted. The rest of the rows are then inserted only if there is no other violation such as long strings or type mismatch, otherwise the appropriate error is returned.

  When REPLACE or ON DUPLICATE KEY UPDATE is used, the rows that need replacing or updating are updated with the given values. This works exactly like an UPDATE statement for those rows. Referential constraints and other integrity checks are enforced and update triggers are activated. The row count returned is simply the total number of rows inserted and updated.

- MySQL user-defined function and procedure syntax is very similar to SQL Standard syntax. A few changes may still be required.

### Firebird Compatibility

Firebird generally follows the SQL Standard. Applications can be ported to HyperSQL without difficulty.

### Apache Derby Compatibility

Apache Derby supports a smaller subset of the SQL Standard compared to HyperSQL. Applications can be ported to HyperSQL without difficulty.

- Use MVCC with `<set database transaction control statement>` if your application is multi-user.

- HyperSQL supports Java language functions and stored procedures with the standard syntax, which is similar to the way Derby supports these features.

### Oracle Compatibility

Recent versions of Oracle support Standard SQL syntax for outer joins and many other operations. In addition, HyperSQL features a setting to support Oracle syntax and semantics for the most widely used non-standard features.

- Use `<set database sql syntax ORA statement>` to enable support for some non-standard syntax of Oracle.

- Use MVCC with `<set database transaction control statement>` if your application is multi-user.

- Fine control over MVCC deadlock avoidance is provided by the `<set database transaction rollback on conflict statement>` and the corresponding `hsqldb.tx_conflict_rollback` connection property.

- If your application relies on Oracle behaviour for nulls in multi-column UNIQUE constraints, use `<set database sql unique nulls statement>` to change the default.

- If your application relies on Oracle behaviour for ordering of nulls in SELECT statements with ORDER BY, but without NULLS FIRST or NULLS LAST, use both `<set database sql nulls last statement>` and `<set database sql nulls order statement>` to change the defaults.

- If you use the non-standard concatenation of possibly NULL values in your select statements, you may need to change the setting for `<set database sql concat nulls statement>`.

- Many Oracle functions are supported, including no-arg functions such as SYSDATE and SYSTIMESTAMP and more complex ones such as TO_DATE and TO_CHAR.

- Non-standard data type definitions such as NUMBER, VARCHAR2, NVARCHAR2, BINARY_DOUBLE, BINARY_FLOAT, LONG, RAW are translated into the closest HyperSQL / SQL Standard equivalent in ORA mode.

- Non-standard DEFAULT definitions for columns, such as the use of DUAL with a SEQUENCE function are supported and translated in ORA syntax mode.

- The DATE type is interpreted as TIMESTAMP(0) in ORA syntax mode.

- The DUAL table and the expressions, ROWNUM, CURRVAL, NEXTVAL are supported in ORA syntax mode.

- HyperSQL natively supports operations involving datetime and interval values. These features are based on the SQL Standard.

- Many subtle automatic type conversions, syntax refinements and other common features are supported.

- SQL routines are generally portable, but some changes are required.

### DB2 Compatibility

DB2 is highly compatible with the SQL Standard (except for its lack of support for the INFORMATION_SCHEMA). Applications can be ported to HyperSQL without difficulty.

- Use `<set database sql syntax DB2 statement>` to enable support for some non-standard syntax of DB2.

- Use MVCC with `<set database transaction control statement>` if your application is multi-user.

- HyperSQL supports almost the entire syntax of DB2 together with many of the functions. Even local temporary tables using the SESSION pseudo schema are supported.

- The DB2 binary type definition FOR BIT DATA, as well as empty definition of column default values are supported in DB2 syntax mode.

- Many DB2 functions are supported.

- The DUAL table and the expressions, ROWNUM, CURRVAL, NEXTVAL are supported in DB2 syntax mode.

- SQL routines are highly portable with minimal change.

### MS SQLServer and Sybase Compatibility

SQLServer has some incompatibilities with the Standard syntax. The most significant is the use of square brackets instead of double quotes for case-sensitive column names.

- Use `<set database sql syntax MSS statement>` to enable support for the `CONVERT(<type definition>, <expression)` function with switched order of arguments

- Use MVCC with `<set database transaction control statement>` if your application is multi-user.

- If you use the non-standard concatenation of possibly NULL values in your select statements, you may need to change the setting for <set database sql concat nulls statement>.

- HyperSQL supports + for string concatenation. It also supports many functions supported by these dialects.

- SQLServer uses a non-standard expression, `SELECT 'A Test String'` to return a single row table. The standard form is `VALUES('A Test String')`. In MSS syntax mode, this type of SELECT is supported.

- SQL routines need quite a lot of changes.

$Revision: 5477 $

Chapter 11. System Management |  | Chapter 13. Properties