Forums PostgreSQL.fr

// Création des listes
List<long> l_round_id = new List<long>();
List<long>  l_season_d = new List<long>();
List<DateTime?> l_end_date = new List<DateTime?>();
List<String> l_groups = new List<string>();
List<DateTime?> l_last_updated = new List<DateTime?>();
List<String> l_name = new List<string>();
List<String> l_ordermethod = new List<string>();
List<DateTime?> l_start_date = new List<DateTime?>();
List<String> l_type = new List<string>();
List<String> l_has_outgroup_matches = new List<string>();

// Remplissage des listes
l_round_id.Add(6786);
l_round_id.Add(6787);
l_round_id.Add(6788);
l_season_d.Add(115);
l_season_d.Add(115);
l_season_d.Add(115);
l_end_date.Add(null);
l_end_date.Add(Utils.parseDatetimeNullable("27/07/2009"));
l_end_date.Add(Utils.parseDatetimeNullable("27/04/2012"));
l_groups.Add("0");
l_groups.Add("1");
l_groups.Add("2");
l_last_updated.Add(Utils.parseDatetimeNullable("27/07/2009"));
l_last_updated.Add(Utils.parseDatetimeNullable("27/07/2009"));
l_last_updated.Add(Utils.parseDatetimeNullable("27/04/2012"));
l_name.Add(null);
l_name.Add("Round 2");
l_name.Add("Round 3");
l_ordermethod.Add(null);
l_ordermethod.Add("Order");
l_ordermethod.Add("Order");
l_start_date.Add(Utils.parseDatetimeNullable("27/07/2009"));
l_start_date.Add(Utils.parseDatetimeNullable("27/07/2009"));
l_start_date.Add(Utils.parseDatetimeNullable("27/04/2012"));
l_type.Add("Table");
l_type.Add("Cup");
l_type.Add("Cup");
l_has_outgroup_matches.Add("yes");
l_has_outgroup_matches.Add("no");
l_has_outgroup_matches.Add("no");

int nbMaj = 0;
using (NpgsqlConnection c = (NpgsqlConnection)conn.getConnexion())
{
	using (NpgsqlCommand command = new NpgsqlCommand("merge_round_loop", c))
	{
		command.CommandType = CommandType.StoredProcedure;
		command.UpdatedRowSource = UpdateRowSource.None;
		// Affectation des paramètres
		command.Parameters.Clear();
		command.Parameters.Add("l_round_id", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Integer);
		command.Parameters.Add("l_season_d", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Integer);
		command.Parameters.Add("l_end_date", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Date);
		command.Parameters.Add("l_groups", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Text);
		command.Parameters.Add("l_last_updated", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Timestamp);
		command.Parameters.Add("l_name", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Text);
		command.Parameters.Add("l_ordermethod", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Text);
		command.Parameters.Add("l_start_date", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Date);
		command.Parameters.Add("l_type", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Text);
		command.Parameters.Add("l_has_outgroup_matches", NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Text);
		command.Parameters[0].Value = l_round_id;
		command.Parameters[1].Value = l_season_id;
		command.Parameters[2].Value = l_end_date;
		command.Parameters[3].Value = l_groups;
		command.Parameters[4].Value = l_last_updated;
		command.Parameters[5].Value = l_name;
		command.Parameters[6].Value = l_ordermethod;
		command.Parameters[7].Value = l_start_date;
		command.Parameters[8].Value = l_type;
		command.Parameters[9].Value = l_has_outgroup_matches;
		try{
			// Ouverture de la connexion et exécution
			c.Open();
			Object result = command.ExecuteScalar();
			//nbMaj = (int)command.ExecuteScalar();
		}
		catch(Exception e)
		{
		}
	}
}
if (nbMaj < l_round_id.Count)
	log.Error("updateRoundProcedureArray() - Toutes les lignes n'ont pas été insérées/updatées : " + nbMaj.ToString() + "/" + l_round_id.Count.ToString());

-- Function: merge_round_loop(integer[], integer[], date[], text[], timestamp without time zone[], text[], text[], date[], text[], text[])
-- DROP FUNCTION merge_round_loop(integer[], integer[], date[], text[], timestamp without time zone[], text[], text[], date[], text[], text[]);
CREATE OR REPLACE FUNCTION merge_round_loop(round_id integer[], season_id integer[], end_date date[], groups text[], last_updated timestamp without time zone[], "name" text[], ordermethod text[], start_date date[], "type" text[], has_outgroup_matches text[])
  RETURNS text AS
