- Reference: Files can be stored in a directory or via network sharing through their references. Example: If the MSDN.PNG file is in the Z:\Images\ directory, the stored reference is Z:\Images\MSDN.PNG.
- File: Files can be stored directly on the database table.
In this article, we’ll demonstrate how to save a file directly in an SQL Server database by using a varbinary(max) field on the table. This field supports binary data up to 2GB.
In SQL Server, there are two types of binary large objects (BLOBs): varbinary(max) default, which stored data in tables, and varbinary(max) FILESTREAM, which store data in the files system. In this example, we’ll use varbinary(max) default.
1. Creating a file storage table
To create a table in SQL Server Management Studio, use the following syntax:
(
Id int,
Nome varchar(50) not null,
Arquivo varbinary(max) not null
)
2. Adding data to the table
After creating the table, use OpenRowSet command alongside Bulk option to insert the desired file:
Select 1, ‘file1.pdf’, BulkColumn
FROM Openrowset( Bulk ‘C:\Relatorio1.pdf’, Single_Blob) as Arquivo
3. Integrating file read/record from Elipse E3
First of all, you’ll need to create a Database in Elipse E3, and only then write and read data via E3Query and scripts.
To add the file to the database, we’ll use this SQL clause in a query:
To send the file, we’ll execute the query with the variables to be replaced. The following script can be used:
VarPath = File
VarFileName = InputBox(“File Name (Ex: file1.pdf, file1.doc, picture.png…)”)
Set Consulta = Screen.Item(“Insert”)
Consulta.SetVariableValue “FileName”, VarFileName
Consulta.SetVariableValue “Path”, VarPath
Screen.Item(“Insert”).Execute(true)
End If
To read the file system’s binary data saved in the database, we’ll use ADODB.Stream component, which can read data for further storage in local disk. The following script and SQL query were used:
Const adSaveCreateOverWrite = 2
arquivo = InputBox(“File Name:”)
‘Consulta que busca o valor binário do banco
Screen.Item(“Consulta1”).SetVariableValue “NomeVar”, arquivo
set rs = Screen.Item(“Consulta1”).GetADORecordset()
bin = rs.Fields.Item(“Arquivo”).Value
Set outputStream = CreateObject(“ADODB.Stream”)
outputStream.Type = 1 ‘ adTypeBinary
outputStream.Open
outputStream.Write bin
outputStream.SaveToFile “C:\” & arquivo, adSaveCreateOverWrite
For further information, run the attached demo application, developed in Elipse E3 version 4.7 build 300 alongside SQL Server 2008 R2.
NOTE: To make sure this example works properly, you must have an SQL Server database installed in the local computer (or another one on the same network that will grant it access). Next, set up the application’s Database object (Database1) and then create the table to store the files.
Attachments:
