La manipulation de fichiers texte, par exemple à des fin de journalisation d’événements, peut se faire directement dans SQL à l’aide de procédure de manipulation de fichiers via des commandes OLE Automation.
Pour ce faire il convient d’utiliser les procédures sp_OA… de MS SQL Server (sp_OACreate, sp_OAMethod, sp_OADestroy).
Voici 5 procédures stockées destinées à :
Рcr̩er un fichier texte en ajout;
– ouvrir un fichier texte en ajout;
– ajouter une ligne au fichier ouvert
– ajouter du texte au fichier ouvert
– fermer un fichier texte.
ATTENTION : à défaut SQL Server interdit l’utilisation des fonctions OLE Automation. Il vous faudra donc l’autoriser par l’intermédiaire de sp_configure (Ole Automation Procedures)
CREATE PROCEDURE P_A_FILE_OPEN_REWRITE @FILE_NAME NVARCHAR(256), -- nom du fichier
@F_HANDLE INT OUTPUT, -- handle OS du fichier
@F_ID INT OUTPUT -- identifiant numérique du fichier
AS
/******************************************************************************************
* Copyright : Frédéric Brouard / SQLpro / SQL spot *
(http://sqlpro.developpez.com - http://www.sqlspot.com) *
* Auteur : Frédéric Brouard / SQL pro *
* Créée le : 2006-02-01 *
* Description : créé un fichier texte en écriture à l'aide d'objets OLE *
******************************************************************************************/
BEGIN
SET NOCOUNT ON
-- valeur de retour d'exécution appel OLE
DECLARE @OLE_RETURN INT
-- création de l'objet
EXECUTE @OLE_RETURN = sp_OACreate 'Scripting.FileSystemObject', @F_HANDLE OUT
IF @OLE_RETURN <> 0 RETURN -1
--Ouvre le fichier (2 = ForWriting, 8 = ForAppending)
EXECUTE @OLE_RETURN = sp_OAMethod @F_HANDLE, 'OpenTextFile', @F_ID OUT, @FILE_NAME, 2, 1
IF @OLE_RETURN <> 0 RETURN -1
END
GO
CREATE PROCEDURE P_A_FILE_OPEN_APPEND @FILE_NAME NVARCHAR(256), -- nom du fichier
@F_HANDLE INT OUTPUT, -- handle OS du fichier
@F_ID INT OUTPUT -- identifiant numérique du fichier
AS
/******************************************************************************************
* Copyright : Frédéric Brouard / SQLpro / SQL spot *
(http://sqlpro.developpez.com - http://www.sqlspot.com) *
* Auteur : Frédéric Brouard / SQL pro *
* Créée le : 2006-02-01 *
* Description : ouvre un fichier texte en ajout à l'aide d'objets OLE *
******************************************************************************************/
BEGIN
SET NOCOUNT ON
-- valeur de retour d'exécution appel OLE
DECLARE @OLE_RETURN INT
-- création de l'objet
EXECUTE @OLE_RETURN = sp_OACreate 'Scripting.FileSystemObject', @F_HANDLE OUT
IF @OLE_RETURN <> 0 RETURN -1
--Ouvre le fichier (2 = ForWriting, 8 = ForAppending)
EXECUTE @OLE_RETURN = sp_OAMethod @F_HANDLE, 'OpenTextFile', @F_ID OUT, @FILE_NAME, 8, 1
IF @OLE_RETURN <> 0 RETURN -1
END
GO
CREATE PROCEDURE P_A_FILE_WRITE_LINE @F_ID INT, -- identifiant numérique du fichier
@LINE NVARCHAR(4000) -- ligne de texte à écrire
AS
/******************************************************************************************
* Copyright : Frédéric Brouard / SQLpro / SQL spot *
(http://sqlpro.developpez.com - http://www.sqlspot.com) *
* Auteur : Frédéric Brouard / SQL pro *
* Créée le : 2006-02-01 *
* Description : ajoute une ligne à un fichier texte l'aide d'objets OLE *
******************************************************************************************/
BEGIN
SET NOCOUNT ON
-- valeur de retour d'exécution appel OLE
DECLARE @OLE_RETURN INT
-- ecrit la ligne dans le fichier
EXECUTE @OLE_RETURN = sp_OAMethod @F_ID, 'WriteLine', Null, @LINE
IF @OLE_RETURN <> 0 RETURN -1
END
GO
CREATE PROCEDURE P_A_FILE_WRITE_DATA @F_ID INT, -- identifiant numérique du fichier
@LINE NVARCHAR(4000) -- texte à ajouter
AS
/******************************************************************************************
* Copyright : Frédéric Brouard / SQLpro / SQL spot *
(http://sqlpro.developpez.com - http://www.sqlspot.com) *
* Auteur : Frédéric Brouard / SQL pro *
* Créée le : 2006-02-01 *
* Description : ajoute du texte à un fichier texte l'aide d'objets OLE *
******************************************************************************************/
BEGIN
SET NOCOUNT ON
-- valeur de retour d'exécution appel OLE
DECLARE @OLE_RETURN INT
-- ecrit la ligne dans le fichier
EXECUTE @OLE_RETURN = sp_OAMethod @F_ID, 'Write', Null, @LINE
IF @OLE_RETURN <> 0 RETURN -1
END
GO
CREATE PROCEDURE P_A_FILE_CLOSE @F_HANDLE INT, -- handle OS du fichier
@F_ID int -- identifiant numérique du fichier
AS
/******************************************************************************************
* Copyright : Frédéric Brouard / SQLpro / SQL spot *
(http://sqlpro.developpez.com - http://www.sqlspot.com) *
* Auteur : Frédéric Brouard / SQL pro *
* Créée le : 2006-02-01 *
* Description : ferme un fichier texte l'aide d'objets OLE *
******************************************************************************************/
BEGIN
SET NOCOUNT ON
DECLARE @OLE_RETURN int
-- fermeture du fichier
EXECUTE @OLE_RETURN = sp_OADestroy @F_ID
-- destruction de l'objet Ole
EXECUTE @OLE_RETURN = sp_OADestroy @F_HANDLE
END
GO
—
Frédéric BROUARD, Spécialiste modélisation, bases de données, optimisation, langage SQL.
Le site sur le langage SQL et les S.G.B.D. relationnels : http://sqlpro.developpez.com/
Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation
* * * * * Enseignant au CNAM PACA et à l’ISEN à Toulon * * * * *