$BODY$ 
DECLARE	
	-- valeurs à insérer
	my_round_id integer;
	my_season_id integer;
	my_end_date date;
	my_groups text;
	my_last_updated timestamp without time zone;
	my_name text;
	my_ordermethod text;
	my_start_date date;
	my_type text;
	my_has_outgroup_matches text;
	-- compteur et retour d'appel à la fonction
	compteur integer;
	retour integer;
	retour_text varchar;
	-- exception
	curtime timestamp;
	table_name text;
	error text;
	request text;
BEGIN 
	-- initalisation du compteur
	compteur := 0;
	retour_text := '';
	
	-- parcours de la liste
	FOR i IN 1..array_upper(round_id,1) LOOP
	
		-- récupération des valeurs des listes
 		my_round_id := round_id[i];
		my_season_id := season_id[i];
		my_end_date := end_date[i];
		my_groups := groups[i];
		my_last_updated := last_updated[i];
		my_name := name[i];	
		my_ordermethod := ordermethod[i];
		my_start_date := start_date[i];
		my_type = type[i];
		my_has_outgroup_matches := has_outgroup_matches[i];
		
		-- appel à la fonction de merge
		retour := merge_round(my_round_id, my_season_id, my_end_date, my_groups, my_last_updated, my_name, my_ordermethod, my_start_date, my_type, my_has_outgroup_matches);
		
		-- MAJ du compteur
		compteur := compteur + retour;

	END LOOP; 

	-- retour du nombre d'enregistrements mis à jour
	RETURN compteur;

END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION merge_round_loop(integer[], integer[], date[], text[], timestamp without time zone[], text[], text[], date[], text[], text[]) OWNER TO postgres;

L'exception System.NullReferenceException s'est produite
  Message=La référence d'objet n'est pas définie à une instance d'un objet.
  Source=Npgsql
  StackTrace:
       à NpgsqlTypes.NpgsqlTypesHelper.DefinedType(Object item)
       à NpgsqlTypes.ArrayNativeToBackendTypeConverter.WriteItem(NpgsqlNativeTypeInfo TypeInfo, Object item, StringBuilder sb)
       à NpgsqlTypes.ArrayNativeToBackendTypeConverter.WriteEnumeration(NpgsqlNativeTypeInfo TypeInfo, IEnumerable col, StringBuilder sb)
       à NpgsqlTypes.ArrayNativeToBackendTypeConverter.WriteItem(NpgsqlNativeTypeInfo TypeInfo, Object item, StringBuilder sb)
       à NpgsqlTypes.ArrayNativeToBackendTypeConverter.FromArray(NpgsqlNativeTypeInfo TypeInfo, Object NativeData)
       à NpgsqlTypes.NpgsqlNativeTypeInfo.ConvertToBackendPlainQuery(Object NativeData)
       à NpgsqlTypes.NpgsqlNativeTypeInfo.ConvertToBackend(Object NativeData, Boolean ForExtendedQuery)
       à Npgsql.NpgsqlCommand.GetClearCommandText()
       à Npgsql.NpgsqlCommand.GetCommandText()
       à Npgsql.NpgsqlQuery.WriteToStream(Stream outputStream)
       à Npgsql.NpgsqlReadyState.QueryEnum(NpgsqlConnector context, NpgsqlCommand command)
       à Npgsql.NpgsqlConnector.QueryEnum(NpgsqlCommand queryCommand)
       à Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb)
       à Npgsql.NpgsqlCommand.ExecuteScalar()
       à ptrs.dal.implementations.postgresql8.DalImportGsm.updateRoundProcedureArray(List`1 l_round_id, List`1 l_season_id, List`1 l_end_date, List`1 l_groups, List`1 l_last_updated, List`1 l_name, List`1 l_ordermethod, List`1 l_start_date, List`1 l_type, List`1 l_has_outgroup_matches) dans C:\projet\PTRS\DataAccessLayer\implementations\postgresql8\DalImportGsm.cs:ligne 2017
  InnerException: 

