/*
 * Copyright (c) 1998, 2021 Oracle and/or its affiliates. All rights reserved.
 *
 * This program and the accompanying materials are made available under the
 * terms of the Eclipse Public License v. 2.0 which is available at
 * http://www.eclipse.org/legal/epl-2.0,
 * or the Eclipse Distribution License v. 1.0 which is available at
 * http://www.eclipse.org/org/documents/edl-v10.php.
 *
 * SPDX-License-Identifier: EPL-2.0 OR BSD-3-Clause
 */

// Contributors:
//     Oracle - initial API and implementation from Oracle TopLink
//     Markus KARG - SQL Anywhere now using WATCOM-SQL instead of Transact-SQL.
package org.eclipse.persistence.testing.tests.customsqlstoredprocedures;

import org.eclipse.persistence.exceptions.DatabaseException;
import org.eclipse.persistence.exceptions.ValidationException;
import java.io.IOException;
import java.io.Writer;
import org.eclipse.persistence.internal.sessions.AbstractSession;

import org.eclipse.persistence.descriptors.ClassDescriptor;
import org.eclipse.persistence.sessions.*;
import org.eclipse.persistence.tools.schemaframework.*;
import org.eclipse.persistence.mappings.*;
import org.eclipse.persistence.queries.*;
import org.eclipse.persistence.testing.models.employee.domain.Project;
import org.eclipse.persistence.testing.models.employee.domain.SmallProject;
import org.eclipse.persistence.testing.models.employee.domain.LargeProject;
import org.eclipse.persistence.testing.models.employee.domain.Employee;
import org.eclipse.persistence.testing.models.employee.relational.EmployeeSystem;

/**
 * <b>Purpose</b>: To define system behavior.
 * <p><b>Responsibilities</b>:    <ul>
 * <li> Login and return an initialized database session.
 * <li> Create and populate the database.
 * </ul>
 */
public class EmployeeCustomSQLSystem extends EmployeeSystem {
    @Override
    public void addDescriptors(DatabaseSession session) {
        session.logout();
        super.addDescriptors(session);
        setCustomSQL(session);

        // Force re-initialize.
        session.login();
    }

