Announcement

Collapse
No announcement yet.

sql tools and blob fields

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

  • sql tools and blob fields

    Eric,

    In the PowerBasic Windows forum I started a thread concerning how to store and retrieve a PDF into a VARBINARY sql 2005 db, retrieve it as a variable, save it to a file and redisplay it as a pdf.

    Here is my last posted code sample.

    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
    Carlo helped me with the saving part. Can't tell if it worked because I can't get the retrieving part to report an data read for that column though the other columns come back without error.

  • #2
    Hey Bob

    You don't need to use a parameter to retrieve the data.
    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)
          sData$ = SQL_ResultColumnStr(1,1,4)
          
          SQL_MsgBox FORMAT$(LEN(TRIM$(sData)))+" characters read.", %MSGBOX_OK

    Comment


    • #3
      Still returns 0 bytes
      Code:
            '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)
            sData = SQL_ResultColumnStr(1,1,4)
            msgbox(str$(SQL_ResultColumnType(1,1,4)))
            msgbox(str$(SQL_ResultColumnLen(1,1,4)))
            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
      Next I'll start by trying to use the SaveLongData and ReadLongData examples that come with Sqlt_pro. They use an mdb so I guess I'll need to add an OLE type field if the pdf is over 64 k.


      Bob Mecler

      Comment


      • #4
        The pdf saving and retrieval worked fine using the sample programs supplied with product for Blob fields but the field demoed was the access mdb memo field. It's limited to 64 K

        Also I don't know which sql type of field it should be. Memo in mdb is what in SQL?

        Bob Mechler

        Comment


        • #5
          You can't use ASCIIZ, Use a string for the PDF returned data. It does work.

          Comment


          • #6
            Changed to string. Still no luck.

            Code:
                  msgbox("Starting retrieval")
                  DIM sData            AS LOCAL STRING
                  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)
                  sData = SQL_ResultColumnStr(1,1,4)
                  msgbox(str$(SQL_ResultColumnType(1,1,4)))
                  msgbox(str$(SQL_ResultColumnLen(1,1,4)))
                  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
            File layout:
            Account char(14)
            DATESTR char(10)
            QUALIFIER char(15)
            PDFDOC varbinary(50)

            I've tried PDFDOC as varbinary(8000) also which is the max but my pdf is only 3757 bytes on disk.

            Is there anything wrong with my SQL table design for PDFDOC?

            As I said the SaveLongData and ReadLongData work for Memo fields in Access MDB but not OLE fields. Code was exactly the same on both variations of sample program.

            There isn't any explanation of how to map an Accesss memo field to an SQL 2005 field. Trying the upsizing wizard returns an overflow error.

            Shouldn't be this hard. People say it works and I'm sure it does once I find the stupid thing I'm doing wrong.

            Bob Mechler

            Comment


            • #7
              SQL 2005 EXPRESS doesn't have any fields that can hold more than 8000 characters (which is probably why it's free).

              mdb memo fields hold up to 64kb so that is probably why it worked using an MDB file and a memo field.

              Might just switch to using and MDB file unless someone can think of a reason not to.

              I made clones of the sample code from the SQL Tools install and just added a PDFDOC field that I used in one version. Other than the fact that the PDFDOC field was an OLE field the code was exactly the same in both methods. The memo field worked and the OLE field did not.

              Bob Mechler

              Comment


              • #8
                >Memo in mdb is what in SQL?

                Data types supported and the literal name for that type (what you have to use in the CREATE/ALTER TABLE statement) are a function of the DBMS and are not 'universal' like "SQL language" is.

                "mdb" assumed to be MS-Jet (Access) ; Jet supports:

                Code:
                Data Source Name:Foundation Data 32
                Database Name   :e:\Landmark\Data\ADCO\a7_data
                DBMS Name       :ACCESS
                DBMS Version    :03.50.0000
                Driver Name     :ODBCJT32.DLL
                Driver ODBC Ver :03.51
                
                Supported Capabilities
                Transactions (DML) : Y
                CREATE TABLE       : Y
                DROP   TABLE       : Y
                ALTER  TABLE       : N
                CREATE INDEX       : Y
                DROP   INDEX       : Y
                Nullable Columns   : N
                Max Concurrent Stmt:  0 (Unable to determine)
                
                           Datatypes Supported
                 SQL Type     DBMS Literal     Column Size
                 --------     ------------     -----------
                     -11      GUID                     36
                      -7      BIT                       1
                      -6      BYTE                      3
                      -4      LONGBINARY       1073741823
                      -3      VARBINARY               255
                      -2      BINARY                  255
                      -1      LONGCHAR         2147483647
                       1      CHAR                    255
                       2      CURRENCY                 19
                       4      INTEGER                  10
                       4      COUNTER                  10
                       5      SMALLINT                  5
                       7      REAL                      7
                       8      DOUBLE                   15
                       9      DATETIME                 19
                      12      VARCHAR                 255
                
                *** END OF REPORT ***
                ( This was from "Access97" database)

                So I think LONGBINARY will do the job for your PDFs.

                You need to check the types available on each different brand of database.

                If "memo" is 255 max, looks like there's a couple of types would fit that bill. I will guess "memo" is just something cutesey-wutesety on the screen.



                MCM
                Michael Mattias
                Member
                Last edited by Michael Mattias; 18 Jul 2009, 07:19 PM.
                Michael Mattias
                Tal Systems (retired)
                Port Washington WI USA
                [email protected]
                http://www.talsystems.com

                Comment


                • #9
                  In Access 2000 the Memo field allowed 64kb which was ok for testing. Memo is probably one of the fields shown supported by the jet database spec. (DAO, I think)

                  Putting a PDF held in a string that was filled using a binary GET$ in PB seems to work fine in the 'Memo' field. The Access help isn't real clear on what can be stored in it but the PDF data string had many 'NULLS' in and it stored and retrieved this field just fine using SQL Tools and the sample programs.

                  My original intent was to do this in SQL 2005 Express but had all kinds of problems with getting the right variable type to work.

                  Haven't tried everything yet.

                  I didn't see a LONGBINARY in the SQL 2005 Express table builder. Image seemed close but they say that data type is going away.

                  Thanks for the additional information.

                  Bob Mechler

                  Comment


                  • #10
                    You could try (n)text or (n)varchar(MAX) as the column's data type.

                    Comment


                    • #11
                      >Memo in mdb is what in SQL?
                      The 'memo' data type (stores text data) in a MDB file is:
                      • Type: LONGCHAR
                      • Data type: SQL_LONGVARCHAR


                      As noted, its max size in Access 2000 is 64kb. Microsoft says:
                      If you want to store formatted text or long documents, you should create an OLE field instead of a Memo field.
                      The 'OLE Object' data type (stores binary data) in a MDB file (and I presume SQL 2005 Express - never used it) is:
                      • Type: LONGBINARY
                      • Data type: SQL_LONGVARBINARY


                      Where:
                      • Type: The datasource-dependent name of the column's data type. i.e. INTEGER. This is what the database manufacturer calls it.
                      • Data type: The column's SQL Data Type. i.e. SQL_INTEGER


                      Using SQL_Tools, SQL_LongResultColumn and SQL_ResultColumnMore are the commands used to read data from either a SQL_LONGVARCHAR (memo) or SQL_LONGVARBINARY (OLE) column (and also a SQL_wLONGVARCHAR Unicode long column).

                      Data is retrieved in chunks using these commands.

                      The default size is 32k bytes. This can be changed with the statement:
                      Code:
                      SQL_SetOptionSInt, %OPT_DATALEN_CHUNK, lValue
                      where lValue is the new size to set. The default is 32,768.

                      However, ODBC drivers add a null-terminator character - Chr$(0) - at the end of each chunk. SQL Tools removes this, which is fine for text data, but not for binary data such as a PDF file.

                      To prevent this, call this beforehand:
                      Code:
                      SQL_SetOptionSInt %OPT_LONGRES_COLTYPE, %SQL_LONGVARBINARY
                      Putting it together, this should work for you:
                      Code:
                          Local lColNum As Long
                          Local lDbsNum As Long
                          Local lSteNum As Long
                          Local sRtn As String
                      
                          lDbsNum = 1   ' match your example
                          lSteNum = 1   ' ""
                          lColNum = 4   ' ""
                          Call SQL_SetOptionSInt %OPT_LONGRES_COLTYPE, %SQL_LONGVARBINARY         ' as retrieving binary data
                          sRtn = SQL_LongResultColumn(lDbsNum, lSteNum, lColNum)
                          Do
                              If IsTrue(SQL_ResultColumnMore(lDbsNum, lSteNum, lColNum)) Then
                                  ' more data to be read from this long result column
                                  ' note: do not use SQL_Okay on SQL_ResultColumnMore:
                                  ' .. SQL_ResultColumnMore returns true (-1) if more data to be read; and false (0) if all data has been read
                                  sRtn = sRtn + SQL_LongResultColumn(lDbsNum, lSteNum, lColNum)
                              Else
                                  ' retrieved all of the data from this column
                                  Exit Do
                              End If
                          Loop
                          ' sRtn holds the PDF data

                      Comment


                      • #12
                        Can't tell if pdf saved right or not.

                        The following is the latest attempt using code and reading the docs as best I can. Still no results. I did go to db compatiblity 9 and use VARBINARY(max). I use the SQL_LongResultColumn code.

                        The code does not return an error when inserting the row. It returns an error when trying to execute SQL_LongResultColumn saying 'Invalid Descriptor Index'

                        Code:
                              local SQL AS STRING, PDF_File as STRING
                              
                              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
                              lResult& = SQL_StmtParamCount 
                              msgbox("Param count = " + str$(lResult&))
                              msgbox("Length of variable = " + str$(LEN(PDF_File)))
                              lResult& = SQL_BindParam(1, _                     'lParameterNumber&
                                                      %SQL_PARAM_INPUT, _      'lParamType&
                                                      %SQL_DEFAULT, _          'lBasType&
                                                      %SQL_LONGVARBINARY, _    'lSQLType&
                                                      LEN(PDF_File), _         'lDisplaySize&
                                                      0, _                     'lDigits&
                                                      STRPTR(PDF_File), _      'lPointerToBuffer& STRPTR per documentation
                                                      LEN(PDF_File), _         'lBufferLen&
                                                      %SQL_LONG_DATA)          'lIndicator&
                              IF SQL_ErrorPending THEN
                                SQL_MsgBox SQL_ErrorQuickAll + " Problem binding", %MSGBOX_OK 
                              end if      
                              lresult& = SQL_Statement(1, 1, %SQL_STMT_EXECUTE,"")
                              msgbox("Length of variable after execute = " + str$(LEN(PDF_File)))
                              IF SQL_ErrorPending THEN
                                SQL_MsgBox SQL_ErrorQuickAll + " Problem Executing ", %MSGBOX_OK 
                                goto ejob
                              end if      
                              '
                              ' Now retrieve the PDF
                              '
                              'Starting retrieval
                              msgbox("Starting retrieval")
                              DIM sData            AS LOCAL STRING
                              DIM sBlock           AS LOCAL STRING
                              sData = "": sBlock = ""
                              SQL = "SELECT ACCOUNT,DATESTR,QUALIFIER,PDFDOC FROM STMTPDFS WHERE ACCOUNT = '1101500'"
                              lresult& = SQL_Statement(1, 1, %SQL_STMT_IMMEDIATE,SQL)     
                              IF SQL_ErrorPending THEN
                                SQL_MsgBox SQL_ErrorQuickAll + " Problem executing return sql stmt ", %MSGBOX_OK 
                                goto ejob
                              end if  
                              SQL_Fetch %NEXT_ROW 
                              IF SQL_ErrorPending THEN
                                SQL_MsgBox SQL_ErrorQuickAll + " Problem Fetching row ", %MSGBOX_OK 
                                goto ejob
                              end if  
                              sAccount$ = SQL_ResultColumnStr(1,1,1)
                              sDatastr$ = SQL_ResultColumnStr(1,1,2)
                              sQualifier$ = SQL_ResultColumnStr(1,1,3)
                              MSGBOX(sAccount$ + $CRLF + _
                              sDatastr$ + $CRLF + _
                              sQualifier$) 
                              IF SQL_ErrorPending THEN
                                SQL_MsgBox SQL_ErrorQuickAll + " Problem returning normal fields ", %MSGBOX_OK 
                                goto ejob
                              end if
                              Call SQL_SetOptionSInt %OPT_LONGRES_COLTYPE,%SQL_LONGVARBINARY
                              MSGBOX("Result columns = " + str$(SQL_ResColCount))
                              sData$ = SQL_LongResultColumn(1,1,4)
                              IF SQL_ErrorPending THEN
                                SQL_MsgBox SQL_ErrorQuickAll + " Problem returning long data ", %MSGBOX_OK 
                                goto ejob
                              end if  
                              Do
                                If IsTrue(SQL_ResultColumnMore(1,1,4)) then
                                  sData$ = sData$ + SQL_LongResultColumn(1,1,4)
                                  IF SQL_ErrorPending THEN                                                    
                                    incr numtimes&
                                    SQL_MsgBox SQL_ErrorQuickAll + " Problem returning MORE long data " + str$(numtimes&), %MSGBOX_OK 
                                    goto ejob
                                  end if  
                                Else  
                                  Exit do
                                End if
                              Loop
                          
                              'Check for unexpected errors
                              IF SQL_ErrorPending THEN
                                  SQL_MsgBox SQL_ErrorQuickAll, %MSGBOX_OK
                              END IF
                              
                              SQL_MsgBox FORMAT$(LEN(sData)) + " characters read.", %MSGBOX_OK
                        Bob Mechler

                        Comment


                        • #13
                          Bob, what is the complete error please, including error numbers?

                          The ODBC error 'invalid descriptor index' is error number 07009. SQL Tools help gives this description:
                          An invalid column number or parameter number was used. For example, you may have used a column number that is larger than the number of columns in a result set, or you may have specified column zero when the STMT_ATTR_USE_BOOKMARKS attribute was not set to the correct value.
                          The column number 4 matches for the retrieval, and if there was an ODBC error with the parameter number in the insertion, this would have been noticed at that stage.

                          This list of ODBC status return codes provides more detail and possible causes.

                          The "Call SQL_SetOptionSInt %OPT_LONGRES_COLTYPE,%SQL_LONGVARBINARY" code is called while the 'statement number' is open, so this may be the cause of the error: once the statement is opened, all the columns are bound.

                          So can you move the "SQL_SetOptionSInt..." line to the top of the code (preferred), or call SQL_CloseStatement and then SQL_SetOptionSInt after the insertion and before the retrieval.

                          "sData$ = SQL_ResultColumnText(1,1,4)" should show if any data is in the PDFDOC column.

                          Note that SQL_LongResultColumn can produce a 'string data, right-truncated' error message, which is normal and can be ignored.

                          ---
                          Some issues with the SQL_BindParam call:

                          The lBasType& parameter should be %BAS_STRING [1] (not %SQL_DEFAULT [99]). However, Steven Pringels found that this should be %SQL_BINARY [-2] for SQL Server 2000, so this may be the same for SQL 2005 Express.

                          The lDisplaySize& parameter may not be valid.
                          Check this by using "lValue = SQL_ResultColumnInfoSInt(1, 1, 4, %RESCOL_DISPLAY_SIZE)".

                          The lIndicator& parameter should be a LONG variable, not an equate.

                          ---
                          Also, suggest you use SQL_BindParameter instead of SQL_BindParam so you can use the database and statement numbers as per the rest of your code.
                          Christopher Carroll
                          Member
                          Last edited by Christopher Carroll; 19 Jul 2009, 04:18 PM. Reason: SQL_BindParameter

                          Comment


                          • #14
                            > ... 'Memo' field. The Access help isn't real clear on what can be stored in it

                            Thru either ODBC or OLE you can query what the various data types support.

                            I believe you are using ODBC via SQL Tools and I KNOW SQL Tools has a function to report on data types.

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

                            Comment


                            • #15
                              The following came up during execution:
                              Instead of the length of PDF_File for DisplaySize it says the following for Column Width

                              Column length = -2 if I used %SQL_LONGVARBINARY with VARBINARY(max) in the DB

                              Column length = 2147483647 IF I use %SQL_LONGVARCHAR with VARCHAR(max) in the DB

                              Error that comes up is

                              68097.157 ODBC 1 1 4 -1 07009 0 -[Microsoft][ODBC SQL Server Driver] Invalid Descriptor Index

                              after it executes the following statement:

                              sData$ = SQL_LongResultColumn(1,1,4)

                              Code:
                                    'Basic structure to insert your PDF file into a binary field
                                    local SQL AS STRING, PDF_File as STRING,lIndicator AS LONG,lChunk  AS LONG
                                    lChunk& = 32768 
                                    
                                    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',?)"
                                    lIndicator& = 1
                                    lresult& = SQL_Statement(1, 1, %SQL_STMT_PREPARE,SQL) '
                                    IF SQL_Errorpending THEN
                                      SQL_MsgBox SQL_ErrorQuickOne + " 1", MSGBOX_OK
                                    END IF
                                    lValue& = len(PDF_file)
                                    lResult& = SQL_BindParameter(1,1,1, _            'lParameterNumber&
                                                            %SQL_PARAM_INPUT, _      'lParamType&
                                                            %BAS_STRING, _           'lBasType&
                                                            %SQL_LONGVARBINARY, _    'lSQLType&
                                                            lValue&, _               'lDisplaySize&
                                                            0, _                     'lDigits&
                                                            STRPTR(PDF_File), _      'lPointerToBuffer& STRPTR per documentation
                                                            LEN(PDF_File), _         'lBufferLen&
                                                            lIndicator&)             'lIndicator&
                                    IF SQL_ErrorPending THEN
                                      SQL_MsgBox SQL_ErrorQuickAll + " Problem binding", %MSGBOX_OK 
                                    end if      
                                    lresult& = SQL_Statement(1, 1, %SQL_STMT_EXECUTE,"")
                                    IF SQL_ErrorPending THEN
                                      SQL_MsgBox SQL_ErrorQuickAll + " Problem Executing ", %MSGBOX_OK 
                                      goto ejob
                                    end if
                                    '
                                    ' Now retrieve the PDF
                                    '
                                    'Starting retrieval
                                    msgbox("Starting retrieval")
                                    DIM sData            AS LOCAL STRING
                                    DIM sBlock           AS LOCAL STRING
                                    sData = "": sBlock = ""
                                    SQL_SetOptionSInt  %OPT_DATALEN_CHUNK, lChunk&      
                                    SQL = "SELECT ACCOUNT,DATESTR,QUALIFIER,PDFDOC FROM STMTPDFS WHERE ACCOUNT = '1101500'"
                                    lresult& = SQL_Statement(1, 1, %SQL_STMT_IMMEDIATE,SQL)     
                                    IF SQL_ErrorPending THEN
                                      SQL_MsgBox SQL_ErrorQuickAll + " Problem executing return sql stmt ", %MSGBOX_OK 
                                      goto ejob
                                    end if  
                                    SQL_Fetch %NEXT_ROW 
                                    IF SQL_ErrorPending THEN
                                      SQL_MsgBox SQL_ErrorQuickAll + " Problem Fetching row ", %MSGBOX_OK 
                                      goto ejob
                                    end if  
                                    sAccount$ = SQL_ResultColumnStr(1,1,1)
                                    sDatastr$ = SQL_ResultColumnStr(1,1,2)
                                    sQualifier$ = SQL_ResultColumnStr(1,1,3)
                                    lValue& = SQL_ResultColumnInfoSInt(1, 1, 4, %RESCOL_DISPLAY_SIZE)
                                    msgbox("Column length = " + str$(lValue&))
                                    IF SQL_ErrorPending THEN
                                      SQL_MsgBox SQL_ErrorQuickAll + " Problem getting column length", %MSGBOX_OK 
                                    end if
                                    MSGBOX(sAccount$ + $CRLF + _
                                    sDatastr$ + $CRLF + _
                                    sQualifier$) 
                                    IF SQL_ErrorPending THEN
                                      SQL_MsgBox SQL_ErrorQuickAll + " Problem returning normal fields ", %MSGBOX_OK 
                                      goto ejob
                                    end if
                                    sData$ = SQL_ResultColumnStr(1,1,4)
                                    msgbox("Len of ResultColumnText = " + str$(len(sData$)) + sData$)
                                    IF SQL_ErrorPending THEN
                                      SQL_MsgBox SQL_ErrorQuickAll + " Problem returning normal fields ", %MSGBOX_OK 
                                      goto ejob
                                    end if            
                                    MSGBOX("Result columns = " + str$(SQL_ResColCount))
                                    sData$ = SQL_LongResultColumn(1,1,4)
                                    IF SQL_ErrorPending THEN
                                      SQL_MsgBox SQL_ErrorQuickAll + " Problem returning long data ", %MSGBOX_OK 
                                      'goto ejob
                                    end if  
                                    Do
                                      If IsTrue(SQL_ResultColumnMore(1,1,4)) then
                                        sData$ = sData$ + SQL_LongResultColumn(1,1,4)
                                        IF SQL_ErrorPending THEN                                                    
                                          incr numtimes&
                                          SQL_MsgBox SQL_ErrorQuickAll + " Problem returning MORE long data " + str$(numtimes&), %MSGBOX_OK 
                                          'goto ejob
                                        end if  
                                      Else  
                                        Exit do
                                      End if
                                    Loop
                                
                                    'Check for unexpected errors
                                    IF SQL_ErrorPending THEN
                                        SQL_MsgBox SQL_ErrorQuickAll, %MSGBOX_OK
                                    END IF
                                    
                                    SQL_MsgBox FORMAT$(LEN(sData)) + " characters read.", %MSGBOX_OK
                              When I used SQL_LONGVARCHAR I get one character back and then it failed

                              Comment


                              • #16
                                Don't have the full answer yet, but see my following post.

                                The retrieval part works ok - if the PDF file is inserted using another package, the above [your latest code] retrieves it. You have to make a couple of small changes, however:
                                • use "SQL_FetchResult" instead of "SQL_Fetch"
                                • move the two "SQL_SetOptionSInt" lines to the top of the code as these should be called before the database statement is opened

                                Comment


                                • #17
                                  Using José Roca's ADO library TB_ADO28.INC and example programs
                                  ADO_Ex_18.BAS to store the PDF AND
                                  ADO_Ex_06.BAS to retrieve it, it was much easier.

                                  Only took about an 2 hrs to make the changes to the code to do the job with my connection string and parameter binding.

                                  If someone would like to see it, I can post these sample programs.

                                  Bob Mechler

                                  Comment


                                  • #18
                                    Bind long data

                                    This works in Access 2000, demonstrating the sending of long (binary) data to an input parameter of a SQL statement using SQL Tools Pro. The comments in the code should provide sufficient explanation. The example uses the OP's SQL structure.

                                    Code:
                                    Function SetLongData() As Long
                                    ' Purpose:      test sending long data (i.e. binary) to an input parameter of a SQL statement
                                        Local lBufferLen As Long
                                        Local lDbsNum As Long
                                        Local lDisplaySize As Long
                                        Local lFile As Long
                                        Local lIndicator As Long
                                        Local lNeedLongDataLen As Long
                                        Local lParamNum As Long
                                        Local lRtn As Long
                                        Local lSteNum As Long
                                        Local lValue As Long
                                        Local sBuffer As String
                                        Local sMsg As String
                                        Local sSql As String
                                    
                                        ' load test file
                                        lFile = FreeFile
                                        Open "C:\PDFS\CULAND2.pdf" For Binary As # lFile
                                        Get$ # lFile, Lof(# lFile), sBuffer
                                        Close # lFile
                                    
                                        ' SQL Tools notes
                                        '   - %SUCCESS [0] or %SUCCESS_WITH_INFO [1] is optimal result of many SQL Tools operations.
                                        '     Use SQL_Okay to validate, which returns T if %SUCCESS or %SUCCESS_WITH_INFO, and F otherwise (an error occurred)
                                        '   - Error checking (i.e. using SQL_ErrorPending) is omitted here for clarity
                                    
                                        ' init SQL Tools
                                        lDbsNum = 1
                                        lSteNum = 1
                                        lNeedLongDataLen = IIf&(SQL_DatabaseInfoStr(lDbsNum, %DB_NEED_LONG_DATA_LEN) = "Y", %TRUE, %FALSE)
                                        ' .. required to determine what value to use for the Indicator: %SQL_LONG_DATA, or (0 - (DataLength + 100))
                                        ' .. Access 2000: N
                                        ' .. SQL Server: ?
                                        ' .. SQL Server Express: ?
                                    
                                        ' prepare SQL statement
                                        sSql = "INSERT INTO STMTPDFS (ACCOUNT, DATESTR, QUALIFIER, PDFDOC) VALUES('z1101500', '2009-07-17', 'UNAUDITED', ?)"
                                        lRtn = SQL_Okay(SQL_Statement(lDbsNum, lSteNum, %SQL_STMT_PREPARE, sSql))
                                        ' .. returns: T - successful; F - otherwise
                                    
                                        ' bind parameter
                                        lParamNum = 1
                                        ' .. used as parameter number, and pointer to buffer as binding long data
                                        lDisplaySize = Len(sBuffer)
                                        ' .. display size can be obtained by:
                                        '       Local lColNum As Long
                                        '       Local lTblNum As Long
                                        '       lColNum = 4                         ' as per SQL statement above for the PDFDOC column
                                        '       lTblNum = SQL_TableNumber(lDbsNum, "STMTPDFS", "TABLE")
                                        '       lDisplaySize = SQL_TableColumnInfoSInt(lDbsNum, lTblNum, lColNum, %TBLCOL_DISPLAY_SIZE)
                                        '   for a SQL_LONGVARCHAR or SQL_LONGVARBINARY column type in Access 2000, it returns 1073741823
                                        '   however if this value is used, an ODBC error occurs in SQL_BindParameter: HY10498 - Invalid precision value
                                        '   a value of 0 produces the same error, but the length of the buffer is accepted - in Access 2000
                                        '   *** this would have to be tested in SQL 2005 Express or any other database
                                        lBufferLen = Len(sBuffer)
                                        ' .. the buffer length can be the length of the data, or it can be a specific size in which case the
                                        '    length of the data passed to each SQL_LongParameter call should match
                                        lRtn = SQL_Okay(SQL_BindParameter( _
                                            lDbsNum, _                              ' database number
                                            lSteNum, _                              ' statement number
                                            lParamNum, _                            ' parameter number
                                            %SQL_PARAM_INPUT, _                     ' parameter type
                                            -2, _                                   ' BASIC data type - see note below
                                            %SQL_LONGVARBINARY, _                   ' SQL data type
                                            lDisplaySize, _                         ' display size
                                            0, _                                    ' number of digits - use 0 as binary data
                                            lParamNum, _                            ' pointer to buffer - use parameter number as long data
                                            lBufferLen, _                           ' buffer length
                                            lIndicator _                            ' indicator
                                        ))
                                        ' .. BASIC data type: this should be %BAS_STRING [1], but -2 [%SQL_BINARY] works for SQL Server 2000 and Access 2000
                                        '    - If %BAS_STRING, if the input has binary data (high ascii, including Chr$(0)), no error is reported,
                                        '      but the SQL statement does not succeed: no data tuple (row) is inserted.
                                        '      In addition, if the input is text only, it will succeed if the length is 7 chars or less, and if it fails,
                                        '      no error is reported. Have not found to-date a buffer size parameter which will increase this.
                                        '    - Note that this is for long data only with %SQL_LONGVARBINARY (%SQL_LONGVARCHAR works for text data).
                                        '    - By changing the BASIC data type to -2 (co-incidentally same value as %SQL_BINARY), the input works
                                        '      for long (binary) data, with no restriction on data length (not tested large values near 1GB yet).
                                        ' .. returns: T - successful; F - otherwise
                                    
                                        ' set indicator - after bind operation as can call repeatedly for different parameters or chunks of data
                                        lIndicator = IIf&(lNeedLongDataLen, -1 * (Len(sBuffer) + 100), %SQL_LONG_DATA)
                                    
                                        ' execute SQL statement
                                        lRtn = SQL_Statement(lDbsNum, lSteNum, %SQL_STMT_EXECUTE, "")
                                        ' .. should return %SQL_NEED_DATA [99]
                                        lRtn = IIf&(lRtn = %SQL_NEED_DATA, %TRUE, SQL_Okay(lRtn))
                                        ' .. returns: T - successful; F - otherwise
                                    
                                        ' ready to send long data to the parameter
                                        lRtn = SQL_NextParameter(lDbsNum, lSteNum)
                                        ' .. should return the number of the parameter (1 in this case)
                                    
                                        ' send the long data to the parameter. can call repeatedly for multiple chunks of data
                                        lRtn = SQL_Okay(SQL_LongParameter(lDbsNum, lSteNum, sBuffer, Len(sBuffer)))
                                        ' .. returns: T - successful; F - otherwise
                                    
                                        ' finished sending the long data to the parameter
                                        lRtn = SQL_NextParameter(lDbsNum, lSteNum)
                                        ' .. should return %SUCCESS as no more parameters (in this case), otherwise it should be
                                        '    the next parameter number and the code looped until completed
                                    
                                        ' operation complete
                                        
                                    'SetLongData_Exit:
                                    End Function

                                    Comment


                                    • #19
                                      Thanks Christopher for your help. I used your code and it definitely saved a binary string to a longvarbinary(max) field in sql 2005 but my retrieve code didn't work.

                                      Code:
                                      Getit:      
                                            msgbox("Starting retrieval")
                                            DIM sData            AS LOCAL STRING
                                            sData = "": sBlock = ""
                                            SQL = "SELECT ACCOUNT,DATESTR,QUALIFIER,PDFDOC FROM STMTPDFS WHERE ACCOUNT = '1101504'"
                                            lresult& = SQL_Statement(1, 1, %SQL_STMT_IMMEDIATE,SQL)     
                                            IF SQL_ErrorPending THEN
                                              SQL_MsgBox SQL_ErrorQuickAll + " Problem executing return sql stmt ", %MSGBOX_OK 
                                              'goto ejob
                                            end if  
                                            SQL_FetchResult 1,1, %NEXT_ROW 
                                            IF SQL_ErrorPending THEN
                                              SQL_MsgBox SQL_ErrorQuickAll + " Problem Fetching row ", %MSGBOX_OK 
                                              'goto ejob
                                            end if  
                                            sAccount$ = SQL_ResultColumnStr(1,1,1)
                                            sDatastr$ = SQL_ResultColumnStr(1,1,2)
                                            sQualifier$ = SQL_ResultColumnStr(1,1,3)
                                            IF SQL_ErrorPending THEN
                                              SQL_MsgBox SQL_ErrorQuickAll + " Problem getting column length", %MSGBOX_OK 
                                            end if
                                            MSGBOX(sAccount$ + $CRLF + _
                                            sDatastr$ + $CRLF + _
                                            sQualifier$) 
                                            IF SQL_ErrorPending THEN
                                              SQL_MsgBox SQL_ErrorQuickAll + " Problem returning normal fields ", %MSGBOX_OK 
                                              'goto ejob
                                            end if
                                            IF SQL_ErrorPending THEN
                                              SQL_MsgBox SQL_ErrorQuickAll + " Problem returning normal fields ", %MSGBOX_OK 
                                              'goto ejob
                                            end if            
                                            sData$ = SQL_LongResultColumn(1,1,4)
                                            msgbox("Len of ResultColumnText = " + str$(len(sData$)) + sData$)
                                            IF SQL_ErrorPending THEN
                                              SQL_MsgBox SQL_ErrorQuickAll + " Problem returning long data ", %MSGBOX_OK 
                                              'goto ejob
                                            end if  
                                            Do
                                              If IsTrue(SQL_ResultColumnMore(1,1,4)) then
                                                sData$ = sData$ + SQL_LongResultColumn(1,1,4)
                                                IF SQL_ErrorPending THEN                                                    
                                                  incr numtimes&
                                                  SQL_MsgBox SQL_ErrorQuickAll + " Problem returning MORE long data " + str$(numtimes&), %MSGBOX_OK 
                                                  'goto ejob
                                                end if  
                                              Else  
                                                Exit do
                                              End if
                                            Loop
                                        
                                            'Check for unexpected errors
                                            IF SQL_ErrorPending THEN
                                                SQL_MsgBox SQL_ErrorQuickAll, %MSGBOX_OK
                                            END IF
                                            
                                            SQL_MsgBox FORMAT$(LEN(sData)) + " characters read.", %MSGBOX_OK
                                      I changed your original code and the field to varchar(max) and made 2 small changes to your code and it placed longvarchar text to the field and I was able to retrieve it with the ADO retrieve program I wrote earlier today.

                                      Code:
                                      'Function SetLongData() As Long
                                      '' Purpose:      test sending long data (i.e. binary) to an input parameter of a SQL statement
                                      '    Local lBufferLen As Long
                                      '    Local lDbsNum As Long
                                      '    Local lDisplaySize As Long
                                      '    Local lFile As Long
                                      '    Local lIndicator As Long
                                      '    Local lNeedLongDataLen As Long
                                      '    Local lParamNum As Long
                                      '    Local lRtn As Long
                                      '    Local lSteNum As Long
                                      '    Local lValue As Long
                                      '    Local sBuffer As String
                                      '    Local sMsg As String
                                      '    Local sSql As String
                                      '
                                      '    ' load test file
                                      '    lFile = FreeFile
                                      '    Open "C:\PDFS\CULAND2.pdf" For Binary As # lFile
                                      '    Get$ # lFile, Lof(# lFile), sBuffer
                                      '    Close # lFile
                                      '
                                      '    ' SQL Tools notes
                                      '    '   - %SUCCESS [0] or %SUCCESS_WITH_INFO [1] is optimal result of many SQL Tools operations.
                                      '    '     Use SQL_Okay to validate, which returns T if %SUCCESS or %SUCCESS_WITH_INFO, and F otherwise (an error occurred)
                                      '    '   - Error checking (i.e. using SQL_ErrorPending) is omitted here for clarity
                                      '
                                      '    ' init SQL Tools
                                      '    lDbsNum = 1
                                      '    lSteNum = 1
                                      '    lNeedLongDataLen = IIf&(SQL_DatabaseInfoStr(lDbsNum, %DB_NEED_LONG_DATA_LEN) = "Y", %TRUE, %FALSE)
                                      '    ' .. required to determine what value to use for the Indicator: %SQL_LONG_DATA, or (0 - (DataLength + 100))
                                      '    ' .. Access 2000: N
                                      '    ' .. SQL Server: ?
                                      '    ' .. SQL Server Express: ?
                                      '
                                      '    ' prepare SQL statement
                                      '    sSql = "INSERT INTO STMTPDFS (ACCOUNT, DATESTR, QUALIFIER, PDFDOC) VALUES('1101504', '2009-07-17', 'UNAUDITED', ?)"
                                      '    lRtn = SQL_Okay(SQL_Statement(lDbsNum, lSteNum, %SQL_STMT_PREPARE, sSql))
                                      '    ' .. returns: T - successful; F - otherwise
                                      '
                                      '    ' bind parameter
                                      '    lParamNum = 1
                                      '    ' .. used as parameter number, and pointer to buffer as binding long data
                                      '    lDisplaySize = Len(sBuffer)
                                      '    ' .. display size can be obtained by:
                                      '    '       Local lColNum As Long
                                      '    '       Local lTblNum As Long
                                      '    '       lColNum = 4                         ' as per SQL statement above for the PDFDOC column
                                      '    '       lTblNum = SQL_TableNumber(lDbsNum, "STMTPDFS", "TABLE")
                                      '    '       lDisplaySize = SQL_TableColumnInfoSInt(lDbsNum, lTblNum, lColNum, %TBLCOL_DISPLAY_SIZE)
                                      '    '   for a SQL_LONGVARCHAR or SQL_LONGVARBINARY column type in Access 2000, it returns 1073741823
                                      '    '   however if this value is used, an ODBC error occurs in SQL_BindParameter: HY10498 - Invalid precision value
                                      '    '   a value of 0 produces the same error, but the length of the buffer is accepted - in Access 2000
                                      '    '   *** this would have to be tested in SQL 2005 Express or any other database
                                      '    lBufferLen = Len(sBuffer)
                                      '    ' .. the buffer length can be the length of the data, or it can be a specific size in which case the
                                      '    '    length of the data passed to each SQL_LongParameter call should match
                                      '    lRtn = SQL_Okay(SQL_BindParameter( _
                                      '        lDbsNum, _                              ' database number
                                      '        lSteNum, _                              ' statement number
                                      '        lParamNum, _                            ' parameter number
                                      '        %SQL_PARAM_INPUT, _                     ' parameter type
                                      '        %BAS_STRING, _                          ' BASIC data type - see note below
                                      '        %SQL_LONGVARCHAR, _                     ' SQL data type
                                      '        lDisplaySize, _                         ' display size
                                      '        0, _                                    ' number of digits - use 0 as binary data
                                      '        lParamNum, _                            ' pointer to buffer - use parameter number as long data
                                      '        lBufferLen, _                           ' buffer length
                                      '        lIndicator _                            ' indicator
                                      '    ))
                                      '    ' .. BASIC data type: this should be %BAS_STRING [1], but -2 [%SQL_BINARY] works for SQL Server 2000 and Access 2000
                                      '    '    - If %BAS_STRING, if the input has binary data (high ascii, including Chr$(0)), no error is reported,
                                      '    '      but the SQL statement does not succeed: no data tuple (row) is inserted.
                                      '    '      In addition, if the input is text only, it will succeed if the length is 7 chars or less, and if it fails,
                                      '    '      no error is reported. Have not found to-date a buffer size parameter which will increase this.
                                      '    '    - Note that this is for long data only with %SQL_LONGVARBINARY (%SQL_LONGVARCHAR works for text data).
                                      '    '    - By changing the BASIC data type to -2 (co-incidentally same value as %SQL_BINARY), the input works
                                      '    '      for long (binary) data, with no restriction on data length (not tested large values near 1GB yet).
                                      '    ' .. returns: T - successful; F - otherwise
                                      '
                                      '    ' set indicator - after bind operation as can call repeatedly for different parameters or chunks of data
                                      '    lIndicator = IIf&(lNeedLongDataLen, -1 * (Len(sBuffer) + 100), %SQL_LONG_DATA)
                                      '
                                      '    ' execute SQL statement
                                      '    lRtn = SQL_Statement(lDbsNum, lSteNum, %SQL_STMT_EXECUTE, "")
                                      '    ' .. should return %SQL_NEED_DATA [99]
                                      '    lRtn = IIf&(lRtn = %SQL_NEED_DATA, %TRUE, SQL_Okay(lRtn))
                                      '    ' .. returns: T - successful; F - otherwise
                                      '
                                      '    ' ready to send long data to the parameter
                                      '    lRtn = SQL_NextParameter(lDbsNum, lSteNum)
                                      '    ' .. should return the number of the parameter (1 in this case)
                                      '
                                      '    ' send the long data to the parameter. can call repeatedly for multiple chunks of data
                                      '    lRtn = SQL_Okay(SQL_LongParameter(lDbsNum, lSteNum, sBuffer, Len(sBuffer)))
                                      '    ' .. returns: T - successful; F - otherwise
                                      '
                                      '    ' finished sending the long data to the parameter
                                      '    lRtn = SQL_NextParameter(lDbsNum, lSteNum)
                                      '    ' .. should return %SUCCESS as no more parameters (in this case), otherwise it should be
                                      '    '    the next parameter number and the code looped until completed
                                      '
                                      '    ' operation complete
                                      '    
                                      ''SetLongData_Exit:
                                      'End Function
                                      Now if I can get the retrieve code working I'll have both and ado and an odbc solution. I'd prefer the odbc solution to get the pdf and save it.

                                      Quite a learning experience for me, thanks for your help.

                                      Bob Mechler

                                      Comment


                                      • #20
                                        Retrieiving PDF from LONGVARCHAR not working still.

                                        No luck so far on retrieving the VARLONGCHAR or VARLONGBINARY field into a variable to be stored as a PDF.

                                        When changing the samples from SQLTOOLS it works with Access but not SQL 2005 EXPRESS. To be fair, the example indicated it used the memo field in access to show the technique.

                                        I would settle for any ODBC code that retrieves a LONGVARCHAR field into a dynamic string. Doesn't need to be larger than 120,000 bytes using SQL 2005 Express.

                                        As I said before the ADO method works just fine so I know I'm storing a PDF properly when using LONGVARCHAR

                                        Maybe someone else can give a crack at it.

                                        Bob Mechler

                                        Comment

                                        Working...
                                        X