select merge_round_loop(
	array[1,6787,53], 
	array[2,2763,53], 
	array[cast('01/07/2009' as date),null,cast('27/04/2012' as date)], 
	array['0',null,'3'], 
	array[cast('26/04/2012' as date),null,cast('27/04/2012' as date)], 
	array['Saison régulière','Round 2','Round 3'], 
	array[null,'Order 2','Order 3'], 
	array[cast('02/06/2008' as date),null,cast('27/04/2012' as date)], 
	array['Table',null,'Table'], 
	array['no',null,'no']);

-- Function: merge_group_team(bigint, bigint, bigint)
-- DROP FUNCTION merge_group_team(bigint, bigint, bigint);
CREATE OR REPLACE FUNCTION merge_group_team(teamid bigint, groupid bigint, roundid bigint)
  RETURNS integer AS
$BODY$ 
DECLARE
	curtime timestamp;
	table_name text;
	error text;
	request text;
	
BEGIN 
	UPDATE group_team SET id = id 
	WHERE team_id = teamid AND group_id = groupid AND round_id = roundid; 
	IF found THEN 
	    RETURN 1; 
	END IF; 
	INSERT INTO group_team(team_id,group_id,round_id) VALUES (teamid, groupid, roundid); 
	RETURN 1; 
	EXCEPTION WHEN OTHERS THEN
		curtime := 'now';
		table_name := 'group_team';
		error := cast(sqlstate as varchar);
		request := 'team_id =' || cast(teamid as varchar) || ', group_id =' || cast(groupid as varchar) || ', round_id =' || cast(roundid as varchar);
		INSERT INTO synchronisation_errors_logs("date", error, table_name, request) 
		VALUES(curtime, error, table_name, request);
		RETURN 0;
		
END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION merge_group_team(bigint, bigint, bigint) OWNER TO postgres;

public void updateGroupTeamProcedure(DataTable dtGroupTeam)
{
	log.Debug("updateGroupTeamProcedure() => Entree");
	using (NpgsqlConnection c = (NpgsqlConnection)conn.getConnexion())
	{
		using (NpgsqlCommand command = new NpgsqlCommand("merge_group_team", c))
		{
			command.CommandType = CommandType.StoredProcedure;
			command.UpdatedRowSource = UpdateRowSource.None;

			command.Parameters.Clear();
			command.Parameters.Add("@teamid", NpgsqlTypes.NpgsqlDbType.Bigint, 4, dtGroupTeam.Columns[0].ColumnName);
			command.Parameters.Add("@groupid", NpgsqlTypes.NpgsqlDbType.Bigint, 4, dtGroupTeam.Columns[1].ColumnName);
			command.Parameters.Add("@roundid", NpgsqlTypes.NpgsqlDbType.Bigint, 4, dtGroupTeam.Columns[2].ColumnName);

			NpgsqlDataAdapter adpt = new NpgsqlDataAdapter();
			adpt.InsertCommand = command;
			// Specify the number of records to be Inserted/Updated in one go. Default is 1.
			//adpt.UpdateBatchSize = 50;
			
			c.Open();
			try
			{
				int recordsInserted = adpt.Update(dtGroupTeam); 
			}
			catch (Exception e)
			{
				log.Error("Erreur : " + e.Message);
			}
		}
	}
	log.Debug("updateGroupTeamProcedure() => Sortie");
}

CREATE OR REPLACE FUNCTION merge_group_team_array(teamid bigint[], groupid bigint[], roundid bigint[])
  RETURNS integer AS

CREATE FUNCTION merge_group_team(teamid BIGINT, groupid BIGINT, roundid BIGINT) RETURNS VOID AS 
$$ 
BEGIN 
    LOOP 
        -- first try to update the row 
        UPDATE group_team SET id = id 
        WHERE team_id = teamid AND group_id = groupid AND round_id = roundid; 
        IF found THEN 
            RETURN; 
        END IF; 
        -- not there, so try to insert the key 
        -- if someone else inserts the same key concurrently, 
        -- we could get a unique-key failure 
        BEGIN 
            INSERT INTO group_team(team_id,group_id,round_id) VALUES (teamid, groupid, roundid); 
            RETURN; 
        EXCEPTION WHEN unique_violation THEN 
            -- do nothing, and loop to try the UPDATE again 
        END; 
    END LOOP; 
END; 
$$ 
LANGUAGE plpgsql;