13 Managing Data in Non-Asset Tables

This chapter describes how to interact with WebCenter Sites database tables that do not hold assets.

There are two ways to work with the data in your custom, non-asset tables:

• Programmatically, using the tags and methods for the CatalogManager API to code forms for data entry and management

• Manually, by using the Oracle WebCenter Sites Explorer tool or the Content form in the WebCenter Sites Management Tools to manually add rows and data to those rows.

To work with assets, you must log in to the WebCenter Sites interface and use the asset forms provided by the WebCenter Sites and Engage applications.

To add large numbers of assets programmatically, use the XMLPost utility, as described in Chapter 20, "Importing Assets of Any Type" and Chapter 21, "Importing Flex Assets."

This chapter contains the following sections:

• Section 13.1, "Methods and Tags"

• Section 13.2, "Coding Data Entry Forms"

• Section 13.3, "Managing the Data Manually"

• Section 13.4, "Deleting Non-Asset Tables"

13.1 Methods and Tags

This section provides an overview of the tags and methods that you use to program how you manage data in non-asset tables and how you interact with those tables in general.

This section contains the following topics:

• Section 13.1.1, "Writing and Retrieving Data"

• Section 13.1.2, "Querying for Data"

• Section 13.1.3, "Lists and Listing Data"

13.1.1 Writing and Retrieving Data

CatalogManager is the WebCenter Sites servlet that manages content and object tables in the database and the TreeManager servlet manages tree tables in the database.

• To access the CatalogManager servlet, you can use the ics.CatalogManager Java method, the CATALOGMANAGER XML tag, or the ics:catalogmanager JSP tag.

• To access the TreeManager servlet, you can use the ics.TreeManager Java method, the TREEMANAGER XML tag, or the ics:treemanager JSP tag.

These methods and tags take name/value pairs from arguments that specify the operation to perform and the table to perform that operation on.

13.1.1.1 CatalogManager

The ics.CatalogManager java method, the CATALOGMANAGER XML tag, and the ics:catalogmanager JSP tag support a number of attributes that operate on object and content tables. The key attribute is ftcmd. By setting ftcmd to addrow, for example, you tell CatalogManager to add one row to the catalog.

CatalogManager security, when enabled, prevents users with the DefaultReader ACL from accessing CatalogManager. You enable CatalogManager security by setting the secure.CatalogManager property, found in the futuretense.ini file, to true. Note that your session will be dropped if you attempt to log out of CatalogManager when CatalogManager security is enabled.

These are the main CATALOGMANAGER XML tag's attributes, passed as argument name/value pairs, that modify the contents of a row or a particular field in a row:

Table 13-1 CATALOGMANAGER XML Tag

argument name="ftcmd" value=	Description
addrow	Adds a single row to a table.
addrows	Adds more than one row to a table.
deleterow	Deletes a row from a table. You must specify the primary key column for the row.
deleterows	Deletes more than one row from a table. You must specify the primary key for the rows.
replacerow	Deletes the existing row in a table and replaces the row with the specified information.
replacerows	Replaces multiple rows in a table. If a value is not specified for a column, the column value is cleared
updaterow	Performs a query against a given table and displays records from a table. The rows displayed match the criteria specified by the value of the parameters.
updaterow2	Like updaterow, updates values in columns for a row in a table; however, where you cannot clear columns with updaterow, updaterow2 lets you clear columns if there is no value for the specified column (for example, if there is no related field in the form).
updaterows	Modifies field values for multiple rows in a table.
updaterows2	Like updaterows, modifies field values for multiple rows in a table; however, where you cannot clear columns with updaterows, updaterows2 lets you clear columns if there is no value for the specified column (for example, if there is no related field in the form).

Any requests going to CatalogManager with the command parameter (ftcmd) must be either a POST request or one of the following commands:

• exportlog

• exportForm

• logout

• selectFromTable

• selectCount

• mirrorgetconfig

• listtables

• retrieve

• retrievebinary

• pingdb

• interrogatetbl

• checksession

• history

• retrieverevision

For more information and a complete list of the CatalogManager commands, see the Oracle Fusion Middleware WebCenter Sites Tag Reference. For information about the ics.CatalogManager Java method, see the Oracle Fusion Middleware WebCenter Sites Java API Reference.

13.1.1.2 Tree Manager

Here are the main ics.TreeManager commands. Note that these operations manipulate data in the tree table only, but do not affect the objects that the tree table nodes refer to.

Table 13-2 TreeManager Commands

Name	Description
addchild	Given a parent node, add a child node.
addchildren	Add multiple child nodes.
copychild	Copy a node and its children to a different parent. All copied nodes point to the same objects.
createtree	Create a tree table.
delchild	Delete a node and its child nodes.
delchildren	Delete multiple nodes.
deletetree	Delete a tree table.
findnode	Find a node in a tree.
getchildren	Get all child nodes.
getnode	Get node and optionally object attributes.
getparent	Get the nodes parent.
listtrees	Get the list of all tree tables.
movechild	Move node and its child nodes to a different parent.
nodepath	Return parent; child path to a node.
setobject	Associate a different object with the node.
validatenode	Verify that a node is in a tree.
verifypath	Verify that a given path exists in a tree.

For information about the ics.TreeManager method, see the Oracle Fusion Middleware WebCenter Sites Java API Reference.

For information about the XML and JSP TREEMANAGER tags, see the Oracle Fusion Middleware WebCenter Sites Tag Reference.

13.1.2 Querying for Data

There are three methods, with XML and JSP tag counterparts, to help your code query for and select content:

Table 13-3 Querying for Data

Method	XML tag	JSP tag	Description
ics.SelectTo	SELECTTO	ics:selectto	Performs a simple select against a single table.
ics.SQL	EXECSQL	ics:sql	Executes an inline SQL statement (embedded in the code).
ics.CallSQL	CALLSQL	ics:callsql	Executes a SQL statement that is stored as a row in the SystemSQL table.

To use ics.CallSQL (or the tags), you code SQL statements and then paste them into the SystemSQL table.

By storing the actual queries in the SystemSQL table and calling them from the individual pages (like you call a pagename or an element), you keep them out of your code, which makes it easier to maintain the SQL used by your site. If you want to change the SQL, you do not have to fix it in every place that you use it, you can just edit it in the SystemSQL table and every element that calls it now calls the edited version.

The ics.CallSQL and ics.SQL methods can execute any legal SQL commands. If a SQL statement does not return a usable list, WebCenter Sites will generate an error. If you choose to use SQL to update or insert data, you must include code that explicitly flushes the resultsets cached against the appropriate tables using the ics.FlushCatalog method.

13.1.3 Lists and Listing Data

A number of ICS methods create lists. The SelectTo method, for example, returns the results of a simple SQL query in a list whose columns reflect the items in the WHAT clause and whose rows reflect matches against the table.

The IList interface can be used to access a list from Java. The lists are available by name using XML or JSP, and values can be iterated using the LOOP tag.

The lists created by WebCenter Sites point to underlying resultsets created from querying the database. Although the lists do not persist across requests, the resultsets do because if are cached.

Note:

Be sure to configure resultset caching appropriately. If the resultset of a query is cached, the list points to a copy of the resultset. If the resultset is not cached, the list points directly at the resultset which can cause database connection resource difficulties.

You can create your own list for use in XML or JSP by implementing a class based on the IList interface. Then your application or page can transform data prior to returning an item in a list or to create a single list from many lists.

The following methods manage lists:

Table 13-4 Methods that Manage Lists

Method	Description
ics.GetList	Returns an IList, given the name of the list.
ics.CopyList	Copies a list.
ics.RenameList	Renames an existing list.
ics.RegisterList	Registers a list by name with WebCenter Sites so that you can reference the list from an XML or JSP element or by using the GetList method.

For an example implementation of an IList, see SampleIList.java in the Samples folder on your WebCenter Sites system.

13.2 Coding Data Entry Forms

This section provides code samples that illustrate how to code forms that accept information entered by a user or visitor and to then write that information to the database using the WebCenter Sites methods and tags.

The examples in this section describe adding a new row, deleting a row, and querying for and then editing an existing row. Each example shows a version for XML, JSP, and Java.

This section contains the following topics:

• Section 13.2.1, "Adding a Row"

• Section 13.2.2, "Deleting a Row"

• Section 13.2.3, "Querying a Table"

• Section 13.2.4, "Querying a Table with an Embedded SQL Statement"

13.2.1 Adding a Row

A simple algorithm for adding a row is as follows:

1. Display a form requesting information for each of the fields in a row.

2. Write that form data to the table.

The following example adds a row to a fictitious table named EmployeeInfo. This table has the following columns:

Table 13-5 Example Adds Row to Table

Field	Data type
id	VARCHAR(6)
phone	VARCHAR(16)
name	VARCHAR(32)

This example presents code from the following elements:

• addrowFORM, an XML element that displays a form that requests an employee ID number, phone number, and name.

• addrowXML, addrowJSP, and addrowJAVA, three versions of an element that writes the information entered by the employee to the EmployeeInfo table

13.2.1.1 The addrowFORM Element

The addrowFORM element displays a form that asks the user to enter information. It looks like this:

Description of the illustration g-addrowform.gif

This is the code that creates the form:

<?xml version="1.0" ?>
<!DOCTYPE FTCS SYSTEM "futuretense_cs.dtd">
<FTCS Version="1.1">
<!-- Documentation/CatalogManager/addrowFORM
-->

<form ACTION="ContentServer" method="post"
REPLACEALL="CS.Property.ft.cgipath">
<input type="hidden" name="pagename" value="Documentation/CatalogManager/addrow"/>

<table>
<tr>
<td>Employee name:</td>
<td><input type="text" value="" name="EmployeeName" size="22" maxlength="32"/></td>
</tr>
<tr>
<td>Employee id number:</td>
<td><input type="text" value="" name="EmployeeID" size="6" maxlength="6"/></td>
</tr>
<tr>
<td>Phone number:</td>
<td><input type="text" value="" name="EmployeePhone" size="12" maxlength="16"/></td>
</tr>
<tr>
<td colspan="2"><input type="submit" name="submit" value="Submit"/></td>
</tr>
</table>

</form>
</FTCS>

Notice that the maxlength modifiers in <INPUT> limit the length of each input to the maximum length that was defined in the schema.

The user fills in the form and clicks the Submit button. The information gathered in the form and the pagename of the addrow page (see the first input type statement in the preceding code sample) is sent to the browser. The browser sends the pagename to WebCenter Sites. WebCenter Sites looks it up in the SiteCatalog table and then invokes that page entry's root element.

