MOGLiCC Tutorial Part B1

B1) Using attributes and metaInfos: Adding SQL table data

The goal of this tutorial part is to replace the hard coded column names and the values within the insert commands by model data. 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 - Add attributes to your classes as shown in the following lines:

model InsertSQL
	class REMITTER
	    attribute ID
	    attribute NAME
	class ORDER
	    attribute ORDERNUMBER
	    attribute REMITTERID

2 - Now, the attribute information must be used in the template file. Place within the foreach-classDescriptor-loop of your Main.tpl file the following lines:

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

3 - Restart MOGLiCC and see how attribute names are written in the result file. Now modify the last modification to

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

$commaSeparatedListOfAttributes

4 - Restart MOGLiCC and view the comma separated attribute list in your result file. Before using it in the insert statement, we have to cut last comma:

#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

5 - Having restarted MOGLiCC you 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 20, column 110]". The information line 20 and column 110 points to a location where the last instruction was "$numberOfChars - 2". The parentheses look good, so this instruction is highly suspicious. Let's try a modification in following lines:

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

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

6 - Now we can introduce the sql table values in the model file like this:

model InsertSQL
	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 InsertSQL
@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) )
	
        INSERT INTO $classDescriptor.name ($commaSeparatedListOfAttributes) VALUES ($commaSeparatedListOfTableValues);  # all the velocity instructions above were needed for this single line of output

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

8 - Well, the metainfo values are now used in the sql statement, however, there are two problems. Firstly, 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 another term there: "sqlValue". Secondly, only the first name of our remitter name reached the result file. Ok, now let's fix these problems in the model data:

model InsertSQL
	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 view the result file. Done! Goal attained. :-)

Back to the tutorial main page

Next tutorial part