import java.io.*;
import java.sql.*;

public class TestCase3
{
	public static void main(String[] args)
	{
		try
		{
			runTest(args[0]);
		}
		catch(Exception exception)
		{
			exception.printStackTrace();
			System.exit(1);
		}
	}

	private static void runTest(String url) throws Exception
	{
		// Connect to the database
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = DriverManager.getConnection(url);

		// Print connection meta-data
		DatabaseMetaData meta = connection.getMetaData();
		System.out.println("product=" + meta.getDatabaseProductName() + " (" + meta.getDatabaseProductVersion() + ")");
		System.out.println("driver=" + meta.getDriverName() + " (" + meta.getDriverVersion() + ")");

		// Create test table and function
		Statement statement = connection.createStatement();
		statement.executeUpdate("DROP TABLE IF EXISTS test_table_2");
		statement.executeUpdate("DROP TABLE IF EXISTS test_table_1");
		statement.executeUpdate("CREATE TABLE test_table_1 (value_1 BIGINT PRIMARY KEY) ENGINE=InnoDB");
		statement.executeUpdate("INSERT INTO test_table_1 VALUES (1)");
		statement.executeUpdate("CREATE TABLE test_table_2 (value_2 BIGINT PRIMARY KEY, " +
				" FOREIGN KEY (value_2) REFERENCES test_table_1 (value_1) ON DELETE CASCADE) ENGINE=InnoDB");
		statement.executeUpdate("DROP FUNCTION IF EXISTS test_function");
		System.out.println("creating function ...");
		statement.executeUpdate("CREATE FUNCTION test_function(value BIGINT) RETURNS BIGINT " +
				"DETERMINISTIC MODIFIES SQL DATA BEGIN " +
				"INSERT INTO test_table_2 VALUES (value); RETURN value; END;"); 

		// Prepare the function call
		System.out.println("preparing function call ...");
		CallableStatement callable = connection.prepareCall("{? = call test_function(?)}");
		callable.registerOutParameter(1,Types.BIGINT);

		System.out.println("calling function with value of 1; this should succeed ...");
		callable.setLong(2,1);
		callable.executeUpdate();

		System.out.println("calling function with value of 2; this should throw an exception (foreign key violation) ...");
		callable.setLong(2,2);
		callable.executeUpdate();

		System.out.println("impossible; we should never get here.");

		// Cleanup test objects
		statement.executeUpdate("DROP TABLE test_table_2");
		statement.executeUpdate("DROP TABLE test_table_1");
		statement.executeUpdate("DROP FUNCTION test_function");

		// Free resources
		statement.close();
		connection.close();
	}
}