13.2.1.2 Root Element for the addrow Page

The root element of the addrow page is responsible for adding the information passed from the addrowFORM element to the database. That is, for adding a row to the EmployeeInfo table and populating that row with the information passed from the addrowFORM element.

There can only be one root element for a WebCenter Sites page (that is, an entry in the SiteCatalog table). This section shows three versions of the root element for the addrow page:

• addrowXML.xml

• addrowJSP.jsp

• addrowJAVA.jsp

addrowXML

This is the code in the XML version of the root element:

<?xml version="1.0" ?>
<!DOCTYPE FTCS SYSTEM "futuretense_cs.dtd">
<FTCS Version="1.1">
<!-- Documentation/CatalogManager/addrowXML
-->

<SETVAR NAME="errno" VALUE="0"/>
<CATALOGMANAGER>

<ARGUMENT NAME="ftcmd" VALUE="addrow"/>
<ARGUMENT NAME="tablename" VALUE="EmployeeInfo"/>
<ARGUMENT NAME="id" VALUE="Variables.EmployeeID"/>
<ARGUMENT NAME="phone" VALUE="Variables.EmployeePhone"/>
<ARGUMENT NAME="name" VALUE="Variables.EmployeeName"/>

</CATALOGMANAGER>
errno=<CSVAR NAME="Variables.errno"/><br/>
</FTCS>

Note:

The example code can use the CATALOGMANAGER tag because the fictitious table, EmployeeInfo, has WebCenter Sites generic field types. addrowXML might not work if EmployeeInfo has database-specific field types. For more information, see Section 12.2.1, "Generic Field Types."

addrowJSP

This is the code in the JSP version of the root element:

<%@ taglib prefix="cs" uri="futuretense_cs/ftcs1_0.tld" %>
<%@ taglib prefix="ics" uri="futuretense_cs/ics.tld" %>
<%//
// Documentation/CatalogManager/addrowJSP
//%>
<%@ page import="COM.FutureTense.Interfaces.*" %>
<%@ page import="COM.FutureTense.Util.ftMessage"%>
<%@ page import="COM.FutureTense.Util.ftErrors" %>
<cs:ftcs>

<ics:setvar name="errno" value="0"/>
<ics:catalogmanager>

<ics:argument name="ftcmd" value="addrow"/>
<ics:argument name="tablename" value="EmployeeInfo"/>
<ics:argument name="id"
value='<%=ics.GetVar("EmployeeID")%>'/>
<ics:argument name="phone" 
value='<%=ics.GetVar("EmployeePhone")%>'/>
<ics:argument name="name" value='<%=ics.GetVar("EmployeeName")%>'/>

</ics:catalogmanager>

errno=<ics:getvar name="errno"/><br/>

</cs:ftcs>

addrowJAVA

This is the code in the Java version of the root element:

<%@ taglib prefix="cs" uri="futuretense_cs/ftcs1_0.tld" %>
<%//
// Documentation/CatalogManager/addrowJAVA
//%>
<%@ page import="COM.FutureTense.Interfaces.*" %>
<%@ page import="COM.FutureTense.Util.ftMessage"%>
<%@ page import="COM.FutureTense.Util.ftErrors" %>
<cs:ftcs>

<!-- user code here -->
<%
ics.SetVar("errno","0");
FTValList vl = new FTValList();
vl.put("ftcmd","addrow");
vl.put("tablename","EmployeeInfo");
vl.put("id",ics.GetVar("EmployeeID"));
vl.put("phone",ics.GetVar("EmployeePhone"));
vl.put("name",ics.GetVar("EmployeeName"));
ics.CatalogManager(vl);
%>
errno=<%=ics.GetVar("errno")%><br />

</cs:ftcs>

13.2.2 Deleting a Row

The following example deletes a row from the fictitious EmployeeInfo table as described in Section 13.2.1, "Adding a Row."

This section presents code from the following elements:

• deleterowFORM, an XML element that displays a form that requests an employee name to delete from the EmployeeInfo table

• deleterowXML, deleterowJSP, and deleterowJAVA, elements that delete a row from the EmployeeInfo table based on the information sent to it from the deleterowFORM element

13.2.2.1 The deleterowFORM Element

The deleterowFORM element displays a form that asks the user to enter an employee name. This is the code that creates the form:

<?xml version="1.0" ?>
<!DOCTYPE FTCS SYSTEM "futuretense_cs.dtd">
<FTCS Version="1.1">
<!-- Documentation/CatalogManager/deleterowFORM
-->

<form ACTION="ContentServer" method="post" REPLACEALL="CS.Property.ft.cgipath">
<input type="hidden" name="pagename" value="Documentation/CatalogManager/deleterow"/>

<table>
<tr>

<td>Employee name:</td>
<td><input type="text" value="Barton Fooman" name="EmployeeName" size="22" maxlength="32"/></td>

</tr>
<tr>

<td colspan="2"><input type="submit" name="submit" value="submit"/></td>

</tr>
</table>
</form>
</FTCS>

The user enters an employee name and clicks the Submit button. The employee name and the pagename for the deleterow page (see the first input type statement in the preceding code sample) are sent to the browser.

