Creating the DynamicQuery View

Viewpoint
Teradata Viewpoint is Teradata's strategic and innovative SOV (single operational view) for Teradata DB, Aster, and HDP Hadoop systems management and monitoring that enables Teradata's Unified Data Architecture (UDA).
Teradata Employee

Creating the DynamicQuery View

The following article describes how to create the view in the DynamicQuery portlet.

Getting the System List

Before constructing the view, we need to construct a list of available Teradata systems and pass the list to the view. We already have the necessary code in place to retrieve the systems, we just need to add them to the view:

DynamicQuery\src\java\com\teradata\portlets\dynamicquery\controllers\DynamicQueryViewController.java

public class DynamicQueryViewController extends Controller
{
// Auto generated functions here

public void summary(ControllerContext ctx)
{
// set 'summary.jsp' as the view for this page.
ctx.setViewName("summary");
/** Add the following section below **/

// Retrieve a List of Teradata systems and attach the list to the view
ctx.addViewObject("systemList", dynamicQueryManager.getSystemList());
/** End add section **/
}
}


Now that we have written the server-side code necessary to retrieve SQL query data from the database, we want to allow the user to:

  1. Input the database credentials (username, password, system) and the SQL query
  2. Submit the SQL query and display the result set

Step 1: Creating the Input Fields

To submit a query to the database, we need to create four input fields to:

  1. Specify the system to run the query on
  2. Specify the username to run the query under
  3. Specify the password for the corresponding username
  4. Specify the SQL query to run on the system

To easily manage the different areas of the view, it will be broken up into different files. First, we will create a new page under the /WEB-INF/portlet-jsp directory called summary-form.jsp. This page will contain the input boxes that the user will enter their credentials in:

DynamicQuery\web\WEB-INF\portlet-jsp\summary-form.jsp


<%@ page contentType="text/html" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<div id="Form${context}" class="summaryForm">
<div class="columnDiv">
<span id="System${context}">SYSTEM</span>
<select id="SystemSelect${context}" class="inputEl">
<c:forEach items="${systemList}" var="system">
<option value="${system.id}">${system.name}</option>
</c:forEach>
</select>
</div>
<div class="columnDiv">
<span id="Username${context}" style="padding-bottom:3px">USERNAME</span>
<input type="text" id="UsernameInput${context}" class="inputEl" />
</div>
<div class="columnDiv">
<span id="Password${context}" style="padding-bottom:3px">PASSWORD</span>
<input type="password" id="PasswordInput${context}" class="inputEl" />
</div>
<div id="submit${context}" class="execBtn">
<button type="button" id="submitBtn" value="submit"
onclick="SubmitQuery${context}();">Execute</button>
</div>
</div>


Next, we will create another new page under the /WEB-INF/portlet-jsp directory called summary-content.jsp. This page will contain the text area for the user to enter the SQL query statement as well as the area for the query results to be displayed:

DynamicQuery\web\WEB-INF\portlet-jsp\summary-content.jsp


<%@ page contentType="text/html" language="java" %>
<div id="SummaryContent${context}" class="summaryContent">
<div id="sqlText${context}" class="SQLTextAreaContainer">
<textarea id="SQLInput${context}" class="SQL" rows="3"></textarea>
</div>
<div id="tableDiv${context}" class="tableDiv"></div>
<br />
<div id="statusMessage${context}" class="statusMessage"></div>
</div>


Next, we will modify our summary.jsp page that was generated by the portlet generator so we will be able to see the two pages we have created in our view. We will remove the line that says "[summary page]" and add our own content:

DynamicQuery\web\WEB-INF\portlet-jsp\summary.jsp

<%@ page contentType="text/html" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
<%@ taglib prefix="vs" uri="http://www.teradata.com/security" %>
<%@ taglib prefix="vp" uri="http://www.teradata.com/core" %>

<jsp:include page="include.jsp"/>
<!-- Add the following section below -->

<div id="Container${context}">
<script type="text/javascript">
TDPortalManager.registerPortlet('${context}', 'Container${context}', null);
</script>
<%@ include file="summary-form.jsp" %>

<%@ include file="summary-content.jsp" %>
<!-- End add section -->

</div>


Lastly, we need to add the necessary styles to the style sheet file dynamicQuery.css:

DynamicQuery\web\css\dynamicQuery.css


.summaryForm
{
padding: 10px 5px;
border-bottom-style: solid;
border-bottom-width: 1px;
border-bottom-color: #9F9F9F;
height: 35px;
font: verdana;
}

.summaryForm .execBtn
{
padding-top: 15px;
}

.summaryForm .columnDiv
{
display: block;
float: left;
width: 125px;
color: #6C6C6C;
}

.summaryForm .columnDiv .inputEl
{
width: 88%;
margin-top: 3px;
}

.summaryContent
{
padding-top: 10px;
padding-bottom: 10px;
}

