none
Does a particular version of ADO.NET cut off variables to a certain length? RRS feed

  • Question

  • Hello,

    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[but 32768 - another issue itself].

    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[and multiple other variations] but cannot find anything.

    I'm dying here. Anyone who can help me out would be great. Thanks
    Wednesday, February 27, 2008 2:58 PM

Answers

  • 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
        IF isIniDB = DBMS.SQLSERVER THEN
            lbAutoCommit = SQLCA.AutoCommit
            SQLCA.AutoCommit = TRUE   
           
            SELECT datalength(pic)
            INTO :llLength
            FROM table
            WHERE condition
            USING SQLCA;       
       
            //set the text limit - ADO.NET limits text size to 32000 bytes
            lsSQL = "SET TEXTSIZE " + String(llLength + 10)
            EXECUTE IMMEDIATE :lsSQL USING SQLCA;   
        END IF
       
        SelectBlob pic
        INTO :lbPicBlob
        FROM table
        WHERE condition;
       
        // set the textsize limit back to 32000
        IF isIniDB = DBMS.SQLSERVER THEN
            //reset the textsize
            SQLCA.AutoCommit = lbAutoCommit
            lsSQL = "SET TEXTSIZE 32000"
            EXECUTE IMMEDIATE :lsSQL USING SQLCA;
        END IF
    Monday, March 3, 2008 7:10 PM

All replies

  • It might help if you could post your code since we can't really tell which data access provider you are using or how you are retrieving the data. The method by which BLOB data is stored and retrieved will vary amongst database systems.

     

    Thursday, February 28, 2008 6:34 PM
  • The problem could be related to how it is defined instead of image create the SQL Server column as varbinary(max) and use the Sybase ADO.NET provider type which is also varbinary.  And most image related operation in .NET uses memory stream.  Check the links below for both SQL Server, .NET and Sybase ADO.NET type docs.

     

    http://msdn2.microsoft.com/en-us/library/ms131092.aspx

     

    http://manuals.sybase.com/onlinebooks/group-adonet/asnetg0100e/adonet/@ebt-link;pt=4658?target=%25N_9799_START_RESTART_N%25

     

    Thursday, February 28, 2008 6:36 PM
  • Hey,

    The code is a simple inline select statement which looks like this...

    ===PowerBuilder v10 Build 9914===

    The picture is stored in the table as an image, and the code looks as follows.

    SELECTBLOB pic
    INTO: lbPicBlob
    FROM etc...

    This code works with Sybase DB's. It works with SQL Server 2000 with an ODBC interface. However, it does not work with SQL Server 2000 and an ADO.NET interface.

    I cannot find anything out there relating to ADO.NET limiting image/text/ntext to 32000 bytes.
    Thursday, February 28, 2008 8:47 PM
  • How do you read data in your code? In some cases you would need to read BLOB data in chunks, depending on how you actually access it from ADO.NET. You might get only first chunk of the data only and do not read rest of the BLOB. Here are samples

     

    http://support.microsoft.com/kb/317034/en-us

    Friday, February 29, 2008 10:55 AM
    Moderator
  • Yes, that would be awesome, but as far as i know there is know way to read in database data from through powerbuilder in chunks, unless anyone else knows of this capability?
    Friday, February 29, 2008 4:06 PM
  • How are you using ADO.NET with PowerBuilder?

    Friday, February 29, 2008 10:10 PM
  • 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
        IF isIniDB = DBMS.SQLSERVER THEN
            lbAutoCommit = SQLCA.AutoCommit
            SQLCA.AutoCommit = TRUE   
           
            SELECT datalength(pic)
            INTO :llLength
            FROM table
            WHERE condition
            USING SQLCA;       
       
            //set the text limit - ADO.NET limits text size to 32000 bytes
            lsSQL = "SET TEXTSIZE " + String(llLength + 10)
            EXECUTE IMMEDIATE :lsSQL USING SQLCA;   
        END IF
       
        SelectBlob pic
        INTO :lbPicBlob
        FROM table
        WHERE condition;
       
        // set the textsize limit back to 32000
        IF isIniDB = DBMS.SQLSERVER THEN
            //reset the textsize
            SQLCA.AutoCommit = lbAutoCommit
            lsSQL = "SET TEXTSIZE 32000"
            EXECUTE IMMEDIATE :lsSQL USING SQLCA;
        END IF
    Monday, March 3, 2008 7:10 PM