Discussion :

[Gold.strike]

Bonjour,

Afin d'optimiser le traitement d'un module de synchronisation, nous avons choisi de remplacer des requêtes d'insertion simple, par de l'insertion multiple, au moyen de 2 procédure stockées :
- une procédure "loop", qui prends en paramètre, une liste par paramètre
- une procédure "merge", qui s'occupe de faire un update si existant, ou un insert le cas échéant.

La base de données utilisée est PostgreSQL, et le langage des procédures est donc le plpgsql.

Il arrive que certains éléments des listes de paramètres soient nuls (date non renseignée, chaine non renseignée, etc...)
Cela provoque une erreur au niveau du code C# : System.NullReferenceException.

Pour faire des tests, j'ai construit mes listes à la main contenant des données nulles :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
 
// 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");

J'appelle ensuite ma procédure stockée "loop" en passant en paramètre ces différentes listes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
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());

Ma procédure stockée "loop" parcourt ces listes pour faire la mise à jour via la procédure stockée "merge":

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
 
-- 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;

Et c'est donc là que mon application me retourne l'exception :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
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:

Voici le détail de la procédure "merge" réalisant les update/insert et relevant les exceptions rencontrées :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
-- Function: merge_round(integer, integer, date, text, timestamp without time zone, text, text, date, text, text)
-- DROP FUNCTION merge_round(integer, integer, date, text, timestamp without time zone, text, text, date, text, text);
CREATE OR REPLACE FUNCTION merge_round(_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 integer AS
$BODY$ 
DECLARE
	curtime timestamp;
	table_name text;
	error text;
	request text;
 
BEGIN 
	-- on essaie d'abord de faire un UPDATE
	UPDATE round
		SET round_id = _round_id, season_id = _season_id, end_date = _end_date, groups = _groups, has_outgroup_matches = _has_outgroup_matches, 
		last_updated = _last_updated, "name" = _name, ordermethod = _ordermethod, start_date = _start_date, "type" = _type
	WHERE round_id = _round_id;
	IF found THEN 
	    RETURN 1; 
	END IF; 
	-- si l'enregistrment n'est pas trouvé, on fais un INSERT
	INSERT INTO round(round_id, season_id, end_date, groups, has_outgroup_matches, last_updated, "name", ordermethod, start_date, "type") 
	VALUES (_round_id, _season_id, _end_date, _groups, _has_outgroup_matches, _last_updated, _name, _ordermethod, _start_date, _type) ;
 	RETURN 1; 
	-- gestion des exceptions
	EXCEPTION WHEN OTHERS THEN
		curtime := 'now';
		table_name := 'season';
		error := cast(sqlstate as varchar);
		request := 'round_id : ' || cast(_round_id as varchar) || ', name : ' || _name || ', season_id : ' || cast(_season_id 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_round(integer, integer, date, text, timestamp without time zone, text, text, date, text, text) OWNER TO postgres;

Quand j'appelle la même fonction depuis Postgre, en passant des paramètres nuls, je n'ai cependant pas de soucis :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
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']);

Les 3 lignes s'insèrent bien, avec les colonnes contenant des valeurs nulles...

Auriez vous une explciation?
Existe t'il un moyen de gérer des paramètres "nullables"?

Merci d'avance,