.summaryContent .SQLTextAreaContainer
{
padding-bottom: 10px;
padding-right: 5px;
padding-left: 5px;
border-bottom-style: solid;
border-bottom-width: 1px;
border-bottom-color: #9F9F9F;
}

.summaryContent .SQLTextAreaContainer .SQL
{
width: 458px;
}

.summaryContent .statusMessage
{
color: #6C6C6C;
font: verdana;
padding-left: 5px;
}

.summaryContent .tableDiv
{
padding-left: 5px;
padding-right: 5px;
padding-top: 5px;
}


Step 2: Executing SQL and Displaying the Result Set

Now that we have the view created, we need to allow the user to execute the SQL query they have created and see the results. To do this, we need to implement the SubmitQuery${context}() function specified in the onclick event handler of the execute button. This function will be added to the summary.jsp:

DynamicQuery\web\WEB-INF\portlet-jsp\summary.jsp

<%@ page contentType="text/html" language="java" %>
<div style="display:none">Hidden div to make IE happy</div>
<%@ include file="include.jsp" %>
<div id="Container${context}">
<script type="text/javascript">
TDPortalManager.registerPortlet('${context}', 'Container${context}', null);
</script>
<%@ include file="summary-form.jsp" %>

<%@ include file="summary-content.jsp" %>
</div>
<!-- Add the following section below -->

<script type="text/javascript">
TDPortalManager.onPortletReady('${context}', function() {
var jqContainer = jQuery('#Container${context}');
TDPortalManager.registerObject('${context}', 'jqContainer', jqContainer);
});
/**
* This function is invoked when the user clicks on the submit button.
*/
function SubmitQuery${context}()
{
var jqCont = TDPortalManager.getObject('${context}', 'jqContainer');
var sql = jQuery('#SQLInput${context}').val();
sql = encodeURIComponent(sql);
var url = '/DynamicQueryPortlet/dataserver/executeQuery';
var data = [];
// Construct the data parameters with format 'key1=value1&key2=value2...'
data.push('context=${context}&');
data.push('system=' + jQuery('#SystemSelect${context}').val() + '&');
data.push('username=' + jQuery('#UsernameInput${context}').val() + '&');
data.push('password=' + jQuery('#PasswordInput${context}').val() + '&');
data.push('sql=' + sql);
// Make a POST call to get the data and refresh the portlet with the result
// set content
TDPortalManager.refreshPortlet('${context}', url, {
type: 'POST',
cache: false,
data: data.join(''),
target: '#tableDiv${context}',
error: function(r, tS, eT) {
alert('Error executing query!');
},
beforeSend: function(r) {
jQuery('#submitBtn${context}').attr('disabled', 'disabled').html('Executing');
},
complete: function(r, s) {
jQuery('#submitBtn${context}').removeAttr('disabled').html('Execute');
}
});
}
</script>
<!-- End add section -->


The javascript function SubmitQuery${context}() is used to make an ajax call to the DynamicQueryDataserverController. Note that the url variable consists of the "/thePortletsDirectory/theDataserverMapName/theDataserverFunctionName". This function demonstrates two integral pieces of client-side portlet development:

  1. The use of the jQuery library for DOM searching and manipulation. For more information, read the Javascript Library page.
  2. The use of the TDAjaxManager for ajax calls. For more information, read the Javascript Library page.

The last element of the portlet's view is the dynamically generated result set table. Once the DynamicQueryDataserverController has done its work (submitting the query and constructing the result set), the ModelAndView object generated by the executeQuery() function is passed to the corresponding .jsp page for rendering. Thus, we must create the page executeQuery.jspunder the directory /WEB-INF/portlet-jsp. The result will be a generated HTML page with the results:

DynamicQuery\web\WEB-INF\portlet-jsp\executeQuery.jsp


<%@ page contentType="text/html" language="java" %>
<%@ taglib prefix="vp" uri="http://teradata.com/viewpoint/core"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<c:if test="${queryFailed != true}">
<vp:tableWidget context="${context}" model="${queryResults}"
htmlAttrs='id="table${context}"' />
</c:if>
<script type="text/javascript">
jQuery('#statusMessage${context}').html("${statusMessage}");
</script>


Take special note of the following line from the summary.jsp file:

DynamicQuery\web\WEB-INF\portlet-jsp\summary.jsp

target: '#tableDiv${context}',


This is the ID of the div that the table content will be placed inside of once the ajax call has been made and the table has been constructed. We use the '#' sign in front of the ID because this indicates to the TDPortalManager that the target parameter is an element ID.

Tags (2)
1 REPLY
Fan

Re: Creating the DynamicQuery View

Hi Raghavender from India,

if anybody has a script generator that will automatically create a fload, mload, or fexp generic framework, please send me to chiram1.etl@gmail.com
thank you