The browser sends the pagename to WebCenter Sites. WebCenter Sites looks it up in the SiteCatalog table and then invokes that page entry's root element.

13.2.2.2 Root Element for the deleterow Page

The root element of the deleterow page is responsible for deleting a row from the EmployeeInfo table, based on the employee name that is sent to it from the deleterowFORM element.

There can only be one root element for a WebCenter Sites page (that is, an entry in the SiteCatalog table). This section shows three versions of the root element for the deleterow page:

• deleterowXML.xml

• deleterowJSP.jsp

• deleterowJAVA.jsp

deleterowXML

This is the code in the XML version of the element:

<?xml version="1.0" ?>
<!DOCTYPE FTCS SYSTEM "futuretense_cs.dtd">
<FTCS Version="1.1">
<!-- Documentation/CatalogManager/deleterowXML
-->
<SETVAR NAME="errno" VALUE="0"/>

<CATALOGMANAGER>

<ARGUMENT NAME="ftcmd" VALUE="deleterow"/>
<ARGUMENT NAME="tablename" VALUE="EmployeeInfo"/>
<ARGUMENT NAME="tablekey" VALUE="name"/>
<ARGUMENT NAME="tablekeyvalue" VALUE="Variables.EmployeeName"/>

</CATALOGMANAGER>

errno=<CSVAR NAME="Variables.errno"/><br/>
</FTCS>

deleterowJSP

This is the code in the JSP version of the element:

<%@ taglib prefix="cs" uri="futuretense_cs/ftcs1_0.tld" %>
<%@ taglib prefix="ics" uri="futuretense_cs/ics.tld" %>
<%//
// Documentation/CatalogManager/deleterowJSP
//%>
<%@ page import="COM.FutureTense.Interfaces.*" %>
<%@ page import="COM.FutureTense.Util.ftMessage"%>
<%@ page import="COM.FutureTense.Util.ftErrors" %>
<cs:ftcs>

<!-- user code here -->
<!-- user code here -->
<ics:setvar name="errno" value="0"/>
<ics:catalogmanager>

<ics:argument name="ftcmd" value="deleterow"/>
<ics:argument name="tablename" value="EmployeeInfo"/>
<ics:argument name="name" value='<%=ics.GetVar("EmployeeName")%>'/>

</ics:catalogmanager>

errno=<ics:getvar name="errno"/><br />

</cs:ftcs>

deleterowJAVA

This is the code in the Java version of the element:

<%@ taglib prefix="cs" uri="futuretense_cs/ftcs1_0.tld" %>
<%//
// Documentation/CatalogManager/deleterowJAVA
//%>
<%@ page import="COM.FutureTense.Interfaces.*" %>
<%@ page import="COM.FutureTense.Util.ftMessage"%>
<%@ page import="COM.FutureTense.Util.ftErrors" %>
<cs:ftcs>

<%
ics.SetVar("errno","0");
FTValList vl = new FTValList();
vl.put("ftcmd","deleterow");
vl.put("tablename","EmployeeInfo");
vl.put("name",ics.GetVar("EmployeeName"));
ics.CatalogManager(vl);
%>
errno=<%=ics.GetVar("errno")%><br />

</cs:ftcs>

13.2.3 Querying a Table

The following sample elements query the fictitious EmployeeInfo table for an employee's name, extract the employee name and displays it in a browser, prompts the user to edit the information, and then writes the edited information to the database.

This section presents code from the following elements:

• SelectNameForm, an XML element that displays a form that requests an employee's name.

• Three versions of the QueryEditRowForm element (XML, JSP, and Java), an element that locates the employee name and loads the information about that employee into a form that the employee can use to edit his or her information

• Three versions of the QueryEditRow element (XML, JSP, and Java), an element that writes the newly edited information to the database.

13.2.3.1 The SelectNameForm Element

The SelectNameForm element displays a simple form that requests the name of the employee who is altering his employee information. This is the code:

<?xml version="1.0" ?>
<!DOCTYPE FTCS SYSTEM "futuretense_cs.dtd">
<FTCS Version="1.1">
<!-- Documentation/CatalogManager/SelectNameForm
-->

<form ACTION="ContentServer" method="post">
<input type="hidden" name="pagename" value="Documentation/CatalogManager/QueryEditRowForm"/>
<TABLE>
<TR>
<TD>Employee name: </TD>
<TD><INPUT type="text" value="" name="EmployeeName" size="22" maxlength="32"/></TD>
</TR>
<TR>
<TD COLSPAN="100%" ALIGN="CENTER">
<input type="submit" name="doit" value="Submit"/></TD>
</TR>
</TABLE>
</form>
</FTCS>

When the employee clicks the Submit button, the information gathered in the Employee Name field and the name of the QueryEditRowForm page (see the first input type statement in the preceding code sample) is sent to the browser.

The browser sends the pagename to WebCenter Sites. WebCenter Sites looks up the pagename in the SiteCatalog table, and then invokes that page entry's root element, QueryEditRowForm.

13.2.3.2 The Root Element for the QueryEditRowForm Page

The root element for the QueryEditRowForm page locates the row in the EmployeeInfo table that matches the string entered in the Employee Name field and then loads the data from that row into a new form. The employee can edit her name and phone number but cannot edit her id. The form looks like this:

Description of the illustration g-queryeditrowform.gif

