To illustrate how dhttp can work both as an application and a service, let’s focus on ways to retrieve SQL data. A typical dhttp plug-in uses some kind of data-viewing widget to present dynamic views of SQL data and attach instrumentation to those views. Figure 15.7 shows the viewer used by another plug-in, called Jobs, which tracks assigned tasks.
You can do an ascending or descending sort on any column by clicking the up or down links surrounding the column’s title. A menu of functions that apply to each row appears in the first column as a dropdown list. In this case, the client focus function constrains the view to only the jobs for the client listed in that row. The contacts function launches the SFA module, in a separate window, and synchronizes its display to the client listed in that row. The create invoice function launches Word and generates an invoice, pulling records from both the SFA and the Jobs databases.
Nothing here is specific to dhttp. It’s just basic web-to-database integration, relying on server-generated pages. Because the server is local, though, the usual rap against this technique—that it requires repeated round trips to the server—carries less force. In this case, it’s a very short round trip. Example 15.6 shows the method that implements the viewer:
Example 15-6. Implementation of the Jobs Data Viewer
sub viewData
{
my ($args) = @_;
my ($history) = $args->{history};
my ($user) = $args->{user};
my @viewcols = ($history) ? @histcols : @dispcols;
my $table = ($history) ? "_jobs" : "jobs";
my $cols = ($history) ? $select_cols . ",EDITOR" : $select_cols;
my $st = sprintf("SELECT $cols FROM $table %s %s %s",
$args->{constraint},
$args->{orderby},
$args->{orderhow},
);
my $results = dbSqlReturnAllHashRefs($main::jobs_dbh,$st);
my $server_name = makeServerName();
my $data = "";
my $th = "<tr>";
$th .= "<td align=center><$fs>menu</td>";
foreach ( @viewcols )
{
$th .= "<td align=center><$fs>";
amy linkpat = "<a href=$server_name/jobs_home?host=$main::hostname&port=
user=$user&orderby=$_&orderhow=ORDERHOW&selectcol=$args->{selectcol}&
selectval=$args->{selectval}&history=$history>";
$th .= $linkpat . "^</a>";
$th =~ s/ORDERHOW/asc/;
$th .= " $_ ";
$th .= $linkpat . "v</a>";
$th =~ s/ORDERHOW/desc/;
$th .= "</td>
";
}
$th .= "</tr>
";
$data .= "<table align=center border cellpadding=4 width=90%>
";
$data .= $th;
foreach my $row (@$results)
{
$data .= "<tr>";
my $escaped_jobclient = escape($row->{JOBCLIENT});
$row->{MENU} = makeMenu($user,$row->{JOBCODE},$escaped_jobclient);
if ($history)
{ $row->{TS} = tsToDate($row->{TS});}
$row->{JOBCLIENT} = "<a href="$server_name/jobs_home?host=$main::hostname&
port=$main::port&user=$user&cmp=$escaped_jobclient"
onClick="window.open('/sfa_home?cmp_name=$escaped_jobclient')">
$row->{JOBCLIENT}</a>";
foreach ( @jobdates )
{ $row->{$_} = shortDate($row->{$_}); }
foreach ( 'MENU', @viewcols )
{ $data .= "<td><$fs>$row->{$_}</td>
"; }
$data .= "</tr>
";
}
$data .= "</table>";
return $data;
}The viewData( ) method receives SQL
WHERE and ORDER BY clauses from
jobs_home( ) and composes an SQL query
accordingly. As it builds the header and body of the HTML table that
it emits, viewData( ) adds links that call /jobs_home again, with arguments
that specify new WHERE and ORDER BY clauses.
This technique is simple, concise, and yet very flexible. Every element of the display is a potential hook for a function that generates a new kind of view, and it’s trivial to create these new views. Suppose, for example, that you want the client name to link directly to a view in the contacts database. Here’s a solution:
$row->{jobclient} = "<a href=$server_name/jobs_home?
user=$user onClick=window.open('/sfa_home?cmp_name=" .
escape($row->{JOBCLIENT}) .
"')>$row->{JOBCLIENT}</a>";This snippet turns the client name into a link. The link’s
address points to the jobs_home( ) method, so
clicking it regenerates the view. But at the same time, the
onClick handler launches the contact manager in
another browser window, calling SFA with the client’s name so
it will come up displaying the client company along with contacts at
that company.
This methodology relies heavily on wrapping methods as URLs, turning scraps of data into URLs, and recycling URLs back into the engine. Coupled with a powerful scripting language like Perl, these techniques confer extraordinary leverage.