[Transfer] SQL Server Strategy and Method for Storage Image Data

xiaoxiao2021-03-06  48

At present, the management of the image data is mostly used in the form of the table entity, that is, the image data is stored in the specified computer directory in the file form, and only the storage path of the image data file is reflected in the database table. This management mode adds difficulty to the maintenance of data, and also gives a certain hidden danger to the security of the data. Therefore, it is necessary to truly do all kinds of data in the database security management, research and exploration directly store the image data in the database relationship table.

In the Visual Basic 6.0 development environment, the author uses the client / server's working mode, and the issue of storing image data in the SQL Server database relationship table has proposed a basic solution for readers.

First, strategic storage of image data

Image database technology has always been committed to solving effective storage and management issues of massive digital images. It is the inheritance and development of database technology. On the one hand, the image data and text data have essential differences. Traditional database technology for successful applications in text data, if it is constantly moving to the image database, the result is often inefficient, Even invalid; on the other hand, many of the results of traditional databases, such as SQL language, indexing techniques, etc. are worthy of image database reference. The combination of the above two as the mainstream of the current development of the current image database technology.

1.1 BLOB data type

Blob is a very huge non-binary or character data, usually a document (.txt, .doc), and pictures (.jpeg, .gif, .bmp), it can be stored in the database. In SQL Server, the blob can be text, ntext, or Image data type. The image data type is stored as a binary data in which the length is uncertain, the maximum length is 2GB.

BLOB data is different from the normal data type in the SQL Server system, and the data value is stored directly on the user-defined field, and the system opens up new storage pages for BLOB type data to store these data. The BLOB type data field is only a 16-byte pointer, which points to the page of the BLOB data stored in the record.

1.2 BLOB design strategy

BLOB data is a large amount of data, which takes up a large number of hard disk space, memory, and network resources, so it is reasonably designed to include a property sheet containing the BLOB data type, which has a big impact on improving storage efficiency and query speed. The general BLOB design principles are as follows:

(1) Use the BLOB data type or use VARCHAR or VARBINARY data type

Binary large objects do not have to store TEXT, NTEXT, or Image data types, which can also be in the table as a VARCHAR or VARBINARY data type village. The selection of the data type is based on the actual size of the BLOB to be stored. If the data will not exceed 8K, then use VARCHAR or VARBINARY data type. If the size of these large objects exceeds 8K, use text, ntext, or image data type.

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

Common design issues are in the database or existing file systems in the database. In most cases, it is best to exist image files with other data. There are many advantages because you store the image data file in the database:

Easy to manage When BLOB is stored in the database with other data, BLOB and tables are backup and recovery. This reduces the chance of forming the form data and the BLOB data, and reduces other users inadvertently deleting paths and risks of the BLOB data location in the file system. In addition, the insertion, update, and deletion of BLOB and other data stored in the database are implemented in the same transaction. This ensures consistency of data and consistency between files and databases. There is also a little benefit that you don't need to set security for files in the file system. Scalability Although the file system is designed to handle a large number of different sizes, the file system cannot optimize a large number of small files. In this case, the database system can be optimized.

Availability database has more usability than file systems. Database replication allows copying, assigning, and potential modification data in a distributed environment. In the case of the main system failure, log transfers provide a method for reserving database backup copies.

Of course, in some cases, it will be a better choice to store pictures in the file system:

(1) The application using the picture requires data stream performance, such as real-time video reproduction.

(2) Applications such as Microsoft PhotoDraw or Adobe Photoshop often access BLOB, which only knows how to access files.

(3) There is a need to use special functions in the NTFS file system, such as remote storage.

Second, the method of storing image data

1 Establish SQL Server Database with Image Yuge

When you need to store image data in the SQL Server database, you should first create a database relationship table containing the Image Data Type field. In the data type supported by the SQL Server database platform, the image data type is primarily used to store binary data such as large segments such as image data. The version after SQL Server 7.0, the image type can store 2GB of data.

2 Establish connections to the database using the Remote Data control

The Remote Data Control is a control used to get remote data in the Visual Basic application. it is at. A interface is provided between the remote data object (RDO) and the data binding control, as long as it provides the location of the data stored, the acquired data and some interface controls can be connected to the database, and the basic operation of the database is implemented.

Connection steps to establish and remote databases using the Remote Data Control are as follows:

1. Add a Remote Data control in the Form of Visual Basic. ,

2. Select an ODBC data source in the drop-down list of the Remote Data Control DataSourceName property. For example, 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 Controls to implement image data storage and editing

OLE is a technique that allows applications to exchange and display data, using OLE, can read information from any application that supports OLE technology, or in any support OLE program to display and edit it. In Visual Basic, you can use the OLE container control with the binding of the Remote Data control to display, store, and edit image type data in the SQL Serve database.

1. Display the image data of the SQL Serve Database Image Type field

Specific steps are as follows:

(1) Add an OLE container control in the Visual Basic's form of the Remote Data control. When you create an OLE container control, an Insert Object ': Dialog box appears. At this point, click the "Cancel" button without having to specify the object for the container.

(2) Set the DataSource property of the OLE container control to the name of the Remote Data control, to achieve the binding of the control.

(3) In the OLE container control. Select the field name to display in the drop-down list of the DataField property. For example, LOGO. (4) Run the application. A data for the logo field containing the image data type will display an image in the OLE container control.

2. Add storage image data in the SQL SEVER Database IMAGE type field.

Specific steps are as follows:

(1) Set the EOFAction property of the Remote Data control to RDADDNEW to implement the data add function of the database.

(2) Add a CommandButton control in the Visual Baisic form, and write the image file embedded in the program code of the OLE container control. E.g:

Private sub fascist_click () OLEL.CREATEEMBED "C: \ Picture .BMP" End Sub

(3) Run the application, click the Remote Data Control, move to a new record, then click the CommandButton control button to achieve the addition of the image data to the storage.

3. Edit the image data of the SQL Serve Database Image Data Type field

Specific steps are as follows:

(1) Add a CommandButton control to the Visual Basic form to write the editing program code for the OLE object. E.g:

Private submmand2 --_click () OLEL.DOVERB VBOLEOPENEND SUB

(2) Run the application, click the Remote Data Control, select the image you want to edit, then click the CommandButton Control button to open the object in a separate application window, and perform an image editing operation.

4. Implementation of the image file automatic storage mode

In practical applications in the database, you often need to store the image file to increase the degree of automation of the data storage operation. For image file batch storage, in the writing of program code, you can make full use of the OLE container controls and methods of the RemoteData controls.

Specific steps are as follows:

(1) Set the Visible property of the OLE and REMOTEDATA control to false so that the OLE and RemoteData controls are not visible.

(2) Establish a batch file for image files.

(3) Write the following basic code to implement batch function.

Private submmand3_click () DIM FileName As StringOpen "C: \tesffile.txt" forlnputaster # 1 'opens the batch file. Dowhilenoteof (1) 'loops to the end of the file. Input # l, filename 'reads into the image file name. MSRDCL.ResultSet.Addnew 'Results Add Record OLEL.CREATEEMBEDFILENAME' Embedded Image File Msrdclose # 1END SUB

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

Five, conclude

With the continuous enhancement of the SQL Serve database management system, the performance is continuously improved, and all kinds of data is completely stored and managed by the database management system, which has become the trend of technology development. Only in this way, the powerful function of the SQL Serve database management system can be fully utilized, and the security of data can be sufficient, so that many work such as database replication, data transfer, etc., it is very easy.

转载请注明原文地址:https://www.9cbs.com/read-116408.html

New Post(0)