Numeric Array parameter problem

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
aegeavaz
Posts: 7
Joined: Mon 06 Feb 2012 10:34
Location: Madrid
Contact:

Numeric Array parameter problem

Post by aegeavaz » Fri 13 Apr 2012 14:59

Hi, we're having problems using .NET decimal values in a Numeric PgSqlArray. Could you please tell us if we are doing something wrong? or is a bug?

We have this simple table:

Code: Select all

CREATE TABLE test_table
(
  f1 character varying(2) NOT NULL, 
  f2 integer NOT NULL, 
  f3 integer NOT NULL, 
  f4 integer NOT NULL, 
  f5 boolean, 
  f6 numeric(44,30),
  CONSTRAINT pk_test_table PRIMARY KEY (f1, f2 , f3 , f4 )
)
WITH (
  OIDS=FALSE
);
And we have created this function:

Code: Select all

CREATE OR REPLACE FUNCTION SaveTestData(p_f1 character varying, p_f2 integer, p_f3 integer[], p_f4 integer[], p_f6 numeric[])
  RETURNS void AS
$BODY$
begin
	insert into test_table (f1, f2, f3, f4, f5, f6)
	select p_f1, p_f2, p_f3[s], p_f4[s], false, p_f6[s]
	from generate_series(1,array_upper(p_f3,1)) as s;
end;
$BODY$
  LANGUAGE plpgsql
And finally this is the .NET code that is getting the error:

Code: Select all

private void AddParameter(PgSqlCommand comm, string parameterName, PgSqlType parameterType, int parameterSize, object parameterValue)
        {
            if (parameterType == PgSqlType.VarChar && parameterSize == 0)
                comm.Parameters.Add(new PgSqlParameter(parameterName, parameterType, parameterSize) { Value = null, Direction = ParameterDirection.Input });
            else
                comm.Parameters.Add(new PgSqlParameter(parameterName, parameterType, parameterSize) { Value = parameterValue, Direction = ParameterDirection.Input });
        }

        private void AddArrayParameter(PgSqlCommand comm, string name, PgSqlType type, object[] array)
        {
                if (array.Length==0)
                comm.Parameters.Add(new PgSqlParameter(name, PgSqlType.Array) { Value = null });
            else
                comm.Parameters.Add(new PgSqlParameter(name, PgSqlType.Array) { Value = new PgSqlArray(array, type, 1, array.Length) });
        }

        private void SaveData(string f1, int f2, int[] f3, int[] f4, decimal[] f6)
        {
            string connectionString = @"Server=-------; Port=-----; Database=-----; User Id=------; Password=--------";
            using (PgSqlConnection conn = new PgSqlConnection(connectionString))
            {
                conn.Open();
                PgSqlCommand command = new PgSqlCommand("savetestdata", conn);
                command.CommandType = CommandType.StoredProcedure;

                AddParameter(command, "p_f1", PgSqlType.VarChar, f1.Length, f1);
                AddParameter(command, "p_f2", PgSqlType.Int, 0, f2);
                AddArrayParameter(command, "p_f3", PgSqlType.Int, Array.ConvertAll(f3, delegate(int value) { return (object)value; }));
                AddArrayParameter(command, "p_f4", PgSqlType.Int, Array.ConvertAll(f4, delegate(int value) { return (object)value; }));
                AddArrayParameter(command, "p_f6", PgSqlType.Numeric, Array.ConvertAll(f6, delegate(decimal value) { return (object)value; }));

                    command.ExecuteNonQuery();  --> PgSqlException: incorrect binary data format in bind parameter 5
                
                conn.Close();
            }
        }
We are using last dotConnect for PostgreSQL build (5.80.341) and PostgreSQL Server 9.1.2 64bits (Windows).

Thanks!!

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

Post by Pinturiccio » Tue 17 Apr 2012 14:44

We have reproduced the issue. It occurs when you set as the p_f6 parameter an array with the decimal type, but not all of its elements have the decimal type.
For example:
1. The following code invokes the exception "PgSqlException: incorrect binary data format in bind parameter 5":

Code: Select all

SaveData("Hi", 2, new int[5] { 1, 3, 5, 7, 8 }, new int[5] { 77, 88, 99, 66, 55 }, new decimal[5] { 5, 5, 5, 5, 5 });
2. The following code will work correctly:

Code: Select all

SaveData("Hi", 2, new int[5] { 1, 3, 5, 7, 8 }, new int[5] { 77, 88, 99, 66, 55 }, new decimal[5] { 5.5M, 5.5M, 5.5M, 5.5M, 5.5M });

aegeavaz
Posts: 7
Joined: Mon 06 Feb 2012 10:34
Location: Madrid
Contact:

Post by aegeavaz » Tue 17 Apr 2012 16:42

Hi, thanks for the reply...

This is the code we've used to call the SaveData:

Code: Select all

List f1List = new List();
List f2List = new List();
List f3List = new List();

for (int i = 0; i ());
And in our real system the decimal list is populated with mathematical calculations the could generate a value with no decimal part (1.5M / 1.5M).

Well...
We'll appreciate any advice regarding this issue.
Thank you.

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

Post by Pinturiccio » Wed 18 Apr 2012 11:22

We have reproduced the issue. It occurs when an element of an array is used, having the decimal type, but no fractional part. We will investigate it and notify you about the results as soon as possible.

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

Post by Pinturiccio » Fri 20 Apr 2012 07:55

We have fixed the bug with decimal array as a parameter value of PgSqlType.Numeric. This fix will be available starting from the next build of dotConnect for PostgreSQL.

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

Post by Pinturiccio » Fri 27 Apr 2012 06:36

The new build of dotConnect for PostgreSQL 5.80.350 is available for download now!
It 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).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=23998

Post Reply