Re: Mapping SQL Result Sets to Browsers -- WEB400

Nathan- Have you seen Spring's JDBC template API? In just 2 lines of
code a programmer can: query the database ( with parms) and return the
result(s) into a hashmap or collection of objects. It supports stored
procedure, updates, inserts, etc. It does NOT generate SQL. It is not
an O/R mapping tool. It simply reduces the lines of code needed when
dealing with JDBC APIs.

-Sarah

On Wed, Jul 2, 2008 at 10:19 PM, Nathan Andelin <nandelin@xxxxxxxxx> wrote:

Scott,

Although I wasn't specifically looking for a utility to show the results of any SQL statement in a browser, your PHP script illustrates the types of things that make scripting environments seductively appealing, and productive. So yes, that's what I'm interested in.

I looked up the meaning of db2_num_fields(), db2_field_name(), and db2_result(), so I could understand the script. The $stmt variable name threw me off; I initially took it to be a string containing an SQL statement, but later found that $stmt was a convention for referencing a result set.

You also make a good point about strongly typed variables in RPG and needing to convert them from various data types to strings suitable for browsers.

I understand the conventional wisdom that these types of things are harder to do in RPG, and that's what I'd like to change through a set of service programs, and utilizing SQL CLI, so your script is helpful in that regard.

I've been developing Web applications for quite a few years, but sometimes one needs to pause and sharpen the ax, so to speak; to look at one's tools and interfaces and make improvements.

Thank you,

Nathan.

----- Original Message ----
From: Scott Klement <web400@xxxxxxxxxxxxxxxx>
To: Web Enabling the AS400 / iSeries <web400@xxxxxxxxxxxx>
Sent: Wednesday, July 2, 2008 5:27:00 PM
Subject: Re: [WEB400] Mapping SQL Result Sets to Browsers

Hi Nathan,

I'm not really sure what you mean, so please forgive me if I'm way off
base. I recently (I was just farting around, honestly) wrote a PHP
script as a kind-of replacement for the "Run SQL Scripts" option in
iNav. It's a pain in the butt to open up iNav, since I rarely use it,
but I pretty much always have a browser open.

At any rate, all I did was write a function like this (this is PHP):

function db_print_table($stmt) {

echo '<table border="1">';
$cols = db2_num_fields($stmt);
$heading=TRUE;

while (db2_fetch_row($stmt)) {
if ($heading) {
echo "<tr>";
for ( $x=0; $x<$cols; $x++ ) {
echo "<th>" . db2_field_name($stmt, $x) . "</th>";
}
echo "</tr>";
$heading=FALSE;
}
echo "<tr>";
for ( $x=0; $x<$cols; $x++ ) {
echo "<td>" . db2_result($stmt, $x) . "</td>";
}
echo "</tr>";
}

echo "</table>";
return TRUE;
}

Basically, I execute my query (via the db2_exec() function provided by
the IBM_DB2 module for PHP) and I pass the returned value to that
db_print_table() routine I've pasted above.

It formats an HTML table from the rows/columns. Not much to it, really.

Is that what you're looking for?

I could probably code the same thing in RPG. The problem with RPG is
that it's a strongly-typed language, so all of the output from the SQL
statement would be in the data type of the database column (zoned,
packed, integer, etc) and I'd have to write code that converts it all,
no matter what size/type/etc of variable, to a character string. Not a
problem in PHP, where any variable can be any data type. For business
rules, RPG's strict data typing works better, but for taking any old
generic result set and making it into HTML, PHP's weak-types are easier.

Is that what you're talking about?

--
This is the Web Enabling the AS400 / iSeries (WEB400) mailing list
To post a message email: WEB400@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/web400
or email: WEB400-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/web400.