Re: Mapping SQL Result Sets to Browsers -- WEB400

Aaron-

I have used Hibernate. That is totally different, as it is an O/R
mapping tool and Spring JDBC template is not. With Spring you write
your own DAOs and your own SQL. If you want to deal with 15 tables
from a single DAO class, that is your choice. The only required
configuration is the data source(s). You can even use multiple
datasources in a single DAO. The Spring template just eliminates the
boilerplate code that is normally needed when using JDBC.

Below is a sample DAO that I have written:

public class RegistrationDaoImpl extends SimpleJdbcDaoSupport {

public void delete(RegistrationVO oRR){
getSimpleJdbcTemplate().update( "delete ENT_REGISTRATION where
EMPLOYEE_ID = ?",new Object[] { new Integer(oRR.getEmpID()) });
}

public RegistrationVO retrieveForAuthUID(String strUID) {
RegistrationVO oRegRec = null;
String sql = "select AUTH_USER_ID, EMPLOYEE_ID from
ENT_REGISTRATION where upper(AUTH_USER_ID) = ?";
oRegRec = getSimpleJdbcTemplate().queryForObject(sql, new
RegistrationRowMapper(), new Object[] { strUID.toUpperCase() });
return oRegRec;
}

public RegistrationVO retrieveForEmpID(int empID){
RegistrationVO oRegRec = null;
String sql = "select AUTH_USER_ID, EMPLOYEE_ID from
ENT_REGISTRATION where EMPLOYEE_ID = ?";
oRegRec = getSimpleJdbcTemplate().queryForObject(sql, new
RegistrationRowMapper(), new Object[] { new Integer(empID) });
return oRegRec;
}

public void create(RegistrationVO oRR){
int empID = oRR.getEmpID();
sql = "insert into ENT_REGISTRATION values (?,?)";
getSimpleJdbcTemplate().update(sql, new Object[] { new
Integer(empID), oRR.getAuthUserID() });
}

public void update(RegistrationVO oRR){
int empID = oRR.getEmpID();
String sql = "update ( select * from ENT_REGISTRATION where
EMPLOYEE_ID = ?) set AUTH_USER_ID = ?";
getSimpleJdbcTemplate().update(sql, new Object[] { new
Integer(empID), oRR.getAuthUserID() });
}

}

The only configuration that I did:

<bean id="RegDAO" class="walgreens.mypackage.dao.RegistrationDaoImpl">
<property name="dataSource"><ref bean="dataSourceA" /></property>
</bean>

<bean id="dataSourceA" class="org.springframework.jndi.JndiObjectFactoryBean">
<property name="jndiName" value="jdbc/myname" />
</bean>

If I wanted to eliminate the config, I could do the datasource config
in code. I chose not to do that because I am also using Spring IoC
and am injecting the "RegDAO" bean into my business classes.

-Sarah

On Thu, Jul 3, 2008 at 7:53 AM, Aaron Bartell <aaronbartell@xxxxxxxxx> wrote:

Sarah, have you used Hibernate? I am curious to know how Springs JDBC
template API differs from Hibernates ORM solution. For example, is there
any per-table configuration files needing to be setup? Do they require a
data access object per table?

I am asking these questions, because it sounds like they may be on their way
to a similar look and feel to how RPG has native DB I/O.

Thanks for your response,
Aaron Bartell
http://mowyourlawn.com

On Thu, Jul 3, 2008 at 6:23 AM, Sarah Poger Gladstone <
listmember@xxxxxxxxxxxxxx> wrote:

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.

--
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.

--
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.