In a perfect MIS in hospital information, images, data access is essential, such as X-rays, CT photo preservation.On the one hand, the image data for the accurate diagnosis in remote clinics provide an important basis for the disease, on the other hand, for fast access to patient information to provide the basic conditions.Access image data in other applications such as GIS also has a wide range of applications.
1, SQL Server image data storage mechanism in
In the MIS SQL Server, for less than 8,000 bytes of image data can be binary-type (binary, varbinary) to represent.But usually want to save some of the medical image pictures are larger than 8000 bytes.SQL Server provides a mechanism that can store 2GB each line of the binary large object (BLOB), such objects may include image, text and ntext data types of three.Image data is stored in binary data type, maximum length of 231-1 (2,147,483,647) bytes.
BLOB data in the MIS SQL Server system stored data is different from the ordinary type, the type of data system for the average user-defined fields directly in the stored data values, and for the BLOB type data, the system opens a new store to store pagethese data, the table stored BLOB type data field is only a 16-byte pointer, the pointer which records stored BLOB data pages.
2, SQL Server data access in the image
In the MIS SQL Server, when the data is less than 8,000 bytes, you can manipulate with ordinary SQL statement (SELECT, INSERT, UPDATE, DELETE) to complete the manipulation of the field, when the data is larger than 8000 bytes,SQL provides a WRITETEXT, READTEXT and UPDATETEXT these three functions to read and modify data.Use of these three functions are:
(1) WRITETEXT {table.column text_ptr} [WITH LOG] {data}
table.column fields in the table, text_ptr 16 bytes for a pointer, data for the write data values.WITH LOG indicate whether an optional parameter to write the log file.
Example:
DECLARE @ ptrval binary (16) - pointer SELECT @ ptrval = TEXTPTR (img_ct) FROM zy_ct WHERE id_ct = 20010101001 WRITETEXT zy_ct.img_ct @ ptrval 0x024324142342134214213421421454353452341
(2) READTEXT {table.column text_ptr offset size} [HOLDLOCK]
table.column fields in the table, text_ptr 16 bytes for a pointer, offset is the offset, that is, start reading from the first few bytes of data, size is the number of bytes to read, HOLDLOCK read the data inallows the other users whether to modify the data.
Example:
DECLARE @ ptrval varbinary (16) SELECT @ ptrval = TEXTPTR (img_ct) FROM zy_ct WHERE id_ct = 20010101001 READTEXT zy_ct.img_ct @ ptrval 1 25
(3) UPDATETEXT
{Table_name.dest_column_name dest_text_ptr} {NULL | insert_offset} {NULL | delete_length} [WITH LOG] [inserted_data | {table_name.src_column_name src_text_ptr}
table_name.dest_column_name order to modify the text, ntext, or image field; dest_text_ptr pointer to point to its; insert_offset to offset, for text and image as bytes from the beginning to start writing a few, for the first few from ntextcharacter (byte) began to write; delete_length delete_length start deleting from the insert_offset of bytes (characters), 0 does not remove the NULL insert_offset when removed from the beginning to the end of all data.To insert data for inserted_data is, but also the src_column_name table table_name src_text_ptr pointer field within the meaning of the data.
Example:
DECLARE @ ptrval binary (16) SELECT @ ptrval = TEXTPTR (img_ct) FROM zy_ct WHERE id_ct = 20010101001 UPDATETEXT zy_ct.img_ct @ ptrval 16 0x54345
Can be seen that the use of these three functions more complicated, although you can generate stored procedures to call the Executive, but there is a flaw in the read data, READTEXT function reads the data can not be directly passed back to the front-end applications.
3, VB 6.0 to access the image data in the
VB 6.0 The ADO Field object provides methods and AppendChunk GetChunk ways to access BLOB data, which is essentially two functions through API calls WRITETEXT, READTEXT and UPDATETEXT these three functions, simplifying the calling method.
(1) GetChunk and AppendChunk methods introduced
GetChunk method to retrieve some or all of its long binary or character data.GetChunk call returns the data will be assigned "variable."If Size is greater than the remaining data, the GetChunk returns only the remaining data without having to fill blank "variable."If the field is empty, then the GetChunk method returns Null.GetChunk each subsequent call to retrieve a previous call to stop at the beginning of GetChunk data.However, if you retrieve data from a field and then in the current record set or read the value of another field, ADO will be considered from the first field to retrieve the data.If you call again on the first field GetChunk method, ADO will be interpreted as a new call GetChunk operation and start from the beginning of records read.The first AppendChunk Field object writes data to field calls, covering all the available data, the subsequent call to the AppendChunk added to the existing data.
Because the system is always limited, and if a read (DPS) to take large amounts of data may cause the server, the client crashes or the server's performance declined significantly, the use of these two functions, to read image data segmentwrite.
(2) program implementation
Program One: write data function
Public Function AppendBlobFromFile (blobColumn As ADODB.Field, ByVal FileName) As BooleanDim FileNumber As Integer 'File number Dim DataLen As Long' length of the file Dim Chunks As Long 'data blocks Dim ChunkAry () As Byte' data block array Dim ChunkSize As Long'data block size Dim Fragment As Long' fragmented data size Dim lngI As Long 'counter On Error GoTo ErrorHandleAppendBlobFromFile = FalseChunkSize = 2048' limit the read block size is 2K FileNumber = FreeFile 'generates a random file number Open FileName For BinaryAccess Read As FileNumber 'Open the image file DataLen = LOF (FileNumber)' get length of the file If IsNull (blobColumn) Then Exit Function If DataLen = 0 Then 'file length 0Close FileNumber AppendBlobFromFile = True Exit FunctionEnd If Chunks = DataLen ChunkSize' blockThe number of Fragment = DataLen Mod ChunkSizeIf Fragment> 0 Then 'the first to write fragmented data ReDim ChunkAry (Fragment - 1) Get FileNumber,, ChunkAry ()' Read file blobColumn.AppendChunk ChunkAry 'AppendChunk function call to write data End If ReDim ChunkAry(ChunkSize - 1) 'open up space for the data block For lngI = 1 To Chunks' loop to read out all of the data block Get FileNumber,, ChunkAry () 'read out a piece of data blobColumn.AppendChunk ChunkAry' block of data in the database to add Next lngI CloseFileNumber 'close the file AppendBlobFromFile = TrueExit FunctionErrorHandle: AppendBlobFromFile = FalseMsgBox Err.Description, vbCritical, "Error writing image data!" End Function
Procedure II: Reading the data function
Public Function ReadbolbToFile (blobColumn As ADODB.Field, ByVal FileName) As BooleanDim FileNumber As Integer 'File number Dim DataLen As Long' length of the file Dim Chunks As Long 'data blocks Dim ChunkAry () As Byte' data block array Dim ChunkSize As Long'data block size Dim Fragment As Long' fragmented data size Dim lngI As Long 'counter On Error GoTo ErrorHandle ReadbolbToFile = False ChunkSize = 2048' define the block size is 2K If IsNull (blobColumn) Then Exit Function DataLen = blobColumn.ActualSize 'access to imagesSize If DataLen <8 Then Exit Function 'image size that is not less than 8 byte image information FileNumber = FreeFile' generates a random file number Open FileName For Binary Access Write As FileNumber 'open storage of image data files Chunks = DataLen ChunkSize'data blocks Fragment = DataLen Mod ChunkSize 'fragmented data If Fragment> 0 Then' there are fragmented data, first read the data ReDim ChunkAry (Fragment - 1) ChunkAry = blobColumn.GetChunk (Fragment) Put FileNumber,, ChunkAry 'writingfile End If ReDim ChunkAry (ChunkSize - 1) 'to open up space for the data block For lngI = 1 To Chunks' loop to read out all the blocks ChunkAry = blobColumn.GetChunk (ChunkSize)' row in the database, read data block Put FileNumber,, ChunkAry() 'blocks of data written to the file Next lngI Close FileNumber' close the file ReadbolbToFile = True Exit FunctionErrorHandle: ReadbolbToFile = False MsgBox Err.Description, vbCritical, "Error reading image data!" End Function
When the BLOB type of field is empty, call AppendChunk or GetChunk function will fail.At this point if you want to insert the image data to the field, you should use the Update statement to assign the initial value of the field, such as the 0x0, so the field is assigned a database system to store BLOB data page address.
4, the Summary
For the preservation of Microsoft SQL Server provides a storage platform binary data, Visual Basic 6.0 to access this data provides a flexible interface.This article describes the use of VB interface to access MIS SQL Server Large binary data approach, not only for image files, the same applies to other types of files.The method is applied to hospital management system, image access, in the access speed of the system and other aspects of the performance of satisfactory results obtained.