Re: Mapping SQL Result Sets to Browsers

[Nathan Andelin <nandelin@xxxxxxxxx>]

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?