Création et appel de procédures stockées et fonctions

Version imprimable

Bonjour tout le monde,

Je me lance dans le grand bain sans savoir nager et avec des parpaings accrochés aux pieds...

En fait je crée des procédures stockées et des fonctions qui seront appelées lors du dump de la base à l'install de mon appli pour faire de la config.

Je voudrais juste que vous me disiez si je suis bon pour la syntaxe :)

Voici mes fonctions :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
-- Get the ConfigurationFileId
CREATE FUNCTION GetConfigurationFileId
(@FileNameToFind NVARCHAR(255))
RETURNS  INT
AS
  DECLARE @IdxConfigurationFile INT
 
  BEGIN
	SELECT @IdxConfigurationFile = IDX_CONFIGURATION_FILE FROM TBL_CONFIGURATION_FILE 
	WHERE STR_FILENAME = @FileNameToFind AND INT_MAJOR = 1 AND INT_MINOR = 0 AND INT_REVISION = 0 AND INT_BUILD = 0;
  END
  RETURN(@IdxConfigurationFile);
GO

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
CREATE FUNCTION CreateConfigurationFile
(@TemplateName NVARCHAR(255), @FileNameToFind NVARCHAR(255), @FileNameToCreate NVARCHAR(255))
RETURNS  INT
AS
  DECLARE @Template INT
  DECLARE @IdxConfigurationFile INT
  DECLARE @FileContent VARBINARY(MAX)
 
  BEGIN
	-- Get the template file
	SELECT @Template = IDX_TEMPLATE FROM TBL_TEMPLATE_FILES WHERE STR_NAME = @TemplateName;
	-- Get the content of the physical configuration file
	SELECT @FileContent = * FROM OPENROWSET(BULK @FileNameToFind, VARBINARY(MAX));
	-- Create the new configuration file in the database
	INSERT INTO TBL_CONFIGURATION_FILES (STR_FILENAME, STR_FILEPATH, INT_MAJOR, INT_MINOR, INT_REVISION, INT_BUILD, CLOB_CONTENT, FLG_IS_VALID, DAT_LAST_CHANGE, IDX_LAST_CHANGE_USER, IDX_TEMPLATE)
	VALUES (@FileNameToCreate, '%HOMEPATH%', 1, 0, 0, 0, @FileContent, 1, GETDATE(), 48264, @Template);
	-- Get the new Id
	SELECT @IdxConfigurationFile = @@IDENTITY;
  END
  RETURN(@IdxConfigurationFile);
GO

Code:

1
2
3
4
5
6
7
8
9
10
11
 
CREATE PROCEDURE SetLink 
    @IdxConfigurationFile INT, 
    @IdxTypeOP INT,
    @IdxScenario INT,
    @IdxOrdre INT,
    @IdxCommande INT 
AS 
	INSERT INTO TBL_CONFIG_FILES_COMMANDES (IDX_CONFIGURATION_FILE, IDX_TYPE_OPERATION, IDX_SCENARIO, INT_ORDRE, IDX_COMMANDE)  
	VALUES (@IdxConfigurationFile, @IdxTypeOP, @IdxScenario, @IdxOrdre, @IdxCommande);
GO

Et mes appels :

Code:

1
2
3
4
5
6
7
8
9
10
11
 
-- Try to get the file Id
SELECT @IdxConfigurationFile = GetConfigurationFileId('LoadInFile.exe.config');
 
IF @IdxConfigurationFile < 0 -- File doesn't exist yet in the database
BEGIN
	SELECT @IdxConfigurationFile = CreateConfigurationFile('CONFIG_LCABALLECTURE', '{WorkingDir}/LCABALLecture_Template.exe.config', 'LCABALLecture.exe.config');
END
 
-- Create the link between the configuration file and the command
EXECUTE SetLink @IdxConfigurationFile, @IdxTypeOP, @IdxScenario, @IdxOrdre, @IdxCommande;

Les paramètres passés aux fonctions sont instanciés avant bien sûr :)

Voila, est-ce que quelque chose vous fait saigner les yeux ? :roll:

23/01/2013, 10h07
Kropernic
Hello Shadam !

Bienvenue dans la grande profondeur :mouarf:.

Quelques petits problèmes de syntaxe mais rien de bien grave (enfin j'ai regardé en diagonale jusqu'ici).

Les BEGIN END sont mal placés. Ils doivent englobés le corps de la procédure/fonction.

Exemple :
Code:

