Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006

    Unanswered: Odd Problem. BLOB's being cut off somewhere.


    I currently have a problem with blobs being cut off.

    From powerbuilder, I am trying to pull in an image that is stored as a blob. This has always worked fine in our software, until a more recent version, and is now presenting me with this problem. Here are the details...

    When the software is installed with a Sybase Database, everything is working great. It pulls in the full size, and there are no problems here.

    However, when the software is installed with a SQL Server DB, problems arise.

    The main problem: When using a ADO.NET DBMS interface to the SQL Server DB, the select statement is only pulling in 32000 bytes.

    Secondary problem: This one may present a problem in the future, if and when i fix the first problem. To narrow it down to see if it was the ADO.NET interface giving me the problem, i connected to the same table on the same server, but using an ODBC interface as opposed to ADO.NET. This gave me the first 32768 bytes.

    So a) ADO.NET when interfacing with SQL Server is only giving me the first 32000 bytes in my selectblob statement. I have narrowed it down to ADO.NET, as the code works fine with Sybase, and ODBC interfacing with SQL Server does not limit it at 32000 bytes.

    and if I get a solution to that, something may then be limiting the blob read in size at 32768. Maybe, maybe not... but it is happening with ODBC|SQL Server.

    Does anyone have any ideas. This is driving me wild. I have pounded google searching for ADO.NET known blob limitations but cannot find anything.

    I'm dying here. Anyone who can help me out would be great. Thanks

  2. #2
    Join Date
    Nov 2004
    on the wrong server
    Provided Answers: 6
    yeah. I would never store files in a database. I store the locations of the files in a database, but not the files.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2006

    Issue Solved

    I have solved my issue, and am replying to myself, because I hate when I find a problem I'm looking for on some message board from 2004, and the guy replies with "I solved my issue. Never mind".

    So, originally the problem was with an ADO.NET DBMS interface, interfacing with a SQL Server Database. Solutions on the web suggested using GetChunk and AppendChunk for VB, but PowerBuilder has nothing like that. The solution involved two things. One is setting autocommit to true on the transaction object, and the other is setting the TEXTSIZE in an inline statement before the retrieval. (This is needed for text, ntext, and image DB types. It is not blob specific.)

    Here is the code

    //Set the textsize limit to be greater than the picture being retrieved
    lbAutoCommit = SQLCA.AutoCommit
    SQLCA.AutoCommit = TRUE

    SELECT datalength(pic)
    INTO :llLength
    FROM table
    WHERE condition

    //set the text limit - ADO.NET limits text size to 32000 bytes
    lsSQL = "SET TEXTSIZE " + String(llLength + 10)
    END IF

    SelectBlob pic
    INTO :lbPicBlob
    FROM table
    WHERE condition;

    // set the textsize limit back to 32000
    //reset the textsize
    SQLCA.AutoCommit = lbAutoCommit
    lsSQL = "SET TEXTSIZE 32000"
    END IF

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts