Problème avec un cursor

***alexandre*** · 11/03/2009, 18h11

Je contine mon périple avec les procédures stockées et je tombe sur une exception qui me laisse perplexe

Code :

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

1
2
 
Exception : com.inet.tds.Tds4SQLException: [NDSQL02\DEV]Cursorfetch : le nombre de variables déclarées dans la liste INTO doit correspondre à celui des colonnes sélectionnées., Level 16, State 1, Procedure updateAuteur, Line 3201000

Voici mon code incriminé :

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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
 
ALTER PROCEDURE [dbo].[updateAuteur]
 
 @Numero_affaire varchar(10) OUTPUT,
 
 @Nom_auteur_plans varchar (500) OUTPUT,
 
 @Adresse_auteur_plans varchar (500) OUTPUT,
 
 @No_telephone_auteur_plans varchar(10) OUTPUT
 
AS
 
BEGIN
 
            /** Locate Auteur Nom + Prenom. */
 
 
 
            DECLARE @nom VARCHAR(100)
 
            DECLARE @prenom VARCHAR(100)
 
            DECLARE curseur_auteur CURSOR FOR SELECT * FROM [dbo].Split (@Nom_auteur_plans,',')
 
            OPEN curseur_auteur
 
            FETCH curseur_auteur INTO @nom, @prenom
 
            WHILE @@FETCH_STATUS = 0
 
            BEGIN
 
                        FETCH curseur_auteur INTO @nom, @prenom
 
            END
 
 
 
            CLOSE curseur_auteur
 
            DEALLOCATE curseur_auteur
 
 
 
            /** Locate Adresse. */
 
            DECLARE @rue VARCHAR(100)
 
            DECLARE @noRue VARCHAR(100)
 
            DECLARE @npa VARCHAR(100)
 
            DECLARE @localite VARCHAR(100)
 
 
 
            DECLARE curseur_adresse CURSOR FOR SELECT * FROM [dbo].Split (@Adresse_auteur_plans,',')
 
            OPEN curseur_adresse
 
            FETCH curseur_adresse INTO @rue, @noRue, @npa, @localite
 
            WHILE @@FETCH_STATUS = 0
 
            BEGIN
 
 
 
                        FETCH curseur_adresse INTO @rue, @noRue, @npa, @localite
 
            END
 
 
 
            CLOSE curseur_auteur
 
            DEALLOCATE curseur_auteur
 
 
 
            DECLARE @affaireId NUMERIC(19)
 
            DECLARE @adresseId NUMERIC(19)
 
            DECLARE @actualAuteurId NUMERIC(19)
 
 
 
 
 
            SET @affaireId = (SELECT (affaireID) FROM Affaire WHERE affaireNum = @Numero_affaire)
 
    SET @actualAuteurId = (SELECT (auteurs_auteurId) FROM Affaire_AuteurPlans WHERE affaire_affaireId = @Numero_affaire)
 
            SET @adresseId = (SELECT (adresseID) FROM Adresse, AuteurPlans WHERE auteurId = @actualAuteurId)
 
 
 
            UPDATE Adresse SET adresseLocalite = @localite, adresseRueNumero = @noRue, adresseNumPostal = @npa , adresseRue = @rue WHERE adresseID = @adresseId
 
            UPDATE AuteurPlans SET  nom = @nom, auteurPrenom = @prenom WHERE auteurId = @actualAuteurId
 
END
 
 
 
 
 
USE [extJaquetA]
 
GO
 
/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 03/11/2009 18:02:57 ******/
 
SET ANSI_NULLS ON
 
GO
 
SET QUOTED_IDENTIFIER ON
 
GO
 
 
 
ALTER FUNCTION [dbo].[Split] (    
 
    @RowData NVARCHAR(MAX),
 
    @Delimeter NVARCHAR(MAX)
 
)
 
RETURNS @RtnValue TABLE (
 
    ID INT IDENTITY(1,1),
 
    Data NVARCHAR(MAX)
 
) 
 
AS
 