1 2 3 4 5 6 7 8 CREATE PROCEDURE MA_PROC @MON_PARAM INT AS BEGIN SET NOCOUNT ON; --facultatif mais si t'en as pas besoin, ça fait moins de trafic réseau --le coprs de la procédure END
Pareil pour une fonction sauf qu'il y a le type de donnée retournée et le retour effectif de la donnée à ajouter mais ça tu le fais bien.

Les begin end délimitent un bloc d'instruction. Pour aider à comprendre, considérons ce bloc comme une seule instruction.

La procédure/fonction a donc son unique instruction pour savoir quoi faire.

C'est un peu pareil si tu veux faire un IF (pas fais gaffe si t'en as dans le code). La syntaxe est la suivante :
Code:

1 2 3 4 5 IF @MA_VAR = 1 --instruction si vrai ELSE --instruction si faux
Tu n'a droit qu'à une seule instruction par branche du IF. Si ton traitement en nécessite plusieurs, alors tu les englobes dans un begin end. (Si t'as fait du delphi, c'est exactement pareil en fait).

Après analyse plus détaillée...

Dans le code de la 2e fonction, tu fais ceci :
Code:

1 2 3 4 INSERT INTO TBL_CONFIGURATION_FILES (STR_FILENAME, STR_FILEPATH, INT_MAJOR, INT_MINOR, INT_REVISION, INT_BUILD, CLOB_CONTENT, FLG_IS_VALID, DAT_LAST_CHANGE, IDX_LAST_CHANGE_USER, IDX_TEMPLATE) VALUES (@FileNameToCreate, '%HOMEPATH%', 1, 0, 0, 0, @FileContent, 1, GETDATE(), 48264, @Template); -- Get the new Id SELECT @IdxConfigurationFile = @@IDENTITY;
Il faut faire attention ici. @@IDENTITY te donne la dernière valeur insérée dans une colonne de type IDENTITY sans autre considération. Ce qui veut dire que si qqn fait une insertion ailleurs juste avant, tu risques d'avoir une mauvaise valeur.

Personnellement, j'utiliserait plutôt SCOPE_IDENTITY() à la place qui te donne la dernière valeur insérée dans une colonne de type IDENTITY dans le "scope" (je crois qu'on peut traduire par contexte) actuel. Ici donc, par ta fonction.

Concernant les appels de fonction, il me semble qu'il est obligatoire de préciser leur schéma.

Mais de toute façon, d'une manière générale, précise toujours le schéma des objets. Ca évitera à SQL SERVER de devoir chercher le schéma par défaut de l'objet. Car tu pourrais avoir une table T1 dans le schéma X et une table T1 dans le schema Y. Si tu fais une requête sur T1 sans préciser son schéma et que tu veux pas celle du schéma par défaut... Tu vois le problème. Mais outre ce problème de faire une requête sur le mauvais objet, cela va améliorer les performances car SQL SERVER saura directement de quel objet il s'agit précisément.

Ok merci de te pencher sur mon cas (désespéré ?).

Pour les BEGIN et END mal placé j'avais vu ça et corrigé.

Par contre depuis ce matin je suis en train de me prendre la tête sur le BULK.

C'est une instruction qui permet d'insérer le contenu d'un fichier or elle ne veut pas fonctionner, ou en tout cas pas comme je le fais :roll:.

MSDN dit :

Code:

1
2
3
4
 
BULK INSERT bulktest..t_float
FROM 'C:\t_float-c.dat' WITH (FORMATFILE='C:\t_floatformat-c-xml.xml');
GO

Moi j'ai fait :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
CREATE FUNCTION CreateConfigurationFile
(@TemplateName NVARCHAR(255), @FileNameToFind NVARCHAR(255), @FileNameToCreate NVARCHAR(255))
RETURNS  INT
AS
  BEGIN
 
	  DECLARE @Template INT
	  DECLARE @IdxConfigurationFile INT
--	  DECLARE @FileContent VARBINARY(MAX)
 
	-- Get the template file
	SELECT @Template = IDX_TEMPLATE FROM TBL_TEMPLATE_FILES WHERE STR_NAME = @TemplateName;
	-- Create the new configuration file in the database
	INSERT INTO TBL_CONFIGURATION_FILES (STR_FILENAME, STR_FILEPATH, INT_MAJOR, INT_MINOR, INT_REVISION, INT_BUILD, FLG_IS_VALID, DAT_LAST_CHANGE, IDX_LAST_CHANGE_USER, IDX_TEMPLATE)
	VALUES (@FileNameToCreate, '%HOMEPATH%', 1, 0, 0, 0, 1, GETDATE(), 48264, @Template);
	-- Get the new Id
	SELECT @IdxConfigurationFile = @@IDENTITY;
	BULK INSERT TBL_CONFIGURATION_FILES.CLOB_CONTENT
	FROM @FileNameToFind
	WHERE IDX_CONFIGURATION_FILE = @IdxConfigurationFile;
  RETURN(@IdxConfigurationFile);
  END
