Announcement

**Scott Slater** · 16 Jul 2009, 07:45 PM

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** · 16 Jul 2009, 07:50 PM

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.

**BOB MECHLER** · 17 Jul 2009, 11:47 AM

Just needed someone to confirm the steps. Thanks,

Bob Mechler

**Carlo Pagani** · 17 Jul 2009, 01:57 PM

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.

**BOB MECHLER** · 17 Jul 2009, 02:05 PM

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

**Carlo Pagani** · 17 Jul 2009, 02:26 PM

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

**BOB MECHLER** · 17 Jul 2009, 03:12 PM

Thanks, I think I can follow this well enough.

Bob Mechler

**BOB MECHLER** · 17 Jul 2009, 03:47 PM

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

**Michael Mattias** · 17 Jul 2009, 06:23 PM

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

Eric has GOT to be smiling about that.

**BOB MECHLER** · 17 Jul 2009, 09:57 PM

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

**Carlo Pagani** · 18 Jul 2009, 01:48 AM

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.

**Michael Mattias** · 18 Jul 2009, 08:32 AM

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.

**BOB MECHLER** · 18 Jul 2009, 12:55 PM

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

**BOB MECHLER** · 18 Jul 2009, 04:31 PM

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

**Carlo Pagani** · 19 Jul 2009, 02:32 AM

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)

**BOB MECHLER** · 21 Jul 2009, 02:50 PM

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

Bob Mechler

**Carlo Pagani** · 21 Jul 2009, 03:12 PM

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!

**BOB MECHLER** · 21 Jul 2009, 04:34 PM

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

**Michael Mattias** · 21 Jul 2009, 05:49 PM

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

Announcement

Storing and retrieving pdf's in SQL TABLE

Storing and retrieving pdf's in SQL TABLE

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment