HowTo:Use a SQL Query to List Records in a Custom Page
From LongJump Support Wiki
HowTo:Use a SQL Query to List Records in a Custom Page
|
For: Developers See more: |
This sample JSP page uses a SQL query to create a list of Customer object records.
To achieve that goal, the page:
- Uses SQL Syntax in the Java execSQL API to issue the query.
(It could also have used the REST execSQL Resource.) - Uses Short URLs to access record details, when the JSP page is displayed as a standalone page, outside of the platform GUI. (That section of the code needs to be enabled.)
- Uses the JavaScript showTabInIFrame function to display record details when the JSP page is displayed as a tab within the platform. (That code is active in the sample.)
- Uses SQL Syntax in the Java execSQL API to issue the query.
- Note:
As noted in the description of Short URLs, if a Record Identifier (aka "record name") has been defined in the object's Record Locators, and if a unique index has been created using the Record Identifier option, then the record name is used in the Short URL. Otherwise, the record ID would be used.
- <!-- Create a View from a SQL Query -->
<%
String query = "SELECT id, customer_name AS Name FROM Customer ORDER BY Name";
%>
<!--
To display this page by itself, independent of the GUI:
1. Activate the "Short URLs" solution in the code below,
and deactivate the "showTabInIframe" solution.
2. Add this page to the platform as "Customers.jsp".
3. Visit it using https:{platform}/networking/pages/Customers.jsp
To display this page as a "Web Tab" within the GUI:
1. Leave the "showTabInIframe" solution activated in the code below.
2. Add this page to the platform as "Customers.jsp".
3. Go to Designer > Web Tabs
4. Click [New Web Tab]
5. Settings:
* Title: Customers (custom)
* Name: customersWebTab
* Web Tab Type: Page
* Pages:/Customers.jsp
6. Click [Ok].
The page now appears as an entry in the application workspace.
When you click that entry, the page opens as a tab in the platform GUI.
-->
<html>
<head>
<title>Customers</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<style type="text/css">
.title1 {
font-family: Arial, Helvetica, sans-serif;
font-size: 24pt;
background-color: #006666;
color: #DCDCDC;
padding: 10px;
}
.td1 {
font-family: Arial, Helvetica, sans-serif;
font-size: 18pt;
background-color: #FFFFFF;
border-color: #006666;
border-width: medium;
color: #006666;
padding-left: 18px;
}
.td2 {
font-family: Arial, Helvetica, sans-serif;
font-size: 12pt;
background-color: #FFFFFF;
border-color: #006666;
border-width: medium;
color: #006666;
padding-left: 2px;
}
</style>
</head>
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="33%" class="title1">Customer</td>
<td width="33%" class="title1"> </td>
<td width="33%" align="right" valign="middle" class="title1"></td>
</tr>
<%
Result result = Functions.execSQL(query);
if(result != null) {
String service_url = "https://"
+ com.platform.api.utility.ServiceConfiguration.getServiceDomainURL();
ParametersIterator iter = result.getIterator();
while(iter.hasNext()) {
Parameters row = iter.next();
String recordID = row.get("id");
String name = row.get("Name");
/*
* SHORT URL Solution
* Use this solution when the current page is displayed by itself,
* independent of the platform GUI.
*/
//String link = service_url + "/networking/record/Customers/"
// + name_or_id;
// Use record "name" if the Record Identifier is defined as
// a unique index. Otherwise use "recordID".
/*
* JAVASCRIPT Solution
* Use this solution when the current page is displayed as a web tab
* within the platform. (The short URL for a record goes to a full GUI
* page. That's the behavior you want when following a link in an email.
* But in the platform, a new tab opens with the whole GUI nested inside.)
*
* This solution requires the object ID.
* To get it:
* - Visit https://{domain}/networking/rest/object/{objectName}
* - Use the content of the <id> tag
*
* Troubleshooting:
* - If the objectID is wrong, the tab label stays as "Loading..."
* and nothing is displayed in the page.
*
* Link Pattern:
* javascript:top.showTabInIframe(uniqueKey, tempTitle, url, displayTitle);
* where: uniqueKey = "{objectID}-1"
*/
String objectID = "436747df2fbd44aaa8d79d0138e9a8e3";
String tempTitle = "Loading...";
String url = "Service?t=498&id=" + recordID
+ "&object_id=" + objectID + "&a=view&policyaction=view";
Boolean displayTitle = true;
String link = "javascript:top.showTabInIframe('" +objectID+ "-1','"
+tempTitle+ "','" +url+ "'," +displayTitle+ ");";
%>
<tr>
<td class="td1"><a href="<%=link%>"><%=name%></a></td>
<td class="td2"><!--Link: <%=link%> --></td>
<td class="td2"><!-- ... --></td>
</tr>
<%
}
}
%>
</table>
</body>
</html>