GO

Pb => Syntaxe incorrecte vers '@FileNameToFind'.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
CREATE FUNCTION CreateConfigurationFile
(@TemplateName NVARCHAR(255), @FileNameToFind NVARCHAR(255), @FileNameToCreate NVARCHAR(255))
RETURNS  INT
AS
  BEGIN
 
	  DECLARE @Template INT
	  DECLARE @IdxConfigurationFile INT
	  DECLARE @FileContent VARBINARY(MAX)
 
	-- Get the template file
	SELECT @Template = IDX_TEMPLATE FROM TBL_TEMPLATE_FILES WHERE STR_NAME = @TemplateName;
	-- Get the content of the physical configuration file
	SELECT * FROM OPENROWSET(BULK @FileNameToFind , VARBINARY(MAX)) as fileContent;
	SELECT @FileContent = fileContent;
	-- Create the new configuration file in the database
	INSERT INTO TBL_CONFIGURATION_FILES (STR_FILENAME, STR_FILEPATH, INT_MAJOR, INT_MINOR, INT_REVISION, INT_BUILD, FLG_IS_VALID, DAT_LAST_CHANGE, IDX_LAST_CHANGE_USER, IDX_TEMPLATE)
	VALUES (@FileNameToCreate, '%HOMEPATH%', 1, 0, 0, 0, 1, GETDATE(), 48264, @Template);
	-- Get the new Id
	SELECT @IdxConfigurationFile = @@IDENTITY;
  RETURN(@IdxConfigurationFile);
  END
GO

Pb => Syntaxe incorrecte vers '@FileNameToFind'.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
CREATE FUNCTION CreateConfigurationFile
(@TemplateName NVARCHAR(255), @FileNameToFind VARCHAR(255), @FileNameToCreate NVARCHAR(255))
RETURNS  INT
AS
  BEGIN
 
	  DECLARE @Template INT
	  DECLARE @IdxConfigurationFile INT
--	  DECLARE @FileContent VARBINARY(MAX)
 
	-- Get the template file
	SELECT @Template = IDX_TEMPLATE FROM TBL_TEMPLATE_FILES WHERE STR_NAME = @TemplateName;
	-- Create the new configuration file in the database
	INSERT INTO TBL_CONFIGURATION_FILES (STR_FILENAME, STR_FILEPATH, INT_MAJOR, INT_MINOR, INT_REVISION, INT_BUILD, FLG_IS_VALID, DAT_LAST_CHANGE, IDX_LAST_CHANGE_USER, IDX_TEMPLATE)
	VALUES (@FileNameToCreate, '%HOMEPATH%', 1, 0, 0, 0, 1, GETDATE(), 48264, @Template);
	-- Get the new Id
	SELECT @IdxConfigurationFile = @@IDENTITY;
	INSERT INTO TBL_CONFIGURATION_FILES(CLOB_CONTENT)
	SELECT * FROM OPENROWSET(BULK @FileNameToFind, VARBINARY(MAX)) AS FileContent;
  RETURN(@IdxConfigurationFile);
  END
GO

Pb => Syntaxe incorrecte vers '@FileNameToFind'.

8O

23/01/2013, 10h31
Kropernic

Je n'ai jamais fait de bulk insert mais on a droit à une clause where sur ce genre d'instruction ???

D'après MSDN, j'ai des doutes...

Aussi, quelle version de sql server utilises-tu ?

Bonjour,
Citation:
Envoyé par Kropernic
Dans le code de la 2e fonction, tu fais ceci :
Code:

1 2 3 4 INSERT INTO TBL_CONFIGURATION_FILES (STR_FILENAME, STR_FILEPATH, INT_MAJOR, INT_MINOR, INT_REVISION, INT_BUILD, CLOB_CONTENT, FLG_IS_VALID, DAT_LAST_CHANGE, IDX_LAST_CHANGE_USER, IDX_TEMPLATE) VALUES (@FileNameToCreate, '%HOMEPATH%', 1, 0, 0, 0, @FileContent, 1, GETDATE(), 48264, @Template); -- Get the new Id SELECT @IdxConfigurationFile = @@IDENTITY;
Il faut faire attention ici. @@IDENTITY te donne la dernière valeur insérée dans une colonne de type IDENTITY sans autre considération. Ce qui veut dire que si qqn fait une insertion ailleurs juste avant, tu risques d'avoir une mauvaise valeur.

Personnellement, j'utiliserait plutôt SCOPE_IDENTITY() à la place qui te donne la dernière valeur insérée dans une colonne de type IDENTITY dans le "scope" (je crois qu'on peut traduire par contexte) actuel. Ici donc, par ta fonction.
Il y a pire : vous ne pouvez pas modifier les données de vos tables de votre base dans une fonction, donc ni INSERT, ni UPDATE, ni DELETE... (sauf sur des variables tables...)
Vous devez utiliser une procédure stockée pour cela (vous pouvez renvoyer votre ID dans un paramètre OUTPUT)

23/01/2013, 11h39
Kropernic

Ah bin je ne savais pas ça ^^

Faut dire, j'ai du écrire 5 fonctions en 5 ans...

Les considérations d'identity n'entrent pas en ligne de compte, c'est un script exécuté à l'installation de la base, il n'y aura pas d'autres entrées que celle du script.

Pour mon histoire de BULK INSERT ça avance un peu...

J'en suis là :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 CREATE FUNCTION CreateConfigurationFile (@TemplateName NVARCHAR(255), @FileNameToFind NVARCHAR(255), @FileNameToCreate NVARCHAR(255)) RETURNS INT AS BEGIN DECLARE @Template INT DECLARE @IdxConfigurationFile INT DECLARE @FileContent VARCHAR(MAX) -- Get the template file SELECT @Template = 4; -- Get the content of the physical configuration file SET @FileContent = (SELECT * FROM OPENROWSET(BULK 'E:\app.config', SINGLE_CLOB, CODEPAGE = 'UTF-8') as fileContent ); -- Create the new configuration file in the database INSERT INTO TBL_CONFIGURATION_FILES (STR_FILENAME, STR_FILEPATH, INT_MAJOR, INT_MINOR, INT_REVISION, INT_BUILD, FLG_IS_VALID, DAT_LAST_CHANGE, CLOB_CONTENT, IDX_LAST_CHANGE_USER, IDX_TEMPLATE) VALUES ('toto5', '%HOMEPATH%', 1, 0, 0, 0, 1, GETDATE(), CONVERT(VARBINARY(MAX),@FileContent), 48264, @Template); -- Get the new Id SELECT @IdxConfigurationFile = @@IDENTITY; RETURN(@IdxConfigurationFile); END GO
Mon souci majeur étant qu'à priori le bulk insert n'accepte pas les variables scalaires, si je mets le chemin pas de souci, si je mets une variable ça casse. Il faut absolument que j'arrive à passer outre...
Deuxième problème, j'ai un gros souci sur l'encodage du binaire inséré. Lorsque je veux le récupérer j'ai des choses du type "攠据摯湩㵧產晴㠭㼢ാ㰊潣普杩牵瑡潩㹮਍†挼湯楦卧捥楴湯㹳਍††猼捥楴湯片畯⁰慮敭∽灡汰捩瑡潩卮瑥楴" autant dire que ce n'est pas vraiment la même chose que mon fichier de départ ^^.

Des idées ?

23/01/2013, 11h54
Kropernic

D'après msdn :

Citation:

code_page

Specific code page number, for example, 850.
http://i.msdn.microsoft.com/areas/gl...tent/clear.gifImportant
SQL Server does not support code page 65001 (UTF-8 encoding).

Ca aide ? :-/

C'EST BON !!!

Le problème était dans les types acceptés par bulk insert, en faisant du convert d'un convert ça passe :mrgreen:
Et pour la variable scalaire, en passant par un EXECUTE c'est bon aussi !

