HowTo:Use a SQL Query to List Records in a Custom Page
From AgileApps Support Wiki
Revision as of 20:05, 28 December 2011 by imported>Aeric
This sample JSP page uses a SQL query to creates a list of object records. To achieve that goal, it:
- 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.)
- Assumes that the
- Uses SQL Syntax in the Java execSQL API to issue the query.
<!-- Create a View from a SQL Query --> <% String query = "SELECT id, customer_name AS Name FROM Customers 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 > Presentation > 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"); /* THE SIMPLEST SOLUTION - A SHORT URL. * 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". /* * 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: * a) The object ID. * - Visit https://{domain}/networking/rest/object/{objectName} * - Use the content of the <id> tag * b) The record ID returned by the SQL query * * Link Pattern: * javascript:top.showTabInIframe(uniquekey, tempTitle, url, displayTitle); */ String objectID = "3e7da19991d64b4a86cd92f040d86fd4"; 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('" +recordID+ "','" +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>