/***
    * License Agreement.
    * 
    * JSPA (POJO-SP)
    * 
    * Copyright (C) 2009 HRX Pty Ltd
    * 
    * This file is part of JSPA.
    * 
   * JSPA is free software: you can redistribute it and/or modify it under the
   * terms of the GNU Lesser General Public License version 3 as published by the
   * Free Software Foundation.
   * 
   * JSPA is distributed in the hope that it will be useful, but WITHOUT ANY
   * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
   * A PARTICULAR PURPOSE. See the Lesser General Public License for more details.
   * 
   * You should have received a copy of the GNU Lesser General Public License
   * along with JSPA. If not, see <http://www.gnu.org/licenses/>.
   */
  package com.hrx.rasp.util.dao.operation;
  
  import java.io.InputStream;
  import java.lang.reflect.Field;
  import java.lang.reflect.InvocationTargetException;
  import java.sql.Array;
  import java.sql.Blob;
  import java.sql.CallableStatement;
  import java.sql.Clob;
  import java.sql.Connection;
  import java.sql.SQLException;
  import java.sql.Timestamp;
  import java.sql.Types;
  import java.util.Collection;
  import java.util.Set;
  
  import org.apache.log4j.Logger;
  
  import com.hrx.rasp.util.dao.ExpressionBuilder;
  import com.hrx.rasp.util.dao.NullValue;
  import com.hrx.rasp.util.dao.OracleHelper;
  import com.hrx.rasp.util.dao.StoredProcUtility;
  import com.hrx.rasp.util.dao.annotations.IN;
  import com.hrx.rasp.util.dao.annotations.INOUT;
  import com.hrx.rasp.util.dao.annotations.OUT;
  import com.hrx.rasp.util.dao.annotations.Secret;
  import com.hrx.rasp.util.dao.exception.StoredProcedureException;
  import com.hrx.rasp.util.dao.exception.StoredProcedurePrepareException;
  import com.hrx.rasp.util.dao.exception.StoredProcedureReservedIndexException;
  import com.hrx.rasp.util.dao.metadata.MetadataHelper;
  import com.hrx.rasp.util.dao.metadata.StoredProcField;
  import com.hrx.rasp.util.dao.metadata.StoredProcMetaDataImpl;
  import com.hrx.rasp.util.dao.metadata.StoredProcPkMetaData;
  
  /***
   * @author dan.stoica <dan.stoica@acslink.net.au>
   *
   * Description for StatementBuilder class
   * 
   */
  public final class StatementBuilder {
  	private final static Logger log = Logger.getLogger(StatementBuilder.class);
  
  	public static final String PK_NOT_FOUND = "Primary key not found";
  	public static final String IN_PARAMETERS_OR_PK_NOT_FOUND = "IN parameters or Primary key not found";
  	static final String PARAMETER_INDEX_IS_RESERVED = "Parameter index is reserved. Parameter name:";
  	public static final String IN_PARAM_NOT_FOUND_FOR_SELECT_SP = "Parameters IN not found for Select Stored Procedure";
  
  	/***
  	 * Description for setObject method
  	 * 
  	 * @param st
  	 *            CallableStatement
  	 * @param index
  	 *            int
  	 * @param arg
  	 *            Object
  	 * @throws SQLException
  	 */
  	protected static void setObject(final CallableStatement st, int index,
  			final Object arg) throws SQLException {
  		if (arg instanceof NullValue)
  			setNullValue(st, index, (NullValue) arg);
  		else
  			st.setObject(index, arg);
  	}
  
  	/***
  	 * Register a given list of DAO properties as stored procedure OUT
  	 * parameters
  	 * 
  	 * @param st
  	 * @param allFields
  	 * @throws SQLException
  	 */
  	protected static void registerOutParameters(final CallableStatement st,
  			final Collection<StoredProcField<OUT>> outFields)
  			throws SQLException {
  		for (StoredProcField<OUT> field : outFields) {
 			OUT parameter = field.getParameter();
 			registerOutParameters(st, parameter.index(), parameter.sqlType(),
 					field.getField(), parameter.sqlArrayName());
 		}
 	}
 
 	/***
 	 * Register a given list of DAO properties as stored procedure OUT
 	 * parameters
 	 * 
 	 * @param st
 	 * @param allFields
 	 * @throws SQLException
 	 */
 	protected static void registerInOutParameters(final CallableStatement st,
 			final Collection<StoredProcField<INOUT>> outFields)
 			throws SQLException {
 		for (StoredProcField<INOUT> field : outFields) {
 			INOUT parameter = field.getParameter();
 			registerOutParameters(st, parameter.index(), parameter.sqlType(),
 					field.getField(), parameter.sqlArrayName());
 		}
 	}
 
 	/***
 	 * Register a DAO property as OUT stored procedure parameter
 	 * 
 	 * @param st
 	 * @param index
 	 * @param sqlType
 	 * @param field
 	 * @param sqlArrayName
 	 * @throws SQLException
 	 */
 	private static void registerOutParameters(final CallableStatement st,
 			int index, int sqlType, Field field, String sqlArrayName)
 			throws SQLException {
 		Class<?> javaType = field.getType();
 
 		if (sqlType == Types.NULL) {
 			// try the guess the type
 			sqlType = StoredProcUtility.getSQLType(javaType);
 		}
 
 		if (sqlType == Types.ARRAY) {
 			if (log.isDebugEnabled()) {
 				log.debug("PREPARE: RegisterOutParameter parameter; index="
 						+ index + ", type: 'ARRAY', arrayName=" + sqlArrayName);
 			}
 			st.registerOutParameter(index, Types.ARRAY, sqlArrayName);
 		} else {
 			// The Oracle JDBC drivers do not support the BOOLEAN parameters for
 			// PL/SQL stored procedures.
 			if (sqlType == Types.BOOLEAN
 					&& st.getClass().toString().toLowerCase()
 							.contains("oracle")) {
 				if (log.isDebugEnabled()) {
 					log
 							.debug("PREPARE: RegisterOutParameter BOOLEAN parameter for Oracle; index="
 									+ index + ", as type:'Types.VARCHAR'");
 				}
 				st.registerOutParameter(index, Types.VARCHAR);
 			} else {
 				if (log.isDebugEnabled()) {
 					log.debug("RegisterOutParameter parameter; index=" + index
 							+ ", type:'" + sqlType + "'");
 				}
 				st.registerOutParameter(index, sqlType);
 			}
 		}
 	}
 
 	/***
 	 * Description for setObject method
 	 * 
 	 * @param st
 	 *            CallableStatement
 	 * @param inParameters
 	 * @param inoutParameters
 	 * @param reservedIndex
 	 *            - the first parameters can be reserved for error code , result
 	 *            set, etc...
 	 * @throws SQLException
 	 * @throws StoredProcedureReservedIndexException
 	 * @throws StoredProcedurePrepareException
 	 */
 	protected static void setInParameters(final CallableStatement st,
 			Object spBean, int reservedIndex,
 			final Collection<StoredProcField<IN>> inParameters)
 			throws SQLException, StoredProcedureReservedIndexException,
 			StoredProcedurePrepareException {
 		for (StoredProcField<IN> param : inParameters) {
 			try {
 				Object value = MetadataHelper.getPropertyValue(
 						param.getField(), spBean);
 				int index = param.getParameter().index();
 				String arrayName = param.getParameter().sqlArrayName();
 
 				if (log.isDebugEnabled()) {
 					StringBuilder msg = new StringBuilder(
 							"PREPARE: Set IN parameter. Field name="
 									+ param.getName() + ", SP index=" + index
 									+ ", value=");
 					msg = param.getField().isAnnotationPresent(Secret.class) ? msg
 							.append("*****")
 							: msg.append(value);
 					if (value instanceof Collection<?>)
 						msg.append(", arrayName=" + arrayName);
 					log.debug(msg);
 				}
 
 				setInParameter(st, param, value, arrayName, index,
 						reservedIndex);
 			} catch (StoredProcedureReservedIndexException e) {
 				throw e;
 			} catch (Throwable e) {
 				throw new StoredProcedurePrepareException(e);
 			}
 		}
 	}
 
 	protected static void setInOutParameters(final CallableStatement st,
 			Object spBean, int reservedIndex,
 			final Collection<StoredProcField<INOUT>> inParameters)
 			throws SQLException, StoredProcedureReservedIndexException,
 			StoredProcedurePrepareException {
 		for (StoredProcField<INOUT> param : inParameters) {
 			try {
 				Object value = MetadataHelper.getPropertyValue(
 						param.getField(), spBean);
 				int index = param.getParameter().index();
 				String arrayName = param.getParameter().sqlArrayName();
 
 				if (log.isDebugEnabled()) {
 					StringBuilder msg = new StringBuilder(
 							"PREPARE: Set INOUT parameter. Field name="
 									+ param.getName() + ", SP index=" + index
 									+ ", value=");
 					msg = param.getField().isAnnotationPresent(Secret.class) ? msg
 							.append("*****")
 							: msg.append(value);
 					if (value instanceof Collection<?>)
 						msg.append(", arrayName=" + arrayName);
 					log.debug(msg);
 				}
 
 				setInParameter(st, param, value, arrayName, index,
 						reservedIndex);
 			} catch (StoredProcedureReservedIndexException e) {
 				throw e;
 			} catch (Throwable e) {
 				throw new StoredProcedurePrepareException(e);
 			}
 		}
 	}
 
 	/***
 	 * Description for setObject method
 	 * 
 	 * @param st
 	 *            CallableStatement
 	 * @param index
 	 *            int
 	 * @param arg
 	 *            Object
 	 * @throws SQLException
 	 * @throws StoredProcedureReservedIndexException
 	 * @throws StoredProcedurePrepareException
 	 */
 	protected static void setInParameter(final CallableStatement st,
 			final StoredProcField<?> parameter, Object value, String arrayName,
 			int index, int minIndex) throws SQLException,
 			StoredProcedureReservedIndexException,
 			StoredProcedurePrepareException {
 		Class<?> objectType = value.getClass();
 		// index is 1-based
 
 		if (index <= minIndex) {
 			throw new StoredProcedureReservedIndexException(
 					StatementBuilder.PARAMETER_INDEX_IS_RESERVED
 							+ parameter.getName());
 		}
 		if (value instanceof NullValue) {
 			setNullValue(st, index, (NullValue) value);
 		} else if (value instanceof String) {
 			st.setString(index, (String) value);
 		} else if (value instanceof Integer || objectType == Integer.TYPE) {
 			st.setInt(index, (Integer) value);
 		} else if (value instanceof Long || objectType == Long.TYPE) {
 			st.setLong(index, (Long) value);
 		} else if (value instanceof Double || objectType == Double.TYPE) {
 			st.setDouble(index, (Double) value);
 		} else if (value instanceof Float || objectType == Float.TYPE) {
 			st.setFloat(index, (Float) value);
 		} else if (value instanceof Short || objectType == Short.TYPE) {
 			st.setShort(index, (Short) value);
 		} else if (value instanceof Boolean || objectType == Boolean.TYPE) {
 			// The Oracle JDBC drivers do not support the BOOLEAN parameters for
 			// PL/SQL stored procedures.
 			if (st.getClass().toString().toLowerCase().contains("oracle")) {
 				st.setString(index, Boolean.toString((Boolean) value));
 			} else {
 				st.setBoolean(index, (Boolean) value);
 			}
 		} else if (value instanceof Timestamp) {
 			st.setTimestamp(index, (Timestamp) value);
 		} else if (value instanceof java.util.Date) {
 			java.sql.Date date = new java.sql.Date((((java.util.Date) value))
 					.getTime());
 			st.setDate(index, date);
 		} else if (value instanceof java.sql.Date) {
 			st.setDate(index, (java.sql.Date) value);
 		} else if (value instanceof Clob) {
 			st.setClob(index, (Clob) value);
 		} else if (value instanceof Blob) {
 			st.setBlob(index, (Blob) value);
 		} else if (value instanceof Collection<?>) {
 			setOracleArray(st, index, (Collection<?>) value, arrayName);
 		} else if (value instanceof InputStream) {
 			st.setBinaryStream(index, (InputStream) value);
 		}
 
 	}
 
 	/***
 	 * Description for setNullValue method
 	 * 
 	 * @param st
 	 *            CallableStatement
 	 * @param index
 	 *            int
 	 * @param nullValue
 	 *            NullValue
 	 * @throws SQLException
 	 */
 	public static void setNullValue(final CallableStatement st,
 			final int index, final NullValue nullValue) throws SQLException {
 		st.setNull(index, nullValue.getSQLType());
 	}
 
 	private static void setOracleArray(final CallableStatement stmt, int index,
 			Collection<?> values, String arrayType)
 			throws StoredProcedurePrepareException, SQLException {
 		if ("N/A".equals(arrayType)) {
 			throw new StoredProcedurePrepareException(
 					"Invalid database ARRAY TYPE name, for IN PARAMETER at index: "
 							+ index);
 		}
 		Connection conn = stmt.getConnection();
 		Array array = OracleHelper.createOracleARRAY(arrayType, values, conn);
 		stmt.setArray(index, array);
 	}
 	
 	
 	/***
 	 * Build the CallableStatement object for the desired insert query
 	 * 
 	 * @param conn
 	 *            The database connection
 	 * @param storedProc
 	 *            The stored procedure
 	 * @param pkClass
 	 *            the expected class of the primary key
 	 * @param args
 	 *            The input values
 	 * @return The callable statement that forms the insert query
 	 * @throws StoredProcedureReservedIndexException
 	 * @throws StoredProcedureException
 	 */
 	protected static CallableStatement getInsertStatement(
 			final Connection conn, final String storedProc,
 			final Collection<StoredProcField<IN>> inParameters,
 			final Collection<StoredProcField<?>> pkParameters, Object spBean,
 			Object pkBean, boolean isEmbeddedId)
 			throws StoredProcedurePrepareException,
 			StoredProcedureReservedIndexException {
 		try {
 			inParameters.removeAll(pkParameters);
 			if (inParameters.size() == 0
 					|| (isEmbeddedId && pkParameters.size() == 0)) {
 				throw new StoredProcedurePrepareException(
 						StatementBuilder.IN_PARAMETERS_OR_PK_NOT_FOUND);
 			}
 
 			int params = pkParameters.size() + inParameters.size();
 
 			String stmtExpression = ExpressionBuilder.getInsert(storedProc,
 					params);
 			CallableStatement st = conn.prepareCall(stmtExpression);
 
 			// register the returned result code and primary key
 			st.registerOutParameter(1, Types.INTEGER);
 			st.registerOutParameter(2, Types.VARCHAR);
 
 			// index is 1-based and the first two indices are the
 			// result code. Also there may be some other index used for primary
 			// key
 
 			setInParameters(st, spBean, 2, inParameters);
 
 			// // register primary key parameters as INOUT parameters
 			// registerInOutParameters(st, pkParameters);
 			for (StoredProcField<?> param : pkParameters) {
 				try {
 					if (!isEmbeddedId) {
 						pkBean = spBean;
 					}
 					setAndRegistreParameter(pkBean, st, param);
 				} catch (StoredProcedureReservedIndexException e) {
 					throw e;
 				} catch (Throwable e) {
 					throw new StoredProcedurePrepareException(e);
 				}
 			}
 			return st;
 		} catch (SQLException se) {
 			throw new StoredProcedurePrepareException(se);
 		}
 	}
 
 	/***
 	 * @param pkBean
 	 * @param st
 	 * @param param
 	 * @throws NoSuchMethodException
 	 * @throws IllegalAccessException
 	 * @throws InvocationTargetException
 	 * @throws SQLException
 	 * @throws StoredProcedureReservedIndexException
 	 */
 	private static void setAndRegistreParameter(Object pkBean,
 			CallableStatement st, StoredProcField<?> param)
 			throws NoSuchMethodException, IllegalAccessException,
 			InvocationTargetException, SQLException,
 			StoredProcedureReservedIndexException,
 			StoredProcedurePrepareException {
 		Object value = MetadataHelper
 				.getPropertyValue(param.getField(), pkBean);
 		if (param.getParameter() instanceof INOUT) {
 			INOUT p = (INOUT) param.getParameter();
 			int index = p.index();
 			setInParameter(st, param, value, p.sqlArrayName(), index, 2);
 			registerOutParameters(st, index, p.sqlType(), param.getField(), p
 					.sqlArrayName());
 		} else if (param.getParameter() instanceof IN) {
 			IN p = (IN) param.getParameter();
 			int index = p.index();
 			setInParameter(st, param, value, p.sqlArrayName(), index, 2);
 		} else if (param.getParameter() instanceof OUT) {
 			OUT p = (OUT) param.getParameter();
 			int index = p.index();
 			registerOutParameters(st, index, p.sqlType(), param.getField(), p
 					.sqlArrayName());
 		}
 	}
 
 	/***
 	 * Build the CallableStatement object for the desired update query
 	 * 
 	 * @param conn
 	 *            The database connection
 	 * @param storedProc
 	 *            The stored procedure
 	 * @param args
 	 *            The input values
 	 * @return The callable statement that forms the update query
 	 * @throws StoredProcedureReservedIndexException
 	 * @throws StoredProcedureException
 	 */
 	protected static CallableStatement getUpdateStatement(
 			final Connection conn, final String storedProc,
 			final Collection<StoredProcField<IN>> inParameters,
 			final Collection<StoredProcField<IN>> pkParameters,
 			final Object spBean, final Object pkBean, boolean isEmbeddedId)
 			throws StoredProcedurePrepareException,
 			StoredProcedureReservedIndexException {
 		try {
 			if (inParameters.size() == 0 || pkParameters.size() == 0) {
 				throw new StoredProcedurePrepareException(
 						StatementBuilder.IN_PARAMETERS_OR_PK_NOT_FOUND);
 			}
 
 			int totalParams = inParameters.size();
 
 			if (isEmbeddedId) {
 				totalParams = totalParams + pkParameters.size();
 			}
 
 			String stmtExpression = ExpressionBuilder.getUpdate(storedProc,
 					totalParams);
 			CallableStatement st = conn.prepareCall(stmtExpression);
 
 			// register the returned result code
 			st.registerOutParameter(1, Types.INTEGER);
 			st.registerOutParameter(2, Types.VARCHAR);
 
 			// index is 1-based; The first two indices are the
 			// result code and result info
 			setInParameters(st, spBean, 2, inParameters);
 
 			// Set primary key parameters if there it is a separate PK object
 			// For not embeddedId the PK parameters are IN parameters too.
 			if (isEmbeddedId) {
 				setInParameters(st, pkBean, 2, pkParameters);
 			}
 
 			return st;
 		} catch (SQLException se) {
 			throw new StoredProcedurePrepareException(se);
 		}
 	}
 
 	/***
 	 * Build the CallableStatement object for the desired update query
 	 * 
 	 * @param conn
 	 *            The database connection
 	 * @param storedProc
 	 *            The stored procedure
 	 * @param args
 	 *            The input values
 	 * @return The callable statement that forms the update query
 	 * @throws StoredProcedureReservedIndexException
 	 * @throws StoredProcedureException
 	 */
 	protected static CallableStatement getDeleteStatement(
 			final Connection conn, final String storedProc,
 			final Collection<StoredProcField<IN>> pkParameters,
 			final Object pkBean) throws StoredProcedurePrepareException,
 			StoredProcedureReservedIndexException {
 		try {
 			if (pkParameters.size() == 0) {
 				throw new StoredProcedurePrepareException(
 						StatementBuilder.PK_NOT_FOUND);
 			}
 			String stmtExpression = ExpressionBuilder.getUpdate(storedProc,
 					pkParameters.size());
 			CallableStatement st = conn.prepareCall(stmtExpression);
 
 			// register the returned result code
 			st.registerOutParameter(1, Types.INTEGER);
 			st.registerOutParameter(2, Types.VARCHAR);
 
 			// index is 1-based; The first two indices are the
 			// result code and result info
 			setInParameters(st, pkBean, 2, pkParameters);
 			return st;
 		} catch (SQLException se) {
 			throw new StoredProcedurePrepareException(se);
 		}
 	}
 
 	/***
 	 * Build the CallableStatement object for the desired select query
 	 * 
 	 * @param conn
 	 *            The database connection
 	 * @param storedProc
 	 *            The stored procedure
 	 * @param inParameters
 	 *            The input values
 	 * @return The CallableStatement that forms the select query
 	 * @throws StoredProcedureReservedIndexException
 	 * @throws StoredProcedureException
 	 */
 	public static CallableStatement getSelectStatement(final Connection conn,
 			final String storedProc,
 			final Set<StoredProcField<IN>> inParameters,
 			final Set<StoredProcField<IN>> pkInParameters, Object spBean,
 			Object pkBean, boolean isEmbeddedId)
 			throws StoredProcedurePrepareException,
 			StoredProcedureReservedIndexException {
 		try {
 			// we have no restriction for in parameter. The Stored procedure can
 			// have no in parameters for a Select ALL operation.
 			int argc = inParameters.size();
 			if (isEmbeddedId) {
 				argc = argc + pkInParameters.size();
 			}
 
 			String stmtExpression = ExpressionBuilder.getSelect(storedProc,
 					argc);
 			CallableStatement st = conn.prepareCall(stmtExpression);
 
 			// register the returned cursor and return code
 			st.registerOutParameter(1, OracleHelper.ORACLE_CURSOR);
 			st.registerOutParameter(2, Types.INTEGER);
 			st.registerOutParameter(3, Types.VARCHAR);
 
 			// index is 1-based and the first tree indices are the
 			// returned cursor, result code and the result text
 			setInParameters(st, spBean, 3, inParameters);
 			if (isEmbeddedId) {
 				setInParameters(st, pkBean, 3, pkInParameters);
 			}
 			return st;
 		} catch (SQLException se) {
 			throw new StoredProcedurePrepareException(se);
 		}
 	}
 
 	/***
 	 * Build the CallableStatement object for the desired select query
 	 * 
 	 * @param conn
 	 *            The database connection
 	 * @param storedProc
 	 *            The stored procedure
 	 * @param inParameters
 	 *            The input values
 	 * @return The CallableStatement that forms the select query
 	 * @throws StoredProcedureReservedIndexException
 	 * @throws StoredProcedureException
 	 */
 	public static CallableStatement getFindStatement(final Connection conn,
 			final String storedProc,
 			final Set<StoredProcField<IN>> pkParameters,
 			final Set<StoredProcField<OUT>> outParameters, Object spBean,
 			Object pkBean, boolean isEmbeddedId)
 			throws StoredProcedurePrepareException,
 			StoredProcedureReservedIndexException {
 		try {
 			if (pkParameters.size() == 0) {
 				throw new StoredProcedurePrepareException(
 						IN_PARAM_NOT_FOUND_FOR_SELECT_SP);
 			}
 			int argc = pkParameters.size() + outParameters.size();
 			String stmtExpression = ExpressionBuilder.getFind(storedProc, argc);
 			CallableStatement st = conn.prepareCall(stmtExpression);
 
 			// register the returned return code and text
 			st.registerOutParameter(1, Types.INTEGER);
 			st.registerOutParameter(2, Types.VARCHAR);
 
 			// index is 1-based and the first tree indices are the
 			// returned cursor, result code and the primary key
 			if (isEmbeddedId) {
 				setInParameters(st, pkBean, 2, pkParameters);
 			} else {
 				setInParameters(st, spBean, 2, pkParameters);
 			}
 
 			registerOutParameters(st, outParameters);
 
 			return st;
 		} catch (SQLException se) {
 			throw new StoredProcedurePrepareException(se);
 		}
 	}
 
 	/***
 	 * Build the CallableStatement object for the desired function call
 	 * 
 	 * @param conn
 	 * @param spBean
 	 * @param meta
 	 * @return
 	 * @throws StoredProcedurePrepareException
 	 * @throws StoredProcedureReservedIndexException
 	 */
 	public static CallableStatement getFunctionCall(final Connection conn,
 			Object spBean, Object pkBean, final StoredProcMetaDataImpl meta,
 			final StoredProcPkMetaData embeddedMeta, boolean embedded)
 			throws StoredProcedurePrepareException,
 			StoredProcedureReservedIndexException {
 		return getStoredProcedureCall(conn, spBean, pkBean, meta, embeddedMeta,
 				embedded, true);
 	}
 
 	public static CallableStatement getStoredProcedureCall(
 			final Connection conn, Object spBean, Object pkBean,
 			final StoredProcMetaDataImpl meta,
 			final StoredProcPkMetaData embeddedMeta, boolean embedded,
 			boolean isFunction) throws StoredProcedurePrepareException,
 			StoredProcedureReservedIndexException {
 		final String storedProc = meta.getStoredProcName();
 		final Set<StoredProcField<IN>> inParameters = meta.getInParameters();
 		final Set<StoredProcField<OUT>> ourParams = meta.getOutParameters();
 		final Set<StoredProcField<INOUT>> inoutParameters = meta
 				.getInOutParameters();
 		try {
 			int argc = inParameters.size() + ourParams.size()
 					+ inoutParameters.size();
 			if (embedded) {
 				argc = argc + embeddedMeta.getInParameters().size()
 						+ embeddedMeta.getOutParameters().size()
 						+ embeddedMeta.getOutParameters().size();
 			}
 
 			CallableStatement st = null;
 			if (isFunction) {
 				st = conn.prepareCall(ExpressionBuilder.getFunctionCall(
 						storedProc, argc));
 			} else {
 				st = conn.prepareCall(ExpressionBuilder.getStoredProcedureCall(
 						storedProc, argc));
 			}
 
 			setInParameters(st, spBean, 0, inParameters);
 			setInOutParameters(st, spBean, 0, inoutParameters);
 			if (embedded) {
 				setInParameters(st, pkBean, 0, embeddedMeta.getInParameters());
 				setInOutParameters(st, pkBean, 0, embeddedMeta
 						.getInOutParameters());
 			}
 			registerOutParameters(st, ourParams);
 			registerInOutParameters(st, inoutParameters);
 
 			return st;
 
 		} catch (SQLException se) {
 			throw new StoredProcedurePrepareException(se);
 		}
 	}
 }