Oracle fractions of second error

Oracle fractions of second error

Postby Believer » Fri 31 Oct 2008 18:28

The following code generates the error, "ORA-01830: date format picture ends before converting entire input string"

Code: Select all
        Dim conn As New UniConnection("Provider=Oracle;Data Source=XE;User Id=IDM;Password=password")
        Dim cmd As New UniCommand("", conn)
        conn.Open()
        cmd.CommandType = CommandType.Text

        cmd.CommandText = "CREATE TABLE Account(RecID int,AccountNo varchar(255),CreatedBy int,OpenDate date,CloseDate date,AccountTypeID int,LastUpdateDT timestamp)"
        cmd.ExecuteNonQuery()
        cmd.CommandText = "Insert Into Account (RecID, AccountNo, CreatedBy, OpenDate, CloseDate, AccountTypeID, LastUpdateDT) Values(1635205,'0050400923356002',Null,{date '2007-04-02'},Null,5390,{timestamp '2008-10-15 05:48:00.123'})"
        cmd.ExecuteNonQuery()


If I change the last date expression to this then it works:
Code: Select all
{timestamp '2008-10-15 05:48:00'}


The command output window shows that the working (Oracle) SQL looks like this:
Code: Select all
Insert Into Account (RecID, AccountNo, CreatedBy, OpenDate, CloseDate, AccountTypeID, LastUpdateDT) Values(1635205,'0050400923356002',Null,TO_DATE('2007-04-02', 'YYYY-MM-DD'),Null,5390,TO_DATE('2008-10-15 05:48:00', 'yyyy-mm-dd hh:mi:ss'))


If I hack into this, adding the fractional seconds back in, as well as ".ff", then the fractions work:
Code: Select all
Insert Into Account (RecID, AccountNo, CreatedBy, OpenDate, CloseDate, AccountTypeID, LastUpdateDT)Values(1635205,'0050400923356002',Null,TO_DATE('2007-04-02', 'YYYY-MM-DD'),Null,5390,TO_TIMESTAMP('2008-10-15 05:48:00.123', 'yyyy-mm-dd hh:mi:ss.ff'))


The conclusion I reached is that UniDirect (or OraDirect) dropped the ".ff" from the converted SQL.

P.S. Although I got this far, I would still like a reply to my earlier generic question about UniSQL as it will still shed light on what I need to do. Thanks.
Believer
 
Posts: 4
Joined: Tue 28 Oct 2008 12:38

Postby AndreyR » Mon 03 Nov 2008 14:39

We reproduced the error and now we are investigating the problem.
The issue is quite complicated, so no timeframe can be provided.
Thank you for the bug report.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect Universal