known-vendor-issues.rst

Known Vendor Issues
===================

This section describes known compatability issues with all the
supported database vendors:

PostgreSQL
----------

DateTime, DateTimeTz and Time Types
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Postgres has a variable return format for the datatype TIMESTAMP(n)
and TIME(n) if microseconds are allowed (n > 0). Whenever you save
a value with microseconds = 0. PostgreSQL will return this value in
the format:

::

    2010-10-10 10:10:10 (Y-m-d H:i:s)

However if you save a value with microseconds it will return the
full representation:

::

    2010-10-10 10:10:10.123456 (Y-m-d H:i:s.u)

Using the DateTime, DateTimeTz or Time type with microseconds
enabled columns can lead to errors because internally types expect
the exact format 'Y-m-d H:i:s' in combination with
``DateTime::createFromFormat()``. This method is twice a fast as
passing the date to the constructor of ``DateTime``.

This is why Doctrine always wants to create the time related types
without microseconds:


-  DateTime to ``TIMESTAMP(0) WITHOUT TIME ZONE``
-  DateTimeTz to ``TIMESTAMP(0) WITH TIME ZONE``
-  Time to ``TIME(0) WITHOUT TIME ZONE``

If you do not let Doctrine create the date column types and rather
use types with microseconds you have replace the "DateTime",
"DateTimeTz" and "Time" types with a more liberal DateTime parser
that detects the format automatically:

::

    use Doctrine\DBAL\Types\Type;
    
    Type::overrideType('datetime', 'Doctrine\DBAL\Types\VarDateTimeType');
    Type::overrideType('datetimetz', 'Doctrine\DBAL\Types\VarDateTimeType');
    Type::overrideType('time', 'Doctrine\DBAL\Types\VarDateTimeType');

Timezones and DateTimeTz
~~~~~~~~~~~~~~~~~~~~~~~~

Postgres does not save the actual Timezone Name but UTC-Offsets.
The difference is subtle but can be potentially very nasty. Derick
Rethans explains it very well
`in a blog post of his <http://derickrethans.nl/storing-date-time-in-database.html>`_.

MySQL
-----

DateTimeTz
~~~~~~~~~~

MySQL does not support saving timezones or offsets. The DateTimeTz
type therefore behave like the DateTime type.

Sqlite
------

DateTime
~~~~~~~~~~

Unlike most database management systems, Sqlite does not convert supplied
datetime strings to an internal storage format before storage. Instead, Sqlite
stores them as verbatim strings (i.e. as they are entered) and expects the user
to use the ``DATETIME()`` function when reading data which then converts the
stored values to datetime strings.
Because Doctrine is not using the ``DATETIME()`` function, you may end up with
"Could not convert database value ... to Doctrine Type datetime." exceptions
when trying to convert database values to ``\DateTime`` objects using

.. code-block:: php

    \Doctrine\DBAL\Types\Type::getType('datetime')->convertToPhpValue(...)

DateTimeTz
~~~~~~~~~~

Sqlite does not support saving timezones or offsets. The DateTimeTz
type therefore behave like the DateTime type.

IBM DB2
-------

DateTimeTz
~~~~~~~~~~

DB2 does not save the actual Timezone Name but UTC-Offsets. The
difference is subtle but can be potentially very nasty. Derick
Rethans explains it very well
`in a blog post of his <http://derickrethans.nl/storing-date-time-in-database.html>`_.

Oracle
------

DateTimeTz
~~~~~~~~~~

Oracle does not save the actual Timezone Name but UTC-Offsets. The
difference is subtle but can be potentially very nasty. Derick
Rethans explains it very well
`in a blog post of his <http://derickrethans.nl/storing-date-time-in-database.html>`_.

OCI8: SQL Queries with Question Marks
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

We had to implement a question mark to named parameter translation
inside the OCI8 DBAL Driver. It works as a very simple parser with two states: Inside Literal, Outside Literal.
From our perspective it should be working in all cases, but you have to be careful with certain
queries:

.. code-block:: sql

    SELECT * FROM users WHERE name = 'bar?'

Could in case of a bug with the parser be rewritten into:

.. code-block:: sql

    SELECT * FROM users WHERE name = 'bar:oci1'

For this reason you should always use prepared statements with
Oracle OCI8, never use string literals inside the queries. A query
for the user 'bar?' should look like:

.. code-block:: php

    $sql = 'SELECT * FROM users WHERE name = ?'
    $stmt = $conn->prepare($sql);
    $stmt->bindValue(1, 'bar?');
    $stmt->execute();

OCI-LOB instances
~~~~~~~~~~~~~~~~~

Doctrine 2 always requests CLOB columns as strings, so that you as
a developer never get access to the ``OCI-LOB`` instance. Since we
are using prepared statements for all write operations inside the
ORM, using strings instead of the ``OCI-LOB`` does not cause any
problems.

Microsoft SQL Server
--------------------

Unique and NULL
~~~~~~~~~~~~~~~

Microsoft SQL Server takes Unique very seriously. There is only
ever one NULL allowed contrary to the standard where you can have
multiple NULLs in a unique column.

DateTime, DateTimeTz and Time Types
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL Server has a variable return format for the datatype DATETIME(n)
if microseconds are allowed (n > 0). Whenever you save
a value with microseconds = 0.

If you do not let Doctrine create the date column types and rather
use types with microseconds you have replace the "DateTime",
"DateTimeTz" and "Time" types with a more liberal DateTime parser
that detects the format automatically:

::

    use Doctrine\DBAL\Types\Type;
    
    Type::overrideType('datetime', 'Doctrine\DBAL\Types\VarDateTime');
    Type::overrideType('datetimetz', 'Doctrine\DBAL\Types\VarDateTime');
    Type::overrideType('time', 'Doctrine\DBAL\Types\VarDateTime');