Water 5-Getting Started and Examples-Level 1 Examples

See also: ice

This example demonstrates using the MySql database from Water. We use Water's 'low level' database interface, SQL, for creating a database, a table and a couple of rows. Then we perform queries on the data.

To run the below examples in Steam XML IDE v5

Download mysql (You can download MySQL Community Server for free from dev.mysql.com/downloads)
Download the JDBC driver called Connector/J from dev.mysql.com/downloads/connector/j/5.1.html The zip file will have a file named like: mysql-connector-java-3.1.7-bin.jar It needs to be in the Java extensions folder. You can see the exact name of this folder in the Java Console after you launch Water. Typically it is something like: C:\Program Files\Java\j2re1.4.2_04\lib\ext
(you can also look in Tools -> Inspect classpath in Steam to find the directory path)
Launch mysql (if configured as Windows service, then MySQL should start automatically)
Just in case you have executed these expressions before, replace 'foo1' with the name of a database that does not exist yet.
Execute the following Water expressions:

<class customer name=req age=req/>

<set DBC = <resource "jdbc:mysql://localhost/"/> />

null

If you have given your db a username and password, you must use them when creating the resource like so: (probably unnecessary)

<set DBC = <resource."jdbc:mysql"  a_uri="jdbc:mysql://localhost:3306/" 
                                   username="root" 
                                   password="password"/>/>

null

DBC.<execute source="SHOW DATABASES"/>

DBC.<execute source="CREATE DATABASE foo1"/>

<set db1 = <resource "jdbc:mysql://localhost/foo1"/> />

null

db1.<execute source="SHOW TABLES"/>

db1.<execute source="CREATE TABLE customer (name varchar(255), age varchar(255))"/>

db1.<execute source="SHOW TABLES"/>

db1.<execute source='INSERT INTO customer VALUES ("Mike","77")'/>

db1.<execute source='INSERT INTO customer VALUES ("Christopher","88")'/>

db1.<execute source="SELECT * from customer"/>

<set query1=db1.<execute maker=customer source="SELECT * from customer"/> />

null

Example: number of rows found
query1.<length/>

2

Example: first row

query1.0

<customer name="Mike" age="77" do_not_run=true/>

Example: first row, value of 'name' column
query1.0.name

"Mike"

Example: convert age from a string to an integer so that you can use it in math operations.

integer.<from query1.0.age/>.<plus 1/>