# Introduction

This document contains informations on compatible versions of supported databases and other database-specific information.

# Supported RDBMS

RDBMS | Driver | Tester
--- | --- | ---
Apache Derby | org.apache.derby.jdbc.EmbeddedDriver | Thomas Fox
Hsqldb | org.hsql.jdbcDriver | Thomas Fox
MySQL | org.gjt.mm.mysql.Driver | Scott Eade
Postgres | org.postgresql.Driver | Scott Eade
Oracle | oracle.jdbc.driver.OracleDriver | Thomas Fox
MS SQL | net.sourceforge.jtds.jdbc.Driver | Greg Monroe

If your RDBMS is not listed here, please read the document about writing DB Adapters.

# Apache Derby

## Supported Versions

Derby 10.5.1.1 introduced support for limit and offset. This is used in within Torque 4, so limit and offset does not work for Derby < 10.5.1.1

# HSQLDB

## Supported Versions

Torque 4 was tested against HSQLDB 2.2.8. It is unknown whether Torque 4 still works with hsqldb 1.x.-

# MySQL

## Supported Versions

Torque 4 was tested against MySQL 5.5. Subselects are known not to work for MySQL < 5.0. There are no other known issues for old MySQL versions.

## Table create options

The MySQL templates can handle create options for tables. For example, the desired create-sql output is

    CREATE TABLE book
    (
        ...
    ) COLLATE=latin1_german2_ci ENGINE=InnoDB;

For this, specify the desired options in the option elements of the table to create:

      <table name="book" description="Book table">
        <option key="COLLATE" value="latin1_german2_ci"/>
        <option key="ENGINE" value="InnoDB"/>
        ...
      </table>

Torque recognizes the following keys to be separated with an equals character("=") between key and value:

- ENGINE
- AVG_ROW_LENGTH
- CHARACTER SET
- DEFAULT CHARACTER SET
- CHECKSUM
- COLLATE
- DEFAULT COLLATE
- CONNECTION
- DATA DIRECTORY
- DELAY_KEY_WRITE
- INDEX DIRECTORY
- INSERT_METHOD
- KEY_BLOCK_SIZE
- MAX_ROWS
- MIN_ROWS
- PACK_KEYS
- PASSWORD
- ROW_FORMAT
- TABLESPACE
- UNION

Torque recognizes the following keys to be separated with a space character(" ") between key and value:

- PARTITION BY

All other keys are ignored.

# PostgreSQL

## Supported Versions

Torque 4 was tested against PostgreSQL 9.1. The CASCADE option for the drop table commands in the generated SQL will only work for PostgreSQL 7.3 and later. Columns declared as BLOB can not be set to null by Torque, as torque maps them to BYTEA but treats them as Types.BLOB internally, which is not accepted by Postgresql. Use one of the other *BINARY types instead, they are all mapped to the postgresql datatype BYTEA. There are no other known issues for old PostgreSQL versions.

# Oracle Howto

## Supported Versions

Torque 4 was tested against Oracle 11g. There are no known issues for Oracle 9i and later. The following issues exist for older versions of Oracle:

The data type TIMESTAMP is not supported up to and including Oracle 8.1.7. Use the types DATE or TIME instead of TIMESTAMP.

## Oracle peculiarities

TODO check for Torque 4 The data type TIME only has day accuracy for Oracle. This is due to the fact that oracle does not support a SQL type TIME. If you are using Oracle 9i and later, you can use the type TIMESTAMP instead which gives you millisecond accuracy.

Oracle does not distinguish between empty strings and null strings. If an empty string is inserted into a table, it will be treated as null. If you want to re-read this column by selecting records which contain an empty string, oracle will return no columns. The only way to select the column is to query for records which contain null in that column.
This behaviour is different in most other databases. So if you want to write code which works for other databases and oracle, you need to consider both cases - the column might be null and it might contain an empty string. For example:

    Criterion c1 = new Criterion(COLUMN, "", Criteria.EQUAL);
    Criterion c2 = new Criterion(COLUMN, null, Criteria.ISNULL);
    criteria.and(c1.or(c2));

# Microsoft SQL Server

## Supported Versions

Torque 4 was tested against MSSQL 2012. There are no known issues for MSSQL versions >= MSSQL 2000. See the jira issue TORQUE-46 for the issues with MSSQL 7.

MSSQL 2005 and earlier do not support the Date data type. So to work with these databases, replace the data type of all DATE columns by the data type TIMESTAMP.

## Database Creation

It helps to name the database the same name as your project. If the name is not the same, you will need to manually change the database name in your properties files. After the database is created, you will need to create a new user, or give an existing user permissions to the new database. Create the new user with SQL Server Authentication, and assign the users default database to the newly created database. Make sure the database user has the db_owner role. Please refer to SQL Server documentation for further details.

## JDBC Driver

There is an excellent open source driver for MS SQL called jTDS. This driver is being used in production mode with Torque and many other JDBC applications. This driver is used for testing Torque with MSSQL, other drivers should work the same, but your milage may vary.

## Generated SQL Code

SQL Scripts generated by the Ant sql target do not contain the fully qualified table names, e.g. user.table. When manually running these scripts, do this as the user you want to own the tables.