Créer des fichier PDF depuis une procédure stockée

Techniques

Techniques / PDF : Créer un PDF depuis une procédure stockée
Original version from Masar Ivica - Visiter nos sponsors

Partie II : Lancer la procédure et générer le fichier PDF à la volée

Objet:

Cet article met en oeuvre une technique un peu particulière afin de créer un fichier PDF à partir d'une procédure stockée. Il montre comment écrire cette dernière et comment l'appeler directement depuis ASP ou en ligne depuis SQL Server.

La procédure stockée aisément modifiable permet soit de générer un fichier sur le disque soit d'en renvoyer le contenu sous la forme d'un flux binaire au navigateur client depuis ASP. Le document généré en lui même est un simple rapport présentant des données sur une colonne.

Cette technique qui peut être étendue pour des exports d'informations depuis la base de données permet de ne pas avoir recours à des outils externes ou des librairies (et les prix exorbitants qui vont avec)

Comment appeler la procédure stockée depuis ASP avec ADO :

La procédure stockée est appelée SQL2PDF. Afin de l'exécuter depuis ASP la méthode la plus simple mais pas forcément la meilleure est de créer une connexion à la base de données et ensuite d'appeler la méthode Execute de l'objet Connection ainsi créé, comme dans l'exemple suivant :

'Une variable permettant de renseigner
'l'argument de la procédure stockée
filename = "myPDF"

'Création de l'objet ADODB Connection
Set cn = Server.CreateObject("ADODB.Connection")

'Ouvrir la base avec l'objet créé
cn.Open "data source name", "userid", "password"

'Executer la procédure afin de générer
'le fichier PDF sans utiliser d'argument
'cn.Execute("SQL2PDF")

'La même chose en passant un argument
' à la procédure:
cn.Execute("SQL2PDF '" & filename & "'")

cn.Close

'Nettoage de la mémoire
Set cn = Nothing

SQL2PDF permet de créer un fichier PDF à partir du texte insérer dans la table suivante : psopdf ( nvarchar(80) ). Il nous faut donc commencer par créer celle-ci :

	CREATE TABLE psopdf (code NVARCHAR(80))

Nous pouvons mainenant créer la procédure stockée SQL2PDF.

Ensuite la table psopdf devra être remplie avec les données que nous désirons voir apparaître dans notre fichier comme le montre les exemples qui suivent.

A la fin la procédure est appelée en fournissant comme argument le nom du fichier à générer sans préciser l'extention de celui-ci (.pdf).

Voici le script permettant de créer la procédure SQL2PDF :

-- Si vous voulez supprimez une ancienne 
-- version de cette procédure : décommenter les lignes suivantes

--		if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sql2pdf]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
--		drop procedure [dbo].[sql2pdf]
--		GO

CREATE PROCEDURE sql2pdf
   @filename VARCHAR(100) 
AS 
  CREATE TABLE #pdf (idnumber INT IDENTITY(1,1)
  		    ,code NVARCHAR(200))
  CREATE TABLE #xref (idnumber INT IDENTITY(1,1)
  		    ,code VARCHAR(30))
  CREATE TABLE #text (idnumber INT IDENTITY(1,1)
  		    ,code VARCHAR(200))

DECLARE @end VARCHAR(7),
  	@beg   VARCHAR(7),
  	@a1    VARCHAR(3),
  	@a2    VARCHAR(3),
  	@ad    VARCHAR(5),
  	@cr    VARCHAR(8),
  	@pr    VARCHAR(9),
  	@ti    VARCHAR(6),
  	@xstr  VARCHAR(10),
  	@page  VARCHAR(8000),
	@pdf   VARCHAR(100),
	@trenutniRed NVARCHAR(200),
  	@rows   INT,
  	@ofset  INT,
  	@len    INT,
  	@nopg   INT,
        @fs 	INT,
	@ole    INT,
	@x 	INT,
	@file   INT,
  	@object INT

