Is SQL Server or ADO.NET cutting off my variable length in inline sel?

[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

 

[genomon]

From SQL BOL (maybe item 3 will help?):

BLOBs and OLE Objects
SQLOLEDB exposes the ISequentialStream interface to support consumer access to Microsoft® SQL Server™ 2000 ntext, text, and image data types as binary large objects (BLOBs). The Read method on ISequentialStream allows the consumer to retrieve large amounts of data in manageable chunks.

SQLOLEDB can use a consumer-implemented IStorage interface when the consumer provides the interface pointer in an accessor bound for data modification.

SQLOLEDB Storage Object Limitations
SQLOLEDB can support only a single open storage object. Attempts to open more than one storage object (attempts to get a reference on more than one ISequentialStream interface pointer) return DBSTATUS_E_CANTCREATE.


In SQLOLEDB, the default value of the DBPROP_BLOCKINGSTORAGEOBJECTS read-only property is VARIANT_TRUE. This indicates that if a storage object is active, some methods (other than those on the storage objects) will fail with E_UNEXPECTED.


The length of data presented by a consumer-implemented storage object must be made known to SQLOLEDB when the row accessor that references the storage object is created. The consumer must bind a length indicator in the DBBINDING structure used for accessor creation.


SQLOLEDB supports the ISequentialStream::Write method for zero-length strings and NULL values only. Attempts to write more than zero bytes through ISequentialStream::Write fail.


If a row contains more than a single large data value, and DBPROP_ACCESSORDER is not DBPROPVAL_AO_RANDOM, the consumer must either use a SQLOLEDB cursor-supported rowset to retrieve row data or process all large data values prior to retrieving other row values. If DBPROP_ACCESSORDER is DBPROPVAL_AO_RANDOM, SQLOLEDB caches all the BLOB data so it can be accessed in any order.

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[bugmenot2]

Hi. Thanks for the reply, but that is all OLE DB related. Do you know of any information that pertains to ADO.NET?

 

[genomon]

Do a BOL search on BLOB, then choose the subject "Managing Long Data Types". Too lengthy to post back here, but maybe helpful as it has ADO code samples with GetChunk & AppendChunk. Sorry 'bout the mixup!

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[bugmenot2]

The GetChunk and AppendChunk looked promising for the first few second, and then it took me down another step when i realized it was in VB.

Unfortunately, there is no way in PowerBuilder to read in data in chunks from a DB into a blob. I know there is from files, but that's it.

Something is limiting these blobs to 32000 bytes in ADO.NET and I can't find the answer for the life me.

 

[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

 

[SQLSister]

HAve a star for coming back and posting the solution.

"NOTHING is more important in a database than integrity." ESquared