Saving the Last Credentials and Query

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

Saving the Last Credentials and Query

It can be useful to save the last credentials and query specified by the user for a portlet instance so these fields do not have to be repopulated every time the user logs out of the portal and then back in. There are several modifications to the current code that need to be made to accomplish this task.

Modify the DynamicQueryPreferences

The DynamicQueryPreferences object needs to be modified to store preferences in the preferences database. Fields need to be added to store the systemName, username, password, and the SQL query.

DynamicQuery\src\java\com\teradata\portlets\dynamicquery\model\DynamicQueryPreferences.java

/**
* The DynamicQueryPreferences object saves the state of the last query that was run by the user.
*/
public class DynamicQueryPreferences extends BaseModel
{
/**
* serial UID. Change this to a unique number for your portlet.
*/
private static final long serialVersionUID = 6960306072306995665L;

//** Add the following section below **/

/**
* The system name for the query
*/
@Preference
private String systemName;

/**
* The username for the query
*/
@Preference
private String username;

/**
* The SQL query text
*/
@Preference
private String sqlQuery;

/**
* Getter for sqlQuery.
*
* @return the sqlQuery
*/
public String getSqlQuery()
{
return sqlQuery;
}

/**
* Setter for sqlQuery.
*
* @param sqlQuery the sqlQuery to set
*/
public void setSqlQuery(final String sqlQuery)
{
this.sqlQuery = sqlQuery;
}

/**
* Getter for systemName.
*
* @return the systemName
*/
public String getSystemName()
{
return systemName;
}

/**
* Setter for systemName.
*
* @param systemName the systemName to set
*/
public void setSystemName(final String systemName)
{
this.systemName = systemName;
}

/**
* Getter for username.
*
* @return the username
*/
public String getUsername()
{
return username;
}

/**
* Setter for username.
*
* @param username the username to set
*/
public void setUsername(final String username)
{
this.username = username;
}
//** End add section */
}


Adding getSystem() to the DynamicQueryManager

The getSystem() function will be used to retrieve the name of the system from the DCS so the name can be stored in the preferences. The function needs to be added to the DynamicQueryManager interface:

DynamicQuery\src\java\com\teradata\portlets\dynamicquery\service\DynamicQueryManager.java

public interface DynamicQueryManager
{
// Current functions here
//** Add the following section below **/

/**
* This function returns a System object that corresponds to the specified systemName parameter
* String. If no corresponding System object is found, <code>null</code> is returned.
*
* @param systemId
* The system id to retrieve the corresponding System object for
* @return A System object that corresponds to the parameter systemId, or <code>null</code> if
* no corresponding System object is found
*/
public System getSystem(final Integer systemId);
//** End add section **/
}


Modifying getSystem() in DynamicQueryManagerImpl

The getSystem() function already exists in the DynamicQueryManagerImpl, so we just need to modify it slightly to match the getSystem() function we declared in the interface. We simply change the private function declaration to public:

DynamicQuery\src\java\com\teradata\portlets\dynamicquery\service\impl\DynamicQueryManagerImpl.java

public class DynamicQueryManagerImpl implements DynamicQueryManager
{
// Current function implementations here

/**
* (non-Javadoc)
*
* @see com.teradata.portlets.dynamicquery.service.DynamicQueryManager#getSystem(Integer)
*/
//** Replace private with public **/
public
//** End replace **/
System getSystem(final Integer systemId)
{
// Current code here
}
}

Saving the Credentials and Query

When the query is submitted by the user, the credentials and the query need to be stored in the preferences database. To do this, we will add the necessary code to the DynamicQueryDataserverController:

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

//** Add the following section below **/
import com.teradata.portlets.dynamicquery.model.DynamicQueryPreferences;
//** End add section **/

