Corrupting Interval value (PgSqlInterval) if command's UnpreparedExecute is enabled

Corrupting Interval value (PgSqlInterval) if command's UnpreparedExecute is enabled

Postby EvilShrike » Tue 15 Mar 2016 14:39

Here's my test case which shows an Interval value is corrupted after insert. I'm inserting a TimeSpan value as 1 millisecond. But value which is inserted is 100 milliseconds. The root of evil is command option UnpreparedExecute. If it's disabled (by default) everything works as it should.

dotConnect Express: 7.4.602
PostgreSQL: 9.1.3 64-bit

Code: Select all
using System;
using System.Data;
using System.Data.Common;
using Devart.Data.PostgreSql;

public static class Program
{
   public static void Main()
   {
      TimeSpan value = TimeSpan.FromMilliseconds(1);
      TimeSpan valueLoaded;

      var conStr = "Host=...;Port=5432;User Id=...;Password=...;Database=...;Unicode=true;";
      using(PgSqlConnection con = new PgSqlConnection(conStr))
      {
         // Arrange:
         con.Open();
         Console.Write("Creating table..");
         var cmd = con.CreateCommand();
         cmd.CommandText = "drop table test";
         try {
            cmd.ExecuteNonQuery();
         } catch {}

         cmd.CommandText = "create table test (timespan interval) with oids";
         cmd.ExecuteNonQuery();
         Console.WriteLine("OK");

         // Arrange:
         Console.Write("Inserting..");

         cmd = con.CreateCommand();
         cmd.CommandText = "insert into test (timespan) values (:p1)";
         
         var parameter = cmd.CreateParameter();
         parameter.ParameterName = "p1";
         var nativeParameter = (PgSqlParameter)parameter;
         nativeParameter.PgSqlType = PgSqlType.Interval;
         nativeParameter.Value = (object)new PgSqlInterval((TimeSpan)value);
         cmd.Parameters.Add(parameter);

         // without this everything works fine:
         cmd.UnpreparedExecute = true;

         cmd.ExecuteNonQuery();
         Console.WriteLine("OK");

         // Test:
         Console.Write("Selecting..");
         cmd = con.CreateCommand();
         cmd.CommandText = "select timespan from test";
         using(var reader = cmd.ExecuteReader())
         {
            var nativeReader = (PgSqlDataReader)reader ;
            reader.Read();
            valueLoaded = (TimeSpan)nativeReader.GetPgSqlInterval(0);
            //valueLoaded = (TimeSpan)reader.GetValue(0);
         }
         Console.WriteLine("OK");

         // Check:
         Console.WriteLine("Value expected: " + value + ", ms: " + value.Milliseconds);
         Console.WriteLine("Value actual  : " + valueLoaded + ", ms: " + valueLoaded.Milliseconds);
      }
   }
}
Last edited by EvilShrike on Fri 18 Mar 2016 11:11, edited 1 time in total.
EvilShrike
 
Posts: 15
Joined: Mon 14 Mar 2016 17:11

Re: Corrupting Internal value (PgSqlInterval) if UnpreparedExecute is enabled

Postby Pinturiccio » Wed 16 Mar 2016 14:31

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Re: Corrupting Internal value (PgSqlInterval) if UnpreparedExecute is enabled

Postby Pinturiccio » Thu 17 Mar 2016 16:12

We have fixed the bug with inserting incorrect values via parameters having the PgSqlType.Interval type when UnpreparedExecute is set to true. We have also fixed the bug with millisecond output when calling the ToString method of the PgSqlInterval class.

The fixes were added in the last moment before releasing the build, and they weren't added to the build announce. We will add them into the announce of the next build, however they are already available since the build 7.4.616.

dotConnect for PostgreSQL 7.4.616 can be downloaded from http://www.devart.com/dotconnect/postgresql/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Re: Corrupting Interval value (PgSqlInterval) if command's UnpreparedExecute is enabled

Postby EvilShrike » Fri 18 Mar 2016 11:15

Cool! Are going to publish the release to nuget.org (express)?
EvilShrike
 
Posts: 15
Joined: Mon 14 Mar 2016 17:11

Re: Corrupting Interval value (PgSqlInterval) if command's UnpreparedExecute is enabled

Postby Pinturiccio » Fri 18 Mar 2016 13:00

We have updated dotConnect for PostgreSQL Express to version 7.4.616 in NuGet: https://www.nuget.org/packages/dotConnect.Express.for.PostgreSQL/7.4.616 . Please try again.
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Re: Corrupting Interval value (PgSqlInterval) if command's UnpreparedExecute is enabled

Postby EvilShrike » Fri 18 Mar 2016 14:09

Works great. Thanks!
EvilShrike
 
Posts: 15
Joined: Mon 14 Mar 2016 17:11


Return to dotConnect for PostgreSQL