Announcement

Collapse
No announcement yet.

sql tools and blob fields

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

  • Christopher Carroll
    replied
    Thanks Eric for that information regarding SQL Express. The code I posted above works for Access, but I did not want to install SQL Express to test it.

    Glad you have it working Bob.

    Leave a comment:


  • BOB MECHLER
    replied
    I appreciate Eric's tenacity with the problem. I can now save and retrieve PDF's using varchar(max) in SQL Express 2005. (Learned a lot in the process too.)

    I'd also like to thank Christopher Carrol for his help, as well as Jose's responses on his forum when I was working on the same issue with ADO.

    Bob Mechler

    Leave a comment:


  • Eric Pearson
    replied
    Following up...

    This turned out to be a quirk in the SQL Express ODBC driver, which requires the un-binding of a long-data column before it can be read. Adding "SQL_UnbindCol 1" to Bob's program fixed the problem.

    (To be clear, SQL Express requires that long-data columns not be bound. SQL Tools automatically binds all of the columns of a result set, so the un-bind step must be added when using various versions of SQL Server, including SQL Express.)

    -- Eric Pearson, Perfect Sync, Inc.
    Last edited by Eric Pearson; 4 Aug 2009, 04:02 AM. Reason: clarification

    Leave a comment:


  • Michael Mattias
    replied
    I can use the ADO retrieve method just fine to retrieve it whether it was stored using the ADO store method or Christopher's ODBC code
    It's happening.

    Someone is picking the right tools for the job without prejudice!

    Oh, Be still my heart!

    MCM

    Leave a comment:


  • BOB MECHLER
    replied
    I'm using LONGVARCHAR to store the data. The field is VARCHAR(max) in the DB. I'll have to research the CAST operator. Not sure how to cast it as VARCHAR(max)?

    Chrisopher method (SetLongData) works as well as the ADO method.

    I can use the ADO retrieve method just fine to retrieve it whether it was stored using the ADO store method or Christopher's ODBC code.



    Bob Mechler

    Leave a comment:


  • Michael Mattias
    replied
    You can try casting the column as, say VARCHAR or VARCHAR2 (must be supported type on DBMS).... e.g.,
    Code:
    Select   CAST (pdf_data AS VARCHAR2)  AS The_Data From TableName where....
    Depending on the engine this essentially gets the DBMS to do the grunt work.

    MCM

    Leave a comment:


  • BOB MECHLER
    replied
    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

    Leave a comment:


  • BOB MECHLER
    replied
    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

    Leave a comment:


  • Christopher Carroll
    replied
    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

    Leave a comment:


  • BOB MECHLER
    replied
    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

    Leave a comment:


  • Christopher Carroll
    replied
    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

    Leave a comment:


  • BOB MECHLER
    replied
    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

    Leave a comment:


  • Michael Mattias
    replied
    > ... '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

    Leave a comment:


  • Christopher Carroll
    replied
    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.
    Last edited by Christopher Carroll; 19 Jul 2009, 03:18 PM. Reason: SQL_BindParameter

    Leave a comment:


  • BOB MECHLER
    replied
    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

    Leave a comment:


  • Christopher Carroll
    replied
    >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

    Leave a comment:


  • Knuth Konrad
    replied
    You could try (n)text or (n)varchar(MAX) as the column's data type.

    Leave a comment:


  • BOB MECHLER
    replied
    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

    Leave a comment:


  • Michael Mattias
    replied
    >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
    Last edited by Michael Mattias; 18 Jul 2009, 06:19 PM.

    Leave a comment:


  • BOB MECHLER
    replied
    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

    Leave a comment:

Working...
X