BEGIN 
 
    DECLARE @Iterator INT
 
    SET @Iterator = 1
 
 
 
    DECLARE @FoundIndex INT
 
    SET @FoundIndex = CHARINDEX(@Delimeter,@RowData)
 
 
 
    WHILE (@FoundIndex>0)
 
    BEGIN
 
        INSERT INTO @RtnValue (data)
 
        SELECT 
 
            Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)))
 
 
 
        SET @RowData = SUBSTRING(@RowData,
 
                @FoundIndex + DATALENGTH(@Delimeter) / 2,
 
                LEN(@RowData))
 
 
 
        SET @Iterator = @Iterator + 1
 
        SET @FoundIndex = CHARINDEX(@Delimeter, @RowData)
 
    END
 
 
 
    INSERT INTO @RtnValue (Data)
 
    SELECT Data = LTRIM(RTRIM(@RowData))
 
 
 
    RETURN
 
END

et les données qui de test qui sont passées :

Code :

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

1
2
3
4
5
6
7
 
msProcedure.setString(1, "0910001");
 
msProcedure.setString(2, "Hilton, Paris");
 
msProcedure.setString(3, "rue de l''Hotel, 21, 66650, Paris");
 .....

**elsuket** · 11/03/2009, 21h19

Bonjour,

le nombre de VARIABLES déclarées dans la liste INTO doit correspondre à celui des colonnes sélectionnées.

C'est une erreur courante quand on manipule des curseurs.
Dans votre cas elle s'explique par le fait que la fonction Split retourne deux colonnes et non pas 4, quoi est le nombre de vos variables.

De préférence n'utilisez les curseurs qu'en tout dernier recours, quand même un WHILE ne peut pas le remplacer (et ça devient déjà très très rare).
Si vous êtes sous SQL Server 2005 ou plus, vous pouvez les oublier à 100%.

Pourquoi ?

- c'est vieux (cela date de COBOL, 1956, ...)
- c'est lent
- c'est moche
- c'est un grand gourmand en ressources

Bref, que des désavantages.
Notez que les SGBDR sont conçus pour manipuler des ensembles de données et des relations.
N'ayez donc pas peur d'écrire des jointures plutôt que d'utiliser des curseurs.
Les performances de votre requêtes et la consommation en ressources de votre système en seront au moins 10 fois améliorées.

Ensuite vous utilisez * dans vos SELECT, ce qui conduit le moteur de base de données à recherche le nom de la colonne retournée par votre fonction, d'autant que celle-ci n'est pas liée au schéma ...
N'utilisez jamais le * sauf dans avec l'opérateur EXISTS , la fonction d'agrégation COUNT() (tous deux sont optimisés pour cela), et pour mettre au point une requête.

Je n'ai pas bien compris pourquoi vous utilisez FETCH sans utiliser les données qui viennent d'être fetchées : en fait vous n'avez fait que parcourir votre curseur, sans y adjoindre un traitement pour le "groupe" de valeurs que vous venez de lire ...

Je n'ai pas compris non plus pourquoi les variables de votre procédure stockée sont en OUTPUT, étant donné que vous ne les affectez jamais au cours de votre traitement.

Voici donc ce que vous auriez dû écrire pour voir votre procédure stockée fonctionner (sans être performante), si votre fonction de découpage d'une chaîne de caractère retournait au moins 4 colonnes (rue, noRue, npa, localite)

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
64
65
66
67
68
69
70
71
72
73
74
75
ALTER PROCEDURE [dbo].[updateAuteur]
	@Numero_affaire varchar(10) OUTPUT,
	@Nom_auteur_plans varchar (500) OUTPUT,
	@Adresse_auteur_plans varchar (500) OUTPUT,
	@No_telephone_auteur_plans varchar(10) OUTPUT