There can only be one root element for a WebCenter Sites page (that is, an entry in the SiteCatalog table). This section shows three versions of the root element for the QueryEditRowForm page:

• QueryEditRowFormXML.xml

• QueryEditRowFormJSP.jsp

• QueryEditRowFormJAVA.jsp

QueryEditRowFormXML

This is the code in the XML version of the element:

<?xml version="1.0" ?>
<!DOCTYPE FTCS SYSTEM "futuretense_cs.dtd">
<FTCS Version="1.1">
<!-- Documentation/CatalogManager/QueryEditRowFormXML
-->

<SETVAR NAME="errno" VALUE="0"/>
<SETVAR NAME="name" VALUE="Variables.EmployeeName"/>
<SELECTTO FROM="EmployeeInfo"
WHERE="name"
WHAT="*"
LIST="MatchingEmployees"/>

<IF COND="Variables.errno=0">
<THEN>
<form ACTION="ContentServer" method="post">
<input type="hidden" name="pagename" value="Documentation/CatalogManager/QueryEditRow"/>
<input type="hidden" name="MatchingID" value="MatchingEmployees.id" REPLACEALL="MatchingEmployees.id"/>
<TABLE>
<TR>
<TD COLSPAN="100%" ALIGN="CENTER">
<H3>Change Employee Information</H3>
</TD>
</TR>
<TR>
<TD>Employee id number: </TD>
<TD><CSVAR NAME="MatchingEmployees.id"/></TD>
</TR>
<TR>
<TD>Employee name: </TD>
<TD><INPUT type="text" value="MatchingEmployees.name" name="NewEmployeeName" size="22" maxlength="32" REPLACEALL="MatchingEmployees.name"/></TD>
</TR>
<TR>
<TD>Phone number: </TD>
<TD><INPUT type="text" value="MatchingEmployees.phone" name="NewEmployeePhone" size="12" maxlength="16" REPLACEALL="MatchingEmployees.phone"/></TD>
</TR>
<TR>
<TD colspan="100%" align="center">
<input type="submit" name="doit" value="Change"/></TD>
</TR>
</TABLE>
</form>
</THEN>
<ELSE>
<P>Could not find this employee.</P>
<CALLELEMENT NAME="Documentation/CatalogManager/SelectNameFormXML"/>
</ELSE>
</IF>
</FTCS>

When the employee clicks the Change button, the information gathered from the two fields and the name of the QueryEditRow page is sent to the browser.

The browser sends the pagename and the field information to WebCenter Sites. WebCenter Sites looks up the pagename in the SiteCatalog table, and then invokes that page entry's root element.

QueryEditRowFormJSP

This is the code in the JSP version of the element:

<%@ taglib prefix="cs" uri="futuretense_cs/ftcs1_0.tld" %>
<%@ taglib prefix="ics" uri="futuretense_cs/ics.tld" %>
<%//
// Documentation/CatalogManager/QueryEditRowFormJSP
//%>
<%@ page import="COM.FutureTense.Interfaces.*" %>
<%@ page import="COM.FutureTense.Util.ftMessage"%>
<%@ page import="COM.FutureTense.Util.ftErrors" %>
<cs:ftcs>

<ics:setvar name="errno" value="0"/>
<ics:setvar name="name" value='<%=ics.GetVar("EmployeeName")%>'/>
<ics:selectto table="EmployeeInfo"
where="name"
what="*"
listname="MatchingEmployees"/>

<ics:if condition='<%=ics.GetVar("errno").equals("0")%>'>
<ics:then>
<form action="ContentServer" method="post">
<input type="hidden" name="pagename" value="Documentation/CatalogManager/QueryEditRow"/>
<input type="hidden" name="MatchingID" value="<ics:listget listname='MatchingEmployees' fieldname='id'/>"/>
<TABLE>
<TR>
<TD COLSPAN="100%" ALIGN="CENTER">
<H3>Change Employee Information</H3>
</TD>
</TR>
<TR>
<TD>Employee id number: </TD>
<TD><ics:listget listname='MatchingEmployees
fieldname='id'/></TD>
</TR>
<TR>
<TD>Employee name: </TD>
<TD><INPUT type="text" value="<ics:listget
listname='MatchingEmployees' fieldname='name'/>"
name="NewEmployeeName" size="22" maxlength="32"/></TD>
</TR>
<TR>
<TD>Phone number: </TD>
<TD><INPUT type="text" value="<ics:listget
listname='MatchingEmployees' fieldname='phone'/>"
name="NewEmployeePhone" size="12" maxlength="16"/>
</TD>
</TR>
<TR>
<TD colspan="100%" align="center">
<input type="submit" name="doit" value="Change"/></TD>
</TR>
</TABLE>
</form>
</ics:then>
<ics:else>
<P>Could not find this employee.</P>
<ics:callelement element="Documentation/CatalogManager/
SelectNameForm"/>
</ics:else>
</ics:if>

</cs:ftcs>

When the employee clicks the Change button, the information gathered from the two fields and the name of the QueryEditRow page is sent to the browser.

The browser sends the pagename and the field information to WebCenter Sites. WebCenter Sites looks up the pagename in the SiteCatalog table, and then invokes that page entry's root element.

QueryEditRowFormJAVA

This is the code in the Java version of the element:

<%@ taglib prefix="cs" uri="futuretense_cs/ftcs1_0.tld" %>
<%//
// Documentation/CatalogManager/QueryEditRowFormJAVA
//%>
<%@ page import="COM.FutureTense.Interfaces.*" %>
<%@ page import="COM.FutureTense.Util.ftMessage"%>
<%@ page import="COM.FutureTense.Util.ftErrors" %>
<cs:ftcs>

<!-- user code here -->
<%
ics.SetVar("errno","0");
ics.SetVar("name",ics.GetVar("EmployeeName"));
StringBuffer errstr = new StringBuffer();
IList matchingEmployees = ics.SelectTo("EmployeeInfo",// tablename
*", // what
"name", // where
"name", // orderby
1, // limit
null, // ics list name
true, // cache?
errstr); // error StringBuffer

if ("0".equals(ics.GetVar("errno")) && matchingEmployees!=null && matchingEmployees.hasData())
{
%>
<form action="ContentServer" method="post">
<input type="hidden" name="pagename" value="Documentation/CatalogManager/QueryEditRow"/>
<%
String id = matchingEmployees.getValue("id");
String name = matchingEmployees.getValue("name");
String phone = matchingEmployees.getValue("phone");
%>
<input type="hidden" name="MatchingID" value="<%=id%>"/>
<TABLE>
<TR>
<TD COLSPAN="100%" ALIGN="CENTER">
<H3>Change Employee Information</H3>
</TD>
</TR>
<TR>
<TD>Employee id number: </TD>
<TD><%=id%></TD>
</TR>
<TR>
<TD>Employee name: </TD>
<TD><INPUT type="text" value="<%=name%>" name="NewEmployeeName" size="22" maxlength="32"/></TD>
</TR>
<TR>
<TD>Phone number: </TD>
<TD><INPUT type="text" value="<%=phone%>" name="NewEmployeePhone" size="12" maxlength="16"/></TD>
</TR>
<TR>
<TD colspan="100%" align="center">
<input type="submit" name="doit" value="Change"/></TD>
</TR>
</TABLE>
</form>
<%
}
else
{
%><P>Could not find this employee.</P>
<%
ics.CallElement("Documentation/CatalogManager/SelectNameForm",null);
}
%>
</cs:ftcs>

When the employee clicks the Change button, the information gathered from the two fields and the name of the QueryEditRow page is sent to the browser.

The browser sends the pagename and the field information to WebCenter Sites. WebCenter Sites looks up the pagename in the SiteCatalog table, and then invokes that page entry's root element.

13.2.3.3 The Root Element for the QueryEditRow Page

The root element for the QueryEditRow page writes the information that the employee entered into the Employee Name and Phone number fields and updates the row in the database.

There can only be one root element for a WebCenter Sites page (that is, an entry in the SiteCatalog table). This section shows three versions of the root element for the QueryEditRow page:

• QueryEditRowXML.xml

• QueryEditRowJSP.jsp

• QueryEditRowJAVA.jsp

QueryEditRowXML

This is the code in the XML version of the element:

<?xml version="1.0" ?>
<!DOCTYPE FTCS SYSTEM "futuretense_cs.dtd">
<FTCS Version="1.1">
<!-- Documentation/CatalogManager/QueryEditRowXML
-->

<SETVAR NAME="errno" VALUE="0"/>

<CATALOGMANAGER>
<ARGUMENT NAME="ftcmd" VALUE="updaterow"/>
<ARGUMENT NAME="tablename" VALUE="EmployeeInfo"/>
<ARGUMENT NAME="id" VALUE="Variables.MatchingID"/>
<ARGUMENT NAME="name" VALUE="Variables.NewEmployeeName"/>
<ARGUMENT NAME="phone" VALUE="Variables.NewEmployeePhone"/>
</CATALOGMANAGER>

<IF COND="Variables.errno=0">
<THEN>
<P>Successfully updated the database.</P>
</THEN>
<ELSE>
<P>Failed to update the information in the database.</P>
</ELSE>
</IF>
</FTCS>

QueryEditRowJSP

This is the code in the JSP version of the element:

<%@ taglib prefix="cs" uri="futuretense_cs/ftcs1_0.tld" %>
<%@ taglib prefix="ics" uri="futuretense_cs/ics.tld" %>
<%//
// Documentation/CatalogManager/QueryEditRowJSP
//%>
<%@ page import="COM.FutureTense.Interfaces.*" %>
<%@ page import="COM.FutureTense.Util.ftMessage"%>
<%@ page import="COM.FutureTense.Util.ftErrors" %>
<cs:ftcs>

<ics:setvar name="errno" value="0"/>

<ics:catalogmanager>
<ics:argument name="ftcmd" value="updaterow"/>
<ics:argument name="tablename" value="EmployeeInfo"/>
<ics:argument name="id" value="<%=ics.GetVar("MatchingID")%>"/>
<ics:argument name="name" 
value='<%=ics.GetVar("NewEmployeeName")%>'/>
<ics:argument name="phone"
value='<%=ics.GetVar("NewEmployeePhone")%>'/>
</ics:catalogmanager>

<ics:if condition='<%=ics.GetVar("errno").equals("0")%>'>
<ics:then>
<P>Successfully updated the database.</P>
</ics:then>
<ics:else>
<p>failed to update the information in the database. errno=<ics:getvar name='errno'/></p>
</ics:else>
</ics:if>

