Accessing Databases

Accessing databases in Water is accomplished through the Water <data_source /> class.

Because Steam XML makes use of the underlying Java environment of the system on which it is running, every Java extension loaded on that system is available to Water to use. Most common database extensions are provided in the form of Java Archives, a.k.a., JAR files.

For each of the examples on this site, I have dropped the corresponding JDBC Driver JAR file(s) into both my Java SDK/jre/lib/ext folder and the Java Runtime/lib/ext folder. These JAR files must be in their proper folders before you start Steam XML for them to take effect.

A Quick Example

Figure 1 shows how Water can be used to access a MySQL database.

<!-- -------------------------------------------------------------
                   M Y S Q L   D R I V E R
-------------------------------------------------------------- -->
<!-- 'com.mysql.jdbc.Driver','org.gjt.mm.mysql.Driver' -->
<!-- we had to set user @% to have select permissions  -->

<data_source 
   a_uri="jdbc:mysql://personal.mycomp.com/rhubarb"
   protocol='com.mysql.jdbc.Driver' 
   user='scott'
   password='tiger'
 />

Figure 1

Parameters of <data_source />

• a_uri :: Specifies the URI for the database system we wish to connect to. In Figure 1 we are connecting to a specific database within the database management system named rhubarb. This URI includes protocol information specific to the JDBC driver you have chosen to use. You must consult your JDBC driver's documentation to find out what this URI should look like.

• protocol :: Specifies the name of the JDBC driver class to use to access the database. Once again, you must consult your JDBC driver implementation documentation to find out what driver name you should use.

• user :: Specifies the username needed to connect to the database. This username must be recognized by your database software, as must the machine you are connecting from.

• password :: Specifies the password needed to connect to the database. Even if you have your username and password correct, there may be other security settings in your database software that prevent you from connecting to the database. Consult your database software documentation if you suspect that your Water code is correct and something else might be preventing your access.

Using Variables

In Figure 1, all of the <data_source /> parameters are set by using string literals. Variables could just as easily have been used; in fact, in many cases, variables would be preferred so that code accessing the database can be made more flexible and secure. Figure 2 shows the use of variables, and includes an example of the use of the <concat /> method to build the data_source URI.

<!-- -------------------------------------------------------------
                   M Y S Q L   D R I V E R
-------------------------------------------------------------- -->
<!-- 'com.mysql.jdbc.Driver','org.gjt.mm.mysql.Driver' -->
<!-- we had to set user @% to have select permissions  -->

<data_source 
   a_uri=<concat "jdbc:mysql://" db_machine "/" database_name />
   protocol='com.mysql.jdbc.Driver' 
   user=login_name
   password=login_pass
 />

Figure 2

Getting Data From the Database

Today, most programmers have the ability to interact with a database by using Structured Query Language (SQL) commands. SQL has become a de facto standard language for communicating with a database, and as such, the creators of the Water language included the capability for making database queries using SQL.

Figure 3 shows a SQL query that retrieves a <result_set /> object. (If the SQL query cannot be completed because of database permissions problems, an error will occur and an error message will be printed.)

<!-- -------------------------------------------------------------
                   M Y S Q L   D R I V E R
-------------------------------------------------------------- -->
<!-- 'com.mysql.jdbc.Driver','org.gjt.mm.mysql.Driver' -->
<!-- we had to set user @% to have select permissions  -->

<data_source 
   a_uri="jdbc:mysql://personal.mycomp.com/rhubarb"
   protocol='com.mysql.jdbc.Driver' 
   user='scott'
   password='tiger'
 />.<execute_sql "SHOW TABLES" />

Figure 3

Using Data in the <result_set />

Upon execution of the code in Figure 3, Steam XML sends the query to the database system, and returns the response. It is then up to the programmer to make use of the data returned in the result_set.

Figure 4 is an example of using Water's very versatile <for_each /> loop to display the contents of each record in the result_set.

<!-- -------------------------------------------------------------
                   M Y S Q L   D R I V E R
-------------------------------------------------------------- -->
<!-- 'com.mysql.jdbc.Driver','org.gjt.mm.mysql.Driver' -->
<!-- we had to set user @% to have select permissions  -->

<data_source 
   a_uri="jdbc:mysql://personal.mycomp.com/rhubarb"
   protocol='com.mysql.jdbc.Driver' 
   user='scott'
   password='tiger'
/>.<execute_sql "SHOW TABLES" />.
<for_each include=vector_key combiner=insert>
   value.<for_each include=string_key combiner=insert >
      value 
   </>.<join <br /> />
</for_each>.<join <hr /> />

Figure 4

Using <for_each /> to Display Data

Water's <for_each /> method is very powerful and flexible. In Figure 4 two for_each loops are used to construct a set of data for display. The outer loop loops over the vector_keys in the <result_set />; these are the result_set records. The inner loop loops over the string_keys of each record in the <result_set />; these are the fields of each record.

