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
 
(54 intermediate revisions by the same user not shown)
Line 1: Line 1:
This sample JSP page illustrates achieves several goals:
<noinclude>
:*
{{Orientation|Developers|Intermediate|15}}
</noinclude>This sample JSP page uses a SQL query to create a list of Customer object records.


To achieve that goal:
:* This page 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]].)
:* It uses [[Platform URLs]] to access record details.
<noinclude>__TOC__</noinclude>
===Preqequisites===
This example assumes that there is an Orders object with fields:
:* <tt>order_num</tt>
:* <tt>customer</tt>
:* <tt>order_date</tt>
:* <tt>order_total</tt>
===Creating the Page===
'''To create this example:'''
# Go to '''[[File:GearIcon.png]] > Developer Resources > Pages'''
# Click '''[New Page]'''
# For the "title", give the page a valid name. For example: <tt>CustomOrderList.jsp</tt>
# Include header files.
# Copy in the sample code below.
# Save the page.
<!--
:'''Note:'''<br>As noted in the description of [[Platform URLs]], if a Record Identifier (aka "record name") has been defined in the object's [[Record Locator]], and if a unique index has been created using the Record Identifier option, then the record name can be used in the URL. Otherwise, the record ID would be used.
-->
:<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";
%>


<!--
  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 "DemoCustomers.jsp".
      3. Visit it using https:{platform}/networking/pages/DemoCustomers.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 "DemoCustomers.jsp".
      3. Go to Designer > Presentation > Web Tabs
      4. Click [New Web Tab]
      5. Settings:
          * Title: Demo Customers (custom)
          * Name: demoCustomersWebTab
          * Web Tab Type: Page
          * Pages:/DemoCustomers.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>
<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 95: Line 67:
   <table width="100%" border="0" cellspacing="0" cellpadding="0">
   <table width="100%" border="0" cellspacing="0" cellpadding="0">
     <tr>
     <tr>
       <td width="33%" class="title1">Customer</td>
       <td width="33%" colspan="3" class="title1">Custom Orders</td>
       <td width="33%" class="title1">&nbsp;</td>
    </tr>     
       <td width="33%" align="right" valign="middle" class="title1"></td>    
    <tr>
      <td width="20px"  class="td1"><u>Order#</u></td>
       <td width="120px" class="td1"><u>Customer</u></td>
       <td width="40px" class="td1"><u>Date</u></td>
      <td width="40px" class="td1"><u>Total</u></td>
     </tr>       
     </tr>       