AS 
BEGIN
    /** Locate Auteur Nom + Prenom. */
    DECLARE @nom VARCHAR(100) 
    DECLARE @prenom VARCHAR(100)
 
    DECLARE curseur_auteur CURSOR FOR
		SELECT *
		FROM [dbo].Split (@Nom_auteur_plans,',') 
    OPEN curseur_auteur
 
    FETCH curseur_auteur INTO @nom, @prenom 
    WHILE @@FETCH_STATUS = 0 
    BEGIN
		FETCH curseur_auteur INTO @nom, @prenom 
    END
    CLOSE curseur_auteur
    DEALLOCATE curseur_auteur
 
    /** Locate Adresse. */ 
    DECLARE @rue VARCHAR(100),
			@noRue VARCHAR(100),
			@npa VARCHAR(100),
			@localite VARCHAR(100),
			@affaireId NUMERIC(19),
			@adresseId NUMERIC(19),
			@actualAuteurId NUMERIC(19)
 
    DECLARE curseur_adresse CURSOR FOR
		SELECT *
		FROM [dbo].Split (@Adresse_auteur_plans,',')
	FOR READ ONLY
 
    OPEN curseur_adresse
    FETCH curseur_adresse INTO @rue, @noRue, @npa, @localite 
    WHILE @@FETCH_STATUS = 0
    BEGIN
		-- Recherche de l'identifiant d'affaire
		SELECT @affaireId = affaireID
		FROM dbo.Affaire
		WHERE affaireNum = @Numero_affaire
 
		-- Recherche de l'identifiant de plan d'auteur
		SELECT @actualAuteurId = auteurs_auteurId
		FROM dbo.Affaire_AuteurPlans
		WHERE affaire_affaireId = @Numero_affaire
 
		-- Recherche de l'adresse de plan d'auteur
		SELECT @adresseId = adresseID
		FROM dbo.Adresse A
		CROSS JOIN dbo.AuteurPlans AP
		WHERE AP.auteurId = @actualAuteurId
 
		UPDATE dbo.Adresse
		SET adresseLocalite = @localite,
			adresseRueNumero = @noRue,
			adresseNumPostal = @npa,
			adresseRue = @rue
		WHERE adresseID = @adresseId
 
		UPDATE dbo.AuteurPlans
		SET nom = @nom,
			auteurPrenom = @prenom
		WHERE auteurId = @actualAuteurId 
 
		FETCH curseur_adresse INTO @rue, @noRue, @npa, @localite 
    END 
    CLOSE curseur_auteur 
    DEALLOCATE curseur_auteur
END

Voici également à quoi aurait du ressembler votre fonction :

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
ALTER FUNCTION [dbo].[Split] (@RowData NVARCHAR(MAX), @Delimeter CHAR(1))
	RETURNS @RtnValue TABLE
	(
		ID INT IDENTITY(1,1),
		DATA NVARCHAR(MAX) 
	) 
AS 
BEGIN  
    DECLARE @Iterator INT 
    SET @Iterator = 1 
 
    DECLARE @FoundIndex INT
    SET @FoundIndex = CHARINDEX(@Delimeter,@RowData) 
 
    WHILE (@FoundIndex>0) 
    BEGIN 
        INSERT INTO @RtnValue (DATA) 
        SELECT LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1))) 
 
        SET @RowData = SUBSTRING(@RowData, @FoundIndex + DATALENGTH(@Delimeter) / 2, LEN(@RowData)) 
 
        SET @Iterator = @Iterator + 1 
        SET @FoundIndex = CHARINDEX(@Delimeter, @RowData) 
    END   
 
    INSERT INTO @RtnValue (DATA) 
    SELECT LTRIM(RTRIM(@RowData))
 
    RETURN 
END

Pourquoi avez mis le type de @Delimeter à NVARCHAR(MAX) ? Ce type de données permet de stocker jusqu'à 1Go de données Unicode ...
Vous est-il vraiment nécessaire d'utiliser Unicode ? si non, passez en VARCHAR(MAX)

En plus vous passez des variables de type VARCHAR, donc SQL Server est obligé avant de les passer à votre fonction de convertir les caractères non-unicode de vos paramètres en Unicode, imaginez le travail

J'ai essayé de réécrire votre procédure stockée mais je n'y parviens pas parce que je n'ai pas la structure de toutes les tables impliquées dans celle-ci.
Je peux vous proposer quelque chose si vous nous procurez cela, la version de SQL Server que vous utilisez, ainsi qu'un exemple de ce que vous passez en paramètre à la fonction Split : que peuvent contenir @Adresse_auteur_plans et @Nom_auteur_plans ?

