AgileApps Support Wiki

Difference between revisions of "HowTo:Use a SQL Query to List Records in a Custom Page"

From AgileApps Support Wiki
imported>Aeric
(Created page with "This sample JSP page illustrates achieves several goals: :* :<syntaxhighlight lang="javascript" enclose="div"> <!-- Create a View from a SQL Query --> <% String query = "SE…")
 
imported>Aeric
Line 1: Line 1:
This sample JSP page illustrates achieves several goals:
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|execSQL API]] to issue the query.<br>(It could also have used the REST [[REST_API:execSQL_Resource|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 [[JavaScript Functions#showTabInIFrame|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
 


:<syntaxhighlight lang="javascript" enclose="div">
:<syntaxhighlight lang="javascript" enclose="div">
<!-- Create a View from a SQL Query -->
<!-- Create a View from a SQL Query -->
<%
<%
     String query = "SELECT id, customer_name AS Name FROM Demo_Customers ORDER BY Name";
     String query = "SELECT id, customer_name AS Name FROM Customers ORDER BY Name";
%>
%>


Line 12: Line 16:
       1. Activate the "Short URLs" solution in the code below,
       1. Activate the "Short URLs" solution in the code below,
         and deactivate the "showTabInIframe" solution.
         and deactivate the "showTabInIframe" solution.
       2. Add this page to the platform as "DemoCustomers.jsp".
       2. Add this page to the platform as "Customers.jsp".
       3. Visit it using https:{platform}/networking/pages/DemoCustomers.jsp
       3. Visit it using https:{platform}/networking/pages/Customers.jsp
    
    
   To display this page as a "Web Tab" within the GUI:
   To display this page as a "Web Tab" within the GUI:
       1. Leave the "showTabInIframe" solution activated in the code below.
       1. Leave the "showTabInIframe" solution activated in the code below.
       2. Add this page to the platform as "DemoCustomers.jsp".
       2. Add this page to the platform as "Customers.jsp".
       3. Go to Designer > Presentation > Web Tabs
       3. Go to Designer > Presentation > Web Tabs
       4. Click [New Web Tab]
       4. Click [New Web Tab]
       5. Settings:
       5. Settings:
           * Title: Demo Customers (custom)
           * Title: Customers (custom)
           * Name: demoCustomersWebTab
           * Name: customersWebTab
           * Web Tab Type: Page
           * Web Tab Type: Page
           * Pages:/DemoCustomers.jsp
           * Pages:/Customers.jsp
       6. Click [Ok].
       6. Click [Ok].
         The page now appears as an entry in the application workspace.
         The page now appears as an entry in the application workspace.
Line 31: Line 35:
<html>
<html>
<head>
<head>
<title>Demo Customers</title>
<title>Customers</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<style type="text/css">
<style type="text/css">
/* Not Used in this Example */
.body1 {
        background-color: #DCDEEE;
        font-family: Arial, Helvetica, sans-serif;
        font-size: 12pt;
        line-height: 24pt;
        color: #336699;
}
.a1 {
        color: #3366CC;
        text-decoration: none;
}
.form1 {
        background-color: #CCCC99;
        padding: 6px;
}
.header1 {
        font-family: Arial, Helvetica, sans-serif;
        font-size: 12pt;
        background-color: #006666;
        color: #DCDCDC;
        font-weight: bold;
}
/* End of Unused Styles */
.title1 {
.title1 {
         font-family: Arial, Helvetica, sans-serif;
         font-family: Arial, Helvetica, sans-serif;
Line 114: Line 91:
         * independent of the platform GUI.
         * independent of the platform GUI.
         */
         */
       //String link = service_url + "/networking/record/Demo_Customers/"  
       //String link = service_url + "/networking/record/Customers/"  
       //            + name_or_id;     
       //            + name_or_id;     
             // Use record "name" if the Record Identifier is defined as  
             // Use record "name" if the Record Identifier is defined as  

Revision as of 20:05, 28 December 2011

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


<!-- 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">&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");
       
       /* 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>
Retrieved from "https://agileappscloud.info/aawiki-src/index.php?title=HowTo:Use_a_SQL_Query_to_List_Records_in_a_Custom_Page&oldid=15967"