[Flow] DateTime with Oracle

[Axel Wüstemann]

Selecting DATE-Values works well, if I declare my model member of type 
string an convert it with a ViewHelper.

If it comes to update or insert I can't figure out, how to deal with:

$sql = "
INSERT INTO FOO (
    DATUM,
    ..
)
VALUES(
     ?,
     ...,

)";

$oraDateFormat = 'DD.MM.YYYY HH24:MI';
$formattedDate = 'TO_DATE(\'' . $object->getDate() . '\',\'' . 
$oraDateFormat . '\')';

$connection->executeUpdate(
    $sql,
    array(
     $formattedDate,
     ...
)

leads to:

INSERT INTO FOO ( DATUM, ... ) VALUES( ?, ... )' with params 
["TO_DATE('09.10.2013 10:10','DD.MM.YYYY HH24:MI')", ...]:

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

The quotes "TO_DATE..." seems to be wrong

So I tried an Expression:

$dateExp = new \Doctrine\ORM\Query\Expr\Func('TO_DATE',
    array(
      '\'' . $object->getDate() . '\'',
      '\'' . $oraDateFormat . '\''
));

$dateExp->__toString() returns

TO_DATE('09.10.2013 10:10', 'DD.MM.YYYY HH24:MI')

but inserting this in the query

$connection->executeUpdate(
    $sql,
    array(
     $dateExp->__toString(),
     ...
)

brings the same result, using only $dateExp instead of 
$dateExp->__toString() does not produce a valid binding.

.. VALUES( ?, ?, ?, ?, ?, ?, ?, ? )' with params [{}, ...]
Warning: oci_bind_by_name(): Invalid variable used for bind

What can I do?

Thanks for any hints.

Axel

Am 27.09.2013 10:21, schrieb Bastian Waidelich:
> Axel Wüstemann wrote:
>
> Hi Axel,
>
>> I need to use an Oracle database with tables and column names not to be
>> declared automatically by doctrine. So I declare all by annotations [...]
>> With the date property I get an error:
>>
>> Could not convert database value "2008-03-07" to Doctrine Type datetime.
>> Expected format: Y-m-d H:i:s
>>
>> In Oracle I set NLS_DATAFORMAT to yyyy-mm-dd.
>
> Maybe http://www.doctrine-project.org/jira/browse/DBAL-434 helps you
>