</cs:ftcs>

QueryEditRowJAVA

This is the code in the Java version of the element:

<%@ taglib prefix="cs" uri="futuretense_cs/ftcs1_0.tld" %>
<%//
// Documentation/CatalogManager/QueryEditRowJAVA
//%>
<%@ page import="COM.FutureTense.Interfaces.*" %>
<%@ page import="COM.FutureTense.Util.ftMessage"%>
<%@ page import="COM.FutureTense.Util.ftErrors" %>
<cs:ftcs>

<!-- user code here -->
<%
ics.SetVar("errno","0");

FTValList args = new FTValList();
args.put("ftcmd","updaterow");
args.put("tablename","EmployeeInfo");
args.put("id",ics.GetVar("MatchingID"));
args.put("name",ics.GetVar("NewEmployeeName"));
args.put("phone",ics.GetVar("NewEmployeePhone"));

ics.CatalogManager(args);

if("0".equals(ics.GetVar("errno")))
{
%><P>Successfully updated the database.</P><%
}
else
{
%><p>failed to update the information in the database. errno=<ics:getvar name='errno'/></p><%
}
%>
</cs:ftcs>

13.2.4 Querying a Table with an Embedded SQL Statement

The following example shows another method of searching for a name in a table. This example also searches the fictitious EmployeeInfo table, returning the rows that match the string supplied by a user, but this time the code uses a SQL query rather than a SELECTTO statement.

This section presents code from the following elements:

• QueryInlineSQLForm, an XML element that displays a form that requests a movie title

• Three versions of the QueryInlineSQL element (XML, JSP, and Java), an element that searches the EmployeeInfo table for names that contain the string entered by the user in the preceding form

13.2.4.1 QueryInlineSQLForm

The QueryInlineSQL element displays a simple form that requests the name to use to search the EmployeeInfo table for. This is the code:

<?xml version="1.0" ?>
<!DOCTYPE FTCS SYSTEM "futuretense_cs.dtd">
<FTCS Version="1.1">
<!-- Documentation/CatalogManager/QueryInlineSQLForm
-->
<form ACTION="ContentServer" method="post">
<input type="hidden" name="pagename" value="Documentation/CatalogManager/QueryInlineSQL"/>

<table>
<tr>
<td>Employee Name:</td>
<td><input type="text" value="Foo,Bar" name="EmployeeName" size="22" maxlength="32"/></td>
</tr>
<tr>
<td colspan="2"><input type="submit" name="submit" value="submit"/></td>
</tr>
</table>

</form>
</FTCS>

When the user clicks the Submit button, the information gathered in the Employee Name field and the name of the QueryInlineSQL page is sent to the browser.

The browser sends the pagename of the QueryInlineSQL page to WebCenter Sites. WebCenter Sites looks up the pagename in the SiteCatalog table, and then invokes that page entry's root element.

13.2.4.2 The Root Element for the QueryInlineSQL Page

The root element for the QueryInlineSQL page executes an inline SQL statement that searches the EmployeeInfo table for entries that match the string sent to it from the QueryInlineSQLForm element.

There can only be one root element for a WebCenter Sites page (that is, an entry in the SiteCatalog table). This section shows three versions of the root element for the QueryInlineSQL page:

• QueryInlineSQLXML.xml,which uses the EXECSQL XML tag to create the SQL query

• QueryInlineSQLJSP.jsp,which uses the ics:sql JSP tag to create the SQL query

• QueryInlineSQLJAVA.jsp,which uses the ics.CallSQL Java method to create the SQL query

QueryInlineSQLXML

This is the code in the XML version of the element:

<!DOCTYPE FTCS SYSTEM "futuretense_cs.dtd">
<FTCS Version="1.1">
<!-- Documentation/CatalogManager/QueryInlineSQLXML
-->

<SETVAR NAME="tablename" VALUE="EmployeeInfo"/>

<SQLEXP OUTSTR="MySQLExpression"
TYPE="OR"
VERB="LIKE"
STR="Variables.EmployeeName"
COLNAME="name"/>

<EXECSQL
SQL="SELECT id,name,phone FROM Variables.tablename WHERE Variables.MySQLExpression"
LIST="ReturnedList"
LIMIT="5"/>

<table border="1" bgcolor="99ccff">
<tr>
<th>id</th>
<th>name</th>
<th>phone</th>
</tr>

<LOOP LIST="ReturnedList">
<tr>
<td><CSVAR NAME="ReturnedList.id"/></td>
<td><CSVAR NAME="ReturnedList.name"/></td>
<td><CSVAR NAME="ReturnedList.phone"/></td>
</tr>
</LOOP>
</table>

</FTCS>

Notice that the SQL statement is not actually embedded in the EXECSQL tag. Instead, a preceding SQLEXP tag creates a SQL expression which is passed as an argument to the EXECSQL call. The EXECSQL tag performs the search and returns the results to the list variable named ReturnedList.

Also notice that the first line of code in the body of the element creates a variable named tablename and sets the value to EmployeeInfo, the name of the table that is being queried. This enables CatalogManager to cache the resultset against the correct table.

QueryInlineSQLJSP

This is the code in the JSP version of the element:

