SQL Server's strategies and methods for storing image data

server| Strategy | Data at present, the management of image data is mostly based on the method of Table + entity, that is, the image data is stored in the specified computer directory in the file form, which only reflects the storage path of the image data file in the database table. This kind of management mode, to the data maintenance increase difficulty, at the same time, also to the data security brings certain hidden trouble. Therefore, to really do all kinds of data in the database security management, research and explore the direct image data stored in the Database relational table is very necessary.

In the Visual Basic 6.0 development environment, the author makes a preliminary discussion on the problem of storing image data in the SQL Server database relational table, and puts forward a set of basic solutions for readers ' reference.

First, the strategy of storing image data

Image database technology has been working to solve the problem of efficient storage and management of massive digital images. It is the inheritance and development of the database technology, on the one hand, the image data and the text data have the essential difference, the traditional database technology which can be used successfully in the Text data field, if the invariable copy to the domain of the image database, the result is often inefficient, and even ineffective; such as SQL language, indexing technology and so on are worth the image database for reference. The combination of the above two aspects becomes the mainstream of the development of image database technology at present.

1.1 BLOB data type

BLOBs are very large, variable binary or character data, usually documents (. txt,. doc) and pictures (. jpeg,. gif,. bmp) that can be stored in a database. In SQL Server, a blob can be a text, ntext, or image data type. The image data type stores binary data of indeterminate length, with a maximum length of 2GB.

BLOB data is stored in a SQL Server system differently from ordinary data types, and for ordinary types of data systems to store data values directly on user-defined fields, and for BLOB type data, the system opens up new storage pages to store the data, A BLOB type data field in a table holds only a 16-byte pointer to the page that holds the BLOB data for that record.

Design strategy of 1.2 blob

BLOB data is a data type with a large amount of data, which consumes a lot of hard disk space, memory and network resources, so it is very important to design a property sheet containing BLOB data type to improve storage efficiency and query speed. The general BLOB design principles are as follows:

(1) Using a BLOB data type or using a varchar or varbinary data type

Binary large objects are not necessarily stored as text, ntext, or image data types, and they can also be used as varchar or varbinary data type villages in tables. The selection of the data type depends on the actual size of the blob that will be stored. If the data does not exceed 8K, then use the varchar or varbinary data type. If the size of these large objects exceeds 8K, then the text, ntext, or image data type is used.

(2) Storage blob in the database or in the file system

The common design question is whether to have the picture in the database or in the file system. In most cases, it is best to have a picture file in the database with other data. There are many advantages to storing image data files in a database:

Easy to manage when a blob is stored in a database with other data, blobs and tables are backed up and restored together with data. This reduces the chance that tabular data is not synchronized with BLOB data and reduces the path and risk of other users inadvertently removing BLOB data from the file system. In addition, inserting, updating, and deleting data in a BLOB and other data stored in the database are implemented in the same transaction. This ensures consistency of the data and consistency between the file and the database. Another benefit is that you do not need to set security for files in the file system separately.

Scalability Although file systems are designed to handle a large number of objects of different sizes, file systems cannot be optimized for a large number of small files. In this case, the database system can be optimized.

Availability databases have more availability than file systems. Database replication allows replication, distribution, and potential modification of data in a distributed environment. In the event of a primary system failure, log transfer provides a way to retain the standby copy of the database.

Of course, in some cases, storing pictures in a file system would be a better choice:

(1) Applications that use pictures require data flow performance, such as real-time video replay.

(2) Applications such as Microsoft PhotoDraw or Adobe Photoshop often access blobs, and these applications only know how to access the files.

(3) Special features such as Remote Storage need to be used in some NTFS file systems.
Second, the method of storing image data

1 Creating a SQL Server database with image Yu segment

When you need to store image data in a SQL Server database, you should first establish a database relational table that contains fields for the image data type. In the data types supported by the SQL Server database platform, the image data type is primarily used to store binary data such as large segments of image data. The version after SQL Server 7.0, the image type can store 2GB of data.

