malformed array literal exception

malformed array literal exception

Postby linkinturk » Tue 19 Mar 2013 11:42

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?
linkinturk
 
Posts: 1
Joined: Tue 19 Mar 2013 11:20

Re: malformed array literal exception

Postby Pinturiccio » Wed 20 Mar 2013 14:20

We have reproduced the issue. We will investigate it and notify you about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 2021
Joined: Wed 02 Nov 2011 09:44

Re: malformed array literal exception

Postby Pinturiccio » Fri 05 Apr 2013 08:24

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/postgresql/download.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
Pinturiccio
Devart Team
 
Posts: 2021
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL