This section presents separate examples for a Web site target to illustrate the objects and methods you use to:
Get the value of a page parameter
Establish a database connection and handle database errors
Create a SQL query and use the page parameter in the WHERE clause
Display the query results in a table with link formatting
In these examples, the target page receives the value of a department passed from a link on another page to a target page parameter. It then retrieves the names and IDs of employees who work in that department.
The employee names are then displayed in a table with a link to an employee detail page. The employee ID is in a query string of the link so that it can be used in another query on the detail page.
The complete source for these examples is provided at the end of this section.
The GetParam method for the psDocument object accesses the value of a page parameter. You can assign the value to a variable and use that variable in other scripts on the page.
Example: getting the value of a page parameter This script would appear after the first heading in the file. It assigns the page parameter value to the variable curr_dept. It also writes the department name on the page:
curr_dept = psDocument.GetParam("Dept");
psDocument.Write("<P>Employees for department <B>");
psDocument.Write(curr_dept + "</B></P>");
The psServer instance is automatically instantiated in your Web target and is available to server scripts on every page. Using psServer methods, you can define new connections at execution time or you can access connections you defined in PowerBuilder. (When you deploy a Web target, the connection information is made available to the application server.)
Example: connecting to a database Here the GetConnection method for psServer instantiates a PSConnectionClass object using the connection profile Employees. If an error occurs, the code calls a WriteError function to display error information:
conn = psServer.GetConnection("Employees");
rows = 0;
if ( conn.GetError() != null )
{
WriteError( "GetConnection", conn );
return;
}
The WriteError function is called only if the error object is not null. The script that defines the WriteError function is shown in the next example and is placed in the Head section of the document. The arguments for WriteError are:
The method that caused the error
The instance of the PSConnectionClass object
The WriteError function calls the GetError method for the connection object to get the first instance of the PSErrorClass object. An error object is available only if an error has occurred; otherwise, GetError returns null.
After getting error information, the GetError function writes the connection name, error code, error message, and the name of the function that failed in the document. The GetCode and GetMessage methods for PSErrorClass get the error code and message.
Example: handling database errors This is the code for the WriteError function:
function WriteError( function_called, connName ) {
errobj = connName.GetError();
str = errobj.GetCode() + " " + errobj.GetMessage();
psDocument.Write("<P>Error: " );
psDocument.Write ( function_called + " " + str );
psDocument.Write("</P>");
return;
}
After you establish a connection, you can retrieve data with a SQL statement and store the result set in a PSCursorClass object. To retrieve data, this code:
Builds a string that is the SQL statement. The curr_dept variable, which holds the page parameter value, is incorporated into the WHERE clause.
Uses the string with the SQL statement as an argument for the CreateCursor method. This method belongs to the PSConnectionClass object.
Assigns the returned result set to the newly instantiated PSCursorClass object called "mycursor".
Checks whether a database error occurred and calls the WriteError function if necessary.
Example: retrieving and storing data The code that creates the cursor and retrieves data looks like this:
//build a SQL statement sqlquery = " SELECT \"employees\".\"fname\" , " + " \"employees\".\"lname\" , " + " \"employees\".\"empid\" " + " FROM \"employees\" " + " WHERE \"employees\".\"deptid\" = " + "'" + curr_dept + "'";
// Do the query and assign the result set to
mycursormycursor = conn.CreateCursor(sqlquery);
if ( conn.GetError() != null ){
WriteError( "CreateCursor", conn );
return;
}
After the rows are retrieved, methods for the PSCursorClass object provide access to the data. Code that writes HTML for displaying data is mixed with method calls that get the data from the PSCursorClass object. This code:
Calls GetRowCount, a method of the PSCursorClass object, to find out how many rows are in the result set
Writes HTML for the Table element
Writes HTML to close the Table element
Loops through the rows in the result set
For each row in the result set, the code:
Writes HTML for a table row with one cell.
Writes an anchor element (<A>) tag with a query string using data from the second column (empid). The GetValue method for the PSCursorClass object gets the data.
Writes text inside the anchor element, using GetValue to get the employee first name from the first column (0) and the last name from the second column (1). Column numbers start with 0 and correspond to the columns in the SQL SELECT statement.
Writes HTML that closes the A, TD, and TR elements.
Calls the MoveNext method for the PSCursorClass object to go to the next row in the result set.
Example: processing rows The code that processes the rows looks like this:
rows = mycursor.GetRowCount();
// Write Table start tag
psDocument.Write("<TABLE BORDER=1>");
// Loop over retrieved rows
// where rows variable is the row count
for (var i=0; i < rows; i++ )
{
// Write TR and TD start tags
psDocument.Write("<TR><TD>");
// Write A element with employee ID in query string
psDocument.Write( "<A HREF=\"detail.htm?Key=");
psDocument.Write( mycursor.GetValue(2) + "\">");
// Write first and last names
psDocument.Write( mycursor.GetValue(0) + " ");
psDocument.Write( mycursor.GetValue(1));
// Write A, TD, and TR end tags
psDocument.Write("</A><TD><TR>");
// Go to next row in result set mycursor.MoveNext();
}
// Write Table end tag
psDocument.Write("</TABLE>");
The complete page looks like this in Source view:
<HTML>
<HEAD>
<%
// function for displaying error information
function WriteError( function_called, connName )
{ errobj = connName.GetError();
str = errobj.GetCode() + " " + errobj.GetMessage();
psDocument.Write("<P>Error: " );
psDocument.Write ( function_called + " " + str );
psDocument.Write("</P>");
return;
}
%>
</HEAD>
<BODY>
<H1>Employees</H1>
<P><% // Get page parameter and write value in document
curr_dept = psDocument.GetParam("Dept");
psDocument.Write("<P>Employees for department <B>"); psDocument.Write(curr_dept + "</B></P>");%></P>
<P><% // Get a connection
conn = psServer.GetConnection("Employees");
rows = 0;
if ( conn.GetError() != null ) {
WriteError( "GetConnection", conn );
return;
}
// Construct the SQL statement
sqlquery = " SELECT \"employees\".\"fname\" , " +
" \"employees\".\"lname\" , " +
" \"employees\".\"empid\" " +
" FROM \"employees\" " +
" WHERE \"employees\".\"deptid\" = " +
"'" + curr_dept + "'";
// Retrieve the data
mycursor = conn.CreateCursor(sqlquery);
if ( conn.GetError() != null ) {
WriteError( "CreateCursor", conn );
return;
}
// Get the number of rows retrieved
rows = mycursor.GetRowCount();
psDocument.Write("<TABLE BORDER=1>");
for (var i=0; i < rows; i++ ) {
// Write TR and TD start tags
psDocument.Write("<TR><TD>");
// Write A element with employee ID in query string
psDocument.Write( "<A HREF=\"detail.htm?Key=");
psDocument.Write( mycursor.GetValue(2) + "\">");
// Write first and last names
psDocument.Write( mycursor.GetValue(0) + " ");
psDocument.Write( mycursor.GetValue(1));
psDocument.Write("</A><TD><TR>");
mycursor.MoveNext();
}
psDocument.Write("</TABLE>");
%>
</BODY>
</HTML>
