/**
 * 
 */
package testsuite.simple;

import java.sql.CallableStatement;
import java.sql.Types;

import testsuite.BaseTestCase;

/**
 * @author Tonci
 *
 */
public class TestBug35199 extends BaseTestCase {

	/**
	 * @param name
	 */
	public TestBug35199(String name) {
		super(name);
		// TODO Auto-generated constructor stub
	}

	public void testBug35199() throws Exception {
		try {
	        // Create test table and function
	        this.stmt = this.conn.createStatement();
	        this.stmt.executeUpdate("DROP TABLE IF EXISTS test35199");
	        this.stmt.executeUpdate("CREATE TABLE test35199 ( " +
	                                "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; ");
	        this.stmt.executeUpdate("DROP FUNCTION IF EXISTS test_function35199");
	        this.stmt.executeUpdate("CREATE FUNCTION test_function35199 (test_code_v varchar(40), " +
	                                "external_system_id_v bigint, " +
	                                "description_v varchar(80)) " +
	                                "RETURNS bigint " +
	                                "LANGUAGE SQL " +
	                                "DETERMINISTIC " +
	                                "MODIFIES SQL DATA " +
	                                "COMMENT 'This function provides lookup/insert/update behavior for table test' " +
	                                "BEGIN " +
	                                "DECLARE test_id_l bigint; " +
	                                "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 test35199 " +
	                                "WHERE test_code = test_code_v AND " +
	                                "external_system_id = external_system_id_v; " +
	                                "" +
	                                "IF test_id_l IS NULL " +
	                                "THEN " +
	                                "INSERT INTO test35199 (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 test35199 " +
	                                "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
	        CallableStatement callable = this.conn.prepareCall("{? = call test_function35199(?,101,?)}");
	        callable.registerOutParameter(1,Types.BIGINT);

	        callable.setString(2,"FOO");
	        callable.setString(3,"BAR");
	        callable.executeUpdate();

		} finally {
			closeMemberJDBCResources();
		}
	}
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		junit.textui.TestRunner.run(TestBug35199.class);
	}

}