public class DynamicQueryDataserverController implements Controller
{
// Current variables/functions here

public void executeQuery(ControllerContext ctx)
{
final String CONTENT_TYPE = "text/html";
final String CONTEXT = "context";
final String SYSTEM = "system";
final String USERNAME = "username";
final String PASSWORD = "password";
final String SQL = "sql";
final String STATUS_MESSAGE = "statusMessage";
final String QUERY_RESULTS = "queryResults";
final String QUERY_FAILED = "queryFailed";

// Set the name of the view
ctx.setViewName("executeQuery");

// The DynamicQueryPreferences object used to save the query being submitted
final DynamicQueryPreferences preferences = new DynamicQueryPreferences();

//** Add the following section below **/

// The context id of the portlet
final String context = ctx.getParameter(CONTEXT);
//** End add section **/

// Get the username parameter from the request
final String username = ctx.getParameter(USERNAME);
// Get the password parameter from the request
final String password = ctx.getParameter(PASSWORD);
// Get the system id parameter from the request
final Integer systemId;
// The TableWidget object to store the query data in
TableWidget table;
// The SQL query to execute
String sql = null;

try
{
// Get the SQL statement parameter from the request
sql = ctx.getParameter(SQL);
// Decode the SQL since it was encoded for the POST
sql = URLDecoder.decode(sql, "UTF-8");
}
catch (final UnsupportedEncodingException uEE)
{
// simply catch the exception since it should never happen; the case will be handled
// below
}

try
{
// Parse the system parameter to get the system id
systemId = Integer.parseInt(ctx.getParameter(SYSTEM));

if (systemId == null || username == null || password == null || sql == null)
{
throw new DynamicQueryException("One or more parameters could not be retrieved!");
}

//** Add the following section below **/

// Set the system name in the preferences object
preferences.setSystemName(manager.getSystem(systemId).getName());
// Set the username in the preferences object
preferences.setUsername(username);
// Set the SQL query in the preferences object
preferences.setSqlQuery(sql);
// Save the last query
ctx.savePreferences("DynamicQuery", preferences);

//** End add section **/

// Execute the query and construct the TableWidget object from the result set of the
// query
table = manager.executeQuery(systemId, username, password, sql);
ctx.addViewObject(STATUS_MESSAGE, "Query succeeded! " + table.getData().length
+ " rows returned!");
ctx.addViewObject(QUERY_RESULTS, table);
}
catch (final DynamicQueryException dQE)
{
ctx.addViewObject(STATUS_MESSAGE, "Query failed! Error: " + dQE.getMessage());
// Set the "queryFailed" flag to true
ctx.addViewObject(QUERY_FAILED, true);
}
catch (final Exception e)
{
ctx.addViewObject(STATUS_MESSAGE, "Failed to process query results! Error: "
+ e.getMessage());
// Set the "queryFailed" flag to true
ctx.addViewObject(QUERY_FAILED, true);
}

// Set the content type of the response
ctx.setContentType(CONTENT_TYPE);
}

// Current functions here
}

Loading the Preferences

Next, we need to load the saved preferences (if there are any) and populate the necessary fields when the portlet is initially drawn. Several lines must be added to the DynamicQueryViewController to accomplish this:

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

//** Add the following section below **/
import com.teradata.portlets.dynamicquery.model.DynamicQueryPreferences;
//** End add section **/
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");
// Retrieve a List of Teradata systems and attach the list to the view
ctx.addViewObject("systemList", dynamicQueryManager.getSystemList());

//** Add the following section below **/

// Retrieve the previous query data
final DynamicQueryPreferences preferences = (DynamicQueryPreferences) ctx.loadPreferences(
"DynamicQuery", new DynamicQueryPreferences());
// Add the data for the previous query
ctx.addViewObject("preferences", preferences);
//** End add section **/

}
}

Modifying the views

Lastly, we need to modify the summary-content.jsp and summary-form.jsp files so the fields are populated with the previous data if it exists:

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">
<!-- Add the following section below -->
${preferences.sqlQuery}
<!-- End add section -->
</textarea>
</div>
<div id="tableDiv${context}" class="tableDiv"></div>
<br />
<div id="statusMessage${context}" class="statusMessage"></div>
</div>


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">
<!-- Add the following section below -->

<option value="${system.id}">${system.name}</option>
<c:choose>
<c:when test="${preferences.systemName == system.name}">
<option value="${system.id}" selected="selected">${system.name}</option>
</c:when>
<c:otherwise>
<option value="${system.id}">${system.name}</option>
</c:otherwise>
</c:choose>
<!-- End add section -->

</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"
<!-- Add the following section below -->

value="${preferences.username}"
<!-- End add section -->

/>
</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${context}" value="submit"
onclick="SubmitQuery${context}();">Execute</button>
</div>
</div>





Tags (2)