In the <result_set /> returned from the "SHOW TABLES" query in Figure 4, each record has only one field: the name of a table in the specified database. If there were more fields in each record, the <join /> method in the example would place a HTML <br /> (line break) code between each field.

Also, notice that a HTML <hr /> code is used to <join /> each record to the one that follows it.

The rhubarb database in the example has three tables: Nouns, Adjectives, and Prepositions. Figure 5a shows the HTML source code of the result of executing the code in Figure 4 on these three table names.

Nouns<hr />Adjectives<hr />Prepositions

Figure 5a

Figure 5b shows what the output might look like in a browser window.

Figure 5b

Listing Employees in a Corporate Database

Now we will use what we have discussed above to contruct a Water program that displays employee information stored in an Employee table in a Corporate database.

For this example, we will access a different type of database software to demonstrate how easily we may switch from one database type to another.

Figure 6a shows the Water code, 6b shows the HTML output, and 6c shows the rendered HTML result.

<!-- -------------------------------------------------------------
                    F I R E B I R D   D B 
-------------------------------------------------------------- -->

<!-- org.firebirdsql.jdbc.FBDriver; FirebirdSQL-1.5.0RC4JDK_1.4.zip -->
<data_source 
   a_uri="jdbc:firebirdsql://personal.mycomp.com/c:\\Program Files\\firebird\rhubarb.fdb"
   protocol='org.firebirdsql.jdbc.FBDriver' 
   user='scott'
   password='tiger'
 />.
<execute_sql "SELECT * FROM Employees" />.
<for_each include=vector_key combiner=insert>
   value.<for_each include=string_key combiner=insert >
      <concat key '=' value />
   </>.<join <br /> />
</for_each>.<join <hr /> />

Figure 6a

f_name=Merrick<br />l_name=Stemen<br />dept=IT<hr />
f_name=Kenneth<br />l_name=Johns<br />dept=Sales<hr />
f_name=Jim<br />l_name=Gregory<br />dept=Maintenance<hr />
f_name=Janet<br />l_name=Storck<br />dept=Sales<hr />
f_name=Clint<br />l_name=Glenn<br />dept=Personnel

Figure 6b

Figure 6c

Oracle Thin Client Example

Figure 7a uses the Oracle Thin Client JDBC driver to access an Oracle database and select records into a <select /> element for a FORM on a web page. Notice that the username and password are not given as parameters because they appear in the URI.

<!-- -------------------------------------------------------------
        O R A C L E   T H I N   J D B C   D R I V E R
-------------------------------------------------------------- -->

<!-- This example creates a select box for a report list -->
<data_source 
a_uri="jdbc:oracle:thin:scott/tiger@oracle_machine:1521:tns_name"
protocol="oracle.jdbc.driver.OracleDriver" />.
<execute_sql> SELECT RN_RPT_NBR 
   FROM
      REPORT_TABLE 
   WHERE
      TUSER_KEY='BUS1'
</>.
<for_each include=vector_key combiner=insert initial_result=<select name="report" /> >
   <option 0=value.RN_RPT_NBR value=value.RN_RPT_NBR selected=false />
</for_each>

Figure 7a

Figure 7b uses the Oracle Thin Client JDBC driver to access an Oracle database and select records to be displayed in a TABLE on a web page.

<!-- -------------------------------------------------------------
        O R A C L E   T H I N   J D B C   D R I V E R
-------------------------------------------------------------- -->

<Example>
<table border=1 width="55%">
<tr><th 0='USER' /><th 0='REPORT' /><th 0='PRINTER' /></tr>
<do>
<data_source 
a_uri="jdbc:oracle:thin:scott/tiger@oracle_machine:1521:tns_name"
protocol="oracle.jdbc.driver.OracleDriver"
/>.<execute_sql> SELECT RN_RPT_NBR,TUSER_KEY,RN_PRNTR 
FROM 
   REPORT_TABLE
WHERE 
   TUSER_KEY='BUS1'
</>.<for_each include=vector_key combiner=insert>
   <TR  >
   <TD 0=value.TUSER_KEY />
   <TD 0=value.RN_RPT_NBR />
   <TD 0=value.RN_PRNTR />
   </TR>
</for_each>.<join "\n" />
</do>
</table>
</Example>

Figure 7b

A Final Word About Database Access

As you can see, Water's database access capabilities are impressive. Water can also take advantage of Java's built-in ODBC driver to connect to ODBC data sources. Because Steam XML is built on Java, any database accessibility you have through Java can be used by Steam XML.

Also, notice that by using Water's Concise XML notation and versatile <for_each /> method, you can easily generate web page elements for displaying your database query results in an appropriate fashion.