Announcement

Collapse
No announcement yet.

Storing and retrieving pdf's in SQL TABLE

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Storing and retrieving pdf's in SQL TABLE

    1. What type of MS SQL field would I set up.
    2. How would I contruct an SQL Statement that would store the PDF itself. (I understand each PDF table would have other fields to id it {account,date,type})
    3. Once I've done a select and the row is returned, how do I then display it?

    I'm thinking GET$ and PUT$ but very unsure about what to do.

    Bob Mechler

  • #2
    You wan to load it from the disk file into memory (string var), and then store it to a BLOB field in SQL. You can optionally base64 encode it beforehand too.
    Scott Slater
    Summit Computer Networks, Inc.
    www.summitcn.com

    Comment


    • #3
      Code:
      Open PDF_FILE$ for Binary as 1
      Get$ 1, Lof(1), sBuff$
      Close 1
      
      SQL INSERT....
      
      to read it back...
      
      SQL SELECT to get raw data back into sBuff$
      
      Open PDF_FILE$ for Binary as 1
      Put$ 1, sBuff$
      Close 1
      
      ViewPDF PDF_FILE$
      
      Kill PDF_FILE$
      That's roughly the steps... give or take.
      Scott Slater
      Summit Computer Networks, Inc.
      www.summitcn.com

      Comment


      • #4
        Just needed someone to confirm the steps. Thanks,

        Bob Mechler

        Comment


        • #5
          Bob, a BINARY field would be best. How you get it in depends on what you've got. If you have SQL tools I will post a routine for you. Also, for completeness, please state the version of MS SQL you are using.

          Comment


          • #6
            Thanks Carlo,

            Using MS SQL 2005 Express for my testing. I have SQL TOOLS pro.
            A co-worker suggested the 'image' field type.

            Bob Mechler

            Comment


            • #7
              ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Binary is the right one to use. I'll cobble together some code for you in next few minutes....

              Code:
              'Basic structure to insert your PDF file into a binary field
              local SQL, PDF_File as STRING
              local lResult as long
              
              SQL = "insert YourTable(SomeIntField, SomeTextField, YourPDFField) "+_
                "values("+Str$(SomeNumericVariable)+",'"+SomeTextVariable+"',?)" 'You will probably have more fields
              lResult = SQL_Statement(1, 1, %SQL_STMT_PREPARE,SQL) '
              
              IF lResult = %Success THEN
                'Bind parameters
                lResult = SQL_BindParameter(1, 1, 1, %SQL_PARAM_INPUT, 99, %SQL_VARBINARY, LEN(PDF_File), 0, 1, LEN(PDF_File), %SQL_LONG_DATA)             'lIndicator&
              
                IF lResult = %Success then
                  'Execute the SQL statement
                  lResult = SQL_Statement(1, 1, %SQL_STMT_EXECUTE, "")
              
                  if lResult = %Success then
                    'Tell the driver that we are about to "fill" a parameter
                    lResult = SQL_NextParameter(1, 1)
              
                    IF lResult = %Success then
                      lResult = SQL_LongParameter(1, 1, PDF_File, LEN(PDF_File))
                    
                      if lResult = %Success then
                         lResult = SQL_NextParameter(1, 1)
                      end if
                    end if
                  end if
                end if
              end if
              if lResult <> %Success then
                'Something failed
                'Report error
              end if
              Last edited by Carlo Pagani; 17 Jul 2009, 02:52 PM.

              Comment


              • #8
                Thanks, I think I can follow this well enough.

                Bob Mechler

                Comment


                • #9
                  Very helpful.

                  The following code adapted from your post seemed to insert the row and it reports <binary data> in the parameterized field PDFDOC but I get 2 function sequence errors at

                  SQL_MsgBox SQL_ErrorQuickOne + " 4", MSGBOX_OK
                  SQL_MsgBox SQL_ErrorQuickOne + " 5", MSGBOX_OK

                  I changed the field to varbinary(50) which I assume adjusts to the size needed, cause there was no choice for just Binary or VarBinary in 2005 Express.

                  Code:
                        'Basic structure to insert your PDF file into a binary field
                        local SQL AS STRING, PDF_File as STRING
                        'local lResult as long
                  
                        OPEN "C:\PDFS\CULAND2.PDF" FOR BINARY AS 1
                          GET$ 1,LOF(1),PDF_File 
                        CLOSE 1
                        SQL = "insert STMTPDFS ([ACCOUNT],[DATESTR],[QUALIFIER],[PDFDOC]) VALUES('1101500','2009-07-17','UNAUDITED',?)" 
                        lresult& = SQL_Statement(1, 1, %SQL_STMT_PREPARE,SQL) '
                        IF SQL_Errorpending THEN
                          SQL_MsgBox SQL_ErrorQuickOne + " 1", MSGBOX_OK
                        END IF
                        
                        IF lresult& = %Success THEN
                          'Bind parameters
                          lresult& = SQL_BindParameter(1, 1, 1, %SQL_PARAM_INPUT, 99, %SQL_VARBINARY, LEN(PDF_File), 0, 1, LEN(PDF_File), %SQL_LONG_DATA)             'lIndicator&
                            IF SQL_Errorpending THEN
                              SQL_MsgBox SQL_ErrorQuickOne + " 2", MSGBOX_OK
                            END IF
                        
                          IF lresult& = %Success then
                            'Execute the SQL statement
                            lresult& = SQL_Statement(1, 1, %SQL_STMT_EXECUTE, "")
                            IF SQL_Errorpending THEN
                              SQL_MsgBox SQL_ErrorQuickOne + " 3", MSGBOX_OK
                            END IF
                        
                            if lresult& = %Success then
                              'Tell the driver that we are about to "fill" a parameter
                              lresult& = SQL_NextParameter(1, 1)
                              IF SQL_Errorpending THEN
                                SQL_MsgBox SQL_ErrorQuickOne + " 4", MSGBOX_OK
                              END IF
                        
                              IF lresult& = %Success then
                                lresult& = SQL_LongParameter(1, 1, PDF_File, LEN(PDF_File))
                                IF SQL_Errorpending THEN
                                  SQL_MsgBox SQL_ErrorQuickOne + " 5", MSGBOX_OK
                                END IF
                              
                                if lresult& = %Success then
                                   lresult& = SQL_NextParameter(1, 1)
                                  IF SQL_Errorpending THEN
                                    SQL_MsgBox SQL_ErrorQuickOne + " 6", MSGBOX_OK
                                  END IF
                                end if
                              end if
                            end if
                          end if
                        end if
                        if lresult& <> %Success then
                          msgbox "Problem somewhere"
                        end if

                  Bob Mechler

                  Comment


                  • #10
                    Interesting... lots of SQL Tools' syntax, but no mention of SQL Tools.

                    Eric has GOT to be smiling about that.
                    Michael Mattias
                    Tal Systems (retired)
                    Port Washington WI USA
                    [email protected]
                    http://www.talsystems.com

                    Comment


                    • #11
                      Pardon me, Michael. Review the start of post. Carlo asked if I had SQL Tools and I replied I had SQL Tools Pro.

                      No slight intended Eric. Fantastic product!

                      Bob Mechler

                      Comment


                      • #12
                        Bob, BINARY was the wrong suggestion as it is a fixed length data type. I should have recommended VARBINARY(MAX) This will allow you to save anything up to 2GB in size.

                        From the SQL Book, "Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length"

                        SQL Tools has certainly made my life working with data a more pleasant experience.

                        Comment


                        • #13
                          Whoa, I went right past those references to SQL Tools. Never saw 'em; lucky I wasn't looking out for a bus so I could safely cross the street.

                          Eric has STILL got to be smiling, albeit for a different reason.
                          Michael Mattias
                          Tal Systems (retired)
                          Port Washington WI USA
                          [email protected]
                          http://www.talsystems.com

                          Comment


                          • #14
                            The next step

                            The following code inserts a record and supposedly a PDF into an SQL 2005 table. The proof of course is if I can retrieve the PDF into a variable. Here is where I've gotten to so far but the results show 0 bytes read.

                            All the other fields are retrieved as you would expect.

                            Maybe I should put this on the Third party forum.

                            Code:
                                  'Basic structure to insert your PDF file into a binary field
                                  local SQL AS STRING, PDF_File as STRING
                                  'local lResult as long
                                  SHELL ENVIRON$("COMSPEC") + " /C C:\PDFS\CULAND2.PDF",0
                                  OPEN "C:\PDFS\CULAND2.PDF" FOR BINARY AS 1
                                    GET$ 1,LOF(1),PDF_File 
                                  CLOSE 1
                                  SQL = "insert STMTPDFS ([ACCOUNT],[DATESTR],[QUALIFIER],[PDFDOC]) VALUES('1101500','2009-07-17','UNAUDITED',?)" 
                                  lresult& = SQL_Statement(1, 1, %SQL_STMT_PREPARE,SQL) '
                                  IF SQL_Errorpending THEN
                                    SQL_MsgBox SQL_ErrorQuickOne + " 1", MSGBOX_OK
                                  END IF
                                  
                                  IF lresult& = %Success THEN
                                    'Bind parameters
                                    lresult& = SQL_BindParameter(1, 1, 1, %SQL_PARAM_INPUT, 99, %SQL_LONGVARBINARY, LEN(PDF_File), 0, 1, LEN(PDF_File), %SQL_LONG_DATA)             'lIndicator&
                                      IF SQL_Errorpending THEN
                                        SQL_MsgBox SQL_ErrorQuickOne + " 2", MSGBOX_OK
                                      END IF
                                  
                                    IF lresult& = %Success then
                                      'Execute the SQL statement
                                      lresult& = SQL_Statement(1, 1, %SQL_STMT_EXECUTE, "")
                                      IF SQL_Errorpending THEN
                                        SQL_MsgBox SQL_ErrorQuickOne + " 3", MSGBOX_OK
                                      END IF
                                  
                                      if lresult& = %Success then
                                        'Tell the driver that we are about to "fill" a parameter
                                        lresult& = SQL_NextParameter(1, 1)
                                        IF SQL_Errorpending THEN
                                          SQL_MsgBox SQL_ErrorQuickOne + " 4", MSGBOX_OK
                                        END IF
                                  
                                        IF lresult& = %Success then
                                          lresult& = SQL_LongParameter(1, 1, PDF_File, LEN(PDF_File))
                                          IF SQL_Errorpending THEN
                                            SQL_MsgBox SQL_ErrorQuickOne + " 5", MSGBOX_OK
                                          END IF
                                        
                                          if lresult& = %Success then
                                             lresult& = SQL_NextParameter(1, 1)
                                            IF SQL_Errorpending THEN
                                              SQL_MsgBox SQL_ErrorQuickOne + " 6", MSGBOX_OK
                                            END IF
                                          end if
                                        end if
                                      end if
                                    end if
                                  end if
                                  if lresult& <> %Success then
                                    msgbox "Problem somewhere"
                                  end if
                                  '
                                  ' Now retrieve the PDF
                                  '
                                  'Starting retrieval
                                  msgbox("Starting retrieval")
                                  DIM sData            AS LOCAL ASCIIZ * 32000
                                  DIM sBlock           AS LOCAL STRING
                                  SQL = "SELECT ACCOUNT,DATESTR,QUALIFIER,PDFDOC FROM STMTPDFS WHERE ACCOUNT = '1101500'"
                                  lresult& = SQL_Statement(1, 1, %SQL_STMT_PREPARE,SQL)     
                                  lresult& = SQL_Statement(1, 1, %SQL_STMT_EXECUTE,SQL)     
                                  SQL_Fetch %NEXT_ROW 
                                  sAccount$ = SQL_ResultColumnStr(1,1,1)
                                  sDatastr$ = SQL_ResultColumnStr(1,1,2)
                                  sQualifier$ = SQL_ResultColumnStr(1,1,3)
                                  SQL_DirectBindColumn(1,1,4,%SQL_LONGVARBINARY,VARPTR(sData),32000)
                                  MSGBOX(sAccount$ + $CRLF + _
                                  sDatastr$ + $CRLF + _
                                  sQualifier$) 
                              
                                  'Check for unexpected errors
                                  IF SQL_ErrorPending THEN
                                      SQL_MsgBox SQL_ErrorQuickAll, %MSGBOX_OK
                                  END IF
                                  
                                  SQL_MsgBox FORMAT$(LEN(TRIM$(sData)))+" characters read.", %MSGBOX_OK

                            Comment


                            • #15
                              varbinary(max) produces a message indicating it must be between 1 to 8000. Must be because it's SQL 2005 EXPRESS.

                              It also may be an old sql 2000 db that I registered on the SQL 2005 EXPRESS management studio. I'll look at that for a while.

                              Bob Mechler

                              Comment


                              • #16
                                Change the DB compatability to 9. If you are using a SQL 2000 DB then it would be limited to 8000 as SQL 2000 did not support VARCHAR(Max)

                                Comment


                                • #17
                                  Ended up using Jose Roca's fine ADO library and have a working program.

                                  Bob Mechler

                                  Comment


                                  • #18
                                    Bob, it's not a SQL Tools issue. I tested with my wrappers and it works. Unfortunately I've written wrappers to suite my SQL tools needs and I use both varbinary and binary fields in SQL 2005 quite happily.... and very fast!

                                    Comment


                                    • #19
                                      I'm sure it's not an SQL Tools issue. It's a me issue. I've asked Eric to look at the code I've posted and see if he can determine what I'm doing wrong or tweak the code where needed.

                                      I just got the ADO libs from Jose to work first, is all.

                                      99% of my SQL work will still be with SQL Tools because for my stuff it's much faster than ADO for transaction processing etc.

                                      Bob Mechler

                                      Comment


                                      • #20
                                        99% of my SQL work will still be [Thru ODBC using ] SQL Tools because for my stuff it's much faster than ADO for transaction processing etc.
                                        Just keep telling yourself that. You'll feel much better.

                                        I just keep telling myself using OLE thru ADO is faster and it makes me feel much better.


                                        MCM
                                        Michael Mattias
                                        Tal Systems (retired)
                                        Port Washington WI USA
                                        [email protected]
                                        http://www.talsystems.com

                                        Comment

                                        Working...
                                        X