<%  
<%  
  // Issue the SQL Query
  String query = "SELECT customer, order_date, order_total FROM Orders ORDER BY customer";
   Result result = Functions.execSQL(query);
   Result result = Functions.execSQL(query);
   if(result != null) {
   if(result != null) {
     String service_url = "https://"
     String service_url = com.platform.api.utility.ServiceConfiguration.getServiceDomainURL();
      + com.platform.api.utility.ServiceConfiguration.getServiceDomainURL();
    String platform_url =  "https://" + service_url + "/networking";
     ParametersIterator iter = result.getIterator();
     ParametersIterator iter = result.getIterator();
     while(iter.hasNext()) {
     while(iter.hasNext()) {
       Parameters row = iter.next();
       Parameters row = iter.next();
       String recordID = row.get("id");
       String recordID = row.get("id");
       String name = row.get("Name");
       String order_num = row.get("order_num");
      String customer = row.get("customer");
      String date = row.get("order_date");
      String total = row.get("order_total");
        
        
       /* THE SIMPLEST SOLUTION -  A SHORT URL.
       /* Formulate links to the records
        * Use this solution when the current page is displayed by itself,
        * independent of the platform GUI.
        */
      //String link = service_url + "/networking/record/Demo_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:
         * The application ID and object ID are required to formulate the links.
         *   a) The object ID.
        * Get them from the URL you see when you visit the object tab in the GUI,
         *       - Visit https://{domain}/networking/rest/object/{objectName}
         * or get them from the metadata displayed in the GUI, or from a REST API.
         *       - Use the content of the <id> tag
        * For example, this REST API returns object metadata:
        *  b) The record ID returned by the SQL query
         *   - Visit https://{{domain}}/networking/rest/object/{objectName}
         *   - Use the content of the <id> tag
         *  
         *  
         * Link Pattern:  
         * Link Pattern:
         *   javascript:top.showTabInIframe(uniquekey, tempTitle, url, displayTitle);
         *   ../servicedesk/index.jsp?applicationId={appID}#_{objectID}/{recordID}
         */
         */
       String objectID = "3e7da19991d64b4a86cd92f040d86fd4";
       String appID = "348f933bf0dc4bfeb5a8e8ade2bb145e";
       String tempTitle = "Loading...";
       String objectID = "9912b3648e8b4b919c23e00efcab8089";
       String url = "Service?t=498&id=" + recordID
       String link = platform_url
                  + "&object_id=" + objectID + "&a=view&policyaction=view";
                  + "/servicedesk/index.jsp?applicationId=" + appID
      Boolean displayTitle = true;     
                  + "#_" + objectID + "/" + recordID;    
      String link = "javascript:top.showTabInIframe('" +recordID+ "','"
 
                  +tempTitle+ "','" +url+ "'," +displayTitle+ ");";
            // Use record "name" if the Record Identifier is defined as // ?-RECORD NAME ??
            // a unique index. Otherwise use "recordID".
%>
%>
     <tr>
     <tr>
         <td class="td1"><a href="<%=link%>"><%=name%></a></td>
         <td class="td1"><a href="<%=link%>"><%=order_num%></a></td>
         <td class="td2"><!--Link: <%=link%> --></td>
         <td class="td2"><%=customer%> --></td>
         <td class="td2"><!-- ... --></td>
        <td class="td2"><%=date%> --></td>
         <td class="td2"><%=total%> --></td>
     </tr>
     </tr>
<%
<%
Line 155: Line 126:
</html>
</html>
</syntaxhighlight>
</syntaxhighlight>
===Viewing the Page===
'''To view the page directly:'''
:* Visit the page in the browser at https://{{domain}}/networking/pages/{pagename}.jsp
:: The page appears by itself, without any platform context.
'''To make the page available as a tab in the GUI:'''
# Go to '''[[File:GearIcon.png]] > Developer Resources > Web Tabs'''
# Click '''[New Web Tab]'''
# Give the tab a display title and a name that can be used to access it from code.<br>For example:
#* '''Display Title -''' <tt>Custom Orders</tt>
#* '''Name -''' <tt>custom_orders_page</tt>
# Set '''Web Tab Type''' to ''Page''
# Select the page you created.
# Click '''[Save]'''<br>The Web Tab is created.
# Log out and the log back in to see the new Web Tab.

Latest revision as of 23:55, 2 May 2014

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:

Preqequisites

This example assumes that there is an Orders object with fields:

  • order_num
  • customer
  • order_date
  • order_total

Creating the Page

To create this example:

  1. Go to GearIcon.png > Developer Resources > Pages
  2. Click [New Page]
  3. For the "title", give the page a valid name. For example: CustomOrderList.jsp
  4. Include header files.
  5. Copy in the sample code below.
  6. Save the page.
<!-- Create a View from a SQL Query -->

<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%" colspan="3" class="title1">Custom Orders</td>
    </tr>      
    <tr>
      <td width="20px"  class="td1"><u>Order#</u></td>
      <td width="120px" class="td1"><u>Customer</u></td>
      <td width="40px"  class="td1"><u>Date</u></td>
      <td width="40px"  class="td1"><u>Total</u></td>
    </tr>      
<% 
  // Issue the SQL Query
  String query = "SELECT customer, order_date, order_total FROM Orders ORDER BY customer";
  Result result = Functions.execSQL(query);
  if(result != null) {
    String service_url = com.platform.api.utility.ServiceConfiguration.getServiceDomainURL();
    String platform_url =  "https://" + service_url + "/networking";
    ParametersIterator iter = result.getIterator();
    while(iter.hasNext()) {
       Parameters row = iter.next();
       String recordID = row.get("id");
       String order_num = row.get("order_num");
       String customer = row.get("customer");
       String date = row.get("order_date");
       String total = row.get("order_total");
       
       /* Formulate links to the records
        *
        * The application ID and object ID are required to formulate the links.
        * Get them from the URL you see when you visit the object tab in the GUI,
        * or get them from the metadata displayed in the GUI, or from a REST API.
        * For example, this REST API returns object metadata:
        *   - Visit https://{{domain}}/networking/rest/object/{objectName}
        *   - Use the content of the <id> tag
        * 
        * Link Pattern:
        *    ../servicedesk/index.jsp?applicationId={appID}#_{objectID}/{recordID}
        */
       String appID = "348f933bf0dc4bfeb5a8e8ade2bb145e";
       String objectID = "9912b3648e8b4b919c23e00efcab8089";
       String link = platform_url 
                   + "/servicedesk/index.jsp?applicationId=" + appID
                   + "#_" + objectID + "/" + recordID;     

            // Use record "name" if the Record Identifier is defined as // ?-RECORD NAME ??
            // a unique index. Otherwise use "recordID".
%>
    <tr>
        <td class="td1"><a href="<%=link%>"><%=order_num%></a></td>
        <td class="td2"><%=customer%> --></td>
        <td class="td2"><%=date%> --></td>
        <td class="td2"><%=total%> --></td>
    </tr>
<%
    }
  }
%>
  </table>
</body>
</html>

Viewing the Page

To view the page directly:

The page appears by itself, without any platform context.

To make the page available as a tab in the GUI:

  1. Go to GearIcon.png > Developer Resources > Web Tabs
  2. Click [New Web Tab]
  3. Give the tab a display title and a name that can be used to access it from code.
    For example:
    • Display Title - Custom Orders
    • Name - custom_orders_page
  4. Set Web Tab Type to Page
  5. Select the page you created.
  6. Click [Save]
    The Web Tab is created.
  7. Log out and the log back in to see the new Web Tab.
Retrieved from "https://agileappscloud.info/aawiki-src/index.php?title=HowTo:Use_a_SQL_Query_to_List_Records_in_a_Custom_Page&oldid=16021"