Create Write-Back Template Files

A write-back template file is an XML-formatted file that contains one or more write-back templates.

A write-back template consists of a WebMessage element that specifies the name of the template, the connection pool, and the SQL statements that are needed to insert and update records in the write-back tables and columns that you've created. When content designers enable a table view for write back, they must specify the name of the write-back template to use to insert and update the records in the table view.

Requirements for a Write-Back Template

A write-back template must meet the following requirements:

• WebMessage: You must specify a name for the write-back template using the name attribute in the WebMessage element.

  For write back to work correctly, when enabling a table view for write back, a content designer must specify the name of the write-back template to be used to insert and update the records in the view.

  This example shows a write-back template called SetQuotaUseID.

  <WebMessage name="SetQuotaUseID">
  

• connectionPool: To meet security requirements, you must specify the connection pool along with the SQL commands to insert and update records. These SQL commands reference the values that are passed in the write-back schema to generate the SQL statements to modify the database table.

• VALUES: Column values can be referenced either by column ID or column position. The use of column ID is preferred.

  Surround string and date values with single quotes. Single quotes aren't required on numerical values.

  • Column ID - Each column ID is alphanumeric and randomly generated. You can find column IDs in the XML definition of the analysis that's available in the Advanced tab of the analysis editor. For example, column ID values such as: @{c5f6e60e1d6eb1098}, @{c3a93e65731210ed1}, '@{c6b8735ea60ff3011}'

    When you use column IDs, write-back continues to work even when the order of columns change.

    

  • Column position - Column positions start numbering with 1. For example, column position values such as: @1, @3, '@5'

    If the order of columns changes, write back no longer works and this is the reason why column IDs are preferred.

• You must include both an <insert> and an <update> element in the template. If you don't want to include SQL commands within the elements, then you must insert a blank space between the opening and closing tags. For example, you must enter the element as:

  <insert> </insert>
  

  Rather than:

  <insert></insert>
  

  If you omit the blank space, then you see a write-back error message such as "The system can't read the Write Back Template 'my_template'".

• If a parameter's data type isn't an integer or real number, then add single quotation marks around it. If the database doesn't do Commits automatically, then add the optional postUpdate node after the insert and update nodes to force the commit. The postUpdate node typically follows this example:

  <postUpdate>COMMIT</postUpdate>
  

Example Write-Back Template File Using Column ID Syntax

A write-back template file that references values by column ID might resemble this example:

<?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1">
<WebMessageTable lang="en-us" system="WriteBack" table="Messages">
   <WebMessage name="SetQuotaUseID">
      <XML>
         <writeBack connectionPool="Supplier">
            <insert>INSERT INTO regiontypequota VALUES(@{c5f6e60e1d6eb1098},@{c5d7e483445037d9e},'@{c3a93e65731210ed1}','@{c6b8735ea60ff3011}',@{c0432jkl53eb92cd8})</insert>
            <update>UPDATE regiontypequota SET Dollars=@{c0432jkl53eb92cd8} WHERE YR=@{c5f6e60e1d6eb1098} AND Quarter=@{c5d7e483445037d9e} AND Region='@{c3a93e65731210ed1}' AND ItemType='@{c6b8735ea60ff3011}'</update>
         </writeBack>
      </XML>
   </WebMessage>
</WebMessageTable>
</WebMessageTables>

Example Write-Back Template File Using Column Position Syntax

A write-back template file that references values by column position might resemble this example:

<?xml version="1.0" encoding="utf-8" ?>
<WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1">
<WebMessageTable lang="en-us" system="WriteBack" table="Messages">
   <WebMessage name="SetQuota">
      <XML>
         <writeBack connectionPool="Supplier">
            <insert>INSERT INTO regiontypequota VALUES(@1,@2,'@3','@4',@5)</insert>
            <update>UPDATE regiontypequota SET Dollars=@5 WHERE YR=@1 AND Quarter=@2 AND Region='@3' AND ItemType='@4'</update>
         </writeBack>
      </XML>
   </WebMessage>
</WebMessageTable>
</WebMessageTables>