Clips Table Overview

The Clips table contains information about each of the clips that exist in the master TIFF file that is created during scanning. It can be used by solution programs to provide access to these arbitrary collections of clips; enabling implementation of the logic necessary to perform image archiving and image based data editing.

The Clips table exists whether clips have been taken or not. If no master TIFF file exists, the Clips table will contain zero rows.

Some of the data in the Clips table also exists in the Clips schema but since the schema does not support SQL statements, the duplication is quite useful, particularly when used with the aggregate functions RAWTIFF and RICHTIFF (See Multi-image TIFF Support below).

NOTE: When whole sheet clipping is specified in an application, the RICHTIFF function does not return an image for a specific field. The image is only returned when you select sparse clips or if you use the ClipServer object, which does correctly navigate the images.

The Clips table is entirely read-only. It may not be used in an UPDATE or DELETE or INSERT statement.

ClipOrdinal can be used to look up a clip in the CLIPS schema rowset. Or, the combination of Document Name, ClipID, and ChildID can be used to look up a clip in the Clips schema rowset.

The FullClipID field is a concatenation of Serial Number + ClipID + ChildID, and is unique and invariant for every clip in a TIFF file.

Phantom Clips

One of the complications that solutions programs face in using clips from ScanTools software is the presence of phantom clips. Phantom clips are clips that describe a particular portion of an image but do not actually contain the pixels for the image. Solutions that use the Clips table to write TIFF files that are readable by downstream applications will generally want to exclude phantom clips. This can be done simply by querying for clips where the ChildID = 0 since only phantoms have non-zero ChildIDs.

However, a solution that includes KFI (key from image) does want phantom clips since they are necessary for bouncing ball editing. These solutions will also desire more control over rotation, scaling, and background form merge since the options specified for archiving may not be appropriate for KFI.

These differing needs mean that the Clips table exposes a large amount of data and that two SQL aggregate functions can be used to composite clips in different ways. To control the creation of phantom clips during scanning, see SessionInfo.TakePhantomClips property.

KFI and Bouncing Ball Editing Support

The Clips table can be used in conjunction with the Edit Failure table to support KFI and bouncing ball editing. You can get all of the clips that apply to a particular field and then use the DataFieldLength and DataFieldStart values to select the proper phantom clip as the cursor moves. Doing bouncing ball or other more image intensive operations will require you to be able to get at exact coordinates of clips relative to a page, hence the inclusion of the (XRes, YRes), (XPos, YPos) and (XSize, YSize) pairs.

Retrieving Clips

Following are examples of how to retrieve clips:

To retrieve all the clips for a particular document:

Select * from clips where [serial number] = 1

To retrieve all the clips from a particular page of a document:

Select * from clips where [serial number] = 1 and side = 0 and sheet = 1

To retrieve all the clips for a particular field of a document type:

Select * from clips where [Field Name] = ‘Birth Date’ and [Document Name] = ‘MM223022-1’

ClipBytes and ClipBytesCount

The ClipBytes column contains a data stream that can be interpreted as a single image TIFF file. (i.e., save the bytes to disk with a .tif extension and it will be a legal file). The ClipBytesCount field is simply a helper field that can be used to allocate memory before asking for the ClipBytes.

In the case of a phantom clip, the ClipBytes column contains an image of a single white pixel.

Multi-image TIFF Support

The ClipBytes columns contain single image TIFF files. However, in many instances you may want a query to return a single, multi-image TIFF file that contains all of the requested clips. To support this usage, a new SQL aggregate function is defined called RAWTIFF(). Aggregate functions return a single value in response to a query. In SQL, aggregate functions can be mixed with any columns named in a GROUP BY clause. However, the Scantron OLE DB Provider does not support GROUP BY so our rule is: A query must consist only of aggregate functions or only of individual columns. Hence, "SELECT name, RAWTIFF(clips) from 1 where name = ‘foobar’ " is invalid.

Using RAWTIFF, retrieving all the clips for a single document would be:

Select RAWTIFF(ClipBytes) from Clips where [serial number] = 1

The return is a single value that has combined all the clip columns into a single multi-page TIFF.

RAWTIFF() returns clips as they exist in the clip file – no background merge, rotation, or scaling is done.

RAWTIFF() is appropriate if you want to work with the native clip data, but will be challenging if you don’t want to know about the processing that archiving usually does. For that scenario, a second aggregate function called RICHTIFF() is defined. Like RAWTIFF(), it will aggregate single clips into multi-image TIFFs, but it will also do the processing called for in the application definition: rotation, scaling, and background merge.

Though RICHTIFF() is an aggregate function, there is no restriction on using it with a single clip, allowing single image clips to be created in archive format.

TIFF File Availability

The hierarchy of TIFF files achievable with the Clips table and aggregate functions is shown below:

1. To get a single image raw TIFF from a clip, save the ClipBytes column to disk.

2. To get a multiple image raw TIFF from one or more clips, use RAWTIFF() to create the aggregate clip file and save it to disk.

3. To get a single image TIFF with archiving processing done apply RICHTIFF() to the ClipBytes column and save it to disk.

4. To get a multiple image TIFF file with archiving processing done apply RICHTIFF() to one or more rows in the Clips table and save the resulting file to disk.

Working with JPEG Files

Note that ScanTools has the ability to create a JPEG file as an output during archiving. JPEG files are inherently single image, so while the SQL functions can create TIFF files that contain bits that are compressed using JPEG compression, there is no direct way to use data services to create a JPEG file. Moreover, TIFF files using JPEG compression are usually considered obsolete and are very poorly supported by most TIFF readers. For non-bitone data, we recommend using Packbits RLE compression. However, using any of the .Net languages it is a relatively simple matter to create a Bitmap object from a valid TIFF stream and then save the bitmap as a JPEG file.

Performance Considerations

Queries issued against the clips table may result in performance degradation. Compared to the number of rows and the size of each row in a document data table, the clips table contains more and larger rows. With the presence of phantom clips that are used for editing, the clips table often contains about one row for every single byte in the data file. This means that the clips table can easily contain hundreds of thousands of rows, even on relatively modest data files. Any query that has to examine every row in the clips table is therefore a potential bottleneck, as performance will degrade linearly with the size of the data file.

Performance degradation can be managed however. Since many applications will want to process data one document at a time, queries that limit the returned data to a single serial number should be appropriate in most cases. For example, the query processor can recognize queries that are limited to a single serial number ("…WHERE [SERIAL NUMBER] = 10") or fullclipid ("…WHERE [FULLCLIPID] = '00034300101500240'"), and examine only the rows in the table that correspond to the desired serial number/fullclipid. Instead of performance degrading linearly with the size of the file, these queries show logarithmic performance degradation.

For more information, see Clips Table Definition, Clips Rowset, and Connection Strings.