Inline select statement in powerbuilder cutting off blob variable. 1

[bugmenot2]

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

 

[bugmenot2]

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