    public StoredProcedureDefinition buildOracleDeleteProcedure() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Delete_Employee");
        proc.addArgument("P_EMP_ID", Long.class);
        proc.addStatement("Delete FROM SALARY where EMP_ID = P_EMP_ID");
        proc.addStatement("Delete FROM EMPLOYEE where EMP_ID = P_EMP_ID");
        return proc;
    }

    public StoredProcedureDefinition buildOracleInsertProcedure() {
        // Assume no identity.
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Insert_Employee");
        proc.addArgument("P_EMP_ID", Long.class);
        proc.addArgument("P_SALARY", Integer.class);
        proc.addArgument("P_END_DATE", java.sql.Date.class);
        proc.addArgument("P_MANAGER_ID", Long.class);
        proc.addArgument("P_START_DATE", java.sql.Date.class);
        proc.addArgument("P_F_NAME", String.class, 40);
        proc.addArgument("P_L_NAME", String.class, 40);
        proc.addArgument("P_GENDER", String.class, 1);
        proc.addArgument("P_ADDR_ID", Long.class);
        proc.addArgument("P_VERSION", Long.class);
        proc.addArgument("P_START_TIME", java.sql.Time.class);
        proc.addArgument("P_END_TIME", java.sql.Time.class);
        proc.addStatement("Insert INTO EMPLOYEE (EMP_ID, END_DATE, MANAGER_ID, START_DATE, F_NAME, L_NAME, GENDER, ADDR_ID, VERSION, START_TIME, END_TIME) " + "VALUES (P_EMP_ID, P_END_DATE, P_MANAGER_ID, P_START_DATE, P_F_NAME, P_L_NAME, P_GENDER, P_ADDR_ID, P_VERSION, P_START_TIME, P_END_TIME)");
        proc.addStatement("Insert INTO SALARY (SALARY, EMP_ID) VALUES (P_SALARY, P_EMP_ID)");
        return proc;
    }

    public PackageDefinition buildOraclePackage() {
        PackageDefinition types = new PackageDefinition();
        types.setName("Cursor_Type");
        types.addStatement("Type Any_Cursor is REF CURSOR");
        return types;
    }

    public StoredProcedureDefinition buildOracleReadAllProcedure() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Read_All_Employees");
        proc.addOutputArgument("RESULT_CURSOR", "CURSOR_TYPE.ANY_CURSOR");
        proc.addStatement("OPEN RESULT_CURSOR FOR Select E.*, S.* from EMPLOYEE E, SALARY S WHERE E.EMP_ID = S.EMP_ID");
        return proc;
    }

    public StoredProcedureDefinition buildOracle2OutCursorsProcedure() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Read_Emp_Add");
        proc.addOutputArgument("CUR1", "CURSOR_TYPE.ANY_CURSOR");
        proc.addOutputArgument("CUR2", "CURSOR_TYPE.ANY_CURSOR");
        proc.addStatement("OPEN CUR1 FOR Select E.*, S.* from EMPLOYEE E, SALARY S WHERE E.EMP_ID = S.EMP_ID");
        proc.addStatement("OPEN CUR2 FOR Select a.* from ADDRESS a");
        return proc;
    }

    public StoredProcedureDefinition buildOracleReadObjectProcedure() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Read_Employee");
        proc.addArgument("P_EMP_ID", Long.class);
        proc.addOutputArgument("RESULT_CURSOR", "CURSOR_TYPE.ANY_CURSOR");
        proc.addStatement("OPEN RESULT_CURSOR FOR Select E.*, S.* from EMPLOYEE E, SALARY S where E.EMP_ID = S.EMP_ID AND E.EMP_ID = P_EMP_ID");
        return proc;
    }

    public StoredProcedureDefinition buildOracleStoredProcedureInOutPut() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("StoredProcedure_InOutput");
        proc.addInOutputArgument("P_EMP_ID", Long.class);
        proc.addInOutputArgument("P_F_NAME", String.class);
        proc.addStatement("P_EMP_ID := P_EMP_ID");
        proc.addStatement("P_F_NAME := P_F_NAME");
        return proc;
    }

    public StoredProcedureDefinition buildOracleStoredProcedureTimestamp() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("StoredProcedure_Timestamp");
        proc.addInOutputArgument("CURRENT_DATE", java.sql.Timestamp.class);
        proc.addStatement("CURRENT_DATE := CURRENT_DATE");
        return proc;
    }

    public StoredProcedureDefinition buildOracleStoredProcedureInOutOutIn() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("StoredProcedure_InOut_Out_In");
        proc.addInOutputArgument("P_INOUT", Long.class);
        proc.addOutputArgument("P_OUT", Long.class);
        proc.addArgument("P_IN", Long.class);
        proc.addStatement("P_OUT := P_INOUT");
        proc.addStatement("P_INOUT := P_IN");
        return proc;
    }

    public StoredProcedureDefinition buildOracleStoredProcedureARRAY() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("StoredProcedure_ARRAY");
        proc.addOutputArgument("P_OUT", "TEST_STRING_ARRAY");
        proc.addStatement("P_OUT := TEST_STRING_ARRAY(10)");
        return proc;
    }

    public static VarrayDefinition buildVARRAYTypeDefinition() {
        VarrayDefinition definition = new VarrayDefinition();

        definition.setName("TEST_STRING_ARRAY");
        definition.setSize(20);
        definition.setType(String.class);
        definition.setTypeSize(30);

        return definition;
    }

    public StoredFunctionDefinition buildOracleStoredFunctionInOutOutIn() {
        StoredFunctionDefinition func = new StoredFunctionDefinition();
        func.setName("StoredFunction_InOut_Out_In");
        func.addInOutputArgument("P_INOUT", Long.class);
        func.addOutputArgument("P_OUT", Long.class);
        func.addArgument("P_IN", Long.class);
        func.setReturnType(Long.class);
        func.addStatement("P_OUT := P_INOUT");
        func.addStatement("P_INOUT := P_IN");
        func.addStatement("RETURN P_OUT");
        return func;
    }

    public NestedTableDefinition buildOracleLOOKUPTABLETYPE() {
        NestedTableDefinition ntd = new NestedTableDefinition();
        ntd.setName("SF_LOOKUP_TBL");
        ntd.setTypeName("SF_LOOKUP_RECORD");
        return ntd;
    }

    public TypeDefinition buildOracleLOOKUPRECORDTYPE() {
        TypeDefinition td = new TypeDefinition();
        td.setName("SF_LOOKUP_RECORD");

        FieldDefinition fd1 = new FieldDefinition();
        fd1.setName("ATTR_1");
        fd1.setTypeName("VARCHAR2");
        fd1.setSize(200);
        td.addField(fd1);

        FieldDefinition fd2 = new FieldDefinition();
        fd2.setName("ATTR_2");
        fd2.setTypeName("VARCHAR2");
        fd2.setSize(200);
        td.addField(fd2);

        return td;
    }

    public PackageDefinition buildOraclePackageStoredFunctionResultCursor() {
        PackageDefinition pkgd = new PackageDefinition();
        pkgd.setName("PackageFunction_ResultCursor");
        pkgd.addStatement("TYPE REF_CURSOR IS REF CURSOR");
        pkgd.addStatement("FUNCTION BUSINESS_DATE (P_CODE IN VARCHAR2, P_LOOKUP_TBL IN SF_LOOKUP_TBL) RETURN REF_CURSOR");
        return pkgd;
    }

    public StoredProcedureDefinition buildOracleUpdateProcedure() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Update_Employee");
        proc.addArgument("P_EMP_ID", Long.class);
        proc.addArgument("P_SALARY", Integer.class);
        proc.addArgument("P_END_DATE", java.sql.Date.class);
        proc.addArgument("P_MANAGER_ID", Long.class);
        proc.addArgument("P_START_DATE", java.sql.Date.class);
        proc.addArgument("P_F_NAME", String.class, 40);
        proc.addArgument("P_L_NAME", String.class, 40);
        proc.addArgument("P_GENDER", String.class, 1);
        proc.addArgument("P_ADDR_ID", Long.class);
        proc.addArgument("P_START_TIME", java.sql.Time.class);
        proc.addArgument("P_END_TIME", java.sql.Time.class);
        proc.addOutputArgument("O_ERROR_CODE", Long.class);
        proc.addStatement("Update SALARY set SALARY = P_SALARY WHERE (EMP_ID = P_EMP_ID)");
        proc.addStatement("Update EMPLOYEE set END_DATE = P_END_DATE, MANAGER_ID = P_MANAGER_ID, " + "START_DATE = P_START_DATE, F_NAME = P_F_NAME, L_NAME = P_L_NAME, GENDER = P_GENDER, ADDR_ID = P_ADDR_ID " + "WHERE (EMP_ID = P_EMP_ID)");
        return proc;
    }

    public StoredProcedureDefinition buildSQLServerDeleteProcedure() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Delete_Employee");
        proc.addArgument("EMP_ID", Long.class);
        proc.addStatement("Delete FROM SALARY where EMP_ID = @EMP_ID");
        proc.addStatement("Delete FROM EMPLOYEE where EMP_ID = @EMP_ID");
        return proc;
    }

    public StoredProcedureDefinition buildSQLServerInsertProcedure() {
        // Assume no identity.
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Insert_Employee");
        proc.addArgument("EMP_ID", Long.class);
        proc.addArgument("SALARY", Integer.class);
        proc.addArgument("END_DATE", java.sql.Date.class);
        proc.addArgument("MANAGER_ID", Long.class);
        proc.addArgument("START_DATE", java.sql.Date.class);
        proc.addArgument("F_NAME", String.class, 40);
        proc.addArgument("L_NAME", String.class, 40);
        proc.addArgument("GENDER", String.class, 1);
        proc.addArgument("ADDR_ID", Long.class);
        proc.addOutputArgument("VERSION", Long.class);
        proc.addArgument("START_TIME", java.sql.Time.class);
        proc.addArgument("END_TIME", java.sql.Time.class);
        proc.addStatement("Insert INTO EMPLOYEE (EMP_ID, END_DATE, MANAGER_ID, START_DATE, F_NAME, L_NAME, GENDER, ADDR_ID, VERSION, START_TIME, END_TIME) " + "VALUES (@EMP_ID, @END_DATE, @MANAGER_ID, @START_DATE, @F_NAME, @L_NAME, @GENDER, @ADDR_ID, @VERSION, @START_TIME, @END_TIME)");
        proc.addStatement("Insert INTO SALARY (SALARY, EMP_ID) VALUES (@SALARY, @EMP_ID)");
        proc.addStatement("SELECT @VERSION = 952");
        return proc;
    }

    public StoredProcedureDefinition buildSQLServerReadAllProcedure() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Read_All_Employees");
        proc.addStatement("Select E.*, S.* from EMPLOYEE E, SALARY S WHERE E.EMP_ID = S.EMP_ID");
        return proc;
    }

    public StoredProcedureDefinition buildSQLServerReadObjectProcedure() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Read_Employee");
        proc.addArgument("EMP_ID", Long.class);
        proc.addStatement("Select E.*, S.* from EMPLOYEE E, SALARY S where E.EMP_ID = S.EMP_ID AND E.EMP_ID = @EMP_ID");
        return proc;
    }

    public StoredProcedureDefinition buildSQLServerSelectWithOutputAndResultSetProcedure() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Select_Output_and_ResultSet");
        proc.addArgument("ARG1", Long.class);
        proc.addOutputArgument("VERSION", Long.class);
        proc.addStatement("SELECT @VERSION = 23");
        proc.addStatement("Select E.*, S.* from EMPLOYEE E, SALARY S where E.EMP_ID = S.EMP_ID AND E.F_NAME = 'Bob'");
        return proc;
    }

    public StoredProcedureDefinition buildSQLServerSelectWithOutputProcedure() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Select_Employee_using_Output");
        proc.addArgument("ARG1", Long.class);
        proc.addOutputArgument("VERSION", Long.class);
        proc.addStatement("SELECT @VERSION = 23");
        return proc;
    }

    public StoredProcedureDefinition buildSQLServerUpdateProcedure() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Update_Employee");
        proc.addArgument("EMP_ID", Long.class);
        proc.addArgument("SALARY", Integer.class);
        proc.addArgument("END_DATE", java.sql.Date.class);
        proc.addArgument("MANAGER_ID", Long.class);
        proc.addArgument("START_DATE", java.sql.Date.class);
        proc.addArgument("F_NAME", String.class, 40);
        proc.addArgument("L_NAME", String.class, 40);
        proc.addArgument("GENDER", String.class, 1);
        proc.addArgument("ADDR_ID", Long.class);
        proc.addArgument("START_TIME", java.sql.Time.class);
        proc.addArgument("END_TIME", java.sql.Time.class);
        proc.addStatement("Update SALARY set SALARY = @SALARY WHERE (EMP_ID = @EMP_ID)");
        // ELBug 339072 - A Row is inserted with version value as 'null' as optimistic locking
        // is turned off. During update, it executes the statement using version = null check
        // in where clause which fails to update the row. So, removing version field from
        // following update statement to fix the issue.
        proc.addStatement("Update EMPLOYEE set END_DATE = @END_DATE, MANAGER_ID = @MANAGER_ID, " + "START_DATE = @START_DATE, F_NAME = @F_NAME, L_NAME = @L_NAME, GENDER = @GENDER, ADDR_ID = @ADDR_ID " + " WHERE (EMP_ID = @EMP_ID)");
        return proc;
    }

    public StoredProcedureDefinition buildSybaseDeleteProcedure() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Delete_Employee");
        proc.addArgument("EMP_ID", Long.class);
        proc.addStatement("Delete FROM SALARY where EMP_ID = @EMP_ID");
        proc.addStatement("Delete FROM EMPLOYEE where EMP_ID = @EMP_ID");
        return proc;
    }

    public StoredProcedureDefinition buildSybaseInsertProcedure() {
        // Assume no identity.
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Insert_Employee");

        proc.addArgument("EMP_ID", Long.class);
        proc.addArgument("SALARY", Integer.class);
        proc.addArgument("END_DATE", java.sql.Date.class);
        proc.addArgument("MANAGER_ID", Long.class);
        proc.addArgument("START_DATE", java.sql.Date.class);
        proc.addArgument("F_NAME", String.class, 40);
        proc.addArgument("L_NAME", String.class, 40);
        proc.addArgument("GENDER", String.class, 1);
        proc.addArgument("ADDR_ID", Long.class);
        proc.addArgument("START_TIME", java.sql.Time.class);
        proc.addArgument("END_TIME", java.sql.Time.class);
        proc.addArgument("VERSION", Long.class);
        proc.addOutputArgument("OUT_VERSION", Long.class);

        proc.addStatement("Insert INTO EMPLOYEE (EMP_ID, END_DATE, MANAGER_ID, START_DATE, F_NAME, L_NAME, GENDER, ADDR_ID, VERSION, START_TIME, END_TIME) " + "VALUES (@EMP_ID, @END_DATE, @MANAGER_ID, @START_DATE, @F_NAME, @L_NAME, @GENDER, @ADDR_ID, @VERSION, @START_TIME, @END_TIME)");
        proc.addStatement("Insert INTO SALARY (SALARY, EMP_ID) VALUES (@SALARY, @EMP_ID)");
        proc.addStatement("Select @OUT_VERSION = 952");

        return proc;
    }

    public StoredProcedureDefinition buildSybaseReadAllProcedure() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Read_All_Employees");
        proc.addStatement("Select E.*, S.* from EMPLOYEE E, SALARY S WHERE E.EMP_ID = S.EMP_ID");
        return proc;
    }

    public StoredProcedureDefinition buildMySQL2ResultSetProcedure() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Read_Emp_Add");
        proc.addStatement("Select E.*, S.* from EMPLOYEE E, SALARY S WHERE E.EMP_ID = S.EMP_ID");
        proc.addStatement("Select a.* from ADDRESS a");
        return proc;
    }

    public StoredProcedureDefinition buildSybaseReadObjectProcedure() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Read_Employee");
        proc.addArgument("EMP_ID", Long.class);
        proc.addStatement("Select E.*, S.* from EMPLOYEE E, SALARY S where E.EMP_ID = S.EMP_ID AND E.EMP_ID = @EMP_ID");
        return proc;
    }

    public StoredProcedureDefinition buildSybaseSelectWithOutputAndResultSetProcedure() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Select_Output_and_ResultSet");
        proc.addArgument("ARG1", Long.class);
        proc.addOutputArgument("VERSION", Long.class);
        proc.addStatement("SELECT @VERSION = 23");
        proc.addStatement("Select E.*, S.* from EMPLOYEE E, SALARY S where E.EMP_ID = S.EMP_ID AND E.F_NAME = 'Bob'");
        return proc;
    }

    public StoredProcedureDefinition buildSybaseWithoutParametersProcedure() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Rise_All_Salaries");
        proc.addStatement("Update SALARY set SALARY = SALARY * 1.1");
        return proc;
    }

    public StoredProcedureDefinition buildSybaseUpdateProcedure() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Update_Employee");
        proc.addArgument("EMP_ID", Long.class);
        proc.addArgument("SALARY", Integer.class);
        proc.addArgument("END_DATE", java.sql.Date.class);
        proc.addArgument("MANAGER_ID", Long.class);
        proc.addArgument("START_DATE", java.sql.Date.class);
        proc.addArgument("F_NAME", String.class, 40);
        proc.addArgument("L_NAME", String.class, 40);
        proc.addArgument("GENDER", String.class, 1);
        proc.addArgument("ADDR_ID", Long.class);
        proc.addArgument("VERSION", Long.class);
        proc.addArgument("START_TIME", java.sql.Time.class);
        proc.addArgument("END_TIME", java.sql.Time.class);
        proc.addStatement("Update SALARY set SALARY = @SALARY WHERE (EMP_ID = @EMP_ID)");
        proc.addStatement("Update EMPLOYEE set END_DATE = @END_DATE, MANAGER_ID = @MANAGER_ID, " + "START_DATE = @START_DATE, F_NAME = @F_NAME, L_NAME = @L_NAME, GENDER = @GENDER, ADDR_ID = @ADDR_ID, " + "VERSION = @VERSION + 1 WHERE ((EMP_ID = @EMP_ID) AND (VERSION = @VERSION))");
        return proc;
    }

    private static StoredProcedureDefinition buildSQLAnywhereDeleteProcedure() {
        final StoredProcedureDefinition procedure = new StoredProcedureDefinition();
        procedure.setName("Delete_Employee");
        procedure.addArgument("_EMP_ID", Long.class);
        procedure.addStatement("DELETE FROM SALARY WHERE EMP_ID = _EMP_ID");
        procedure.addStatement("DELETE FROM EMPLOYEE WHERE EMP_ID = _EMP_ID");
        return procedure;
    }

    private static StoredProcedureDefinition buildSQLAnywhereInsertProcedure() {
        // Assume no identity.
        final StoredProcedureDefinition procedure = new StoredProcedureDefinition();
        procedure.setName("Insert_Employee");
        procedure.addArgument("_EMP_ID", Long.class);
        procedure.addArgument("_SALARY", Integer.class);
        procedure.addArgument("_END_DATE", java.sql.Date.class);
        procedure.addArgument("_MANAGER_ID", Long.class);
        procedure.addArgument("_START_DATE", java.sql.Date.class);
        procedure.addArgument("_F_NAME", String.class, 40);
        procedure.addArgument("_L_NAME", String.class, 40);
        procedure.addArgument("_GENDER", String.class, 1);
        procedure.addArgument("_ADDR_ID", Long.class);
        procedure.addArgument("_START_TIME", java.sql.Time.class);
        procedure.addArgument("_END_TIME", java.sql.Time.class);
        procedure.addArgument("_VERSION", Long.class);
        procedure.addOutputArgument("_OUT_VERSION", Long.class);
        procedure.addStatement("INSERT INTO EMPLOYEE (EMP_ID, END_DATE, MANAGER_ID, START_DATE, F_NAME, L_NAME, GENDER, ADDR_ID, VERSION, START_TIME, END_TIME) VALUES (_EMP_ID, _END_DATE, _MANAGER_ID, _START_DATE, _F_NAME, _L_NAME, _GENDER, _ADDR_ID, _VERSION, _START_TIME, _END_TIME)");
        procedure.addStatement("INSERT INTO SALARY (SALARY, EMP_ID) VALUES (_SALARY, _EMP_ID)");
        procedure.addStatement("SET _OUT_VERSION = 952");
        return procedure;
    }

    private static StoredProcedureDefinition buildSQLAnywhereReadAllProcedure() {
        final StoredProcedureDefinition procedure = new StoredProcedureDefinition();
        procedure.setName("Read_All_Employees");
        procedure.addStatement("SELECT E.*, S.* FROM EMPLOYEE E JOIN SALARY S ON E.EMP_ID = S.EMP_ID");
        return procedure;
    }

    private static StoredProcedureDefinition buildSQLAnywhereReadObjectProcedure() {
        final StoredProcedureDefinition procedure = new StoredProcedureDefinition();
        procedure.setName("Read_Employee");
        procedure.addArgument("_EMP_ID", Long.class);
        procedure.addStatement("SELECT E.*, S.* FROM EMPLOYEE E JOIN SALARY S ON E.EMP_ID = S.EMP_ID WHERE E.EMP_ID = _EMP_ID");
        return procedure;
    }

    private static StoredProcedureDefinition buildSQLAnywhereSelectWithOutputAndResultSetProcedure() {
        final StoredProcedureDefinition procedure = new StoredProcedureDefinition();
        procedure.setName("Select_Output_and_ResultSet");
        procedure.addArgument("ARG1", Long.class);
        procedure.addOutputArgument("VERSION", Long.class);
        procedure.addStatement("SET VERSION = 23");
        procedure.addStatement("SELECT E.*, S.* FROM EMPLOYEE E JOIN SALARY S ON E.EMP_ID = S.EMP_ID WHERE E.F_NAME = 'Bob'");
        return procedure;
    }

    private static StoredProcedureDefinition buildSQLAnywhereWithoutParametersProcedure() {
        final StoredProcedureDefinition procedure = new StoredProcedureDefinition();
        procedure.setName("Rise_All_Salaries");
        procedure.addStatement("UPDATE SALARY SET SALARY = SALARY * 1.1");
        return procedure;
    }

    private static StoredProcedureDefinition buildSQLAnywhereUpdateProcedure() {
        final StoredProcedureDefinition procedure = new StoredProcedureDefinition();
        procedure.setName("Update_Employee");
        procedure.addArgument("_EMP_ID", Long.class);
        procedure.addArgument("_SALARY", Integer.class);
        procedure.addArgument("_END_DATE", java.sql.Date.class);
        procedure.addArgument("_MANAGER_ID", Long.class);
        procedure.addArgument("_START_DATE", java.sql.Date.class);
        procedure.addArgument("_F_NAME", String.class, 40);
        procedure.addArgument("_L_NAME", String.class, 40);
        procedure.addArgument("_GENDER", String.class, 1);
        procedure.addArgument("_ADDR_ID", Long.class);
        procedure.addArgument("_VERSION", Long.class);
        procedure.addArgument("_START_TIME", java.sql.Time.class);
        procedure.addArgument("_END_TIME", java.sql.Time.class);
        procedure.addStatement("UPDATE SALARY SET SALARY = _SALARY WHERE EMP_ID = _EMP_ID");
        procedure.addStatement("UPDATE EMPLOYEE SET END_DATE = _END_DATE, MANAGER_ID = _MANAGER_ID, START_DATE = _START_DATE, F_NAME = _F_NAME, L_NAME = _L_NAME, GENDER = _GENDER, ADDR_ID = _ADDR_ID, VERSION = _VERSION + 1 WHERE EMP_ID = _EMP_ID AND VERSION = _VERSION");
        return procedure;
    }

    public StoredProcedureDefinition buildDB2SelectWithOutputAndResultSetProcedure() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("OUT_RES_TEST");
        proc.addOutputArgument("OUT1", Integer.class);
        proc.addOutputArgument("OUT2", Integer.class);
        proc.addOutputArgument("OUT3", Integer.class);
        proc.addStatement("DECLARE C2 CURSOR WITH RETURN FOR SELECT F_NAME FROM EMPLOYEE");
        // DB2 driver seems to have an issue if you assign a value to outputs... so leave as null for now.
        //proc.addStatement("SELECT MAX(SALARY) INTO OUT1 FROM SALARY");
        //proc.addStatement("SELECT MAX(SALARY) INTO OUT2 FROM SALARY");
        //proc.addStatement("SELECT MAX(SALARY) INTO OUT3 FROM SALARY");
        proc.addStatement("OPEN C2");
        return proc;
    }

    class PervasiveStoredProcedureDefinition extends StoredProcedureDefinition {
        protected String returnString;

        public PervasiveStoredProcedureDefinition() {
            this.returnString = "";
        }

        public void setReturnString (String theString) {
            this.returnString = theString;
        }

        @Override
        protected void printReturn(Writer writer, AbstractSession session) throws ValidationException {
            try {
                writer.write(returnString);
            } catch (IOException ioException) {
                throw ValidationException.fileError(ioException);
            }
        }
    }

    public StoredProcedureDefinition buildPervasiveDeleteProcedure() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Delete_Employee");
        proc.addArgument("EMP_ID", Long.class);
        proc.addStatement("Delete FROM SALARY where EMP_ID = :EMP_ID");
        proc.addStatement("Delete FROM EMPLOYEE where EMP_ID = :EMP_ID");
        return proc;
    }

    public StoredProcedureDefinition buildPervasiveInsertProcedure() {
        // Assume no identity.
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Insert_Employee");
        proc.addArgument("EMP_ID", Long.class);
        proc.addArgument("SALARY", Integer.class);
        proc.addArgument("END_DATE", java.sql.Date.class);
        proc.addArgument("MANAGER_ID", Long.class);
        proc.addArgument("START_DATE", java.sql.Date.class);
        proc.addArgument("F_NAME", String.class, 40);
        proc.addArgument("L_NAME", String.class, 40);
        proc.addArgument("GENDER", String.class, 1);
        proc.addArgument("ADDR_ID", Long.class);
        proc.addArgument("START_TIME", java.sql.Time.class);
        proc.addArgument("END_TIME", java.sql.Time.class);
        proc.addStatement("Insert INTO EMPLOYEE (EMP_ID, END_DATE, MANAGER_ID, START_DATE, F_NAME, L_NAME, GENDER, ADDR_ID, START_TIME, END_TIME) " + "VALUES (:EMP_ID, :END_DATE, :MANAGER_ID, :START_DATE, :F_NAME, :L_NAME, :GENDER, :ADDR_ID, :START_TIME, :END_TIME)");
        proc.addStatement("Insert INTO SALARY (SALARY, EMP_ID) VALUES (:SALARY, :EMP_ID)");
        return proc;
    }

    public StoredProcedureDefinition buildPervasiveReadAllProcedure() {
        PervasiveStoredProcedureDefinition proc = new PervasiveStoredProcedureDefinition();
        proc.setReturnString (
        " RETURNS (" +
        "EMP_ID BIGINT, " +
        "F_NAME CHAR(40), " +
        "L_NAME CHAR(40), " +
        "START_DATE DATE, " +
        "END_DATE DATE, " +
        "START_TIME TIME, " +
        "END_TIME TIME, " +
        "GENDER CHAR(1), " +
        "ADDR_ID BIGINT, " +
        "MANAGER_ID BIGINT, " +
        "VERSION BIGINT, " +
        "EMP_ID BIGINT, " +
        "SALARY INTEGER " +
        ")"
        );
        proc.setName("Read_All_Employees");
        proc.addStatement("Select E.*, S.* from EMPLOYEE E, SALARY S WHERE E.EMP_ID = S.EMP_ID");
        return proc;
    }

    public StoredProcedureDefinition buildPervasiveReadObjectProcedure() {
        PervasiveStoredProcedureDefinition proc = new PervasiveStoredProcedureDefinition();
        proc.setReturnString (
        " RETURNS (" +
        "EMP_ID BIGINT, " +
        "F_NAME CHAR(40), " +
        "L_NAME CHAR(40), " +
        "START_DATE DATE, " +
        "END_DATE DATE, " +
        "START_TIME TIME, " +
        "END_TIME TIME, " +
        "GENDER CHAR(1), " +
        "ADDR_ID BIGINT, " +
        "MANAGER_ID BIGINT, " +
        "VERSION BIGINT, " +
        "EMP_ID BIGINT, " +
        "SALARY INTEGER " +
        ")"
        );
        proc.setName("Read_Employee");
        proc.addArgument("EMP_ID", Long.class);
        proc.addStatement("Select E.*, S.* from EMPLOYEE E, SALARY S where E.EMP_ID = S.EMP_ID AND E.EMP_ID = :EMP_ID");
        return proc;
    }

    public StoredProcedureDefinition buildPervasiveStoredProcedureInOutPut() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("StoredProcedure_InOutput");
        proc.addInOutputArgument("EMP_ID", Long.class);
        proc.addInOutputArgument("F_NAME", String.class);
        proc.addStatement("SET :EMP_ID = :EMP_ID");
        proc.addStatement("SET :F_NAME = :F_NAME");
        return proc;
    }

    public StoredProcedureDefinition buildPervasiveSelectWithOutputAndResultSetProcedure() {
        PervasiveStoredProcedureDefinition proc = new PervasiveStoredProcedureDefinition();
        proc.setReturnString (
        " RETURNS (" +
        "EMP_ID BIGINT, " +
        "F_NAME CHAR(40), " +
        "L_NAME CHAR(40), " +
        "START_DATE DATE, " +
        "END_DATE DATE, " +
        "START_TIME TIME, " +
        "END_TIME TIME, " +
        "GENDER CHAR(1), " +
        "ADDR_ID BIGINT, " +
        "MANAGER_ID BIGINT, " +
        "VERSION BIGINT, " +
        "EMP_ID BIGINT, " +
        "SALARY INTEGER " +
        ")"
        );
        proc.setName("Select_Output_and_ResultSet");
        proc.addArgument("ARG1", Long.class);
        proc.addOutputArgument("VERSION", Long.class);
        proc.addStatement("SET :VERSION = 23");
        proc.addStatement("Select E.*, S.* from EMPLOYEE E, SALARY S where E.EMP_ID = S.EMP_ID AND E.F_NAME = 'Bob'");
        return proc;
    }

    public StoredProcedureDefinition buildPervasiveSelectWithOutputProcedure() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Select_Employee_using_Output");
        proc.addArgument("ARG1", Long.class);
        proc.addOutputArgument("VERSION", Long.class);
        proc.addStatement("SET :VERSION = 23");
        return proc;
    }

    public StoredProcedureDefinition buildPervasiveUpdateProcedure() {
        StoredProcedureDefinition proc = new StoredProcedureDefinition();
        proc.setName("Update_Employee");
        proc.addArgument("EMP_ID", Long.class);
        proc.addArgument("SALARY", Integer.class);
        proc.addArgument("END_DATE", java.sql.Date.class);
        proc.addArgument("MANAGER_ID", Long.class);
        proc.addArgument("START_DATE", java.sql.Date.class);
        proc.addArgument("F_NAME", String.class, 40);
        proc.addArgument("L_NAME", String.class, 40);
        proc.addArgument("GENDER", String.class, 1);
        proc.addArgument("ADDR_ID", Long.class);
        proc.addArgument("START_TIME", java.sql.Time.class);
        proc.addArgument("END_TIME", java.sql.Time.class);
        proc.addStatement("Update SALARY set SALARY = :SALARY WHERE (EMP_ID = :EMP_ID)");
        proc.addStatement("Update EMPLOYEE set END_DATE = :END_DATE, MANAGER_ID = :MANAGER_ID, " + "START_DATE = :START_DATE, F_NAME = :F_NAME, L_NAME = :L_NAME, GENDER = :GENDER, ADDR_ID = :ADDR_ID " + " WHERE (EMP_ID = :EMP_ID)");
        return proc;
    }

    /**
     * Also creates the procs.
     */
    @Override
    public void createTables(DatabaseSession session) {
        super.createTables(session);
        org.eclipse.persistence.internal.databaseaccess.DatabasePlatform platform = session.getLogin().getPlatform();
        SchemaManager schema = new SchemaManager((session));

        if (platform.isSQLServer()) {
            schema.replaceObject(buildSQLServerDeleteProcedure());
            schema.replaceObject(buildSQLServerReadAllProcedure());
            schema.replaceObject(buildSQLServerReadObjectProcedure());
            schema.replaceObject(buildSQLServerInsertProcedure());
            schema.replaceObject(buildSQLServerUpdateProcedure());
            schema.replaceObject(buildSQLServerSelectWithOutputProcedure());
            schema.replaceObject(buildSQLServerSelectWithOutputAndResultSetProcedure());
        }
        if (platform.isSybase()) {
            session.getLogin().handleTransactionsManuallyForSybaseJConnect();
            schema.replaceObject(buildSybaseDeleteProcedure());
            schema.replaceObject(buildSybaseReadAllProcedure());
            schema.replaceObject(buildSybaseReadObjectProcedure());
            schema.replaceObject(buildSybaseInsertProcedure());
            schema.replaceObject(buildSybaseUpdateProcedure());
            schema.replaceObject(buildSybaseSelectWithOutputAndResultSetProcedure());
            schema.replaceObject(buildSybaseWithoutParametersProcedure());
        }
        if (platform.isMySQL()) {
            schema.replaceObject(buildMySQL2ResultSetProcedure());
        }

        if (platform.isSQLAnywhere()) {
            schema.replaceObject(EmployeeCustomSQLSystem.buildSQLAnywhereDeleteProcedure());
            schema.replaceObject(EmployeeCustomSQLSystem.buildSQLAnywhereReadAllProcedure());
            schema.replaceObject(EmployeeCustomSQLSystem.buildSQLAnywhereReadObjectProcedure());
            schema.replaceObject(EmployeeCustomSQLSystem.buildSQLAnywhereInsertProcedure());
            schema.replaceObject(EmployeeCustomSQLSystem.buildSQLAnywhereUpdateProcedure());
            schema.replaceObject(EmployeeCustomSQLSystem.buildSQLAnywhereSelectWithOutputAndResultSetProcedure());
            schema.replaceObject(EmployeeCustomSQLSystem.buildSQLAnywhereWithoutParametersProcedure());
        }

        if (platform.isOracle()) {
            // Drop the dependent type before calling replaceObject to avoid error.
            try {
                session.executeQuery(new DataModifyQuery("DROP TYPE SF_LOOKUP_TBL FORCE"));
            } catch (Exception exception) {
            }
            try {
                session.executeQuery(new DataModifyQuery("DROP TYPE SF_LOOKUP_RECORD FORCE"));
            } catch (Exception exception) {
            }
            schema.replaceObject(buildOracleStoredProcedureInOutPut());
            schema.replaceObject(buildOracleStoredProcedureInOutOutIn());
            schema.replaceObject(buildOracleStoredProcedureTimestamp());
            schema.replaceObject(buildOracleStoredProcedureARRAY());
            schema.replaceObject(buildVARRAYTypeDefinition());
            schema.replaceObject(buildOracleStoredFunctionInOutOutIn());
            schema.replaceObject(buildOraclePackage());
            schema.replaceObject(buildOracleDeleteProcedure());
            schema.replaceObject(buildOracleReadAllProcedure());
            schema.replaceObject(buildOracleReadObjectProcedure());
            schema.replaceObject(buildOracleInsertProcedure());
            schema.replaceObject(buildOracleUpdateProcedure());
            schema.replaceObject(buildOracle2OutCursorsProcedure());
            schema.replaceObject(buildOracleLOOKUPRECORDTYPE());
            schema.replaceObject(buildOracleLOOKUPTABLETYPE());
            schema.replaceObject(buildOraclePackageStoredFunctionResultCursor());

            try {
                session.executeNonSelectingCall(new SQLCall("CREATE OR REPLACE PACKAGE BODY PackageFunction_ResultCursor IS " +
                        "FUNCTION BUSINESS_DATE (P_CODE IN VARCHAR2, P_LOOKUP_TBL IN SF_LOOKUP_TBL) RETURN REF_CURSOR IS " +
                        "L_CURSOR REF_CURSOR; BEGIN OPEN L_CURSOR FOR SELECT SYSDATE FROM DUAL; RETURN L_CURSOR; " +
                        "END BUSINESS_DATE; " +
                        "END PackageFunction_ResultCursor;"));
            } catch (Exception exception) {
            }
        }
        if (platform.isDB2()) {
            schema.replaceObject(buildDB2SelectWithOutputAndResultSetProcedure());
        }

        if (platform.isPervasive()) {
            schema.replaceObject(buildPervasiveReadAllProcedure());
            schema.replaceObject(buildPervasiveInsertProcedure());
            schema.replaceObject(buildPervasiveDeleteProcedure());
            schema.replaceObject(buildPervasiveInsertProcedure());
            schema.replaceObject(buildPervasiveReadAllProcedure());
            schema.replaceObject(buildPervasiveReadObjectProcedure());
            schema.replaceObject(buildPervasiveStoredProcedureInOutPut());
            schema.replaceObject(buildPervasiveSelectWithOutputAndResultSetProcedure());
            schema.replaceObject(buildPervasiveSelectWithOutputProcedure());
            schema.replaceObject(buildPervasiveUpdateProcedure());
       }
    }

    protected void setCommonSQL(Session session) {
        ClassDescriptor empDescriptor = session.getDescriptor(Employee.class);
        empDescriptor.getQueryManager().setDoesExistSQLString("select EMP_ID FROM EMPLOYEE WHERE EMP_ID = #EMP_ID");

        OneToOneMapping managerMapping = (OneToOneMapping)empDescriptor.getMappingForAttributeName("manager");
        managerMapping.setSelectionSQLString("select * FROM EMPLOYEE WHERE EMP_ID = #MANAGER_ID");
        OneToOneMapping oneToOne = (OneToOneMapping)empDescriptor.getMappingForAttributeName("address");
        oneToOne.setSelectionSQLString("select * FROM ADDRESS WHERE ADDRESS_ID = #ADDR_ID");

        OneToManyMapping oneToMany = (OneToManyMapping)empDescriptor.getMappingForAttributeName("managedEmployees");
        oneToMany.setSelectionSQLString("select E.*, S.* FROM EMPLOYEE E, SALARY S WHERE E.EMP_ID = S.EMP_ID AND E.MANAGER_ID = #EMP_ID");

        DirectCollectionMapping directCollection = (DirectCollectionMapping)empDescriptor.getMappingForAttributeName("responsibilitiesList");
        directCollection.setSelectionSQLString("select DESCRIP FROM RESPONS WHERE EMP_ID = #EMP_ID");
        directCollection.setDeleteAllSQLString("delete FROM RESPONS WHERE EMP_ID = #EMP_ID");
        directCollection.setInsertSQLString("insert into RESPONS (EMP_ID, DESCRIP) values (#EMP_ID, #DESCRIP)");

        ManyToManyMapping manyToMany = (ManyToManyMapping)empDescriptor.getMappingForAttributeName("projects");
        manyToMany.setDeleteAllSQLString("delete FROM PROJ_EMP WHERE EMP_ID = #EMP_ID");
        manyToMany.setDeleteSQLString("delete FROM PROJ_EMP WHERE EMP_ID = #EMP_ID AND PROJ_ID = #PROJ_ID");
        manyToMany.setInsertSQLString("insert into PROJ_EMP (EMP_ID, PROJ_ID) values (#EMP_ID, #PROJ_ID)");

        ClassDescriptor projectDescriptor = session.getDescriptor(Project.class);
        projectDescriptor.getQueryManager().setDoesExistSQLString("select PROJ_ID FROM PROJECT WHERE PROJ_ID = #PROJ_ID");
        DeleteObjectQuery deleteQuery = new DeleteObjectQuery();
        deleteQuery.addCall(new SQLCall("delete FROM PROJECT WHERE PROJ_ID = #PROJ_ID"));
        deleteQuery.addCall(new SQLCall("delete FROM LPROJECT WHERE PROJ_ID = #PROJ_ID"));
        projectDescriptor.getQueryManager().setDeleteQuery(deleteQuery);

        // Must disable locking for project delete to work.
        projectDescriptor.setOptimisticLockingPolicy(null);
        ClassDescriptor smallProjectDescriptor = session.getDescriptor(SmallProject.class);
        smallProjectDescriptor.setOptimisticLockingPolicy(null);
        ClassDescriptor largeProjectDescriptor = session.getDescriptor(LargeProject.class);
        largeProjectDescriptor.setOptimisticLockingPolicy(null);
    }

    protected void setCustomSQL(Session session) {
        setCommonSQL(session);
        if (session.getLogin().getPlatform().isSybase()) {
            setSybaseSQL(session);
        } else if (session.getLogin().getPlatform().isSQLAnywhere()) {
            EmployeeCustomSQLSystem.setSQLAnywhereSQL(session);
        } else if (session.getLogin().getPlatform().isSQLServer()) {
            setSQLServerSQL(session);
        } else if (session.getLogin().isAnyOracleJDBCDriver()) {// Require output cursor support.
            setOracleSQL(session);
        } else if (session.getLogin().getPlatform().isPervasive()) {
            setPervasiveSQL(session);
        } else {
            ClassDescriptor empDescriptor = session.getDescriptor(new Employee());
            empDescriptor.getQueryManager().setReadObjectSQLString("select E.*, S.* FROM EMPLOYEE E, SALARY S WHERE E.EMP_ID = S.EMP_ID AND E.EMP_ID = #EMP_ID");
            empDescriptor.getQueryManager().setReadAllSQLString("select E.*, S.* FROM EMPLOYEE E, SALARY S WHERE E.EMP_ID = S.EMP_ID");
            ManyToManyMapping manyToMany = (ManyToManyMapping)empDescriptor.getMappingForAttributeName("projects");
            ReadAllQuery readQuery = new ReadAllQuery();
            readQuery.addCall(new SQLCall("select P.*, L.* FROM LPROJECT L, PROJECT P, PROJ_EMP PE WHERE ((P.PROJ_ID = L.PROJ_ID) AND (PE.EMP_ID = #EMP_ID) AND (P.PROJ_ID = PE.PROJ_ID))"));
            readQuery.addCall(new SQLCall("select P.* FROM PROJECT P, PROJ_EMP PE WHERE (PE.EMP_ID = #EMP_ID) AND (P.PROJ_ID = PE.PROJ_ID) AND (P.PROJ_TYPE = 'S')"));
            manyToMany.setCustomSelectionQuery(readQuery);
            UpdateObjectQuery updateQuery = new UpdateObjectQuery();
            updateQuery.addCall(new SQLCall("update EMPLOYEE SET END_DATE = #END_DATE, MANAGER_ID = #MANAGER_ID, START_DATE = #START_DATE, F_NAME = #F_NAME, L_NAME = #L_NAME, GENDER = #GENDER, ADDR_ID = #ADDR_ID, VERSION = ##VERSION WHERE ((EMP_ID = #EMP_ID) AND (VERSION = #VERSION))"));
            updateQuery.addCall(new SQLCall("update SALARY SET SALARY = #SALARY WHERE (EMP_ID = #EMP_ID)"));
            empDescriptor.getQueryManager().setUpdateQuery(updateQuery);
            InsertObjectQuery insertQuery = new InsertObjectQuery();
            insertQuery.addCall(new SQLCall("insert INTO EMPLOYEE (END_DATE, EMP_ID, MANAGER_ID, START_DATE, F_NAME, L_NAME, GENDER, ADDR_ID, VERSION, START_TIME, END_TIME) VALUES (#END_DATE, #EMP_ID, #MANAGER_ID, #START_DATE, #F_NAME, #L_NAME, #GENDER, #ADDR_ID, #VERSION, #START_TIME, #END_TIME)"));
            insertQuery.addCall(new SQLCall("insert INTO SALARY (SALARY, EMP_ID) VALUES (#SALARY, #EMP_ID)"));
            empDescriptor.getQueryManager().setInsertQuery(insertQuery);
            DeleteObjectQuery deleteQuery = new DeleteObjectQuery();
            deleteQuery.addCall(new SQLCall("delete FROM EMPLOYEE WHERE EMP_ID = #EMP_ID"));
            deleteQuery.addCall(new SQLCall("delete FROM SALARY WHERE EMP_ID = #EMP_ID"));
            empDescriptor.getQueryManager().setDeleteQuery(deleteQuery);
        }
    }

    protected void setOracleSQL(Session session) {
        ClassDescriptor empDescriptor = session.getDescriptor(new Employee());
        StoredProcedureCall call;

        // Currently the rowcount does not work, so disable locking.
        empDescriptor.setOptimisticLockingPolicy(null);
        ReadObjectQuery readQuery = new ReadObjectQuery();
        call = new StoredProcedureCall();
        call.setProcedureName("Read_Employee");
        call.addNamedArgument("P_EMP_ID", "EMP_ID");
        call.useNamedCursorOutputAsResultSet("RESULT_CURSOR");
        readQuery.setCall(call);
        empDescriptor.getQueryManager().setReadObjectQuery(readQuery);
        ReadAllQuery readAllQuery = new ReadAllQuery();
        call = new StoredProcedureCall();
        call.setProcedureName("Read_All_Employees");
        call.useNamedCursorOutputAsResultSet("RESULT_CURSOR");
        readAllQuery.setCall(call);
        empDescriptor.getQueryManager().setReadAllQuery(readAllQuery);
        DeleteObjectQuery deleteQuery = new DeleteObjectQuery();
        call = new StoredProcedureCall();
        call.setProcedureName("Delete_Employee");
        call.addNamedArgument("P_EMP_ID", "EMP_ID");
        deleteQuery.setCall(call);
        empDescriptor.getQueryManager().setDeleteQuery(deleteQuery);
        InsertObjectQuery insertQuery = new InsertObjectQuery();
        call = new StoredProcedureCall();
        call.setProcedureName("Insert_Employee");
        call.addNamedArgument("P_EMP_ID", "EMP_ID");
        call.addNamedArgument("P_SALARY", "SALARY");
        call.addNamedArgument("P_END_DATE", "END_DATE");
        call.addNamedArgument("P_MANAGER_ID", "MANAGER_ID");
        call.addNamedArgument("P_START_DATE", "START_DATE");
        call.addNamedArgument("P_F_NAME", "F_NAME");
        call.addNamedArgument("P_L_NAME", "L_NAME");
        call.addNamedArgument("P_GENDER", "GENDER");
        call.addNamedArgument("P_ADDR_ID", "ADDR_ID");
        call.addNamedArgument("P_VERSION", "VERSION");
        call.addNamedArgument("P_START_TIME", "START_TIME");
        call.addNamedArgument("P_END_TIME", "END_TIME");
        insertQuery.setCall(call);
        empDescriptor.getQueryManager().setInsertQuery(insertQuery);
        UpdateObjectQuery updateQuery = new UpdateObjectQuery();
        call = new StoredProcedureCall();
        call.setUsesBinding(true);
        call.setShouldCacheStatement(true);
        call.setProcedureName("Update_Employee");
        call.addNamedArgument("P_EMP_ID", "EMP_ID");
        call.addNamedArgument("P_SALARY", "SALARY");
        call.addNamedArgument("P_END_DATE", "END_DATE");
        call.addNamedArgument("P_MANAGER_ID", "MANAGER_ID");
        call.addNamedArgument("P_START_DATE", "START_DATE");
        call.addNamedArgument("P_F_NAME", "F_NAME");
        call.addNamedArgument("P_L_NAME", "L_NAME");
        call.addNamedArgument("P_GENDER", "GENDER");
        call.addNamedArgument("P_ADDR_ID", "ADDR_ID");
        call.addNamedArgument("P_START_TIME", "START_TIME");
        call.addNamedArgument("P_END_TIME", "END_TIME");
        call.addNamedOutputArgument("O_ERROR_CODE", "O_ERROR_CODE", Long.class);
        updateQuery.setCall(call);
        empDescriptor.getQueryManager().setUpdateQuery(updateQuery);
        ManyToManyMapping manyToMany = (ManyToManyMapping)empDescriptor.getMappingForAttributeName("projects");
        manyToMany.setSelectionSQLString("select P.*, L.* FROM LPROJECT L, PROJECT P, PROJ_EMP PE WHERE ((P.PROJ_ID = L.PROJ_ID (+)) AND (PE.EMP_ID = #EMP_ID) AND (P.PROJ_ID = PE.PROJ_ID))");
    }

    protected void setPervasiveSQL(Session session) {
        ClassDescriptor empDescriptor = session.getDescriptor(new Employee());
        StoredProcedureCall call;

        // Currently the rowcount does not work, so disable locking.
        empDescriptor.setOptimisticLockingPolicy(null);
        session.getLogin().getPlatform().setUsesNativeSQL(true);

        ReadObjectQuery readQuery = new ReadObjectQuery();
        call = new StoredProcedureCall();
        call.setProcedureName("Read_Employee");
        call.addNamedArgument("EMP_ID");
        call.setReturnsResultSet(true);
        readQuery.setCall(call);
        empDescriptor.getQueryManager().setReadObjectQuery(readQuery);

        ReadAllQuery readAllQuery = new ReadAllQuery();
        call = new StoredProcedureCall();
        call.setProcedureName("Read_All_Employees");
        call.setReturnsResultSet(true);
        readAllQuery.setCall(call);
        empDescriptor.getQueryManager().setReadAllQuery(readAllQuery);

        DeleteObjectQuery deleteQuery = new DeleteObjectQuery();
        call = new StoredProcedureCall();
        call.setProcedureName("Delete_Employee");
        call.addNamedArgument("EMP_ID");
        deleteQuery.setCall(call);
        empDescriptor.getQueryManager().setDeleteQuery(deleteQuery);

        InsertObjectQuery insertQuery = new InsertObjectQuery();
        call = new StoredProcedureCall();
        call.setProcedureName("Insert_Employee");
        call.setUsesBinding(true);
        call.setShouldCacheStatement(true);
        call.addNamedArgument("EMP_ID");
        call.addNamedArgument("SALARY");
        call.addNamedArgument("END_DATE");
        call.addNamedArgument("MANAGER_ID");
        call.addNamedArgument("START_DATE");
        call.addNamedArgument("F_NAME");
        call.addNamedArgument("L_NAME");
        call.addNamedArgument("GENDER");
        call.addNamedArgument("ADDR_ID");
        call.addNamedArgument("START_TIME");
        call.addNamedArgument("END_TIME");
        insertQuery.setCall(call);
        empDescriptor.getQueryManager().setInsertQuery(insertQuery);

        UpdateObjectQuery updateQuery = new UpdateObjectQuery();
        call = new StoredProcedureCall();
        call.setProcedureName("Update_Employee");
        call.addNamedArgument("EMP_ID");
        call.addNamedArgument("SALARY");
        call.addNamedArgument("END_DATE");
        call.addNamedArgument("MANAGER_ID");
        call.addNamedArgument("START_DATE");
        call.addNamedArgument("F_NAME");
        call.addNamedArgument("L_NAME");
        call.addNamedArgument("GENDER");
        call.addNamedArgument("ADDR_ID");
        //call.addNamedArgument("VERSION");
        call.addNamedArgument("START_TIME");
        call.addNamedArgument("END_TIME");
        updateQuery.setCall(call);
        empDescriptor.getQueryManager().setUpdateQuery(updateQuery);

        ManyToManyMapping manyToMany = (ManyToManyMapping)empDescriptor.getMappingForAttributeName("projects");
        manyToMany.setSelectionSQLString("select P.*, L.* FROM PROJ_EMP PE, PROJECT P LEFT OUTER JOIN LPROJECT L ON (L.PROJ_ID = P.PROJ_ID) WHERE ((PE.EMP_ID = #EMP_ID) AND (P.PROJ_ID = PE.PROJ_ID))");
    }


    protected void setSQLServerSQL(Session session) {
        ClassDescriptor empDescriptor = session.getDescriptor(new Employee());
        StoredProcedureCall call;

        // Currently the rowcount does not work, so disable locking.
        empDescriptor.setOptimisticLockingPolicy(null);
        session.getLogin().getPlatform().setUsesNativeSQL(true);

        ReadObjectQuery readQuery = new ReadObjectQuery();
        call = new StoredProcedureCall();
        call.setProcedureName("Read_Employee");
        call.addNamedArgument("EMP_ID");
        call.setReturnsResultSet(true);
        readQuery.setCall(call);
        empDescriptor.getQueryManager().setReadObjectQuery(readQuery);

        ReadAllQuery readAllQuery = new ReadAllQuery();
        call = new StoredProcedureCall();
        call.setProcedureName("Read_All_Employees");
        call.setReturnsResultSet(true);
        readAllQuery.setCall(call);
        empDescriptor.getQueryManager().setReadAllQuery(readAllQuery);

        DeleteObjectQuery deleteQuery = new DeleteObjectQuery();
        call = new StoredProcedureCall();
        call.setProcedureName("Delete_Employee");
        call.addNamedArgument("EMP_ID");
        deleteQuery.setCall(call);
        empDescriptor.getQueryManager().setDeleteQuery(deleteQuery);

        InsertObjectQuery insertQuery = new InsertObjectQuery();
        call = new StoredProcedureCall();
        call.setProcedureName("Insert_Employee");
        call.setUsesBinding(true);
        call.setShouldCacheStatement(true);
        call.addNamedArgument("EMP_ID");
        call.addNamedArgument("SALARY");
        call.addNamedArgument("END_DATE");
        call.addNamedArgument("MANAGER_ID");
        call.addNamedArgument("START_DATE");
        call.addNamedArgument("F_NAME");
        call.addNamedArgument("L_NAME");
        call.addNamedArgument("GENDER");
        call.addNamedArgument("ADDR_ID");
        //call.addNamedArgument("VERSION");
        call.addNamedOutputArgument("VERSION", "EMPLOYEE.VERSION", java.math.BigDecimal.class);
        call.addNamedArgument("START_TIME");
        call.addNamedArgument("END_TIME");
        insertQuery.setCall(call);
        empDescriptor.getQueryManager().setInsertQuery(insertQuery);

        UpdateObjectQuery updateQuery = new UpdateObjectQuery();
        call = new StoredProcedureCall();
        call.setProcedureName("Update_Employee");
        call.addNamedArgument("EMP_ID");
        call.addNamedArgument("SALARY");
        call.addNamedArgument("END_DATE");
        call.addNamedArgument("MANAGER_ID");
        call.addNamedArgument("START_DATE");
        call.addNamedArgument("F_NAME");
        call.addNamedArgument("L_NAME");
        call.addNamedArgument("GENDER");
        call.addNamedArgument("ADDR_ID");
        //call.addNamedArgument("VERSION");
        call.addNamedArgument("START_TIME");
        call.addNamedArgument("END_TIME");
        updateQuery.setCall(call);
        empDescriptor.getQueryManager().setUpdateQuery(updateQuery);

        ManyToManyMapping manyToMany = (ManyToManyMapping)empDescriptor.getMappingForAttributeName("projects");
        manyToMany.setSelectionSQLString("select P.*, L.* FROM PROJ_EMP PE, PROJECT P LEFT OUTER JOIN LPROJECT L ON (L.PROJ_ID = P.PROJ_ID) WHERE ((PE.EMP_ID = #EMP_ID) AND (P.PROJ_ID = PE.PROJ_ID))");
    }

    protected void setSybaseSQL(Session session) {
        ClassDescriptor empDescriptor = session.getDescriptor(new Employee());
        StoredProcedureCall call;

        ReadObjectQuery readQuery = new ReadObjectQuery();
        call = new StoredProcedureCall();
        call.setProcedureName("Read_Employee");
        call.addNamedArgument("EMP_ID");
        call.setReturnsResultSet(true);
        readQuery.setCall(call);
        empDescriptor.getQueryManager().setReadObjectQuery(readQuery);

        ReadAllQuery readAllQuery = new ReadAllQuery();
        call = new StoredProcedureCall();
        call.setProcedureName("Read_All_Employees");
        call.setReturnsResultSet(true);
        readAllQuery.setCall(call);
        empDescriptor.getQueryManager().setReadAllQuery(readAllQuery);

        DeleteObjectQuery deleteQuery = new DeleteObjectQuery();
        call = new StoredProcedureCall();
        call.setProcedureName("Delete_Employee");
        call.addNamedArgument("EMP_ID");
        deleteQuery.setCall(call);
        empDescriptor.getQueryManager().setDeleteQuery(deleteQuery);

        InsertObjectQuery insertQuery = new InsertObjectQuery();
        call = new StoredProcedureCall();
        call.setProcedureName("Insert_Employee");
        call.setUsesBinding(true);
        call.setShouldCacheStatement(true);
        call.addNamedArgument("EMP_ID");
        call.addNamedArgument("SALARY");
        call.addNamedArgument("END_DATE");
        call.addNamedArgument("MANAGER_ID");
        call.addNamedArgument("START_DATE");
        call.addNamedArgument("F_NAME");
        call.addNamedArgument("L_NAME");
        call.addNamedArgument("GENDER");
        call.addNamedArgument("ADDR_ID");
        call.addNamedArgument("VERSION");
        call.addNamedArgument("START_TIME");
        call.addNamedArgument("END_TIME");
        call.addNamedInOutputArgumentValue("OUT_VERSION", Long.valueOf(0), "EMPLOYEE.VERSION", Long.class);
        insertQuery.setCall(call);
        empDescriptor.getQueryManager().setInsertQuery(insertQuery);

        UpdateObjectQuery updateQuery = new UpdateObjectQuery();
        call = new StoredProcedureCall();
        call.setProcedureName("Update_Employee");
        call.addNamedArgument("EMP_ID");
        call.addNamedArgument("SALARY");
        call.addNamedArgument("END_DATE");
        call.addNamedArgument("MANAGER_ID");
        call.addNamedArgument("START_DATE");
        call.addNamedArgument("F_NAME");
        call.addNamedArgument("L_NAME");
        call.addNamedArgument("GENDER");
        call.addNamedArgument("ADDR_ID");
        call.addNamedArgument("VERSION");
        call.addNamedArgument("START_TIME");
        call.addNamedArgument("END_TIME");
        updateQuery.setCall(call);
        empDescriptor.getQueryManager().setUpdateQuery(updateQuery);

        ManyToManyMapping manyToMany = (ManyToManyMapping)empDescriptor.getMappingForAttributeName("projects");
        manyToMany.setSelectionSQLString("select P.*, L.* FROM PROJ_EMP PE, PROJECT P LEFT OUTER JOIN LPROJECT L ON (L.PROJ_ID = P.PROJ_ID) WHERE ((PE.EMP_ID = #EMP_ID) AND (P.PROJ_ID = PE.PROJ_ID))");
    }

    private static void setSQLAnywhereSQL(final Session session) {
        final ClassDescriptor employeeDescriptor = session.getDescriptor(new Employee());

        final StoredProcedureCall readEmployeeCall = new StoredProcedureCall();
        readEmployeeCall.setProcedureName("Read_Employee");
        readEmployeeCall.addNamedArgument("_EMP_ID", "EMP_ID");
        readEmployeeCall.setReturnsResultSet(true);
        employeeDescriptor.getQueryManager().setReadObjectQuery(new ReadObjectQuery(readEmployeeCall));

        final StoredProcedureCall readAllEmployeesCall = new StoredProcedureCall();
        readAllEmployeesCall.setProcedureName("Read_All_Employees");
        readAllEmployeesCall.setReturnsResultSet(true);
        employeeDescriptor.getQueryManager().setReadAllQuery(new ReadAllQuery(readAllEmployeesCall));

        final StoredProcedureCall deleteEmployeeCall = new StoredProcedureCall();
        deleteEmployeeCall.setProcedureName("Delete_Employee");
        deleteEmployeeCall.addNamedArgument("_EMP_ID", "EMP_ID");
        employeeDescriptor.getQueryManager().setDeleteQuery(new DeleteObjectQuery(deleteEmployeeCall));

        final StoredProcedureCall insertEmployeeCall = new StoredProcedureCall();
        insertEmployeeCall.setProcedureName("Insert_Employee");
        insertEmployeeCall.setUsesBinding(true);
        insertEmployeeCall.setShouldCacheStatement(true);
        insertEmployeeCall.addNamedArgument("_EMP_ID", "EMP_ID");
        insertEmployeeCall.addNamedArgument("_SALARY", "SALARY");
        insertEmployeeCall.addNamedArgument("_END_DATE", "END_DATE");
        insertEmployeeCall.addNamedArgument("_MANAGER_ID", "MANAGER_ID");
        insertEmployeeCall.addNamedArgument("_START_DATE", "START_DATE");
        insertEmployeeCall.addNamedArgument("_F_NAME", "F_NAME");
        insertEmployeeCall.addNamedArgument("_L_NAME", "L_NAME");
        insertEmployeeCall.addNamedArgument("_GENDER", "GENDER");
        insertEmployeeCall.addNamedArgument("_ADDR_ID", "ADDR_ID");
//        insertEmployeeCall.addNamedArgument("_VERSION", "VERSION");
        insertEmployeeCall.addNamedArgument("_START_TIME", "START_TIME");
        insertEmployeeCall.addNamedArgument("_END_TIME", "END_TIME");
        // The order of the arguments shouldn't matter because they are named,
        // but SQLAnywhere 10 for some reason can't handle named parameters
        // (In JPA tests:
        // CALL SProc_Read_InOut(address_id_v = ? , street_v = ? )
        // bind => [17 => ADDRESS_ID, => STREET]
        // fails with java.sql.SQLException: [Sybase][ODBC Driver]Invalid parameter type
        // Until that fixed, naming is switched off
        // (SQLAnywherePlatform.shouldPrintStoredProcedureArgumentNameInCall() returns false),
        // and therefore arguments should be passed exactly in the same order as parameters defined in the sp.
        // After this is fixed (m.b. in SQLAnywhere 11?) the order of the attributes should be returned to original
        // (where it does NOT follow the order of sp parameters).
        insertEmployeeCall.addNamedArgument("_VERSION", "VERSION");
        insertEmployeeCall.addNamedInOutputArgumentValue("_OUT_VERSION", Long.valueOf(0), "EMPLOYEE.VERSION", Long.class);
        employeeDescriptor.getQueryManager().setInsertQuery(new InsertObjectQuery(insertEmployeeCall));

        final StoredProcedureCall updateEmployeeCall = new StoredProcedureCall();
        updateEmployeeCall.setProcedureName("Update_Employee");
        updateEmployeeCall.addNamedArgument("_EMP_ID", "EMP_ID");
        updateEmployeeCall.addNamedArgument("_SALARY", "SALARY");
        updateEmployeeCall.addNamedArgument("_END_DATE", "END_DATE");
        updateEmployeeCall.addNamedArgument("_MANAGER_ID", "MANAGER_ID");
        updateEmployeeCall.addNamedArgument("_START_DATE", "START_DATE");
        updateEmployeeCall.addNamedArgument("_F_NAME", "F_NAME");
        updateEmployeeCall.addNamedArgument("_L_NAME", "L_NAME");
        updateEmployeeCall.addNamedArgument("_GENDER", "GENDER");
        updateEmployeeCall.addNamedArgument("_ADDR_ID", "ADDR_ID");
        updateEmployeeCall.addNamedArgument("_VERSION", "VERSION");
        updateEmployeeCall.addNamedArgument("_START_TIME", "START_TIME");
        updateEmployeeCall.addNamedArgument("_END_TIME", "END_TIME");
        employeeDescriptor.getQueryManager().setUpdateQuery(new UpdateObjectQuery(updateEmployeeCall));

        final ManyToManyMapping manyToMany = (ManyToManyMapping) employeeDescriptor.getMappingForAttributeName("projects");
        manyToMany.setSelectionSQLString("SELECT P.*, L.* FROM PROJ_EMP PE JOIN PROJECT P ON PE.PROJ_ID = P.PROJ_ID LEFT OUTER JOIN LPROJECT L ON P.PROJ_ID = L.PROJ_ID WHERE PE.EMP_ID = #EMP_ID");
    }

}