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:
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:
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, theCATALOGMANAGER
XML tag, or theics:catalogmanager
JSP tag. -
To access the TreeManager servlet, use the
ics.TreeManager
Java method, theTREEMANAGER
XML tag, or theics: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.
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 |
---|---|
|
Adds a single row to a table. |
|
Adds multiple rows to a table. |
|
Deletes a row from a table. You must specify the primary key column for the row. |
|
Deletes multiple rows from a table. You must specify the primary key for the rows. |
|
Deletes the existing row in a table and replaces the row with the specified information. |
|
Replaces multiple rows in a table. If a value is not specified for a column, the column value is cleared. |
|
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. |
|
Like |
|
Modifies field values for multiple rows in a table. |
|
Like |
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.
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 |
---|---|
|
Given a parent node, adds a child node. |
|
Adds multiple child nodes. |
|
Copies a node and its children to a different parent. All copied nodes point to the same objects. |
|
Creates a tree table. |
|
Deletes a node and its child nodes. |
|
Deletes multiple nodes. |
|
Deletes a tree table. |
|
Finds a node in a tree. |
|
Gets all child nodes. |
|
Gets node and optionally object attributes. |
|
Gets the nodes parent. |
|
Gets the list of all tree tables. |
|
Moves node and its child nodes to a different parent. |
|
Returns parent; child path to a node. |
|
Associates a different object with the node. |
|
Verifies that a node is in a tree. |
|
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.
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 |
---|---|---|---|
|
|
|
Performs a simple select against a single table. |
|
|
|
Executes an inline SQL statement (embedded in the code). |
|
|
|
Executes a SQL statement that is stored as a row in the |
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.
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 |
---|---|
|
Returns an |
|
Copies a list. |
|
Renames an existing list. |
|
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 |
For an example implementation of an IList
, see SampleIList.java
in the Samples
folder on your WebCenter Sites system.
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:
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 |
---|---|
|
|
|
|
|
|
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
, andaddrowJAVA
, three versions of an element that writes the information entered by the employee to theEmployeeInfo
table.
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.
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>
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 theEmployeeInfo
table. -
deleterowXML
,deleterowJSP
,deleterowJAVA
: Elements that delete a row from theEmployeeInfo
table based on the information sent to it from thedeleterowFORM
element.
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.
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>
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.
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.
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 follows Description of Figure 12-1 follows](img/g-queryeditrowform.gif)
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>
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.
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 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.
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>
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 theEmployeeInfo
table for names that contain the string entered by the user in the preceding form.
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.
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 theEXECSQL
XML tag to create the SQL query. -
QueryInlineSQLJSP.jsp
: Uses theics:sql
JSP tag to create the SQL query. -
QueryInlineSQLJAVA.jsp
: Uses theics.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.