2 using the remote Data control to establish a connection to the database

A remote Data control is a control that is used in a Visual Basic application to obtain remote information. It's in. The interface between a remote data object (RDO) and a data-bound control allows you to connect to the database and implement basic operations on the database, provided it provides information about the location of the data store, the data obtained, and some interface controls.

To use the remote Data control to establish a connection to the remoted database, follow these steps:

1. Add a remote Data control to a form in Visual Basic. ，

2. Select an ODBC data source in the Drop-down list for the remote Data Control DataSourceName property. such as pubs.

3. Enter the SQL query statement in the SQL property of the remote Data control. For example, Select * from pub_info.

2.3 Binding OLE and Remote Data control to realize the storage and editing of image data

OLE is a technique that allows applications to exchange and display data between each other, using OLE to read information from any application that supports OLE technology, or to display and edit it in any program that supports OLE. In Visual Basic, you can use the bindings of OLE container controls and remote data controls to display, store, and edit image type data in a SQL serve database.

1. Display image data for SQL Serve database image Type field

The specific steps are as follows:

(1) Add an OLE container control in a form with Visual Basic that has joined the remote Data control. When you create an OLE container control, the Insert Object: dialog box appears, and then you click the Cancel button and you do not have to specify an object for the container.

(2) Sets the DataSource property of the OLE container control to the name of the remote Data control, which implements the binding of the control.

(3) In the OLE container control. Select the name of the field to display in the Drop-down list for the DataField property. For example, logo.

(4) Run the application. Data for the Logo field that contains the image data type displays an image in the OLE container control.

2. Add stored image data in the SQL Sever database image Type field

The specific steps are as follows:

(1) The Eofaction property of remote Data control is set to Rdaddnew to realize the data adding function of database.

(2) Add a CommandButton control to the visual Baisic form and write the image file to embed the program code for the OLE container control. For example:

Private Sub Command_click ()

Olel.createembed "C:\ picture. Bmp

End Sub

(3) Run the application, click the remote Data Control record indicating button, move to the new record, and then click the CommandButton Control button to implement the image data added to the warehouse operation.

3. Edit image data for SQL Serve database image data Type field

The specific steps are as follows:

(1) Add a CommandButton control to the form in Visual Basic to write editing code for the OLE object. For example:

Private Sub Command2--_click ()

Olel.doverb Vboleopen

End Sub

(2) Run the application, click the Record indicating button of the remote Data control, select the image you want to edit, and then click the CommandButton Control button to open the object in the separate application window to edit the image.

4, the realization of image file automatic storage way

In the practical application of database, it is often necessary to store the image files in batches in order to improve the automatization of data warehousing operation. For the batch storage of image files, in the program code writing, can make full use of OLE container control and RemoteData control of many properties and methods to achieve.

The specific steps are as follows:

(1) Set the OLE and RemoteData control's Visible property to False so that OLE and RemoteData controls are not visible.

(2) Create batch files for image files.

(3) write the following basic code to achieve batch processing functions.

Private Sub Command3_Click ()

Dim FileName as String

Open "C:\tesffile. TXT "forlnputas#1" opens the batch file.

Dowhilenoteof (1) ' loops to the end of the file.

Input#l,filename ' reads the image file name.

MSRDCl.Resultset.AddNew ' result set add record

Olel.createembedfilename ' Embed image file

MSRDCl.Resultset.Update

Loop

Close #1

End Sub

(4) Run the application. Click the CommandButton control button to complete the automatic storage of the image file.

V. Concluding remarks

With the continuous enhancement of SQL Serve database management system and the continuous improvement of performance, it has become a trend of technology development that all kinds of data are stored and managed completely by database management system. Only in this way, the powerful functions of SQL serve database management system can be fully brought into full play, data security can be fully protected, making such as database duplication, data transfer and many other work, become very simple and easy.