Anticlue

BLOBS and CF

A question popped up on Macromedia's forums regarding row size in MS SQL server. There is a max of 8072, which was hit with a nvarchar of 4000.

To solve this problem, the user was going to use an nText datatype and limit the size to 4000 using JS.

My thoughts are if the users of the system are writing more the 4000 bytes of info, why not use it? The text and ntext data types can store and retrieve fields up to 2GB. The CF Administrator blob default byte setting is 65000. (With MX make sure the Blob data type is check in the DSN settings) All that is stored in the SQL server Blob column is a 16-byte pointer to the first page of the column's page chain.

The best way to handle the insert is with the WRITETEXT command.

WRITETEXT takes three arguments: the table.column to be updated, the pointer that points to the data to be updated, and the data to be updated.

So to handle this from CF.
<cfstoredproc procedure="spInsertRow" datasource="#Application.dbConnection#">
<cfprocparam Type="IN" cfsqltype="CF_SQL_BLOB" value="#PreserveSingleQuotes(FORM.txtArea)#" dbvarname="@blobValue">
</cfstoredproc>

And with the SQL db create a stored procedure.
Create Procedure spInsertRow
@blobValue Text
As
DECLARE
@textptr binary(16)

BEGIN TRAN
SELECT @textptr=TEXTPTR(BlobFieldinTable) FROM Table (UPDLOCK)

WRITETEXT #table.blobfield @textptr @blobValue

COMMIT TRAN
GO

For retrieving the data,
DECLARE @txtptr binary(16), @blobLength

BEGIN TRAN
SELECT @txtptr=TEXTPTR(blobColumn),
@blobLength=DATALENGTH(blobColumn)
FROM Table (HOLDLOCK) WHERE ID=1

READTEXT table.blobColumn @txtptr 0 @blobLength
COMMIT TRAN

Another userful item to mention is that the LIKE clause doesn't work with TEXT. Instead you have to use the PATINDEX().

WHERE PATINDEX('%NAME%', blobColumn) <> 0

So I guess the overall assessment is that one can handle the blob functionality in the db. The UI just needs to be careful on how it handles the amount of memory a BLOB can consume.