Menu

1/3: Upload file to shared folder and save link in database

+3 votes

Many times when working with databases you need to store files that are related to the information in the database. I know that we can store a file in the database, but that takes up a lot of storage in the DB, so a better way is to save the file on a file share and just store the link to the file in the DB.
I can of course first save it, then copy the path to the file and then insert it in a field in the database, but it would be mush easier to do it all at the same time using dbfront, especially for a less advanced user.

I think you could do this as a Subtype to a text field. So if we chose that Subtype, we would be able to configure the location. And the user would have a button beside the text box for uploading the file. In read only mode the user would only have a link to the file.

Regards,
Ulf Tallmyr

in Features (Todo) by (600 points)
edited by
Interesting idea.  Could you add a bit more detail by editing your answer to explain: 1) why dbFront should manage this, 2) how you would want it to be configured and work within dbFront?
Which database is this request for?
In my case it's for MSSQL, but I think that it would by useful for all.
There are three answers below for each of the three supported databases.   In your case I need to do nothing more because MSSQL already properly supports what you need.   Enjoy.   Excellent write-up by the way.

3 Answers

+1 vote

DONE: Microsoft SQL Server already implements this functionality at the database level.

For Microsoft SQL Server the solution would be to enable and use FILESTREAM.

There are two specific options:

  1. The first option is to use FILESTREAM backed varbinary columns.
    FILESTREAM backed columns are completely transparent to dbFront and require no special handling.
  2. The second option is to create a FILETABLE which uses FILESTREAM. A FILETABLE not only stores the files in the file system but automatically reflects file structure changes back into the table.

For detailed instructions on implementing FILESTREAM please see:

Based on your request a FILETABLE is exactly what you need. If FILESTREAM is enabled then you can use the following to create a FILETABLE.

-- Create File Group
ALTER DATABASE AdventureWorksLT
ADD FILEGROUP FileStreamFileGroup CONTAINS FILESTREAM
GO

-- Create FILESTREAM File and add to FILEGROUP
ALTER DATABASE AdventureWorksLT ADD FILE (
	NAME = 'AdventureWorksLT_Filestream', 
	FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\AdventureWorksLT_Filestream'
)
TO FILEGROUP FileStreamFileGroup;
GO

-- Create a FILETABLE named DocumentStore
CREATE TABLE DocumentStore AS FileTable WITH (   
	FileTable_Directory = 'DocumentTable',  
	FileTable_Collate_Filename = database_default  
);  
GO 

-- Create a VIEW to get the Files Path
CREATE VIEW vDocumentStore_FilePath as (
    SELECT 
		ds.stream_id, 
		FileTableRootPath() as rootpath, 
		ds.file_stream.GetFileNamespacePath() as filepath, 
		FileTableRootPath() + ds.file_stream.GetFileNamespacePath() AS fullpath
	FROM DocumentStore ds
);
GO

The newly created view [vDocumentStore_FilePath] can be joined to the new [DocumentStore] table within dbFront to expose the network accessible path.

by (44.5k points)
edited by

This is an interesting answer and it begs the question - Why not just use FILESTREAM on SQL Server to store the file? Well, if it's in a format that dbFront can open and display such as .pdf or .jpg then that's fine but if it's in a non-dbFront supported file type, then I think that this is a useful enhancement. This may be a way to get around the licensing problem of support for opening MS Office files in dbFront that are stored as FILESTREAM. If you can find a way of opening the document (outside of dbFront on the user's PC, just as if they'd clicked on it in Explorer) referenced in the stored path with its associated MS Office application then I think it's worth considering.

Regards
Colin

Very interesting comment @ColinGraham.  I will keep that in mind.
0 votes

Oracle has a partial implementation but it is not fully functional.

For more details see:

NOTE: Please vote on this Feature Request and Answer to let me know you want better support for Oracle.

by (44.5k points)
0 votes

MySQL has no database level support for handling files.

NOTE: Please vote on this Feature Request and this Answer to let me know you want support for MySQL.

by (44.5k points)
Welcome to the dbFront Q&A site, where you can ask questions and receive answers from other members of the community.
 | Minimalist Answer Theme by Digitizor Media
 |
Powered by Question2Answer
...