Skip to content. | Skip to navigation

Sections
Personal tools
You are here: Home Accelerate Articles Validate data types in U2 using XML schema

Validate Data Types in U2 Using XML Schema

Filed under: , ,
Rocket U2 (UniData and 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.

Level: Intermediate

Michael Rajkowski ( mrajkowski@rs.com), U2 Senior Technical Support Engineer, Rocket Software

02 Nov 2007


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 "Rocket U2: The big picture" (Technical Article, August 2005).

               
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:

               
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.

Important information
If you are using UniData, do not forget to compile with the I option.

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).

               
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:

               
: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.

               
    <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"/>

               
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.

About the fullSchema.xsd
fullSchema.xsd is a more sensible XML schema that is available in the download file (see Downloads).

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).

               
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.

               
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.

               
: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.

Downloads

Description Name Size Download method
U2 basic code and schema file udtExample.zip 4KB HTTP

 

Resources

Learn
  • "Rocket U2: The big picture"  Gain a basic understanding of the Rocket U2 product line, and gather information about the extended relational data model, architecture, benefits, and associated tools products.
  • UniData and UniVerse manuals: Find various guides for all of your UniData and UniVerse needs.

Get products and technologies
  • Download and get your hands on U2 application development tools and middleware products.

About the author

Michael Rajkowski photo

Michael Rajkowski is a member of the U2 support organization.  He provides world-class support for the U2 products, and often assists Sales, Engineering, Education and Product Management in bringing the best solutions to our partners.  Mike has worked in the MultiValue industry for over 22 years, and is an active, and sometimes outspoken member of the Colorado MultiValue User Group (CMUG)  He is well versed in all the U2 Products, and enjoys projects where U2 is integrated with other technologies.  Mike has a Bachelor of Science degree in Computer Science from New York Institute of Technology and an MBA, with a concentration in Total Quality Management, from Dowling College 

 

Document Actions
Filed under: , ,