HowTo:Use a SQL Query to List Records in a Custom Page

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
Jump to: navigation, search

For:   Developers
Level: Intermediate
Time: 15 minutes

See more:
    ◾ HowTo Guides

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.)
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">&nbsp;</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>
Personal tools