Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

New TIBCO Community Coming Soon
In early summer, TIBCO plans to launch a new community—with a new user experience, enhanced search, and expanded capabilities for member engagement with answers and discussions! In advance of that, the current myibi community will be retired on April 30. We will continue to provide updates here on both the retirement of myibi and the new community launch.

What You Need to Know about Our New Community
We value the wealth of knowledge and engagement shared by community members and hope the new community will continue cultivating networking, knowledge sharing, and discussion.

During the transition period, from April 20th until the new community is launched this summer, myibi users should access the TIBCO WebFOCUS page to engage.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     WF Maintain -- Support for BLOB data?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
WF Maintain -- Support for BLOB data?
 Login/Join
 
Virtuoso
posted
Does Maintain work with BLOB data fields in SQL server? I very much look forward to anyone named Mark answering this question.

J.



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
Master
posted Hide Post
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
 
Posts: 663 | Location: New York | Registered: May 08, 2003Report This Post
<JG>
posted
Want BLOB insert, SQL stored procedure.

How you call that from Maintain I have no idea.
 
Report This Post
Platinum Member
posted Hide Post
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...


Regards,
Dave

http://www.daveayers.com

WebFocus/Maintain 7.6.4-8
on Win2000 and 2003 Server
 
Posts: 165 | Location: Detroit Metro | Registered: September 17, 2003Report This Post
<JG>
posted
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.
 
Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     WF Maintain -- Support for BLOB data?

Copyright © 1996-2020 Information Builders