DATE ISSUE. MONTH AND DAY EXCHANGED.

DATE ISSUE. MONTH AND DAY EXCHANGED.

Postby JORGEMAL » Fri 04 Dec 2009 18:40

I wonder if this is a DotConnect issue so please let me know for further investigation.

I am inserting a date field into a table using the (year, month day) overload of PgSqlTimeStamp but when I go and check the record I get month and day exchanged.

For example, if I specify July 1st, 1940 I get January 7th, 1940.

Also, if I specify a date like January 31, 1940 I get the following exception:

date/time field value out of range: "31/01/1940 AD"
PgSqlException
at Devart.Data.PostgreSql.PgSqlDataReader.e(Int32 A_0)
at Devart.Data.PostgreSql.PgSqlCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at Devart.Common.DbCommandBase.ExecuteNonQuery()
at Artista.Modifica(String& strTipoMensaje, Int16& intTotalMensajes, String[,] arrMensajes, String strOrigen)


Please advice.
JORGEMAL
 
Posts: 165
Joined: Thu 03 Jul 2008 23:55

Postby Shalex » Mon 07 Dec 2009 10:54

It seems like date format mismatch on your client side (the regional settings of Windows) and your database (the datestyle setting in postgresql.conf).

I have checked the 4.65.57 version of dotConnect for PostgreSQL with the following code:
Code: Select all
    using (PgSqlConnection conn = new PgSqlConnection(ConfigurationManager.ConnectionStrings["connStr"].ConnectionString))
    {
        conn.Open();
        PgSqlCommand command = conn.CreateCommand();
        command.CommandText = "insert into timetable (datecolumn) values (@datecolumn)";
        PgSqlParameter param = new PgSqlParameter();
        param.ParameterName = "@datecolumn";
        param.PgSqlType = PgSqlType.Date;
        param.Value = DateTime.Now.Date;
        command.Parameters.Add(param);

        command.ExecuteNonQuery();
    }

Here is a script of timetable from my database at PostgreSQL Server 8.4:
Code: Select all
CREATE TABLE timetable
(
  datecolumn date
)

Please tell us how should we modify this sample to reproduce the problem? Also please tell us the date format at your client and database side.
Shalex
Devart Team
 
Posts: 7793
Joined: Thu 14 Aug 2008 12:44

Postby JORGEMAL » Mon 07 Dec 2009 15:16

Shalex wrote:It seems like date format mismatch on your client side (the regional settings of Windows) and your database (the datestyle setting in postgresql.conf).

I have checked the 4.65.57 version of dotConnect for PostgreSQL with the following code:
Code: Select all
    using (PgSqlConnection conn = new PgSqlConnection(ConfigurationManager.ConnectionStrings["connStr"].ConnectionString))
    {
        conn.Open();
        PgSqlCommand command = conn.CreateCommand();
        command.CommandText = "insert into timetable (datecolumn) values (@datecolumn)";
        PgSqlParameter param = new PgSqlParameter();
        param.ParameterName = "@datecolumn";
        param.PgSqlType = PgSqlType.Date;
        param.Value = DateTime.Now.Date;
        command.Parameters.Add(param);

        command.ExecuteNonQuery();
    }

Here is a script of timetable from my database at PostgreSQL Server 8.4:
Code: Select all
CREATE TABLE timetable
(
  datecolumn date
)

Please tell us how should we modify this sample to reproduce the problem? Also please tell us the date format at your client and database side.



All of my code to insert a record is within a class so I prepare the date information as follows before calling my class insert method:
Artista.FechaNac = new PgSqlTimeStamp(dteFechaNac.Date.Year, dteFechaNac.Date.Month, dteFechaNac.Date.Day);

Where dteFechaNac is an ASPX control that displays a calendar and the user can select a date.
Also, "Artista" is the name of my class and "FechaNac" is a static property. As you can see, I use the "year, month, day" overload of PgSqlTimeStamp.

My table field is of DATE type.

I have reviewed my Postgresql.conf and I have the following entry:
datestyle = 'iso, mdy'

Also, I checked the regional settings in my PC and the short date format is set to dd/MM/yyyy. I tried changing it to MM/dd/yyyy but I have the same result.

Respectfully,
Jorge Maldonado
JORGEMAL
 
Posts: 165
Joined: Thu 03 Jul 2008 23:55

Postby Shalex » Tue 08 Dec 2009 14:48

