call stored procedure from DAL

package org.rahmadnet.learning.spdal;

import java.sql.Connection;
import java.sql.PreparedStatement;

import org.junit.Test;
import org.openbravo.base.provider.OBProvider;
import org.openbravo.dal.core.OBContext;
import org.openbravo.dal.service.OBDal;
import org.openbravo.model.ad.process.Parameter;
import org.openbravo.model.ad.process.ProcessInstance;
import org.openbravo.model.ad.ui.Process;
import org.openbravo.test.base.OBBaseTest;

public class StoredProcedureTest extends OBBaseTest
{
    @Test
    public void testStoredProcedure()
    {
        setUserContext("0");

        // get the process, we know that 800000 is the generate shiptments from invoice sp
        final Process process = OBDal.getInstance().get(Process.class, "199");

        // Create the pInstance
        final ProcessInstance pInstance = OBProvider.getInstance().get(ProcessInstance.class);
        // sets its process
        pInstance.setProcess(process);
        // must be set to true
        pInstance.setActive(true);
        pInstance.setRecordID("0");

        // get the user from the context
        pInstance.setUserContact(OBContext.getOBContext().getUser());

        // now create a parameter and set its values
        final Parameter parameter = OBProvider.getInstance().get(Parameter.class);
        parameter.setSequenceNumber("1");
        parameter.setParameterName("Selection");
        parameter.setString("Y");

        // set both sides of te bidirection association
        pInstance.getADParameterList().add(parameter);
        parameter.setProcessInstance(pInstance);

        // persist to the db
        OBDal.getInstance().save(pInstance);

        // flush, this gives pInstance an ID
        OBDal.getInstance().flush();

        System.err.println(pInstance.getId());

        // call the SP
        try {
            // first get a connection
            final Connection connection = OBDal.getInstance().getConnection();
            // connection.createStatement().execute("CALL M_InOut_Create0(?)");
            final PreparedStatement ps = connection.prepareStatement("SELECT * FROM M_InOut_Create0(?)");
            ps.setString(1, pInstance.getId());
            ps.execute();
        } catch (Exception e) {
            // TODO: handle exception
            throw new IllegalStateException(e);
        }

        System.err.println(pInstance.getResult());
        System.err.println(pInstance.getErrorMsg());
    }
}