- Product: Voyager
- Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare
A brief overview of the "BLOB" functions in Voyager Prepackaged Access Reports, with some examples.
MARC records (the BIB, MFHD and Authority records) have a few common fields and many optional/repeatable fields.
To make accessing these data easier for reporting purposes, Ex Libris breaks out a number of fields into Access tables (for example, BIB_TEXT, BIB_HEADING, and BIB_INDEX). For fixed fields consider using the MARC*_VW tables and for URLs use the ELINK_INDEX table.
The full MARC records are stored in "BLOBs" (stands for "Binary Large OBjects"). There are three tables that contain them: BIB_DATA, MFHD_DATA and AUTH_DATA.
The records can't be used in the form they are provided in these tables, but require special, advanced processing.
To use the records in Prepackaged Reports, Ex Libris provides MARC extract functions that have been included along with the on-board Access functions.
There are seven BLOB functions.
Extract a MARC record identified by BIB_ID, MFHD_ID or AUTH_ID
These three functions aren't useful on their own. They ask Voyager for a MARC record. You use one of these three as a building block for the four other functions that follow. Your query will also include at least one table in which the ID field is unique. For example:
You'll wrap one of the following four GetField* functions around one of the above.
Extract one or more fields from that MARC record
- GetField(*MARC Record*, “300”, 1)
- GetFieldRaw(*MARC Record*, “300”, 1)
- GetFieldAll(*MARC Record*, “300”)
Extract a subfield from that field
- GetSubField(*MARC Field*, “c”, 1)
GetField* functions synopsis:
- Gives you a single occurrence of a MARC field
- Same as GetFieldAll but get only one field
- Get entire first occurrence of 938 tag: GetField(GetBIBBLOB ([BIB_TEXT].[ BIB_ID]),”938”,1)
- Get first 650 field, subfields a and x: GetField(GetBibBlob([BIB_TEXT].[BIB_ID],"650",1,"ax")
- Gives you all occurrences of a MARC field
- Gives you all subfields (separated by spaces)
- Get entire 938 tag: (GetFieldAll(GetBIBBLOB([BIB_TEXT].[BIB_ID]),”938”)
- Get all of the 866s in a MFHD: GetFieldAll(GetMFHDBlob([MFHD_MASTER].[MFHD_ID]),'866')
- Gives you one occurrence of a MARC field, including the tag, indicators and subfield coding
- It is the only way to get the indicators
- It is the only function that works with GetSubField
- Get the third 650 field in a bib record: GetFieldRaw(GetBibBlob([BIB_TEXT].[BIB_ID]),'650',3)
- Gives you one occurrence of a MARC subfield
- Get specified subfield "a" of 938 tag occurrence: GetSubField(GetFieldRaw(GetBIBBLOB ([BIB_TEXT].[ BIB_ID]),”938”,1),”a”,1)
Example of a BLOB query using the GETFIELD function
This query extracts the 300 field:
Having trouble seeing a long BLOB function or other lengthy expression? Put your cursor in the cell you want to view and hit Shift-F2. This opens the contents of the cell in an Access zoom box.
Example of Using GETFIELDALL function in a query
This query gets the title, call number of all 5xx (note) fields from BIB records:
Notice that second parameters can be truncated to do generic matching. In this example to get any 5xx field, you can use "5". Use an empty string ("") to match any subfield.
Note also that multiple fields are put on multiple rows and may be "hidden" until the row is made taller:
Example of Using the GETSUBFIELD function
GetSubfield must be used in combination with GetFieldRaw; both of these functions require you to specify "Which" matching field/subfield you want:
Notice in the above we imbed the call to "larger" functions into the "smaller" function. For example: GetSubField(GetFieldRaw(GetBibBLOB(BIB_ID),“300”,1),“c”,1):
Example of Checking for field presence
This query looks for MFHDs with multiple (more than one) 866 fields; we don't care what is in the the second field, just that it is there:
Example of Using a Criteria to screen for field content
This query looks for books under 21 centimeters:
Note that adding a criterion to a BLOB query makes it run even slower. That's why it is recommended that you run BLOB queries on a subset of your data by using a Make Table query and running your BLOB query against that table.
Reading a BLOB query
Example: The second $x from the first 650 in a BIB record.
Work from the inside outward:
Are we blobbing a bib, MFHD, or authority? A bib.
GetBibBlob: Which one? The one with this bib_id.
GetFieldRaw: Which field? 650. Which occurrence? The first.
GetSubField: Which one? Subfield x. Which occurrence of subfield x in this field? The second.
BLOB functions run on the workstation, not the server. Before they can run, the entire MARC record must be transferred to the workstation across the network.
This is why BLOB queries are so slow, and if at all possible, should be avoided thru the use of other tables, or using the scan function in Global Data Change.
If you must use a BLOB query, reduce the size of your sample as much as possible. See: Strategies for using BLOB functions in Voyager Prepackaged Access Reports
Constructing custom SQL queries by request and troubleshooting unexpected results from customer-created SQL queries falls outside the scope of Support. The above has been posted for informational purposes. Voyager-L and Developer Network are useful resources for finding helpful custom SQL or obtaining assistance from peers in troubleshooting custom queries.
- Article last edited: 26-07-2021