1. Tell us your current versions of dotConnect for PostgreSQL (x.xx.xx) and PostgreSQL Server (x.x.x).
2. Are you using protocol 2.0 or 3.0? Post here your connection string.
3. Could you please execute this code? Does it work correctly?
Code: Select all
    using (PgSqlConnection conn = new PgSqlConnection(ConfigurationManager.ConnectionStrings["connStr"].ConnectionString))
    {
        conn.Open();
        PgSqlCommand command = conn.CreateCommand();
        command.CommandText = "insert into timetable (datecolumn) values (@datecolumn)";
        PgSqlParameter param = new PgSqlParameter();
        param.ParameterName = "@datecolumn";
        param.PgSqlType = PgSqlType.Date;
        param.Value = new PgSqlTimeStamp(2009, 1, 31);;
        command.Parameters.Add(param);

        command.ExecuteNonQuery();
    }

The script for timetable is above.
Shalex
Devart Team
 
Posts: 7793
Joined: Thu 14 Aug 2008 12:44

Postby JORGEMAL » Wed 09 Dec 2009 00:33

1. Version of dotConnect for PostgreSQL: 4.0.12.0
Version of PostgreSQL: 8.3.1

2. I am using Protocol 2.0, here is the connection string:


3. The code you sent executes correctly so now I am sure the problem has to do with me.
Everything was working fine until I changed my development PC but I basically installed it and configured it as usual.

I do not really know where to look for an answer.
I will very much appreciate any advice.

Respectfully,
Jorge Maldonado


Shalex wrote:1. Tell us your current versions of dotConnect for PostgreSQL (x.xx.xx) and PostgreSQL Server (x.x.x).
2. Are you using protocol 2.0 or 3.0? Post here your connection string.
3. Could you please execute this code? Does it work correctly?
Code: Select all
    using (PgSqlConnection conn = new PgSqlConnection(ConfigurationManager.ConnectionStrings["connStr"].ConnectionString))
    {
        conn.Open();
        PgSqlCommand command = conn.CreateCommand();
        command.CommandText = "insert into timetable (datecolumn) values (@datecolumn)";
        PgSqlParameter param = new PgSqlParameter();
        param.ParameterName = "@datecolumn";
        param.PgSqlType = PgSqlType.Date;
        param.Value = new PgSqlTimeStamp(2009, 1, 31);;
        command.Parameters.Add(param);

        command.ExecuteNonQuery();
    }

The script for timetable is above.
JORGEMAL
 
Posts: 165
Joined: Thu 03 Jul 2008 23:55

Postby Shalex » Wed 09 Dec 2009 12:55

1. We recommend you to use Protocol 3.0 (the Protocol=Ver30; connection string parameter). This should help. Why did you decide to choose the 2.0 protocol in your project?
2. Make sure that you use the PgSqlTimeStamp(year, month, day) overload in your code correctly.

If this doesn't help, please send us a small test project with the DDL script to reproduce the error in our environment.
Shalex
Devart Team
 
Posts: 7793
Joined: Thu 14 Aug 2008 12:44

Postby JORGEMAL » Wed 09 Dec 2009 16:57

I changed protocol to v30 without success. I do not really know why I was using protocol v20, I guess because of some tests I did in the past on queries with several results.

I tried to attach a file but I did not find how so I would like to send you and email with a document where I include code from my app for your review; please tell me an address.

Anyway, I am considering installing my machine again because this issue started when I changed my development PC and I have noticed some "very small and almost insignificant" strange behavior in my Visual Studio.

ADDITIONAL COMMENTS:
* I created my database with ENCODING: WIN1252 (for western languages) because UTF-8 restricts me from using some characters like Ñ for example.

I appreciate your support.

Jorge Maldonado


Shalex wrote:1. We recommend you to use Protocol 3.0 (the Protocol=Ver30; connection string parameter). This should help. Why did you decide to choose the 2.0 protocol in your project?
2. Make sure that you use the PgSqlTimeStamp(year, month, day) overload in your code correctly.

If this doesn't help, please send us a small test project with the DDL script to reproduce the error in our environment.
JORGEMAL
 
Posts: 165
Joined: Thu 03 Jul 2008 23:55

Postby Shalex » Thu 10 Dec 2009 09:19

Please try to localize the problem in a small project. My e-mail address is (alexsh at devart*com).
Shalex
Devart Team
 
Posts: 7793
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL