5(h). Map Parameter Constant Rule - <map-param-const>

• Inserting a run-time input parameter into a column of a database table
• Passing a run-time input parameter as the parameter to a procedure

Sometimes, the constant value that has to be stored in a database column, can only be determined at run-time. In such a case, the constant value cannot be specified in the mapping file, and instead a paramater that is passed to the Connect XML-2-DB program at run-time, has to take the place of a constant value that is mapped to a database column. The <map-param-const> lets you specify which parameter is to be mapped to which database column.

The format of the <map-param-const> element is as follows:

      <!ELEMENT map-param-const       EMPTY>
      <!ATTLIST map-param-const
          element-name           CDATA #REQUIRED
          table-reference        CDATA #IMPLIED
          proc-reference         CDATA #IMPLIED
          column-name            CDATA #IMPLIED
          param-name             CDATA #IMPLIED
          input-param-posn       CDATA #REQUIRED
          source-datetime-format CDATA #IMPLIED>
	

In the map-param-const rule, both table-reference and column-name must be provided or both proc-reference and param-name must be provided.

The <map-param-const> element can be used to

• Insert a run-time input parameter into a column of a database table
• Passing a run-time input parameter as the value of a parameter to a procedure

5(hi). Inserting a run-time input parameter into a column of a database table

In this case, the <map-param-const> rule can be used, with the following conditions:

• The attributes element-name, table-reference, column-name and input-param-posn must be provided.
• input-param-posn is a number greater than 0. Since multiple input parameters are provided during run-time to the Connect XML-2-DB tool, then input-param-posn indicates which input parameter has been mapped to this column.
• By 'run-time input parameters' we mean the parameters that are passed to the Connect XML-2-DB tool at runtime. For example, if the Connect XML-2-DB tool is invoked as follows:

  java ExecXMLToDb dbConfig.txt mapFile.xml xmlDataFile.xml 255 Jan-10-2000

  then 255 and Jan-10-2002 are the run-time input parameters. Since '255' is the first parameter, then it's input-param-posn is 1, and for 'Jan-10-2002' the input-param-posn is 2.

• In the case of Oracle databases, the column-name value must be in uppercase, unless the column was created with quotes around the name, in which mixed case should be used.

Examples of mapping rules that map input-parameters to database columns are provided below:

     <!-- "255" is stored in the REGION_ID column -->
     <map-param-const
         element-name="software-sale"
         table-reference="SoftwareSales"
         column-name="REGION_ID"
         input-param-posn="1">
     </map-param-const>

     <!-- "Jan-10-2000" is stored in the DATE_CREATED column -->
     <map-param-const
         element-name="software-sale"
         table-reference="SoftwareSales"
         column-name="DATE_CREATED"
         input-param-posn="2"
         source-datetime-format="MMM-dd-yyyy">
     </map-param-const>
	

5(hii). Passing a run-time input parameter as the parameter to a procedure

In this case, the <map-param-const> rule can be used, with the following conditions:

• The attributes element-name, proc-reference, param-name and input-param-posn must be provided.
• input-param-posn is a number greater than 0. Since multiple input parameters are provided during run-time to the Connect XML-2-DB tool, then input-param-posn indicates which input parameter has been mapped to this column.
• By 'run-time input parameters' we mean the parameters that are passed to the Connect XML-2-DB tool at runtime. For example, if the Connect XML-2-DB tool is invoked as follows:

  java ExecXMLToDb dbConfig.txt mapFile.xml xmlDataFile.xml 255 Jan-10-2000

  then 255 and Jan-10-2002 are the run-time input parameters. Since '255' is the first parameter, then it's input-param-posn is 1, and for 'Jan-10-2002' the input-param-posn is 2.

• In the case of Oracle databases, the param-name value must be in uppercase.
• In the case of SQL Server databases, the param-name value must be prefixed with a &.
• param-name refers to the procedure parameter name. The input-param-posn refers to the position of the input parameter provided during runtime to the Connect XML-2-DB program.

Examples of mapping rules that map input-parameters to parameters of procedures are provided below:

      <!-- Oracle example --> 	
      <!-- "255" is passed as the value for the I_REGION_ID parameter of the procedure 
                                                                      AddSoftwareSale -->
      <map-param-const
          element-name="software-sale"
          proc-reference="AddSoftwareSale"
          param-name="I_REGION_ID"
          input-param-posn="1">
      </map-param-const>

      <!-- SQL Server example --> 	
      <!-- "Jan-10-2000" is passed as the value for the 
                @i_date_created parameter of the procedure AddSoftwareSale -->           
      <map-param-const
          element-name="software-sale"
          proc-reference="AddSoftwareSale"
          param-name="@i_date_created"
          input-param-posn="2"
          source-datetime-format="MMM-dd-yyyy">
      </map-param-const>