Does Maintain work with BLOB data fields in SQL server? I very much look forward to anyone named Mark answering this question.

J.

I qualify.

I am afraid that there is not BLOB support in Maintain. We don't even like TX fields. What we can do is support a very large ALPHA (A2000 or so) in an MFD so maybe you could translate the field from the BLOB into a large alpha before trying to manipulate it.

Mark Derwin

Want BLOB insert, SQL stored procedure.

How you call that from Maintain I have no idea.

JG,

You could EXEC a .fex from maintain, and run the stored procedure from it, but you can't pass the BLOB between these procedures, through you could pass a location.

I think there is a way to run SQL directly from Maintain, but the syntax escapes me at the moment...

If you can call a focexec from maintain then you can load and read your images using
two stored procedures.

This is based on a simple table having two columns
(use varbinary(max)for the blob as the image dataype is depreciated in the latest versions of MSSQl)

TABLE [dbo].[BLOBS](
[ID] [int] NOT NULL,
[BLOB] [varbinary](max) NULL

Stored procedure to load blobs, it requires that you have the correct level of
authorization to the tempdb database as well as the database where the blobs are to go.

SQL SQLMSS
CREATE PROC dbo.LoadBlob
@ID int,
@FileName varchar(255)
AS
DECLARE @SqlStatement nvarchar(MAX)
SET @SqlStatement = 'INSERT INTO tempdb.dbo.BlobData(BlobData)
SELECT *
FROM OPENROWSET (BULK ''' + @FileName + ''', SINGLE_BLOB) as f;'
EXEC sp_executesql @SqlStatement

UPDATE dbo.BLOBS
SET BLOB = (SELECT BlobData FROM tempdb.dbo.BlobData)
WHERE dbo.BLOBS.ID= @ID
END

To actually load a blob call the stored procedure as follows

-* set the key and the blob source location as variables
-* the blob can be on the MSSQL server or on a drive mapped to the server
-*
-SET &ID=44;
-SET &FILENAME='c:\ibi\blob7.gif';
-*
-* the row that you are inserting the blob into must exist as it is an update not an insert
-* skip the insert step if your row already exists
-*
SQL SQLMSS
INSERT INTO [dbo].[BLOBS]
VALUES(&ID.EVAL,0x0)
END
-*
-* load the blob
-*
SQL SQLMSS
CREATE TABLE tempdb.dbo.BlobData(BlobData varbinary(max))

DECLARE @return_value int
EXEC @return_value = [dbo].[LoadBlob]
@ID = &ID.EVAL,
@FileName = N'&FILENAME.EVAL'
SELECT 'Return Value' = @return_value

DROP TABLE tempdb.dbo.BlobData
END

Stored procedure to read blobs, It requires that xp_cmdshell is enabled in the
surface area configuration on your MSSQL server
(this may not be the case dependant on release and version)

substitute your servername\instance as required

-* Because of the way bcp processes variable length data a format file is required
-* to tell it to ignore the 1st 8 bytes of the varchar(max) column
-* this file must be on the MSSQL server it's should look like the following
-* three lines the 9.0 refers to the version, BLOB is the name of the column
-* containing the blob (i have called the file c:\ibi\bcp.fmt)

 
9.0
1
1       SQLBINARY     0       0       ""   1     BLOB         ""

SQL SQLMSS
CREATE PROC dbo.GetBlob
@ID int,
@filename VARCHAR(300)
AS
DECLARE @cmd VARCHAR(500)
SELECT @filename
SET @cmd = 'bcp "SELECT BLOB FROM master.dbo.BLOBS WHERE ID='+ CAST(@ID AS VARCHAR)+'" queryout '+@filename+' -T -f c:\ibi\bcp.fmt -S '
EXEC master..xp_cmdshell @cmd;
END


To actually read a blob call the stored procedure as follows

-* set the key and the blob destination location as variables
-* the blob can be created on the MSSQL server or on a drive mapped to the server
-*
-SET &ID=44;
-SET &FILENAME='c:\ibi\blob44.gif';
-*
SQL SQLMSS
DECLARE @return_value int
EXEC @return_value = [dbo].[GetBlob]
@ID = &ID.EVAL,
@filename = N'&FILENAME.EVAL'
SELECT 'Return Value' = @return_value
END


Happy BLOBING.