SELECT @pdf = 'C:\' + @filename + '.pdf'  
  SET @page = ''
  SET @nopg = 0
  SET @object = 6
  SET @end = 'endobj'
  SET @beg = ' 0 obj'
  SET @a1 = '<<'
  SET @a2 = '>>'
  SET @ad = ' 0 R'
  SET @cr = CHAR(67) + CHAR(114) + CHAR (101) + CHAR(97) + CHAR(116) + CHAR (111) + CHAR(114)
  SET @pr = CHAR(80) + CHAR(114) + CHAR (111) + CHAR(100) + CHAR(117) + CHAR (99 ) + CHAR(101) + CHAR(114)
  SET @ti = CHAR(84) + CHAR(105) + CHAR (116) + CHAR(108) + CHAR(101)
  SET @xstr = ' 00000 n'
  SET @ofset = 396  
  INSERT INTO #xref(code) VALUES ('xref')
  INSERT INTO #xref(code) VALUES ('0 10')
  INSERT INTO #xref(code) VALUES ('0000000000 65535 f')
  INSERT INTO #xref(code) VALUES ('0000000017' + @xstr)
  INSERT INTO #xref(code) VALUES ('0000000790' + @xstr)
  INSERT INTO #xref(code) VALUES ('0000000869' + @xstr)
  INSERT INTO #xref(code) VALUES ('0000000144' + @xstr)
  INSERT INTO #xref(code) VALUES ('0000000247' + @xstr)
  INSERT INTO #xref(code) VALUES ('0000000321' + @xstr)
  INSERT INTO #xref(code) VALUES ('0000000396' + @xstr)  
  INSERT INTO #pdf (code) VALUES ('%' + CHAR(80) + CHAR(68) + CHAR (70) + '-1.2')
  INSERT INTO #pdf (code) VALUES ('%ÓÓÓÓ')
  INSERT INTO #pdf (code) VALUES ('1' + @beg)
  INSERT INTO #pdf (code) VALUES (@a1)
  INSERT INTO #pdf (code) VALUES ('/' + @cr + ' (Dypso BackOffice' + CHAR(80) + CHAR(83) + CHAR (79) + CHAR(80) + CHAR(68) + CHAR (70) + ')')
  INSERT INTO #pdf (code) VALUES ('/' + @pr + ' (stored procedure for ms sql)')
  INSERT INTO #pdf (code) VALUES ('/' + @ti + ' (SQL2' + CHAR(80) + CHAR(68) + CHAR (70) + ')')
  INSERT INTO #pdf (code) VALUES (@a2)
  INSERT INTO #pdf (code) VALUES (@end)
  INSERT INTO #pdf (code) VALUES ('4' + @beg)
  INSERT INTO #pdf (code) VALUES (@a1)
  INSERT INTO #pdf (code) VALUES ('/Type /Font')
  INSERT INTO #pdf (code) VALUES ('/Subtype /Type1')
  INSERT INTO #pdf (code) VALUES ('/Name /F1')
  INSERT INTO #pdf (code) VALUES ('/Encoding 5' + @ad)
  INSERT INTO #pdf (code) VALUES ('/BaseFont /Courier')
  INSERT INTO #pdf (code) VALUES (@a2)
  INSERT INTO #pdf (code) VALUES (@end)
  INSERT INTO #pdf (code) VALUES ('5' + @beg)
  INSERT INTO #pdf (code) VALUES (@a1)
  INSERT INTO #pdf (code) VALUES ('/Type /Encoding')
  INSERT INTO #pdf (code) VALUES ('/BaseEncoding /WinAnsiEncoding')
  INSERT INTO #pdf (code) VALUES (@a2)
  INSERT INTO #pdf (code) VALUES (@end)
  INSERT INTO #pdf (code) VALUES ('6' + @beg)
  INSERT INTO #pdf (code) VALUES (@a1)
  INSERT INTO #pdf (code) VALUES ('  /Font ' + @a1 + ' /F1 4' + @ad + ' ' + @a2 + '  /ProcSet [ /' + CHAR(80) + CHAR(68) + CHAR (70) + ' /Text ]')
  INSERT INTO #pdf (code) VALUES (@a2)
  INSERT INTO #pdf (code) VALUES (@end)
  INSERT INTO #text(code) (SELECT code FROM psopdf)
  SELECT @x = COUNT(*) FROM #text
  SELECT @x = (@x / 60) + 1
  WHILE  @nopg < @x
    BEGIN
      DECLARE SysKursor  INSENSITIVE SCROLL CURSOR 
      FOR SELECT SUBSTRING((code + SPACE(81)), 1, 80) FROM #text WHERE idnumber BETWEEN ((@nopg * 60) + 1) AND ((@nopg + 1) * 60 )
      FOR READ ONLY    
      OPEN SysKursor
      FETCH NEXT FROM SysKursor INTO @trenutniRed
      SELECT @object = @object + 1
      SELECT @page = @page +  ' ' + CAST(@object AS VARCHAR) + @ad
      SELECT @len = LEN(@object) + LEN(@object + 1)
      INSERT INTO #pdf (code) VALUES (CAST(@object AS VARCHAR)  + @beg)
      INSERT INTO #pdf (code) VALUES (@a1)
      INSERT INTO #pdf (code) VALUES ('/Type /Page')
      INSERT INTO #pdf (code) VALUES ('/Parent 3' + @ad)
      INSERT INTO #pdf (code) VALUES ('/Resources 6' + @ad)
      SELECT @object = @object + 1
      INSERT INTO #pdf (code) VALUES ('/Contents ' + CAST(@object AS VARCHAR) + @ad)
      INSERT INTO #pdf (code) VALUES (@a2)
      INSERT INTO #pdf (code) VALUES (@end)
      SELECT @ofset = @len + 86 + @ofset
      INSERT INTO #xref(code) (SELECT SUBSTRING('0000000000' + CAST(@ofset AS VARCHAR), 
    	LEN('0000000000' + CAST(@ofset AS VARCHAR)) - 9, 
    	LEN('0000000000' + CAST(@ofset AS VARCHAR))) + @xstr)  
      INSERT INTO #pdf (code) VALUES (CAST(@object AS VARCHAR)  + @beg)
      INSERT INTO #pdf (code) VALUES (@a1)
      SELECT @object = @object + 1
      INSERT INTO #pdf (code) VALUES ('/Length ' + CAST(@object AS VARCHAR) + @ad)
      INSERT INTO #pdf (code) VALUES (@a2)
      INSERT INTO #pdf (code) VALUES ('stream')
      INSERT INTO #pdf (code) VALUES ('BT')
      INSERT INTO #pdf (code) VALUES ('/F1 10 Tf')
      INSERT INTO #pdf (code) VALUES ('1 0 0 1 50 802 Tm')
      INSERT INTO #pdf (code) VALUES ('12 TL')
      WHILE @@Fetch_Status = 0
         BEGIN
             INSERT INTO #pdf (code) VALUES ('T* (' + @trenutniRed + ') Tj')
             FETCH NEXT FROM  SysKursor INTO @trenutniRed
          END
      INSERT INTO #pdf (code) VALUES ('ET')
      INSERT INTO #pdf (code) VALUES ('endstream')
      INSERT INTO #pdf (code) VALUES (@end)
      SELECT @rows = (SELECT COUNT(*) FROM #text WHERE idnumber BETWEEN ((@nopg * 60) + 1) AND ((@nopg + 1) * 60 ))* 90 + 45
      SELECT @nopg = @nopg + 1    
      SELECT @len = LEN(@object) + LEN(@object - 1)
      SELECT @ofset = @len + 57 + @ofset + @rows
      INSERT INTO #xref(code) (SELECT SUBSTRING('0000000000' + CAST(@ofset AS VARCHAR), 
     	LEN('0000000000' + CAST(@ofset AS VARCHAR)) - 9, 
   	LEN('0000000000' + CAST(@ofset AS VARCHAR))) + @xstr)   
      INSERT INTO #pdf (code) VALUES (CAST(@object AS VARCHAR)  + @beg)
      INSERT INTO #pdf (code) VALUES (@rows)
      INSERT INTO #pdf (code) VALUES (@end)
      SELECT @len = LEN(@object) + LEN(@rows)
      SELECT @ofset = @len + 18 + @ofset
      INSERT INTO #xref(code) (SELECT SUBSTRING('0000000000' + CAST(@ofset AS VARCHAR), 
    	LEN('0000000000' + CAST(@ofset AS VARCHAR)) - 9, 
    	LEN('0000000000' + CAST(@ofset AS VARCHAR))) + @xstr)  
      CLOSE SysKursor
      DEALLOCATE SysKursor
    END
    INSERT INTO #pdf (code) VALUES ('2' + @beg)
    INSERT INTO #pdf (code) VALUES (@a1)
    INSERT INTO #pdf (code) VALUES ('/Type /Catalog')
    INSERT INTO #pdf (code) VALUES ('/Pages 3' + @ad)
    INSERT INTO #pdf (code) VALUES ('/PageLayout /OneColumn')
    INSERT INTO #pdf (code) VALUES (@a2)
    INSERT INTO #pdf (code) VALUES (@end)
    UPDATE #xref SET code = (SELECT code FROM #xref WHERE idnumber = (SELECT MAX(idnumber) FROM #xref)) WHERE idnumber = 5
    DELETE FROM #xref WHERE idnumber = (SELECT MAX(idnumber) FROM #xref)
    INSERT INTO #pdf (code) VALUES ('3' + @beg)
    INSERT INTO #pdf (code) VALUES (@a1)
    INSERT INTO #pdf (code) VALUES ('/Type /Pages')
    INSERT INTO #pdf (code) VALUES ('/Count ' + CAST(@nopg AS VARCHAR))
    INSERT INTO #pdf (code) VALUES ('/MediaBox [ 0 0 595 842 ]')
    INSERT INTO #pdf (code) VALUES ('/Kids [' + @page + ' ]')
    INSERT INTO #pdf (code) VALUES (@a2)
    INSERT INTO #pdf (code) VALUES (@end)
    SELECT @ofset = @ofset + 79
    UPDATE #xref SET code =(SELECT SUBSTRING('0000000000' + CAST(@ofset AS VARCHAR), 
  	LEN('0000000000' + CAST(@ofset AS VARCHAR)) - 9, 
  	LEN('0000000000' + CAST(@ofset AS VARCHAR))) + @xstr) WHERE idnumber = 6
    INSERT INTO #xref(code) VALUES ('trailer')
    INSERT INTO #xref(code) VALUES (@a1)
    SELECT @object = @object + 1
    UPDATE #xref SET code = '0 ' + CAST(@object AS VARCHAR) WHERE idnumber = 2
    INSERT INTO #xref(code) VALUES ('/Size ' + CAST(@object AS VARCHAR))
    INSERT INTO #xref(code) VALUES ('/Root 2' + @ad)
    INSERT INTO #xref(code) VALUES ('/Info 1' + @ad)
    INSERT INTO #xref(code) VALUES (@a2)
    INSERT INTO #xref(code) VALUES ('startxref')
    SELECT @len = LEN(@nopg) + LEN(@page)
    SELECT @ofset = @len + 86 + @ofset
    INSERT INTO #xref(code) VALUES (@ofset)
    INSERT INTO #xref(code) VALUES ('%%' + CHAR(69) + CHAR (79) + CHAR(70))
    INSERT INTO #pdf (code) (SELECT code FROM #xref) 
    --SELECT code FROM #pdf
    SELECT @trenutniRed = 'del '+ @pdf
    EXECUTE @ole = sp_OACreate 'Scripting.FileSystemObject', @fs OUT
    EXEC master..xp_cmdshell @trenutniRed, NO_OUTPUT

