MOGLiCC Tutorial Part B1

B1) Using attributes and metaInfos: Adding SQL table data

The goal of this tutorial part is to replace the column names and values of the database tables in the sql statements (which are hard coded in the template file) by data read dynamically from the model file. A first guess to do so could be to use metainfo elements in the following way:

class REMITTER
    metainfo ID 123
    metainfo NAME Peter Pepper

The problem with this solution is that to a metainfo element no further information can be attached. Later on we will have to provide information about the field type (alphanummeric or number). Therefore we do not choose this way and follow another one...

1 - In your model file ./input/StandardModelProvider/SQLModelfile.txt, add attributes to your classes as shown in the following lines:

model SQLInsertData

    class REMITTER
        attribute ID
        attribute NAME

    class ORDER
        attribute ORDERNUMBER
        attribute REMITTERID

2 - Now, these attributes must be used in the template file. Replace the body of your ./input/VelocityModelBasedLineInserter/SQLInsertScript/Main.tpl by file the following lines:

#set( $classDescriptorList = $model.getClassDescriptorList() )
#foreach( $classDescriptor in $classDescriptorList )

    #set( $attributeDescriptorList = $classDescriptor.getAttributeDescriptorList() )
		
    #foreach ($attributeDescriptor in $attributeDescriptorList)
		
        $attributeDescriptor.name
			
    #end
	
#end

Restart MOGLiCC and see how attribute names are written in the result file.

3 - We like to have a comma separated list of attribute names. To achieve this, replace the body of the Main.tpl by

#set( $classDescriptorList = $model.getClassDescriptorList() )
#foreach( $classDescriptor in $classDescriptorList )

    #set( $attributeDescriptorList = $classDescriptor.getAttributeDescriptorList() )
    #set( $commaSeparatedListOfAttributes = "" )
	
    #foreach ($attributeDescriptor in $attributeDescriptorList)
	
        #set( $commaSeparatedListOfAttributes = $commaSeparatedListOfAttributes + $attributeDescriptor.name + ", ")
		
    #end

    $commaSeparatedListOfAttributes
	
#end

Restart MOGLiCC and view the comma separated attribute list in your result file.

4 - Before using this comma separated list of attributes for building insert statements, we have to cut the last comma. To so replace the body of the Main.tpl again by

#set( $classDescriptorList = $model.getClassDescriptorList() )
#foreach( $classDescriptor in $classDescriptorList )

    #foreach( $classDescriptor in $classDescriptorList )

        #set( $attributeDescriptorList = $classDescriptor.getAttributeDescriptorList() )
        #set( $commaSeparatedListOfAttributes = "" )
	
        #foreach ($attributeDescriptor in $attributeDescriptorList)
	
            #set( $columnName = $attributeDescriptor.name)
            #set( $commaSeparatedListOfAttributes = $commaSeparatedListOfAttributes + $columnName + ", ")
		
        #end

        #set( $numberOfChars = $commaSeparatedListOfAttributes.length() )
        #set( $commaSeparatedListOfAttributes = $commaSeparatedListOfAttributes.substring(0, $numberOfChars - 2) )
        INSERT INTO $classDescriptor.name ($commaSeparatedListOfAttributes) VALUES ('id1','Peter Pepper');

    #end
	
#end

5 - Having restarted MOGLiCC we face an error. Next to the start script you see an error.report file. It says: " Problem for plugin 'VelocityModelBasedLineInserter': UNEXPECTED PROBLEM: ParseErrorException: Lexical error, Encountered: " " (32), after : "-" at Main.tpl[line 22, column 110]". The information line 22 and column 110 points to the location where the last instruction is "$numberOfChars - 2". The parentheses look good, so this instruction is highly suspicious. Let's try a modification. We do the substraction one line early when declaring the local Velocity variable numberOfChars:

#set( $numberOfChars = $commaSeparatedListOfAttributes.length() - 2 )
#set( $commaSeparatedListOfAttributes = $commaSeparatedListOfAttributes.substring(0, $numberOfChars) )

Restart MOGLiCC. This solves the problem and the attribute names are used in the insert statement.

6 - Well, now we can focus on the content of the database tables. We introduce the table values in the model file like this:

model SQLInsertData

    class REMITTER
        attribute ID
            metainfo sqlValue 123
        attribute NAME
            metainfo sqlValue Peter Pepper

    class ORDER
        attribute ORDERNUMBER
            metainfo sqlValue 667788
        attribute REMITTERID
            metainfo sqlValue 123

7 - Correspondingly, we have to adapt the template file to use this information. The following lines show the complete content of Main.tpl file necessary to attain the goal of this section:

@TargetDir <applicationRootDir>
@TargetFileName insert.sql        # hard coded file name reduces flexibility!
@NameOfValidModel SQLInsertData
@CreateNew true

#set( $classDescriptorList = $model.getClassDescriptorList() )

#foreach( $classDescriptor in $classDescriptorList )

        #set( $attributeDescriptorList = $classDescriptor.getAttributeDescriptorList() )
        #set( $commaSeparatedListOfAttributes = "" )
        #set( $commaSeparatedListOfTableValues = "" )
	
        #foreach ($attributeDescriptor in $attributeDescriptorList)
	
            #set( $columnName = $attributeDescriptor.name)
            #set( $tableValue = $attributeDescriptor.getMetaInfoValueFor("tableValue") )

            #set( $commaSeparatedListOfAttributes = $commaSeparatedListOfAttributes + $columnName + ", ")
            #set( $commaSeparatedListOfTableValues = $commaSeparatedListOfTableValues + $tableValue + ", ")
		
        #end

        # Cut the last comma from both lists
        #set( $numberOfChars = $commaSeparatedListOfAttributes.length() - 2 )
        #set( $commaSeparatedListOfAttributes = $commaSeparatedListOfAttributes.substring(0, $numberOfChars) )
	
        #set( $numberOfChars = $commaSeparatedListOfTableValues.length() - 2 )
        #set( $commaSeparatedListOfTableValues = $commaSeparatedListOfTableValues.substring(0, $numberOfChars) )
	
        # all the velocity instructions above were needed for this single line of output
        INSERT INTO $classDescriptor.name ($commaSeparatedListOfAttributes) VALUES ($commaSeparatedListOfTableValues);  

#end

Here you can see, that you can use Velocity comments to increase the understandibility of your template code. These Velocity comments are removed by the Velocity engine (called in VelocityEngineProvider of MOGLiCC) and will not enter the MOGLiCC output. Within the body, this works only if the whole line represents the comment (as shown in line above the insert statement).

Restart MOGLiCC and we face another problem.

8 - Well, the metainfo values are now used in the sql statement, however, the metainfo element "tableValue" used in the modelfile was not found in the model data. Let's have a look there and see why. Oh man, we have used another term in the model file: "sqlValue". Let's adapt the model file:

model SQLInsertData
	class REMITTER
	    attribute ID
		  metainfo tableValue 123
	    attribute NAME
		  metainfo tableValue Peter Pepper
	class ORDER
	    attribute ORDERNUMBER
		  metainfo tableValue 667788
	    attribute REMITTERID
		  metainfo tableValue 123

Restart MOGLiCC and the result looks better. But do you see the tiny problem? The tableValue "Peter Pepper" did not fully enter the result file. The model parser uses spaces to parse single lines. To use spaces in metainfo elements we have to do the following:

metainfo tableValue "Peter Pepper"

That should it be. Restart MOGLiCC and view the result file. Done! Goal attained. :-)

Next tutorial part ->

<- Back to the tutorial main page