Le code sera plus clair je pense :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
CREATE FUNCTION CreateConfigurationFile
(@TemplateName NVARCHAR(255), @FileNameToFind NVARCHAR(255), @FileNameToCreate NVARCHAR(255))
RETURNS  INT
AS
  BEGIN
 
	DECLARE @Template INT
	DECLARE @IdxConfigurationFile INT
 
	-- Get the template file
	SELECT @Template = IDX_TEMPLATE FROM TBL_TEMPLATE_FILES WHERE STR_NAME = @TemplateName;
 
	-- Create the new configuration file in the database
	EXEC('INSERT INTO TBL_CONFIGURATION_FILES (STR_FILENAME, STR_FILEPATH, INT_MAJOR, INT_MINOR, INT_REVISION, INT_BUILD, FLG_IS_VALID, DAT_LAST_CHANGE, CLOB_CONTENT, IDX_LAST_CHANGE_USER, IDX_TEMPLATE)
	SELECT '+ @FileNameToCreate +', ''%HOMEPATH%'', 1, 0, 0, 0, 1, GETDATE(), CONVERT(VARBINARY(MAX), CONVERT(NVARCHAR(MAX),(SELECT * FROM OPENROWSET(BULK '''+ @FileNameToFind +''', SINGLE_CLOB) as fileContent ))), 48264, '+ @Template +';')
 
	-- Get the new Id
	SELECT @IdxConfigurationFile = @@IDENTITY;
	RETURN(@IdxConfigurationFile);
  END
GO

Merci pour ton aide Griftou :ccool:

23/01/2013, 14h19
Kropernic

Citation:

Envoyé par Shadam

Merci pour ton aide Griftou :ccool:

C'est normal ! Avec toute l'aide que je reçois ici, il est normal que j'aide un peu à mon tour.

Ouai bon bah finalement c'est pas bon du tout :mur:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 CREATE FUNCTION CreateUnicodeConfigurationFile (@TemplateName NVARCHAR(255), @FileNameToFind NVARCHAR(255), @FileNameToCreate NVARCHAR(255)) RETURNS INT AS BEGIN DECLARE @Template INT -- Get the template file SELECT @Template = IDX_TEMPLATE FROM TBL_TEMPLATE_FILES WHERE STR_NAME = @TemplateName; -- Create the new configuration file in the database EXECUTE('INSERT INTO TBL_CONFIGURATION_FILES (STR_FILENAME, STR_FILEPATH, INT_MAJOR, INT_MINOR, INT_REVISION, INT_BUILD, FLG_IS_VALID, DAT_LAST_CHANGE, CLOB_CONTENT, IDX_LAST_CHANGE_USER, IDX_TEMPLATE) SELECT '+ @FileNameToCreate +', ''%HOMEPATH%'', 1, 0, 0, 0, 1, GETDATE(), CONVERT(VARBINARY(MAX), CONVERT(NVARCHAR(MAX),(SELECT * FROM OPENROWSET(BULK '''+ @FileNameToFind +''', SINGLE_NCLOB) as fileContent ))), 48264, '+ @Template +';') -- Get the new Id RETURN(@@IDENTITY); END GO
Citation:

Envoyé par SQL-Server

Msg*443, Niveau*16, État*14, Procédure*CreateUnicodeConfigurationFile, Ligne*10
Utilisation non valide d'un opérateur présentant des effets secondaires ou d'un opérateur dépendant du temps 'EXECUTE STRING' dans une fonction.

Le coup du EXECUTE ça fonctionnait lorsque j'exécutais la commande comme une simple requête mais dans une fonction il n'en veut pas :calim2:

23/01/2013, 14h41
aieeeuuuuu

Citation:

Le coup du EXECUTE ça fonctionnait lorsque j'exécutais la commande comme une simple requête mais dans une fonction il n'en veut pas :calim2:

Ha oui, j'oubliais, pas de DELETE, pas de INSERT, pas de UPDATE, et... pas de EXECUTE dans une fonction !

En d'autres termes : vous ne pouvez pas modifier les données de vos tables depuis une fonction.
23/01/2013, 14h58
Shadam

Oui c'est bon ;)

Du coup j'ai une procédure qui s'occupe de l'Insert puis une fonction qui fait le select derrière pour récupérer le nouvel index créer.

Merci de votre aide à tous les deux.
24/01/2013, 09h43
aieeeuuuuu

Comme je disais, vous pourriez vous passer de la fonction et renvoyer l'ID de la ligne insérée directement depuis la procédure stockée via un parametre OUTPUT...
24/01/2013, 14h46
Kropernic

Est-ce qu'on a le droit d'appeler une procédure qui modifie les données dans une fonction ?
24/01/2013, 16h39
aieeeuuuuu

Citation:

Envoyé par Kropernic

Est-ce qu'on a le droit d'appeler une procédure qui modifie les données dans une fonction ?

Non : pas d'EXECUTE dans une fonction...

On ne peut pas modifier des données des tables de la base depuis une fonction, ni directement, ni indirectement.
24/01/2013, 20h16
Kropernic

:cry:
25/01/2013, 07h51
Rei Ichido

Citation:

Envoyé par aieeeuuuuu

Comme je disais, vous pourriez vous passer de la fonction et renvoyer l'ID de la ligne insérée directement depuis la procédure stockée via un parametre OUTPUT...

Ou si c'est appelé par un programme externe, on peut directement finir par un SELECT.