 | Level: Intermediate Michael Rajkowski (mrajkow@us.ibm.com), Software Engineer, U2 Client Support, IBM
02 Nov 2007 IBM® U2 (IBM UniData® and IBM UniVerse®) stores all of
its data without any type validation. Thus the same field can contain an integer, a
string, or even an internal date structure. While this may be useful to the
application developer, it can cause problems when integrating the data from your U2
environment to an external environment. In this article, validate the data against a schema and prevent invalid data with update triggers.
Introduction
Do you need to integrate the data in your U2 environment with data in other sources or to an external environment? Thanks to the XML support in U2, you have a simple way to validate your data.
|
File Information
UniData and UniVerse
have a similar file called STUDENT in UniData and STUDENT.F in UniVerse. If you are
using UniVerse, please add the ".F" to the end of the file name for the examples.
Also, note that the references to the _XML_ directory are intended for those of you
using UniData; for UniVerse, the directory name is &XML&. |
|
This article explains how to set up a validation routine, as well as how to hook it into a file trigger, so no future updates will write any data that does not match the defined schema.
Please note that this article is not intended to teach you XML or XML schemas. It is intended to show how you can accomplish data type validation using the XML support in U2.
In order to follow along with the steps in this article, you need to log in to U2.
UniData users should log in to the Demo account, while UniVerse users need to log in to the HS.SALES account.
If you do not have access to a U2 database, you can download a copy of the Personal
Edition. (See Resources for a link.)
Validating with XML
Looking at the data
Let's start by looking at the data with a typical U2 query statement. Listing 1 shows the results of a simple list statement against the
student file. If you are not familiar with U2 or MultiValued databases, please refer
to the article "IBM
U2: The big picture" (developerWorks, August 2005).
Listing 1. Sample output from U2 query
LIST STUDENT LNAME FNAME MAJOR MINOR ADVISOR SEMESTER COURSE_NBR COURSE_GRD
SAMPLE 1 13:06:49 Sep 24 2007 1
STUDENT..... Last Name...... First Name Major Minor Advisor. Term Crs # GD.
521-81-4564 Smith Harry CH PY Carnes FA93 CS130 A
CS100 B
PY100 B
SP94 CS131 B
CS101 B
PE220 A
1 record listed
|
Rendering the output as XML
Thanks to the command line arguments, you can generate XML from the previous
statement by including the TOXML keyword. Listing 2 shows
the resulting XML:
Listing 2. Sample XML output from U2 query
LIST STUDENT LNAME FNAME MAJOR MINOR ADVISOR SEMESTER COURSE_NBR COURSE_GRD
SAMPLE 1 TOXML
<?xml version="1.0"?>
<ROOT>
<STUDENT _ID = "521814564" LNAME = "Smith" FNAME = "Harry" MAJOR = "CH" MINOR =
"PY" ADVISOR = "Carnes">
<CGA-MV SEMESTER = "FA93">
<CGA-MS COURSE_NBR = "CS130" COURSE_GRD = "A"/>
<CGA-MS COURSE_NBR = "CS100" COURSE_GRD = "B"/>
<CGA-MS COURSE_NBR = "PY100" COURSE_GRD = "B"/>
</CGA-MV>
<CGA-MV SEMESTER = "SP94">
<CGA-MS COURSE_NBR = "CS131" COURSE_GRD = "B"/>
<CGA-MS COURSE_NBR = "CS101" COURSE_GRD = "B"/>
<CGA-MS COURSE_NBR = "PE220" COURSE_GRD = "A"/>
</CGA-MV>
</STUDENT>
</ROOT>
|
Creating the schema
One feature of U2 is the ability to create a schema using the command line query
language. You can create the schema by itself or along with the XML file.
For complete syntax of the U2 query language, please refer to the U2 manuals.
|
Interesting fact about the TO clause
Note that the name given to the TO clause in this example
is "studentSchema". If you look in the _XML_ directory, you will find the studentSchema.xsd file. |
|
Use the following command to create the schema and the xml file:
LIST STUDENT LNAME FNAME MAJOR MINOR ADVISOR SEMESTER COURSE_NBR
COURSE_GRD TOXML SCHEMAONLY TO studentSchema
Validating an XML document to the schema
Now that you know how to create an XML document from a U2 query statement and how to generate a schema from a query statement, let's see how to validate the XML with the schema.
The following listing (Listing 3) is a simple U2 basic
program that validates the XML with the schema. (Note that the code can be found in
the Downloads section and is called CHECKDATA).
Listing 3. CHECKDATA program
001: *
002: * For UniData use the following include.
003: $INCLUDE INCLUDE XML.H
004: *
005: * For UniVerse use the following include.
006: *$INCLUDE UNIVERSE.INCLUDE XML.H
007: *
008: ***
009: PRINT "Enter the select Criteria for the STUDENT file":;INPUT OPT
010: *
011: CMD = "LIST STUDENT LNAME FNAME MAJOR MINOR ADVISOR SEMESTER COURSE_NBR CORSE_GRD "
012: PRINT "Use XMLEXECUTE to get the XML: ":
013: STATUS = XMLEXECUTE( CMD:OPT, '', XML.DOC, XSD.DOC)
014: GOSUB CHECK.ERROR
015: *
016: XSD.NAME = "studentSchema.xsd"
017: *
018: PRINT "Validate the XML against the schema: ":
019: STATUS = XDOMValidate( XML.DOC, XML.FROM.STRING, XSD.NAME, XML.FROM.FILE)
020: GOSUB CHECK.ERROR
021: PRINT "IT IS VALID"
022: *
|
|
Running the program
You need to enter
SAMPLE 1 at the input, or all items will be in the XML document. |
|
When you compile and run the program in Listing 3, you will see the following:
Listing 4. Running the CHECKDATA program
:RUN BP CHECKDATA
Enter the select Criteria for the STUDENT file?SAMPLE 1
Use XMLEXECUTE to get the XML: COMMAND SUCCESS
Validate the XML against the schema: COMMAND SUCCESS
IT IS VALID
|
Restricting the schema
In Listing 4, the XML is valid since the schema for the example is not that restrictive, all attributes are defined as the string type, and there are no restrictions on the minimum or maximum occurrences of elements.
Listing 5. Excerpt from the studentSchema.xsd
<xsd:element name="STUDENT">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="CGA-MV" minOccurs="0" maxOccurs="unbounded"/>
</xsd:sequence>
<xsd:attribute name="_ID" type="xsd:string"/>
<xsd:attribute name="LNAME" type="xsd:string"/>
<xsd:attribute name="FNAME" type="xsd:string"/>
<xsd:attribute name="MAJOR" type="xsd:string"/>
<xsd:attribute name="MINOR" type="xsd:string"/>
<xsd:attribute name="ADVISOR" type="xsd:string"/>
</xsd:complexType>
</xsd:element>
|
|
Breaking the validation
For demonstration purposes a type of negativeInteger is used to break the validation, since none of the items have a negative ID. |
|
As you see from Listing 5, all the attributes are defined as the string type. So
"123" is just as valid as "some text". To restrict the schema,
let's change the type for the _ID attribute to
"negativeInteger":
<xsd:attribute name="_ID"
type="xsd:negativeInteger"/>
Listing 6. Run CHECKDATA again
RUN BP CHECKDATA
Enter the select Criteria for the STUDENT file?SAMPLE 1
Use XMLEXECUTE to get the XML: COMMAND SUCCESS
Validate the XML against the schema: COMMAND FAILED
29 Error at file 'U2XMLMemoryBufferID', line 4, column 105.
Message: Datatype error: Type:InvalidDatatypeFacetException,
Message:Value '+521814564' must be less than or equal to MaxInclusive '-1' .
:
|
Cleansing the data
Creating a validation routine
Now that you have seen how to validate, you have to decide what you want to do with this ability. One thing you may want to do is to validate all the data in your file. Unlike the previous example that uses no selection criteria, a different approach is to have the program generate an XML string for each item and display those items that do not validate.
To simplify the programming, you can move the validation logic to its own subroutine
and pass in the XML to validate against the fullSchema.xsd.
This is a simple program that validates all the items and displays the results on the screen.
Listing 7 includes a program that uses the new subroutine to validate all items in the student file.
The subroutine is called validateStudentItem and can be found in the download file
(see Downloads).
Listing 7. validateStudentFile program
001: *
002: * For UniData use the following include.
003: $INCLUDE INCLUDE XML.H
004: *
005: * For UniVerse use the following include.
006: *$INCLUDE UNIVERSE.INCLUDE XML.H
007: **
008: OPEN "STUDENT" TO STUDENT.FILE ELSE STOP "Can not open STUDENT"
009: SELECT STUDENT.FILE TO 1
010: DONE = 0
011: CMD = "LIST STUDENT LNAME FNAME MAJOR MINOR ADVISOR SEMESTER COURSE_NBR COURSE_GRD "
012: LOOP
013: READNEXT ID FROM 1 ELSE DONE = 1
014: UNTIL DONE
015: OPT = " WITH @ID = '":ID:"'"
016: STATUS = XMLEXECUTE( CMD:OPT, '', XML.DOC, XSD.DOC)
017: CALL validateStudentItem( XML.DOC, STATUS, ERRORTEXT )
018: IF STATUS THEN
019: PRINT ID, "Item is not valid"
020: PRINT ; PRINT ERRORTEXT ; PRINT ; PRINT "---------"
021: END
022: REPEAT
|
One final step is necessary to ensure that only valid data gets into the U2 database
-- prevent modifications that are not valid, based on the schema. To accomplish this
validation, you can integrate the validateStudentItem used above with an update trigger, thereby limiting updates to only those valid items.
Validating with a trigger
An update trigger is called before the data is updated. If the trigger indicates
that it is not okay to write the data, no update occurs.
With this in mind, you can create a trigger that validates the item against the
schema and, as long as it is valid, allow the update.
|
The studentValidateTrigger
program
Included in the download file in the Downloads section, save the program to the BP file, catalog it, and add it to
the file as the update trigger. Note: This only works with UniData. |
|
When the trigger is called, the data to be written is not in the database. You cannot generate the XML with the XMLEXECUTE command without a slight modification.
The trigger needs to save a copy of the item to a work file. You can then generate the XML by making a slight modification to the command string and options sent to the XMLEXECUTE command.
Listing 8. Excerpt from studentValidationTrigger
020: ** Get the XML for the item in process
021: CMD = 'LIST WORK_FILE USING DICT STUDENT LNAME FNAME MAJOR MINOR ADVISOR SE
MESTER COURSE_NBR COURSE_GRD WITH @ID LIKE "':recordId:'"'
022: STATUS = XMLEXECUTE(CMD, "RECORD":@VM:"STUDENT", XML.DOC, XSD.DOC)
|
In Listing 8, above, you see that, in addition to the use of the USING clause on the LIST command, you needed to change the
element name for the record to STUDENT. By default, U2 uses the file name. If you did
not change the record name, it
would have been "WORK_FILE".
Once you have our trigger program compiled, catalogued, and added as the update
trigger action to the STUDENT file, you need to set up some files for logging.
Error logs for the trigger
Since you could potentially have an item in the WORK_FILE that is not valid for the
STUDENT file, you need a way to save the information and the errors that occurred.
This example uses two additional files called XML_ERRORS and XML_LOG. Please create these prior to testing the trigger program.
The XML_ERRORS file contains the XML generated from the item in the WORK_FILE, and the XML_ERRORS is the text message generated from the failed validation.
Listing 9. Testing the trigger
:AE STUDENT 123.456
Top of New "123.456" in "STUDENT".
*--: FI
Error from trigger: Error at file 'U2XMLMemoryBufferID', line 4, column 26. Mess
age: Datatype error: Type:InvalidDatatypeFacetException, Message:Value '+123.456
' with fraction digits '3' exceeds fraction digit facet of '0' .
Error at file 'U2XMLMemoryBufferID', line 4, column 26. Message: Required attrib
ute 'FNAME' was not provided
Error at file 'U2XMLMemoryBufferID', line 4, column 26. Message: Required attrib
ute 'LNAME' was not provided
[AE] UniBasic WRITE failed, STATUS=2, check triggers.
Quit "123.456" in file "STUDENT" not created.
: |
This insures that the data entered into the database is the type you have defined.
Now that you have data-type checking on a file, your application will have to be modified to handle invalid data.
Final note
Now that you have data-type checking on a file, your application will have to be modified to handle invalid data.
The ON ERROR branch of WRITE is taken in the event the trigger prevents the write. Make sure that your code uses this branch to deal with the error and to take the appropriate action.
Download | Description | Name | Size | Download method |
|---|
| U2 basic code and schema file | udtExample.zip | 4KB | HTTP |
|---|
Resources Learn
-
"IBM U2: The big picture"
(developerWorks, August 2005): Gain a basic understanding of the IBM U2 product line, and gather information about the extended relational data model, architecture, benefits, and associated tools products.
-
developerWorks U2
product page: Get the resources you need to advance your skills in the U2 arena.
-
UniData and UniVerse
manuals: Find various guides for all of your UniData and UniVerse needs.
-
International
UniVerse & UniData User Group: Find articles and assistance relating to the IBM U2
-
developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
-
Technology bookstore: Browse for books on these and other technical topics.
Get products and technologies
-
IBM
U2 Personal Edition: Download
and get your hands on U2 application development tools and middleware products.
- Build your next development project with IBM trial software, available for download directly from developerWorks.
Discuss
About the author | 
| | Michael Rajkowski is a Software Engineer in the IBM U2 Client Support Group. He has over 20 Years experience with MultiValued databases. Michael holds a Bachelor of Science in Computer Science from NYIT, and a MBA from Dowling College (Degree Concentration: Total Quality Management). |
Rate this page
| |
