year,month,day parameters describe unrepresentable datetime

Jeff_W · Post by **Jeff_W** » Fri 29 Jan 2010 01:02

Hi
I receive this message from the Oracle Client provider in dot connect Universal

My query was a very basic 'select * from invoice'.

No message from the Oracle provider, it just displays the data. Database is 10g. The app we are currently working on may need the direct access in certain circumstances and your own info seems to indicate that your direct connection is quicker.

We have been using unidac in Delphi for our previous app version and do not encounter an error if we connect direct.

Interestingly I use your Oradeveloper Studio and it displays the info in client and direct mode, so is probably a Delphi app. The only thing I can add is that the database is almost certainly coded for the USA and my regional settings are for New Zealand. Could this difference in regional settings be a possible cause of the error?

Shalex · Post by **Shalex** » Fri 29 Jan 2010 10:51

Please give us the following information:
1) your version (x.xx.xx) of dotConnect Universal;
2) if you are using dotConnect Universal for connecting to Oracle database in the OCI mode, please specify the exact versions of Oracle server and Oracle Client; if you are using the Direct mode (the direct=true; parameter in the connection string), tell us only the exact version of your Oracle server;
3) the regional settings of your test machine (e.g., Standards and formats, Location, Language for the non-Unicode programs);
4) the character set of your Oracle server (e.g., NLS_CHARACTERSET, NLS_NCHAR_CHARACTERSET, NLS_LANGUAGE);

5) please send us a small test project with the DDL/DML script of your invoice table (with the data you retrieve when the error occurs). We will try to reproduce the error in our environment.

Jeff_W · Post by **Jeff_W** » Thu 04 Feb 2010 22:30

Database: current version is Oracle 11g. I am simply switching from client mode to direct mode and back on the same database. This is a database from a third party product

Version: 11.1.0.7.0 (database/tns/pSQL)
NLS:
PARAMETER VALUE
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET AL32UTF8
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

(drawn from OraDeveloper Studio v2.55.141, which does not exhibit the error in direct mode, but does look like it uses .NET)

dotConnect Universal: This is a trial mode and we have seen enough to proceed to purchase. Devart.data is 5.0.21.0 and devart.data.universal is 3.0.4.0

Regional Settings are standard (unchanged) English (New Zealand)

Shalex · Post by **Shalex** » Mon 08 Feb 2010 11:52

I cannot reproduce the problem.
1. Could you please send to us the DDL/DML script of your invoice table (or a similar table with a test data where the problem persists as well)?
2. Have you used your 'select * from invoice' query as a select command to fill a table of your DataSet generated with our DataSet Wizard? Is this your scenario? Please send us your call stack.
3. Tell us your connection string (mark your credentials with asterisks).

Jeff_W · Post by **Jeff_W** » Sat 13 Feb 2010 00:50

Turns out the problem is with one field. This field is read correctly through Oracle's own client (dotconnect oracle provider) but not the direct client (dotconnect oracleclient provider.

The field type is: TIMESTAMP(0) WITH TIME ZONE

Datetime and date fields read correctly.

Shalex · Post by **Shalex** » Mon 15 Feb 2010 15:17

I am using dotConnect Universal v 3.0.4 (the Tools | Universal | About menu of Visual Studio) and Oracle Server 11.1.0.6.0 (the NLS parameter are the same as yours).
Script:

Code: Select all

create table timestamptable(
col1 TIMESTAMP(0) WITH TIME ZONE
);
insert into timestamptable(col1) values (timestamp'2010-02-15 16:43:18.000000000 -02:00');

C# code:

Code: Select all

using (UniConnection conn = new UniConnection()) {
  conn.ConnectionString = "Provider=Oracle;direct=true;server=***;SID=***;port=***;uid=scott;pwd=tiger;";
  try {
    conn.Open();
    UniCommand comm = conn.CreateCommand();
    comm.CommandText = "select * from timestamptable";
    UniDataReader reader = comm.ExecuteReader();
    reader.Read();
    DateTime dt = reader.GetDateTime(0);
    Console.WriteLine("Success: {0}", dt.ToString());
  }
  catch (UniException ex) {
    Console.WriteLine(ex.Message, "Inner: {0}", ex.InnerException);
  }
  finally {
    Console.ReadLine();
  }
}

There are no errors. Please correct me, how should I modify this sample to reproduce the error.

Jeff_W · Post by **Jeff_W** » Tue 16 Feb 2010 08:56

As this is a commercial product and an Oracle runtime for which we are not permitted to have write access, I cannot reproduce your process. I also can't post a DDL script publicly as I have a non-disclosure in place. I suspect your combination of data may work. I could email the DDL script (from Oradeveloper studio).

Suggestions welcome.

StanislavK · Post by **StanislavK** » Thu 18 Feb 2010 09:26

If your non-disclosure policy allows to send us the DDL script without publishing it at the forum, you may send a letter to support * devart * com or use our contact form:
http://www.devart.com/company/contact.html

Please mention either the link to this topic or your forum nick in the letter.

David Koizkowski · Post by **David Koizkowski** » Thu 08 Apr 2010 16:11

I'm getting the same error using Dotconnect 5.6.0 Pro

{"Year, Month, and Day parameters describe an un-representable DateTime."}

IS there a fix for this?

AndreyR · Post by **AndreyR** » Fri 09 Apr 2010 14:41

I have replied to you here:
http://www.devart.com/forums/viewtopic.php?t=17598.