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

public class testFails
{
    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");
        statement.executeUpdate("CREATE TABLE test ( " +
                                "test_id  BIGINT AUTO_INCREMENT PRIMARY KEY, " +
                                "test_code varchar(40) character set latin1 collate latin1_bin NOT NULL, " +
                                "external_system_id BIGINT NOT NULL, " +
                                "last_modified_date datetime NOT NULL, " +
                                "description varchar(80) character set latin1 collate latin1_bin default NULL, " +
                                "UNIQUE KEY test_u_code_system (test_code,external_system_id), " +
                                "KEY test_f_system (external_system_id) " +
                                ") " +
                                "ENGINE=InnoDB DEFAULT CHARSET=latin1; ");
        statement.executeUpdate("DROP FUNCTION IF EXISTS test_function");
        System.out.println("creating function ...");
        statement.executeUpdate("CREATE FUNCTION test_function (test_code_v varchar(40), " +
                                "external_system_id_v bigint(20), " +
                                "description_v varchar(80)) " +
                                "RETURNS bigint(20) " +
                                "LANGUAGE SQL " +
                                "DETERMINISTIC " +
                                "MODIFIES SQL DATA " +
                                "COMMENT 'This function provides lookup/insert/update behavior for table test' " +
                                "BEGIN " +
                                "DECLARE test_id_l bigint(20); " +
                                "IF test_code_v IS NULL OR external_system_id_v IS NULL " +
                                "THEN " +
                                "RETURN NULL; " +
                                "END IF; " +
                                "SELECT test_id " +
                                "INTO test_id_l " +
                                "FROM test " +
                                "WHERE test_code = test_code_v AND " +
                                "external_system_id = external_system_id_v; " +
                                "" +
                                "IF test_id_l IS NULL " +
                                "THEN " +
                                "INSERT INTO test (test_id, " +
                                "test_code, " +
                                "external_system_id, " +
                                "last_modified_date, " +
                                "description) " +
                                "VALUES (NULL, " +
                                "test_code_v, " +
                                "external_system_id_v, " +
                                "NOW(), " +
                                "description_v); " +
                                "SET test_id_l = LAST_INSERT_ID(); " +
                                "ELSE " +
                                "IF description_v IS NOT NULL " +
                                "THEN " +
                                "UPDATE test " +
                                "SET last_modified_date = NOW(), " +
                                "description = description_v " +
                                "WHERE test_id = test_id_l; " +
                                "END IF; " +
                                "END IF; " +
                                "" +
                                "RETURN(test_id_l); " +
                                "END; "); 

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

        System.out.println("calling function ...");
        callable.setString(2,"FOO");
        callable.setString(3,"BAR");
        callable.executeUpdate();

        // Cleanup test objects
        statement.executeUpdate("DROP TABLE IF EXISTS test");
        statement.executeUpdate("DROP FUNCTION IF EXISTS test_function");

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