[SQL Server] Générer un fichier pdf via Transact SQL

  • Imprimer

Générer un fichier pdf via Transact SQL

DECLARE @filename VARCHAR(100)

CREATETABLE #pdf(idnumber INTIDENTITY(1,1)

              ,code NVARCHAR(200))

CREATETABLE #xref(idnumber INTIDENTITY(1,1)

              ,code VARCHAR(30))

CREATETABLE #text(idnumber INTIDENTITY(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

INSERTINTO #xref(code)VALUES ('xref')

INSERTINTO #xref(code)VALUES ('0 10')

INSERTINTO #xref(code)VALUES ('0000000000 65535 f')

INSERTINTO #xref(code)VALUES ('0000000017'+ @xstr)

INSERTINTO #xref(code)VALUES ('0000000790'+ @xstr)

INSERTINTO #xref(code)VALUES ('0000000869'+ @xstr)

INSERTINTO #xref(code)VALUES ('0000000144'+ @xstr)

INSERTINTO #xref(code)VALUES ('0000000247'+ @xstr)

INSERTINTO #xref(code)VALUES ('0000000321'+ @xstr)

INSERTINTO #xref(code)VALUES ('0000000396'+ @xstr)

INSERTINTO #pdf(code)VALUES ('%'+CHAR(80)+CHAR(68)+CHAR (70)+'-1.2')

INSERTINTO #pdf(code)VALUES ('%ÓÓÓÓ')

INSERTINTO #pdf(code)VALUES ('1'+ @beg)

INSERTINTO #pdf(code)VALUES (@a1)

INSERTINTO #pdf(code)VALUES ('/'+ @cr +' (Ivica Masar '+CHAR(80)+CHAR(83)+CHAR (79)+CHAR(80)+CHAR(68)+CHAR (70)+')')

INSERTINTO #pdf(code)VALUES ('/'+ @pr +' (stored procedure for ms sql Cette adresse e-mail est protégée contre les robots spammeurs. Vous devez activer le JavaScript pour la visualiser.)')

INSERTINTO #pdf(code)VALUES ('/'+ @ti +' (SQL2'+CHAR(80)+CHAR(68)+CHAR (70)+')')

INSERTINTO #pdf(code)VALUES (@a2)

INSERTINTO #pdf(code)VALUES (@end)

INSERTINTO #pdf(code)VALUES ('4'+ @beg)

INSERTINTO #pdf(code)VALUES (@a1)

INSERTINTO #pdf(code)VALUES ('/Type /Font')

INSERTINTO #pdf(code)VALUES ('/Subtype /Type1')

INSERTINTO #pdf(code)VALUES ('/Name /F1')

INSERTINTO #pdf(code)VALUES ('/Encoding 5'+ @ad)

INSERTINTO #pdf(code)VALUES ('/BaseFont /Courier')

INSERTINTO #pdf(code)VALUES (@a2)

INSERTINTO #pdf(code)VALUES (@end)

INSERTINTO #pdf(code)VALUES ('5'+ @beg)

INSERTINTO #pdf(code)VALUES (@a1)

INSERTINTO #pdf(code)VALUES ('/Type /Encoding')

INSERTINTO #pdf(code)VALUES ('/BaseEncoding /WinAnsiEncoding')

INSERTINTO #pdf(code)VALUES (@a2)

INSERTINTO #pdf(code)VALUES (@end)

INSERTINTO #pdf(code)VALUES ('6'+ @beg)

INSERTINTO #pdf(code)VALUES (@a1)

INSERTINTO #pdf(code)VALUES (' /Font '+ @a1 +' /F1 4'+ @ad +' '+ @a2 +' /ProcSet [ /'+CHAR(80)+CHAR(68)+CHAR (70)+' /Text ]')

INSERTINTO #pdf(code)VALUES (@a2)

INSERTINTO #pdf(code)VALUES (@end)

INSERTINTO #text(code)(SELECT'this is testing, RSingh'as code)--FROM psopdf)

