SQL Server FileStream --> Clarion datatype for VARBINARY(MAX)?

1

Anyone experienced with combination Clarion - SQL Server FILESTREAM

Currently when I have to insert PDF,WORD documents in database , two tables are involved

CREATE TABLE [dbo].[PD_FL](
	[SysID] [int] NOT NULL,
	[GUID] [char](16) NOT NULL,
	[UseGUIDFilename] [tinyint] NOT NULL,
	[StorageMethod] [char](4) NOT NULL,
	[StorageMedium] [char](8) NOT NULL,
	[BlobFileName] [char](255) NULL,
	[OriginalFileNameAndPath] [varchar](511) NOT NULL,
	[StoredFileNameAndPath] [varchar](511) NULL,
	[FileName] [char](255) NOT NULL,
	[FileExtension] [char](20) NULL,
	[FileDate] [int] NULL,
	[FileTime] [int] NULL,
	[FileSizeBytes] [int] NULL,
	[FileSizeKB] [int] NULL,
	[FileDescription] [char](255) NULL,
	[FileType] [char](20) NOT NULL,
	[FileCategory] [char](20) NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[PD_DB](
	[BlobData] [image] NULL,
	[GUID] [char](16) NOT NULL,
 CONSTRAINT [KeyByGUID] PRIMARY KEY CLUSTERED 
(
	[GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

In table [PD_DB] , Clarion field [BlobData] is defined as BLOB,BiNARY .

Everything works fine.
I am trying to implement FILESTREAM on SQL Server .
Filestream says that you need to add another field [FileStreamDataGUID] , and “big data field” has to be VARBINARY(MAX)

Example script for sql table creation :

CREATE TABLE [FileStreamDataStorage]
(
[GUID] [char](16) NOT NULL,
[FileStreamData] VARBINARY(MAX) FILESTREAM NULL,
[FileStreamDataGUID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID()
)
ON [PRIMARY]
FILESTREAM_ON FileStreamGroup
GO

Question is :
What is Clarion datatype for VARBINARY(MAX) , maybe same as previous (BLOB,BiNARY) .
Also SQL Server data type UNIQUEIDENTIFIER ROWGUIDCOL, Clarion equivalent ?

Thanks

2

Yes, I have done work with File Stream in SQL:
In the dictionary:

FileStreamDataGUID    CSTRING(37),NAME('FILESTREAMDATAGUID | READONLY')

Do not allow the Clarion to add the column to the table. Do that via SQL

FileStreamDataGUID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID()

The file stream column in the dictionary:

FileStreamData BLOB,BINARY

In SQL:

FileStreamData VARBINARY(MAX)
2 Likes
3

Still errors .
From document management there is an error:

image
image1266Ă—318 65.8 KB

Tried also from another form . Created form with two fields ( GUID, FILENAME) . That form gave me an error
image

SQL server table as in previous.
Clarion table:

image
image908Ă—351 71.3 KB

Any suggestion?

4

Maybe try remove the Binary from the Blob field in the Clarion Dictionary, recompile and try again? Might just work?

And in my MSSQL environment I use varchar(MAX) for BLOB Fields in Clarion

5

I’ve already tried. SQL Server messsage:
FILESTREAM can only be declared for VARBINARY columns.

6

Have you enabled file stream on your SQL Server?

There are two steps for this.
First, you must enable filestream on the SQL Server instance in the SQL Configuration Management Tool. You must enabled for transact SQL.

image
image935Ă—742 112 KB

Second, you have to configure file stream access level. Open Mgmt Studio and a query window on the master database and run:

sys.sp_configure 'filestream access LEVEL', '1'
GO
RECONFIGURE WITH OVERRIDE
GO

You can read more details here.

7

Also, when you get a file system error (90), you really need to get the underlying error message with FileError() and FileErrorCode(). That will let you know what error was generated on the backend.

1 Like
8

I use below Err4Msg() function to easily get all the error functions info in a Message(), or log file or debug passing (1).

You don’t need to explicitly check for ErroCode()=90, there are other error codes that also set File Error info like 47. Note that FileErrorCode() is a STRING and can contain alpha so check for Blank or “~” Not.

  MAP
Err4Msg  PROCEDURE(Byte NoCRLF=0),STRING  !Fromat ErrorCode() & Error() & FileError... for Message() Stop() Halt() or Log file (NoCRLF)
  END
!-----------------------------------------
Err4Msg  PROCEDURE(Byte NoCRLF=0)!,STRING 
  !Example: IF ERRORCODE() THEN STOP('Failed ADD(xxx)' & Err4Msg()).
  !Note: Return starts '<13,10><13,10>Error Code:' so no need to put in the Message()
  CODE
  IF ~ERRORCODE() THEN RETURN ''.   
  IF ~NoCRLF THEN 
     RETURN '<13,10><13,10>Error Code: ' & ERRORCODE()&' '&ERROR() & |
             CHOOSE(~FILEERRORCODE(),'','<13,10>Driver Error: ' & FILEERRORCODE()&' '&FILEERROR() ) & | 
             CHOOSE(~ERRORFILE(),'','<13,10>File Name: ' & ERRORFILE() )
  END 
  !NoCRLF<>0 is 1 line format for use by logging
  RETURN ERRORCODE()&' '&ERROR() & |     ! {148}
         CHOOSE(~FILEERRORCODE(),'',' [Driver ' & FILEERRORCODE()&' '&FILEERROR() &']' ) & | 
         CHOOSE(~ERRORFILE(),'',' {{' & ERRORFILE() & '}' )

Examples:

   PUT(Vendor)
   IF ERRORCODE() THEN 
      MESSAGE('Failed on PUT(Vendor) '  & Err4Msg(),'Error')
   END 
!Result Message
!     Failed on PUT(Vendor)
!
!     Error Code: ERRORCODE()  ERROR()
!     Driver Error: FILEERRORCODE() FILEERROR()             Shows only if <>''
!     File Name: ERRORFILE()                                Shows only if <>''


   !Pass (1) for no 13,10 in text
   !This is NOT prefixed by 'Error:' so you must add it
   CLOSE(Vendor)
   IF ERRORCODE() THEN DebugOut('CLOSE(Vendor) Error: ' & Err4Msg(1)). 
!--------------------------------------------------------------------
1 Like
9

After Carl mentioned all error formats, I have updated error message, but still errors.

image

Fileerror() : Invalid character value to cast specification
fileerrorcode() : 22005
errorcode() : 90
ERRORFILE() : PD_DB
ERROR() :  File system error

Rick mentioned FILESTREAM
Outside of Clarion , in SQL management studio I inserted data into PD_DB table

DECLARE @File AS VARBINARY(MAX)
 
SELECT @File = CAST(BulkColumn AS VARBINARY(MAX))
         FROM OPENROWSET(BULK 'C:\upload\X1.TXT', SINGLE_BLOB ) AS fs_FILE;
 
INSERT INTO dbo.[PD_DB]
(GUId, [BlobData],[FileStreamDataGUID] )
 
SELECT 'QQWWEERRTTZZUUUU',  @File ,newid()

image
image1372Ă—242 58.8 KB

I can’t believe that three fields can make so many problems.

10

Ive been storing blobs like this for years - blob table, blob types (ie: type=invoice, type=photo, whatever).

I’ve never felt the need to deal with filestream. I typically use binary blobs, but when I dont, I use text and then b64 encode the data when stored. All of this, collectively, saves a lot of hassle, etc.

Is filestream a requirement of your process?

11

I guess. The predominant version of SQL Server is the Express edition. Due to the edition limit, I don’t want to run into problems when saving in BLOB large documents within our document management application. Saving to external files leads to potential data consistency problems (deleting external files).

12

I just use stringTheory or SVs CWUTILS fileToBlob functions.

All works well

13

The limitation was per database last time I looked. If size is an issue, put the blobs in a separate DB, compressed if need be. Or move to a free SQL.