<?xml version="1.0" ?>
<%@ taglib prefix="cs" uri="futuretense_cs/ftcs1_0.tld" %>
<%@ taglib prefix="ics" uri="futuretense_cs/ics.tld" %>
<%//
// Documentation/CatalogManager/QueryInlineSQLJSP
//%>
<%@ page import="COM.FutureTense.Interfaces.*" %>
<%@ page import="COM.FutureTense.Util.ftMessage"%>
<%@ page import="COM.FutureTense.Util.ftErrors" %>
<cs:ftcs>

<!-- user code here -->
<ics:setvar name="tablename" value="EmployeeInfo"/>

<%
// no ics:sqlexp tag, must do in java
String sqlexp = ics.SQLExp("EmployeeInfo","OR","LIKE",ics.GetVar("EmployeeName"),"name");
String sql = "SELECT id,name,phone FROM "+ics.GetVar("tablename")+" WHERE "+sqlexp;
%>
<ics:sqltable='<%=ics.GetVar("tablename")%>'
sql='<%=sql%>'
listname="ReturnedList"
limit="5"/>

<table border="1" bgcolor="99ccff">
<tr>
<th>id</th>
<th>name</th>
<th>phone</th>
</tr>

<ics:listloop listname="ReturnedList">
<tr>
<td><ics:listget listname="ReturnedList" fieldname="id"/></td>
<td><ics:listget listname="ReturnedList" fieldname="name"/></td>
<td><ics:listget listname="ReturnedList" fieldname="phone"/></td>
</tr>
</ics:listloop>

</table>

</cs:ftcs>

Notice that the SQL statement is not actually embedded in the ics:sql tag. Instead, a preceding Java expression creates a SQL expression that is passed as an argument to the ics:sqlcall. (The code example uses Java because there is no JSP equivalent of the SQLEXP tag.) The ics:sql tag performs the search and returns the results to the list variable named ReturnedList.

Also notice that the first line of code in the body of the element creates a variable named tablename and sets the value to EmployeeInfo, the name of the table that is being queried. This enables CatalogManager to cache the resultset against the correct table.

QueryInlineSQLJava

This is the code in the Java version of the element:

<%@ taglib prefix="cs" uri="futuretense_cs/ftcs1_0.tld" %>
<%//
// Documentation/CatalogManager/QueryInlineSQLJAVA
//%>
<%@ page import="COM.FutureTense.Interfaces.*" %>
<%@ page import="COM.FutureTense.Util.ftMessage"%>
<%@ page import="COM.FutureTense.Util.ftErrors" %>
<cs:ftcs>

<%

ics.SetVar("tablename","EmployeeInfo");

String sqlexp = ics.SQLExp(ics.GetVar("tablename"),"OR","LIKE",ics.GetVar("EmployeeName"),"name");
String sql = "SELECT id,name,phone FROM "+ics.GetVar("tablename")+" WHERE "+sqlexp;
StringBuffer errstr = new StringBuffer();

IList list = ics.SQL(ics.GetVar("tablename"),sql,null,5,true,errstr);

%>

<table border="1" bgcolor="99ccff">
<tr>
<th>id</th>
<th>name</th>
<th>phone</th>
</tr>

<%

while (true)
{
%>
<tr>
<td><%=list.getValue("id")%></td>
<td><%=list.getValue("name")%></td>
<td><%=list.getValue("phone")%></td>
</tr>
<%
if (list.currentRow() == list.numRows())
break;
list.moveTo(list.currentRow()+1);
}
%>

</table>
</cs:ftcs>

Notice that the SQL statement is not actually embedded in the ics.SQL statement. Instead, a preceding ics.SQLExp statement creates a SQL expression which is passed as an argument to the EXECSQL call. The ics.SQL statement performs the search and returns the results to the list variable named ReturnedList.

Also notice that this code also creates a variable named tablename and sets the value to EmployeeInfo (the name of the table that is being queried), before the code for the query. This enables CatalogManager to cache the resultset against the correct table.

13.3 Managing the Data Manually

You can add data to a table manually with either the Oracle WebCenter Sites Explorer tool or the forms in the WebCenter Sites Management Tools.

Oracle WebCenter Sites Explorer is the right choice in the following situations:

• If you are creating a page entry for a new page in the SiteCatalog table.

• If you are creating a row for an element in the ElementCatalog table and are coding that element with the editor in Oracle WebCenter Sites Explorer.

• If you need to add a small amount of data to a table that you have created to support some function of your site. That is, to add a small amount of data to a table that does not hold assets. (For example, to add rows to the MimeType table.)

Oracle WebCenter Sites Explorer has online help that you can use if you need information about adding, editing, or deleting rows. Additionally, Chapter 4, "Programming with Oracle WebCenter Sites" describes how to add page entries to the SiteCatalog table and elements to the ElementCatalog table.

The WebCenter Sites Management Tools are the right choice in the following situations:

• To add users or ACLs to the system.

• When you want to modify the cache settings for a page entry in the SiteCatalog table. Typically it is easier to complete this task in the ContentManagement form than it is to enter the information directly into the column using Oracle WebCenter Sites Explorer.

The WebCenter Sites Management Tools are documented in the Oracle Fusion Middleware WebCenter Sites Administrator's Guide.

13.4 Deleting Non-Asset Tables

Note that if you delete a non-asset table that is being revision tracked from the database, the tracking table will not be removed. To prevent this, be sure that you disable revision tracking for the table before deleting it.