Page 1 of 1

malformed array literal exception

Posted: Tue 19 Mar 2013 11:42
by linkinturk
Hi.
I want to run function which parameter is composite type array.

I have a table like this :

Code: Select all

CREATE TABLE super_tournament
(
  s_tournament_id integer NOT NULL,
  name character varying(100) NOT NULL,
  active smallint NOT NULL);
This is my composite type :

Code: Select all

CREATE TYPE super_tournament_type AS
   (_s_tournament_id integer,
    _name character varying(100),
    _active smallint);


And this is function uses composite type's array as parameter :

Code: Select all

CREATE OR REPLACE FUNCTION betradar_meta_integration.super_tournament_func(p_super_tournament super_tournament_type[])
  RETURNS void AS
$BODY$
BEGIN
	WITH upsert AS (
		UPDATE super_tournament
		SET
			"name" = amc._name,
			active = amc._active
		FROM UNNEST (p_super_tournament) AS amc
		WHERE s_tournament_id = amc._s_tournament_id
		RETURNING amc.*
	) INSERT INTO super_tournament
	  (
	  	s_tournament_id,
	  	"name",
	  	active
	  )
	  
	  SELECT * FROM UNNEST (p_super_tournament) AS amc WHERE NOT EXISTS (
		SELECT
			1
		FROM
			upsert
	) ;
END
 $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
on .net side I have a Poco object like this :

Code: Select all

 public class super_tournament
    {
        public Int32 s_tournament_id { get; set; }
        public string name { get; set; }
        public Int32 active { get; set; }
    }
And this my method :

Code: Select all

        public static void Test<T>(List<T> data,string typeName,string parameterName,string functionName,PgSqlConnection connection) where T : new()
        {
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            var propertyInfos = typeof(T).GetProperties();
            var array = new object[data.Count];
            PgSqlRowType type = PgSqlRowType.GetRowType(typeName, connection);
            for (var i = 0; i < data.Count; i++)
            {
                var row = new PgSqlRow(type);
                int say = 0;
                foreach (var propertyInfo in propertyInfos)
                {
                    row[say] = data[i].GetType().GetProperty(propertyInfo.Name).GetValue(data[i], null);
                    say++;
                }
                array[i] = row;
            }
            var parray = new PgSqlArray(array, PgSqlType.Row, 1, array.Count());
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = functionName;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add( new PgSqlParameter { ParameterName = parameterName, PgSqlType = PgSqlType.Array, RowType = type, Value = parray, Direction = ParameterDirection.Input });
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd.Dispose();
            }
        }
and i run like this :

Code: Select all

List<super_tournament> denemes = new List<super_tournament>();
denemes.Add(new super_tournament{active = 1,name = "Premiere League",s_tournament_id = 11});
denemes.Add(new super_tournament { active = 1, name = "Serie A", s_tournament_id = 12 });
denemes.Add(new super_tournament { active = 1, name = "Bundesliga", s_tournament_id = 13 });
Test(superTournamentListe, "super_tournament_type", "p_super_tournament", "super_tournament_func", connection);
and its give this error :
{Error: 22P02: malformed array literal: "{"(11,"Premiere League",1)","(12,"Serie A",1)","(13,Bundesliga,1)"}"}
i think string character must be in quote not double quote.
But i have no idea how to fix it.
Can you help me?

Re: malformed array literal exception

Posted: Wed 20 Mar 2013 14:20
by Pinturiccio
We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.

Re: malformed array literal exception

Posted: Fri 05 Apr 2013 08:24
by Pinturiccio
We have fixed the bug with passing an array of the row type as a parameter value.
The new build of dotConnect for PostgreSQL 6.6.217 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://forums.devart.com/viewtopic.php?t=26317