Introduction

Last week, we went over JDBC in a little more detail then our first pass, describing options for Connection and Statement creation, .
We also discussed how to get meta data out of ResultSet's and JDBC Connection's.
We discussed JDBC SQLException and SQLWarning methods.
We then switched topics and began look at the infrastructure that might be behind scalable applications (in particular, web applications) that use a database.
Today, we consider two main topics executing sequences of commands, say for a transaction, directly on a database without having to go back and forth with a client language, and then consider security in database systems.

Stored Procedures

A stored procedure is a piece of SQL code consisting of declarative and/or procedural instructions and stored in the DBMS catalog (the DBMSs database of what objects it's managing).
It must be invoked explicitly by calling it from an application or command prompt.
This is different form triggers which are implicitly invoked when some event happens.
Like triggers different DBMSs support slightly different syntaxes for procedures.
Sqlite does not support stored procedures as fall as I can tell.

Example Stored Procedure

Consider the following simple example that works for MySQL:

CREATE TABLE DEMO_TABLE (a INTEGER);
INSERT INTO DEMO_TABLE VALUES (1), (2), (3), (4);
DELIMITER //
CREATE PROCEDURE getAllDemos()
  BEGIN
    SELECT * FROM demo_table;
  END //
DELIMITER ;
CALL getAllDemos();

The BEGIN ... END block is where we can put a sequence of SQL commands.
Since each of these commands ends with a semi-colon, we use the Mysql DELIMITER command to let Mysql no that we won't have finished entering the overall procedure until we type //
After we are done entering the procedure, we change the delimiter back to semi-colon.
To run the procedure we use the CALL command.
To get rid of a procedure you don't want you can use the DROP PROCEDURE command:
```
DROP PROCEDURE getAllDemos;
```
There is also an ALTER PROCEDURE command.

Example Procedure with Arguments

The example of the last slide didn't involve and SQL arguments, so let's look at example that does:

CREATE TABLE DEMO_TABLE (a INTEGER);
INSERT INTO DEMO_TABLE VALUES (1), (2), (3), (4);
DELIMITER //
CREATE PROCEDURE sumGreater(IN min_value INT, OUT sum_values INT)
  BEGIN
    SELECT SUM(a) INTO sum_values 
    FROM DEMO_TABLE
    WHERE a >= min_value;
  END //
DELIMITER ;
CALL sumGreater(2, @output);
SELECT @output;

Notice the argument list to the procedure: IN variables are inputs to the procedure, OUT variables are output from the procedure. One can also have INOUT variables which are both.
OUT variables can be later used in SELECT commands and the like after the procedure is executed.
Notice when used outside of procedure their values are mapped to global variables (@ variables -- In Mysql terminology called user-defined variables ).
If we hadn't declared OUT sum_values INT, we could have used @sum_values within the procedure. This would actually be setting a user-defined variable.

Quiz

Which of the following is true?

JDBC Statement's can never be created so that when executed the ResultSet is able to iterate backward through result rows.
We defined a scalable web-site to be one that can accommodate web-site increased usage.
A distributed hash-table might be used by a content delivery network.

Procedure with Multiple statements and Variables

Let's look at a more advanced procedure with some local variables, looping, and multiple statements:

CREATE TABLE DEMO_TABLE (a INTEGER);
INSERT INTO DEMO_TABLE VALUES (1), (2), (3), (4);
DELIMITER //
CREATE PROCEDURE deleteButThree(IN start INT, IN end INT)
  BEGIN
  DECLARE i INT;
  SET i = start;
  label1: LOOP
    IF i <> 3 THEN
       DELETE FROM DEMO_TABLE WHERE a = i;
    END IF;
    SET i = i + 1;
    IF i < end THEN 
      ITERATE label1;
      END IF;
    LEAVE label1;
  END LOOP label1;
  END //
DELIMITER ;
CALL deleteButThree(2, 5);

Remarks on Example

Notice we use the DECLARE command to declare local variables, we use the SET command to change the values of variables.
The above illustrates and IF cond THEN statements [ELSEIF conditions THEN statements] [ELSE conditions THEN statements] END IF; block.

Mysql also supports CASE statements:

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

We can create labeled LOOP blocks with the syntax: name_of_label: LOOP ...some statements... END LOOP name_of_label;
The label is optional.
The ITERATE label1; goes to the label in the above.
The LEAVE label1; command exits the label (like a break; statement in Java).

Besides LOOP, Mysql supports:

REPEAT
  some_statements
UNTIL condition END REPEAT;


WHILE condition DO
    ...
END WHILE;

Cursors

You might be wondering how we can cycle over the results of a query and do some operation in a procedure.
The basic idea is to use a cursor.
There are four commands associated with cursors: DECLARE a cursor based on a query, OPEN a cursor for processing, FETCH next row data from a cursor into variables, CLOSE the cursor.

Let's look at a simple example:

CREATE TABLE DEMO_TABLE (a INTEGER);
INSERT INTO DEMO_TABLE VALUES (1), (2), (3), (4);
DELIMITER //
CREATE PROCEDURE sumSquares(OUT sum_squares INT)
  BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE term INT DEFAULT 0;
  DECLARE demo_cursor CURSOR FOR SELECT a FROM DEMO_TABLE;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  SET sum_squares = 0;
  OPEN demo_cursor;
  label1: LOOP
    FETCH demo_cursor INTO term;
    IF done THEN LEAVE label1;
    END IF;
    SET sum_squares = sum_squares + term * term;
  END LOOP label1;
  END //
DELIMITER ;
CALL sumSquares(@out);
SELECT @out;

Notice in the above we have multiple DECLARE statements. All DECLARE statements must appear before any operations like SET, OPEN, etc in the BEGIN...END block.

Calling Procedures from JDBC

CallableStatement is a subclass of PreparedStatement which we have seen code for previously.

It adds the method prepareCall which can be used to set up and then execute a call to a stored procedure from Java:

import java.sql.CallableStatement;
...
CallableStatement stmt = conn.prepareCall("{call deleteButThree(?, ?)}");
stmt.setInt(1, 2);
stmt.setInt(2, 5);
stmt.execute();

CREATE FUNCTION

In addition to stored procedure, Mysql supports the create of function which can be used within expressions within SQL commands.

Below is an example of creating and using a function:

CREATE FUNCTION hello (s CHAR(20))
  RETURNS CHAR(50) DETERMINISTIC
  RETURN CONCAT('Hello, ',s,'!');
SELECT hello('world');

Postgres, ORACLE, DB2 support CREATE FUNCTION.
Sqlite does not support it, but its C API has functions like: sqlite3_create_function().

Programming Databases

Outline