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

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
EvilShrike
Posts: 19
Joined: Mon 14 Mar 2016 17:11

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

Post by 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.

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

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

Post by 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: 2192
Joined: Wed 02 Nov 2011 09:44

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

Post by 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/postgr ... nload.html (trial version) or from Registered Users' Area (for users with valid subscription only).

EvilShrike
Posts: 19
Joined: Mon 14 Mar 2016 17:11

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

Post by EvilShrike » Fri 18 Mar 2016 11:15

Cool! Are going to publish the release to nuget.org (express)?

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

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

Post by 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/dotConne ... QL/7.4.616 . Please try again.

EvilShrike
Posts: 19
Joined: Mon 14 Mar 2016 17:11

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

Post by EvilShrike » Fri 18 Mar 2016 14:09

Works great. Thanks!

Post Reply