EXECUTE @ole = sp_OAMethod @fs, 'OpenTextFile', @file OUT, @pdf, 8, 1

IF LEN(@filename)>0 
			BEGIN
				-- Chose to create the file directly with 
				-- SQL Server
			
			    DECLARE SysKursor  INSENSITIVE SCROLL CURSOR 
			    FOR SELECT code FROM #pdf ORDER BY idnumber
			    FOR READ ONLY    
			    OPEN SysKursor
			    FETCH NEXT FROM SysKursor INTO @trenutniRed
			    WHILE @@Fetch_Status = 0
				BEGIN
				  EXECUTE @ole = sp_OAMethod @file, 'WriteLine', Null, @trenutniRed
				  FETCH NEXT FROM  SysKursor INTO @trenutniRed 
			        END
			    CLOSE SysKursor
			    DEALLOCATE SysKursor
			END
		ELSE
			BEGIN
				-- Create the file by calling the stored 
				-- proc from ASP and get the result back
				
					SELECT code FROM #pdf ORDER BY idnumber
			END
			
		
			    
    
    DELETE FROM psopdf
    EXECUTE @ole = sp_OADestroy @file
    EXECUTE @ole = sp_OADestroy @fs

Lancer la procédure et générer un fichier PDF à la volée

Commentaire sur l'article

Développement Web

Download Products

What's New ?

Créer des fichier PDF depuis une procédure stockée

Techniques