SELECT @x =COUNT(*)FROM #text

SELECT @x =(@x / 60)+ 1

WHILE @nopg < @x

   BEGIN

     DECLARE SysKursor INSENSITIVESCROLLCURSOR

     FORSELECTSUBSTRING((code +SPACE(81)), 1, 80)FROM #text WHERE idnumber BETWEEN((@nopg * 60)+ 1)AND((@nopg + 1)* 60 )

     FORREAD ONLY  

     OPEN SysKursor

     FETCHNEXTFROM SysKursor INTO @trenutniRed

     SELECT @object = @object + 1

     SELECT @page = @page +' '+CAST(@object ASVARCHAR)+ @ad

     SELECT @len =LEN(@object)+LEN(@object + 1)

     INSERTINTO #pdf(code)VALUES (CAST(@object ASVARCHAR)+ @beg)

     INSERTINTO #pdf(code)VALUES (@a1)

     INSERTINTO #pdf(code)VALUES ('/Type /Page')

     INSERTINTO #pdf(code)VALUES ('/Parent 3'+ @ad)

     INSERTINTO #pdf(code)VALUES ('/Resources 6'+ @ad)

     SELECT @object = @object + 1

     INSERTINTO #pdf(code)VALUES ('/Contents '+CAST(@object ASVARCHAR)+ @ad)

     INSERTINTO #pdf(code)VALUES (@a2)

     INSERTINTO #pdf(code)VALUES (@end)

     SELECT @ofset = @len + 86 + @ofset

     INSERTINTO #xref(code)(SELECTSUBSTRING('0000000000'+CAST(@ofset ASVARCHAR),

      LEN('0000000000'+CAST(@ofset ASVARCHAR))- 9,

      LEN('0000000000'+CAST(@ofset ASVARCHAR)))+ @xstr)

     INSERTINTO #pdf(code)VALUES (CAST(@object ASVARCHAR)+ @beg)

     INSERTINTO #pdf(code)VALUES (@a1)

     SELECT @object = @object + 1

     INSERTINTO #pdf(code)VALUES ('/Length '+CAST(@object ASVARCHAR)+ @ad)

     INSERTINTO #pdf(code)VALUES (@a2)

     INSERTINTO #pdf(code)VALUES ('stream')

     INSERTINTO #pdf(code)VALUES ('BT')

     INSERTINTO #pdf(code)VALUES ('/F1 10 Tf')

     INSERTINTO #pdf(code)VALUES ('1 0 0 1 50 802 Tm')

     INSERTINTO #pdf(code)VALUES ('12 TL')

     WHILE@@Fetch_Status= 0

         BEGIN

             INSERTINTO #pdf(code)VALUES ('T* ('+ @trenutniRed +') Tj')

             FETCHNEXTFROM SysKursor INTO @trenutniRed

         END

     INSERTINTO #pdf(code)VALUES ('ET')

     INSERTINTO #pdf(code)VALUES ('endstream')

     INSERTINTO #pdf(code)VALUES (@end)

     SELECT @rows =(SELECTCOUNT(*)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

     INSERTINTO #xref(code)(SELECTSUBSTRING('0000000000'+CAST(@ofset ASVARCHAR),

       LEN('0000000000'+CAST(@ofset ASVARCHAR))- 9,

       LEN('0000000000'+CAST(@ofset ASVARCHAR)))+ @xstr)  

     INSERTINTO #pdf(code)VALUES (CAST(@object ASVARCHAR)+ @beg)

     INSERTINTO #pdf(code)VALUES (@rows)

     INSERTINTO #pdf(code)VALUES (@end)

     SELECT @len =LEN(@object)+LEN(@rows)

     SELECT @ofset = @len + 18 + @ofset

     INSERTINTO #xref(code)(SELECTSUBSTRING('0000000000'+CAST(@ofset ASVARCHAR),

      LEN('0000000000'+CAST(@ofset ASVARCHAR))- 9,

      LEN('0000000000'+CAST(@ofset ASVARCHAR)))+ @xstr)

     CLOSE SysKursor

     DEALLOCATE SysKursor

   END

   INSERTINTO #pdf(code)VALUES ('2'+ @beg)

   INSERTINTO #pdf(code)VALUES (@a1)

   INSERTINTO #pdf(code)VALUES ('/Type /Catalog')

   INSERTINTO #pdf(code)VALUES ('/Pages 3'+ @ad)

   INSERTINTO #pdf(code)VALUES ('/PageLayout /OneColumn')

   INSERTINTO #pdf(code)VALUES (@a2)

   INSERTINTO #pdf(code)VALUES (@end)

   UPDATE #xref SET code =(SELECT code FROM #xref WHERE idnumber =(SELECTMAX(idnumber)FROM #xref))WHERE idnumber = 5

   DELETEFROM #xref WHERE idnumber =(SELECTMAX(idnumber)FROM #xref)

   INSERTINTO #pdf(code)VALUES ('3'+ @beg)

   INSERTINTO #pdf(code)VALUES (@a1)

   INSERTINTO #pdf(code)VALUES ('/Type /Pages')

   INSERTINTO #pdf(code)VALUES ('/Count '+CAST(@nopg ASVARCHAR))

   INSERTINTO #pdf(code)VALUES ('/MediaBox [ 0 0 595 842 ]')

   INSERTINTO #pdf(code)VALUES ('/Kids ['+ @page +' ]')

   INSERTINTO #pdf(code)VALUES (@a2)

   INSERTINTO #pdf(code)VALUES (@end)

   SELECT @ofset = @ofset + 79

   UPDATE #xref SET code =(SELECTSUBSTRING('0000000000'+CAST(@ofset ASVARCHAR),

     LEN('0000000000'+CAST(@ofset ASVARCHAR))- 9,

     LEN('0000000000'+CAST(@ofset ASVARCHAR)))+ @xstr)WHERE idnumber = 6

   INSERTINTO #xref(code)VALUES ('trailer')

   INSERTINTO #xref(code)VALUES (@a1)

   SELECT @object = @object + 1

   UPDATE #xref SET code ='0 '+CAST(@object ASVARCHAR)WHERE idnumber = 2

   INSERTINTO #xref(code)VALUES ('/Size '+CAST(@object ASVARCHAR))

   INSERTINTO #xref(code)VALUES ('/Root 2'+ @ad)

   INSERTINTO #xref(code)VALUES ('/Info 1'+ @ad)

   INSERTINTO #xref(code)VALUES (@a2)

   INSERTINTO #xref(code)VALUES ('startxref')

   SELECT @len =LEN(@nopg)+LEN(@page)

   SELECT @ofset = @len + 86 + @ofset

   INSERTINTO #xref(code)VALUES (@ofset)

   INSERTINTO #xref(code)VALUES ('%%'+CHAR(69)+CHAR (79)+CHAR(70))

   INSERTINTO #pdf(code)(SELECT code FROM #xref)

   --SELECT code FROM #pdf

   SELECT @trenutniRed ='del '+ @pdf

   EXECUTE @ole =sp_OACreate'Scripting.FileSystemObject', @fs OUT

   EXECmaster..xp_cmdshell@trenutniRed, NO_OUTPUT

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

   DECLARE SysKursor INSENSITIVESCROLLCURSOR

   FORSELECT code FROM #pdf ORDERBY idnumber

   FORREAD ONLY  

   OPEN SysKursor

   FETCHNEXTFROM SysKursor INTO @trenutniRed

   WHILE@@Fetch_Status= 0

       BEGIN

       EXECUTE @ole =sp_OAMethod@file,'WriteLine',Null, @trenutniRed

       FETCHNEXTFROM SysKursor INTO @trenutniRed

       END

   CLOSE SysKursor

   DEALLOCATE SysKursor

   --DELETE FROM psopdf

   EXECUTE @ole =sp_OADestroy@file

   EXECUTE @ole =sp_OADestroy@fs