Managing Data in Non-Asset Tables

12 Managing Data in Non-Asset Tables

How do you interact with WebCenter Sites database tables that do not hold assets? You can work with the data in your custom, non-asset tables programmatically and manually. You use tags and methods for the CatalogManager API to code forms for data entry and management. And, through the Explorer tool you 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 Oracle WebCenter Sites: Engage applications. To add large numbers of assets programmatically, use the XMLPost utility, as described in Importing Assets of Any Type and Importing Flex Assets.

Topics:

12.1 Using Methods and Tags to Program Data Management in Non-Asset Tables

Would you like to program how you manage and interact with non-asset tables? Java methods such as CatalogManager and TreeManager, and XML tags such as CATALOGMANAGER and TREEMANAGER are available to help you do just that.

See these topics:

12.1.1 About Writing and Retrieving Data

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

To access the CatalogManager servlet, use the ics.CatalogManager Java method, the CATALOGMANAGER XML tag, or the ics:catalogmanager JSP tag.
To access the TreeManager servlet, 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 on which to perform that operation.

12.1.1.1 Security Through CatalogManager

The ics.CatalogManager Java method, the CATALOGMANAGER XML tag, and the ics:catalogmanager JSP tag support several 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 wcs_properties.json file, to true. Note that your session will be dropped if you attempt to log out of CatalogManager when CatalogManager security is enabled.

The table below shows the main CATALOGMANAGER XML tag attributes. They are passed as argument name/value pairs that modify the contents of a row or a particular field in a row.

Table 12-1 CATALOGMANAGER XML Tag

argument name="ftcmd" value=	Description
`addrow`	Adds a single row to a table.
`addrows`	Adds multiple rows to a table.
`deleterow`	Deletes a row from a table. You must specify the primary key column for the row.
`deleterows`	Deletes multiple rows 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 them 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 Tag Reference for Oracle WebCenter Sites Reference. For information about the ics.CatalogManager Java method, see the Java API Reference for Oracle WebCenter Sites.

12.1.1.2 Tree Manager Commands for Managing the Tree Tables

The table below shows 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 12-2 TreeManager Commands

Name	Description
`addchild`	Given a parent node, adds a child node.
`addchildren`	Adds multiple child nodes.
`copychild`	Copies a node and its children to a different parent. All copied nodes point to the same objects.
`createtree`	Creates a tree table.
`delchild`	Deletes a node and its child nodes.
`delchildren`	Deletes multiple nodes.
`deletetree`	Deletes a tree table.
`findnode`	Finds a node in a tree.
`getchildren`	Gets all child nodes.
`getnode`	Gets node and optionally object attributes.
`getparent`	Gets the nodes parent.
`listtrees`	Gets the list of all tree tables.
`movechild`	Moves node and its child nodes to a different parent.
`nodepath`	Returns parent; child path to a node.
`setobject`	Associates a different object with the node.
`validatenode`	Verifies that a node is in a tree.
`verifypath`	Verifies that a given path exists in a tree.

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

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

12.1.2 Methods for Querying for Data

This table shows the three methods, with XML and JSP tag counterparts, to help your code query for and select content:

Table 12-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. To change the SQL, you do not have to fix it in every place that you use it, just edit it in the SystemSQL table so every element 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. To update or insert data using SQL, you must include code that explicitly flushes the resultsets cached against the appropriate tables using the ics.FlushCatalog method.

12.1.3 Lists and Listing Data

Several 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 is 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 if they are cached.

Note:

Be sure to configure resultset caching appropriately. The list points to a copy of the query's cached 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. Your application or page can transform data before returning an item in a list or to create a single list from many lists. This table shows the methods that manage lists:

Table 12-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.

12.2 Coding Data Entry Forms

We have code samples to show you how you can code forms in which site visitors enter information, and how that information should be stored in the database. Use these code samples to learn how to add a new row, run a query for a row, and edit or delete a row. Each sample shows a version for XML, JSP, and Java.

See these topics:

12.2.1 How To Add a Row

A simple algorithm for adding a row is as follows:

Display a form requesting information for each of the fields in a row.
Write that form data to the table.

This example adds a row to a fictitious table named EmployeeInfo with the columns shown in the following table:

Table 12-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.

12.2.1.1 The addrowFORM Element

The addrowFORM element displays a form that asks the user to enter employee name, ID, and phone.

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.

12.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. See 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>

12.2.2 How To Delete a Row

The following example deletes a row from the fictitious EmployeeInfo table.

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, deleterowJAVA: Elements that delete a row from the EmployeeInfo table based on the information sent to it from the deleterowFORM element.

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

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

12.2.3 How To Query 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.

12.2.3.1 The SelectNameForm Element

The SelectNameForm element displays a simple form that requests the name of the employee who is altering 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 page name to WebCenter Sites. WebCenter Sites looks up the page name in the SiteCatalog table, and then invokes that page entry's root element, QueryEditRowForm.

12.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 the name and phone number but cannot edit the ID number.

The Change Employee Information form looks like this:

Figure 12-1 Change Employee Information Form

Description of "Figure 12-1 Change Employee Information Form"

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 page name and the field information to WebCenter Sites. WebCenter Sites looks up the page name 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>

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>

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

12.2.4 How To Query 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.

12.2.4.1 QueryInlineSQLForm

The QueryInlineSQL element displays a simple form that requests the name for which to search the EmployeeInfo table. 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 page name of the QueryInlineSQL page to WebCenter Sites. WebCenter Sites looks up the page name in the SiteCatalog table and then invokes that page entry's root element.

12.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: Uses the EXECSQL XML tag to create the SQL query.
QueryInlineSQLJSP.jsp: Uses the ics:sql JSP tag to create the SQL query.
QueryInlineSQLJAVA.jsp: 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:sql call. (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.

12.3 Consideration About Deleting Non-Asset Tables

All you need to do before deleting a non-asset table is disable revision-tracking.

To delete a non-asset table which is being revision tracked, first disable revision tracking for the table. Otherwise, the table can't be removed.