@++

***alexandre*** · 12/03/2009, 08h35

Bonjour,

Merci du temps que vous consacrez, je ne suis pas un spécialiste db comme vous pouvez le constater (la fonction split je l ai trouvé sur un blog de microsoft)

Le problème que j ai avec cette fonction est qu elle me retourne avec ce test

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
 
      DECLARE @nom VARCHAR(100)
      DECLARE @id int
      DECLARE @prenom VARCHAR(100)
      DECLARE @Nom_auteur_plans VARCHAR(100)
      SET @Nom_auteur_plans = 'Despond, Ludovic'
 
	  DECLARE @index int;	  
      DECLARE curseur_auteur CURSOR FOR SELECT * FROM [dbo].Split (@Nom_auteur_plans,',')
      OPEN curseur_auteur
      FETCH curseur_auteur INTO @nom, @prenom
      WHILE @@FETCH_STATUS = 0
      BEGIN
            print @nom
            print @prenom
            FETCH curseur_auteur INTO @nom, @prenom
      END
      CLOSE curseur_auteur
      DEALLOCATE curseur_auteur

Les données suivantes :

Code :

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

1
2
3
4
5
 
1
Despond
2
Ludovic

Et moi je ne veux que récupérer les valeurs et les affectées à nom et prénom sans avoir le id (pour un cas plus simple que le précédent)

EDIT

et test encore plus intriguant c est que si je récupère ceci

Code :

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

1
2
3
4
5
6
7
8
9
10
 
     FETCH curseur_auteur INTO @nom, @prenom
      WHILE @@FETCH_STATUS = 0
      BEGIN
            print @prenom
 
            FETCH curseur_auteur INTO @nom, @prenom
      END
      CLOSE curseur_auteur
      DEALLOCATE curseur_auteur

La j'obtiens :

Despond
Ludovic

Mais ces deux résultats ne sont contenus que dans la variable nom

EDIT 2

En fait c'est parcqu'il n' y a qu'une seule colonne pour stocké la données splités ...

Qu elle est la meilleur façon de procéder pour récupérer alors les valeurs contenus dans un String séparés par des virgules

ca peut être

Nom, Prenom
Rue, NoDeRue, Npa, Localite

**elsuket** · 12/03/2009, 08h51

Bizarre, quand je fais le test suivant :

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
DECLARE @toto VARCHAR(50),
		@nom VARCHAR(20),
		@ID INT
 
SET @toto = 'Despond, Ludovic'
 
DECLARE cur CURSOR FOR
	SELECT ID, DATA
	FROM dbo.Split ('Despond, Ludovic', ',')
FOR READ ONLY
 
OPEN cur
FETCH NEXT FROM cur INTO @ID, @nom
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @nom
	FETCH NEXT FROM cur INTO @ID, @nom
END
 
DEALLOCATE cur

J'obtiens bien :

Despond
Ludovic

Je reste persuadé que si vous nous donniez le DDL de vos tables et ce que vous voulez faire, on pourrait réécrire cela en quelques requêtes simples

@++

***alexandre*** · 12/03/2009, 08h59

Hello,

J'ai finalement laisser tomber la fonction split pour récupérer uniquement mes valeurs

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
      DECLARE @nom VARCHAR(100)
      DECLARE @id int
      DECLARE @prenom VARCHAR(100)
      DECLARE @Nom_auteur_plans VARCHAR(100)
      SET @Nom_auteur_plans = 'Despond, Ludovic'
 
      DECLARE @FoundIndex INT
      SET @FoundIndex = CHARINDEX(',',@Nom_auteur_plans)
      SET @nom= SUBSTRING(@Nom_auteur_plans, 1,@FoundIndex -1 )
      SET @prenom= SUBSTRING(@Nom_auteur_plans, @FoundIndex + 1,LEN(@Nom_auteur_plans) )
      print @nom	   
      print @prenom

Problème avec un cursor

Développement SQL Server

Discussions similaires

Partager

Partager