Numeric Array parameter problem

Numeric Array parameter problem

Postby 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!!
aegeavaz
 
Posts: 7
Joined: Mon 06 Feb 2012 10:34
Location: Madrid

Postby 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 });
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44

Postby 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 < 15; i++)
{
     f1List.Add(i);
     f2List.Add(i);
     f3List.Add(i * 20M);
}

SaveData("AA", 1, f1List.ToArray(), f2List.ToArray(), f3List.ToArray());


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.
aegeavaz
 
Posts: 7
Joined: Mon 06 Feb 2012 10:34
Location: Madrid

Postby 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: 2020
Joined: Wed 02 Nov 2011 09:44

Postby 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: 2020
Joined: Wed 02 Nov 2011 09:44

Postby 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
Pinturiccio
Devart Team
 
Posts: 2020
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL