Overview of Binary Files:
QuerySurge's JDBC-compliant Flat File driver can be used to test any binary file such as image data (e.g., .png, .jpg, .gif, .tiff, .bmp files), text data, or other file data (e.g. MS Word .docx files or Excel .xlsx files, etc.) These files are commonly stored as BLOBs (Binary Large OBject) in databases. (Additional information on QuerySurge and BLOB data is available here.) When you run a QueryPair that returns BLOB data, QuerySurge performs a byte-by-byte comparison between Source and Target BLOB columns to check whether the data is an exact match or not.
Query Local Binary Files:
Querying of local binary files can be done with any existing Flat File Connection, as in this case, the paths to the binary file are controlled in the query. (For information on setting up Flat File Connections, see Configuring Connections: Delimited Flat Files.) Using a Flat File Connection, binary files can be directly queried using the FILE_READ() function. The syntax for this function follows, along with examples.
The Basic Syntax:
The core FILE_READ() syntax to query a binary file is quite simple:
SELECT FILE_READ('<address of binary file>');
Query Filesystem Binary Files:
Binary files in the filesystem can be queried by specifying the file path as an argument to the FILE_READ() function. Note that file paths are relative to the QuerySurge Agent, not the QuerySurge App server. In the following example, the image file is found in the C:\Images directory of the machine running the QuerySurge Agent. Files stored on a network file share drive can be queried similarly, however there may be additional permissions issues.
Query Web Binary Files:
Querying remote files is also possible with FILE_READ(), by specifying the file via a URL. The following example shows how to query an image file stored on a Web Server.
Viewing BLOB Results:
It is important to note that, when QuerySurge processes binary data, the results appear in QuerySurge with the "{blob}" notation. If you see the "{blob}" notation in your results, this means that your data has been acquired by QuerySurge, and the QuerySurge comparison has run on your data. You can see BLOB data cell-by-cell, by choosing a cell in your data popup, and right-clicking on it. Using the context menu, you can save the BLOB data to a file. (This approach is used to avoid unexpected large downloads across your network to your browser.)
Note: When testing any large binary file, additional processing time may be required to execute the test. Additionally, sufficient space should be available to to store the resultset as binary files may be large.
Note: Because BLOBs are simply an ordered collection of bytes as far as QuerySurge is concerned, QuerySurge does not know the file type involved. You may need to determine the data type you are dealing with, and save it with an appropriate file extension in order for your OS to recognize the file.
Note: The user login under which the QuerySurge Agent is running must have read access to the directory path in which your flat files are located. This is especially important to verify if the directory is a network share directory, however it may be important even for local directories. If you need to change the user login under which the QuerySurge Agent is running, see: Setting an Agent to Run Under a Specific Login for instructions.