Numeric Array parameter problem

aegeavaz · 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 · 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 · 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 · 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 · 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 · 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