SQL in action

This weekend I built a simple SQL exercise to explain the way to embed external libraries into weecod. Truly nothing extraordinary when you’ll see how you can do it in less than 30 lines of code. See it in action: http://weecod.com/sqldemo

Step 1: Create a new exercise

I use Java as the backend language to play with SQL. Go to your Dashboard > Question Editor > Create a new Java programming exercise.

Step 2: Add the external dependencies

I've packaged two libraries into a single zip file:

• HSQLDB, a relational database written in Java (BSD license). It's lightweight, fast and SQL standards-compliant.
• A simple helper class to display the results of a SQL statement in the console (to test a solution) and to validate two SQL statements return the same result (to validate a solution). You can obtain the source code from the zip file.

Upload the libs in "Add external dependencies".

Step 3: A solution skeleton

class SQL {
 static String query = "SELECT * FROM customer";
}

Candidates will have to update the “query” string to provide their own solution.

Step 4: Code to test a solution

import java.sql.*;
import com.weecod.sql.*;

public class Tester {

 public static void main(String[] args) throws SQLException {
   Connection conn = DriverManager.getConnection("jdbc:hsqldb:mem:mydb", "sa", "");
   conn.createStatement().executeUpdate("CREATE TABLE customer (firstname VARCHAR(32), lastname VARCHAR(32))");

   PreparedStatement insert = conn.prepareStatement("INSERT INTO customer VALUES (?,?)");
   String[][] inserts = { { "Andrew", "Walton" }, { "Lili", "Ma" }, { "Laurent", "Giroux" } };
   for (String[] d : inserts) {
     insert.setString(1, d[0]);
     insert.setString(2, d[1]);
     insert.executeUpdate();
   }
   SQLHelper.displayResults(SQL.query, conn);
//##DISPLAY_BEGIN##
//##DISPLAY_END##
 }
}

Here I create a standalone database and a table for customers. This piece of code perform some inserts to allow the candidate to test the query under practical conditions of use. The result is displayed in the console. Simple.

Step 5: Code to validate a solution

import java.sql.*;
import com.cartser.codemachine.java.*;
import com.cartser.codemachine.java.lafouine.*;
import com.weecod.sql.*;

public class Validator extends WeecodValidator {

 Connection conn;
 String validSelect = "SELECT firstname FROM customer WHERE firstname LIKE 'L%' ORDER BY firstname";

 public void validate() throws Exception {
   prepareDB();
   SQLHelper.validateResults(SQL.query, validSelect, conn);
 }

 void prepareDB() throws SQLException {
   conn = DriverManager.getConnection("jdbc:hsqldb:mem:mydb", "sa", "");
   conn.createStatement().executeUpdate("CREATE TABLE customer (firstname VARCHAR(32), lastname VARCHAR(32))");

   PreparedStatement insert = conn.prepareStatement("INSERT INTO customer VALUES (?,?)");
   String[][] inserts = { { "Laurie", "Akia" }, { "Andrew", "Walton" }, { "Lara", "Croft" } };
   for (String[] d : inserts) {
     insert.setString(1, d[0]);
     insert.setString(2, d[1]);
     insert.executeUpdate();
   }
 }
}

It’s quite similar to the code displayed in the previous step, except that this time the solution is checked to see if it works correctly: the result from a valid query (validSelect) is compared to the result from the candidate’s query. If they’re different SQLHelper.validateResults throws an exception and the solution is invalidated.