Example usage for java.sql Connection prepareCall

List of usage examples for java.sql Connection prepareCall

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Connection
  6. prepareCall

Introduction

In this page you can find the example usage for java.sql Connection prepareCall.

Prototype

CallableStatement prepareCall(String sql) throws SQLException;

Source Link

Document

Creates a CallableStatement object for calling database stored procedures.

Usage

From source file:com.cws.esolutions.core.dao.impl.WebMessagingDAOImpl.java

/**
 * @see com.cws.esolutions.core.dao.interfaces.IWebMessagingDAO#retrieveMessage(String)
 *///from  w  w  w  .ja  v a2 s . c  om
public synchronized List<Object> retrieveMessage(final String messageId) throws SQLException {
    final String methodName = IWebMessagingDAO.CNAME
            + "#retrieveMessage(final String messageId) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug(messageId);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<Object> svcMessage = null;

    try {
        sqlConn = dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);
        stmt = sqlConn.prepareCall("{CALL retrServiceMessage(?)}");
        stmt.setString(1, messageId);

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (stmt.execute()) {
            resultSet = stmt.getResultSet();

            if (resultSet.next()) {
                resultSet.first();
                svcMessage = new ArrayList<Object>();
                svcMessage.add(resultSet.getString(1)); // svc_message_id
                svcMessage.add(resultSet.getString(2)); // svc_message_title
                svcMessage.add(resultSet.getString(3)); // svc_message_txt
                svcMessage.add(resultSet.getString(4)); // svc_message_author
                svcMessage.add(resultSet.getTimestamp(5)); // svc_message_submitdate
                svcMessage.add(resultSet.getBoolean(6)); // svc_message_active
                svcMessage.add(resultSet.getBoolean(7)); // svc_message_alert
                svcMessage.add(resultSet.getBoolean(8)); // svc_message_expires
                svcMessage.add(resultSet.getTimestamp(9)); // svc_message_expirydate
                svcMessage.add(resultSet.getTimestamp(10)); // svc_message_modifiedon
                svcMessage.add(resultSet.getString(11)); // svc_message_modifiedby

                if (DEBUG) {
                    DEBUGGER.debug("svcMessage: {}", svcMessage);
                }
            }
        }
    } catch (SQLException sqx) {
        ERROR_RECORDER.error(sqx.getMessage(), sqx);

        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }

        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    return svcMessage;
}

From source file:com.cws.esolutions.core.dao.impl.WebMessagingDAOImpl.java

/**
 * @see com.cws.esolutions.core.dao.interfaces.IWebMessagingDAO#retrieveMessages()
 *//*from www.  j  av  a  2 s.co m*/
public synchronized List<Object[]> retrieveMessages() throws SQLException {
    final String methodName = IWebMessagingDAO.CNAME + "#retrieveMessages() throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<Object[]> response = null;

    try {
        sqlConn = dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);
        stmt = sqlConn.prepareCall("{CALL retrServiceMessages()}");

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (stmt.execute()) {
            resultSet = stmt.getResultSet();

            if (DEBUG) {
                DEBUGGER.debug("ResultSet: {}", resultSet);
            }

            if (resultSet.next()) {
                resultSet.beforeFirst();
                response = new ArrayList<Object[]>();

                while (resultSet.next()) {
                    Object[] data = new Object[] { resultSet.getString(1), // svc_message_id
                            resultSet.getString(2), // svc_message_title
                            resultSet.getString(3), // svc_message_txt
                            resultSet.getString(4), // svc_message_author
                            resultSet.getTimestamp(5), // svc_message_submitdate
                            resultSet.getBoolean(6), // svc_message_active
                            resultSet.getBoolean(7), // svc_message_alert
                            resultSet.getBoolean(8), // svc_message_expires
                            resultSet.getTimestamp(9), // svc_message_expirydate
                            resultSet.getTimestamp(10), // svc_message_modifiedon
                            resultSet.getString(11) // svc_message_modifiedby
                    };

                    if (DEBUG) {
                        DEBUGGER.debug("data: {}", data);
                    }

                    response.add(data);
                }
            }
        }
    } catch (SQLException sqx) {
        ERROR_RECORDER.error(sqx.getMessage(), sqx);

        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }

        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    return response;
}

From source file:com.cws.esolutions.core.dao.impl.WebMessagingDAOImpl.java

/**
 * @see com.cws.esolutions.core.dao.interfaces.IWebMessagingDAO#retrieveAlertMessages()
 *///from   w w  w. jav a 2s  .  c om
public synchronized List<Object[]> retrieveAlertMessages() throws SQLException {
    final String methodName = IWebMessagingDAO.CNAME + "#retrieveAlertMessages() throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<Object[]> response = null;

    try {
        sqlConn = dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);
        stmt = sqlConn.prepareCall("{CALL retrAlertMessages()}");

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (stmt.execute()) {
            resultSet = stmt.getResultSet();

            if (DEBUG) {
                DEBUGGER.debug("ResultSet: {}", resultSet);
            }

            if (resultSet.next()) {
                resultSet.beforeFirst();
                response = new ArrayList<Object[]>();

                while (resultSet.next()) {
                    Object[] data = new Object[] { resultSet.getString(1), // svc_message_id
                            resultSet.getString(2), // svc_message_title
                            resultSet.getString(3), // svc_message_txt
                            resultSet.getString(4), // svc_message_author
                            resultSet.getTimestamp(5), // svc_message_submitdate
                            resultSet.getBoolean(6), // svc_message_active
                            resultSet.getBoolean(7), // svc_message_alert
                            resultSet.getBoolean(8), // svc_message_expires
                            resultSet.getTimestamp(9), // svc_message_expirydate
                            resultSet.getTimestamp(10), // svc_message_modifiedon
                            resultSet.getString(11) // svc_message_modifiedby
                    };

                    if (DEBUG) {
                        DEBUGGER.debug("data: {}", data);
                    }

                    response.add(data);
                }
            }
        }
    } catch (SQLException sqx) {
        ERROR_RECORDER.error(sqx.getMessage(), sqx);

        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }

        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    return response;
}

From source file:com.cws.esolutions.core.dao.impl.ServerDataDAOImpl.java

/**
 * @see com.cws.esolutions.core.dao.interfaces.IServerDataDAO#listServers(int)
 *///ww w  .  ja va 2s  . c om
public synchronized List<String[]> listServers(final int startRow) throws SQLException {
    final String methodName = IServerDataDAO.CNAME + "#listServers(final int startRow) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("Value: {}", startRow);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<String[]> responseData = null;

    try {
        sqlConn = dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{CALL retrServerList(?)}");
        stmt.setInt(1, startRow);

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (stmt.execute()) {
            resultSet = stmt.getResultSet();

            if (DEBUG) {
                DEBUGGER.debug("resultSet: {}", resultSet);
            }

            if (resultSet.next()) {
                resultSet.beforeFirst();
                responseData = new ArrayList<String[]>();

                while (resultSet.next()) {
                    String[] serverData = new String[] { resultSet.getString(1), // T1.SYSTEM_GUID
                            resultSet.getString(2), // T1.SYSTEM_REGION
                            resultSet.getString(3), // T1.NETWORK_PARTITION
                            resultSet.getString(4), // T1.OPER_HOSTNAME
                            resultSet.getString(5), // T1.OWNING_DMGR
                            resultSet.getString(6), // T2.GUID
                            resultSet.getString(7) // T2.NAME
                    };

                    if (DEBUG) {
                        for (Object obj : serverData) {
                            DEBUGGER.debug("Value: {}", obj);
                        }
                    }

                    responseData.add(serverData);
                }

                if (DEBUG) {
                    for (Object[] objArr : responseData) {
                        for (Object obj : objArr) {
                            DEBUGGER.debug("Value: {}", obj);
                        }
                    }
                }
            }
        }
    } catch (SQLException sqx) {
        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }

        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    return responseData;
}

From source file:common.dao.impl.BaseDAOImpl.java

public List<Map<String, Object>> callQueryProcedure(final String sql, final Object[] params) {
    logger.debug("start to call procedure" + sql + ", params is " + params);
    final List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
    getCurrentSession().doWork(new Work() {
        public void execute(Connection conn) throws SQLException {
            CallableStatement cs = conn.prepareCall(sql);
            if (params != null) {
                logger.debug("params is not null it's members is " + Arrays.asList(params));
                for (int i = 0; i < params.length; i++) {
                    cs.setObject(i + 1, params[i]);
                }//from   ww w.  j  a v a  2 s  .  c  o m
            } else
                logger.debug("params is null");
            ResultSet rs = cs.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int colCount = metaData.getColumnCount();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= colCount; i++) {
                    String colName = metaData.getColumnName(i);
                    map.put(colName, rs.getObject(colName));
                }
                result.add(map);
            }
            rs.close();
            cs.close();
        }
    });
    return result;
}

From source file:net.sourceforge.msscodefactory.cfasterisk.v2_2.CFAstOracle.CFAstOracleSysClusterTable.java

public void createSysCluster(CFAstAuthorization Authorization, CFAstSysClusterBuff Buff) {
    final String S_ProcName = "createSysCluster";
    if (!schema.isTransactionOpen()) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName,
                "Transaction not open");
    }/*from  w  w  w  . j ava  2s. com*/
    ResultSet resultSet = null;
    CallableStatement stmtCreateByPKey = null;
    try {
        int SingletonId = Buff.getRequiredSingletonId();
        long ClusterId = Buff.getRequiredClusterId();
        Connection cnx = schema.getCnx();
        stmtCreateByPKey = cnx.prepareCall("begin " + schema.getLowerDbSchemaName()
                + ".crt_sysclus( ?, ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + " ); end;");
        int argIdx = 1;
        stmtCreateByPKey.registerOutParameter(argIdx++, OracleTypes.CURSOR);
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtCreateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtCreateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtCreateByPKey.setString(argIdx++, "SYSC");
        stmtCreateByPKey.setInt(argIdx++, SingletonId);
        stmtCreateByPKey.setLong(argIdx++, ClusterId);
        stmtCreateByPKey.execute();
        resultSet = (ResultSet) stmtCreateByPKey.getObject(1);
        if (resultSet == null) {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "crt_sysclus() did not return a result set");
        }
        try {
            if (resultSet.next()) {
                CFAstSysClusterBuff createdBuff = unpackSysClusterResultSetToBuff(resultSet);
                if (resultSet.next()) {
                    resultSet.last();
                    throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                            "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
                }
                Buff.setRequiredSingletonId(createdBuff.getRequiredSingletonId());
                Buff.setRequiredClusterId(createdBuff.getRequiredClusterId());
                Buff.setRequiredRevision(createdBuff.getRequiredRevision());
            } else {
                throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                        "Expected a single-record response, " + resultSet.getRow() + " rows selected");
            }
        } catch (SQLException e) {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "crt_sysclus() did not return a valid result set");
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
        if (stmtCreateByPKey != null) {
            try {
                stmtCreateByPKey.close();
            } catch (SQLException e) {
            }
            stmtCreateByPKey = null;
        }
    }
}

From source file:net.sourceforge.msscodefactory.cfasterisk.v2_4.CFAsteriskOracle.CFAsteriskOracleServiceTypeTable.java

public void createServiceType(CFSecurityAuthorization Authorization, CFSecurityServiceTypeBuff Buff) {
    final String S_ProcName = "createServiceType";
    if (!schema.isTransactionOpen()) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName,
                "Transaction not open");
    }/*ww w  . ja va2s  .  c  om*/
    ResultSet resultSet = null;
    CallableStatement stmtCreateByPKey = null;
    try {
        String Description = Buff.getRequiredDescription();
        Connection cnx = schema.getCnx();
        stmtCreateByPKey = cnx.prepareCall("begin " + schema.getLowerDbSchemaName()
                + ".crt_svctype( ?, ?, ?, ?, ?, ?, ?" + ", " + "?" + " ); end;");
        int argIdx = 1;
        stmtCreateByPKey.registerOutParameter(argIdx++, OracleTypes.CURSOR);
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtCreateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtCreateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtCreateByPKey.setString(argIdx++, "SVCT");
        stmtCreateByPKey.setString(argIdx++, Description);
        stmtCreateByPKey.execute();
        resultSet = (ResultSet) stmtCreateByPKey.getObject(1);
        if (resultSet == null) {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "crt_svctype() did not return a result set");
        }
        try {
            if (resultSet.next()) {
                CFSecurityServiceTypeBuff createdBuff = unpackServiceTypeResultSetToBuff(resultSet);
                if (resultSet.next()) {
                    resultSet.last();
                    throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                            "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
                }
                Buff.setRequiredServiceTypeId(createdBuff.getRequiredServiceTypeId());
                Buff.setRequiredDescription(createdBuff.getRequiredDescription());
                Buff.setRequiredRevision(createdBuff.getRequiredRevision());
                Buff.setCreatedByUserId(createdBuff.getCreatedByUserId());
                Buff.setCreatedAt(createdBuff.getCreatedAt());
                Buff.setUpdatedByUserId(createdBuff.getUpdatedByUserId());
                Buff.setUpdatedAt(createdBuff.getUpdatedAt());
            } else {
                throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                        "Expected a single-record response, " + resultSet.getRow() + " rows selected");
            }
        } catch (SQLException e) {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "crt_svctype() did not return a valid result set");
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
        if (stmtCreateByPKey != null) {
            try {
                stmtCreateByPKey.close();
            } catch (SQLException e) {
            }
            stmtCreateByPKey = null;
        }
    }
}

From source file:net.sourceforge.msscodefactory.cfasterisk.v2_4.CFAsteriskOracle.CFAsteriskOracleMimeTypeTable.java

public void createMimeType(CFSecurityAuthorization Authorization, CFInternetMimeTypeBuff Buff) {
    final String S_ProcName = "createMimeType";
    if (!schema.isTransactionOpen()) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName,
                "Transaction not open");
    }/*from  w  ww  .  j  ava  2 s.co  m*/
    ResultSet resultSet = null;
    CallableStatement stmtCreateByPKey = null;
    try {
        String Name = Buff.getRequiredName();
        String Description = Buff.getRequiredDescription();
        Connection cnx = schema.getCnx();
        stmtCreateByPKey = cnx.prepareCall("begin " + schema.getLowerDbSchemaName()
                + ".crt_mimetype( ?, ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + " ); end;");
        int argIdx = 1;
        stmtCreateByPKey.registerOutParameter(argIdx++, OracleTypes.CURSOR);
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtCreateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtCreateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtCreateByPKey.setString(argIdx++, "MIME");
        stmtCreateByPKey.setString(argIdx++, Name);
        stmtCreateByPKey.setString(argIdx++, Description);
        stmtCreateByPKey.execute();
        resultSet = (ResultSet) stmtCreateByPKey.getObject(1);
        if (resultSet == null) {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "crt_mimetype() did not return a result set");
        }
        try {
            if (resultSet.next()) {
                CFInternetMimeTypeBuff createdBuff = unpackMimeTypeResultSetToBuff(resultSet);
                if (resultSet.next()) {
                    resultSet.last();
                    throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                            "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
                }
                Buff.setRequiredMimeTypeId(createdBuff.getRequiredMimeTypeId());
                Buff.setRequiredName(createdBuff.getRequiredName());
                Buff.setRequiredDescription(createdBuff.getRequiredDescription());
                Buff.setRequiredRevision(createdBuff.getRequiredRevision());
                Buff.setCreatedByUserId(createdBuff.getCreatedByUserId());
                Buff.setCreatedAt(createdBuff.getCreatedAt());
                Buff.setUpdatedByUserId(createdBuff.getUpdatedByUserId());
                Buff.setUpdatedAt(createdBuff.getUpdatedAt());
            } else {
                throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                        "Expected a single-record response, " + resultSet.getRow() + " rows selected");
            }
        } catch (SQLException e) {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "crt_mimetype() did not return a valid result set");
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
        if (stmtCreateByPKey != null) {
            try {
                stmtCreateByPKey.close();
            } catch (SQLException e) {
            }
            stmtCreateByPKey = null;
        }
    }
}

From source file:net.sourceforge.msscodefactory.cfacc.v2_0.CFAccOracle.CFAccOracleServiceTypeTable.java

public void createServiceType(CFAccAuthorization Authorization, CFAccServiceTypeBuff Buff) {
    final String S_ProcName = "createServiceType";
    if (!schema.isTransactionOpen()) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName,
                "Transaction not open");
    }/*from ww w .  ja  v  a  2s  .c  om*/
    ResultSet resultSet = null;
    CallableStatement stmtCreateByPKey = null;
    try {
        String Description = Buff.getRequiredDescription();
        Connection cnx = schema.getCnx();
        stmtCreateByPKey = cnx.prepareCall("begin " + schema.getLowerSchemaDbName()
                + ".crt_svctype( ?, ?, ?, ?, ?, ?, ?" + ", " + "?" + " ); end;");
        int argIdx = 1;
        stmtCreateByPKey.registerOutParameter(argIdx++, OracleTypes.CURSOR);
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtCreateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtCreateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtCreateByPKey.setString(argIdx++, "SVCT");
        stmtCreateByPKey.setString(argIdx++, Description);
        stmtCreateByPKey.execute();
        resultSet = (ResultSet) stmtCreateByPKey.getObject(1);
        if (resultSet == null) {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "crt_svctype() did not return a result set");
        }
        try {
            if (resultSet.next()) {
                CFAccServiceTypeBuff createdBuff = unpackServiceTypeResultSetToBuff(resultSet);
                if (resultSet.next()) {
                    resultSet.last();
                    throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                            "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
                }
                Buff.setRequiredServiceTypeId(createdBuff.getRequiredServiceTypeId());
                Buff.setRequiredDescription(createdBuff.getRequiredDescription());
                Buff.setRequiredRevision(createdBuff.getRequiredRevision());
                Buff.setCreatedByUserId(createdBuff.getCreatedByUserId());
                Buff.setCreatedAt(createdBuff.getCreatedAt());
                Buff.setUpdatedByUserId(createdBuff.getUpdatedByUserId());
                Buff.setUpdatedAt(createdBuff.getUpdatedAt());
            } else {
                throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                        "Expected a single-record response, " + resultSet.getRow() + " rows selected");
            }
        } catch (SQLException e) {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "crt_svctype() did not return a valid result set");
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
        if (stmtCreateByPKey != null) {
            try {
                stmtCreateByPKey.close();
            } catch (SQLException e) {
            }
            stmtCreateByPKey = null;
        }
    }
}

From source file:net.sourceforge.msscodefactory.cfasterisk.v2_0.CFAstOracle.CFAstOracleServiceTypeTable.java

public void createServiceType(CFAstAuthorization Authorization, CFAstServiceTypeBuff Buff) {
    final String S_ProcName = "createServiceType";
    if (!schema.isTransactionOpen()) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName,
                "Transaction not open");
    }//from ww w .  j a v  a2  s . com
    ResultSet resultSet = null;
    CallableStatement stmtCreateByPKey = null;
    try {
        String Description = Buff.getRequiredDescription();
        Connection cnx = schema.getCnx();
        stmtCreateByPKey = cnx.prepareCall("begin " + schema.getLowerSchemaDbName()
                + ".crt_svctype( ?, ?, ?, ?, ?, ?, ?" + ", " + "?" + " ); end;");
        int argIdx = 1;
        stmtCreateByPKey.registerOutParameter(argIdx++, OracleTypes.CURSOR);
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtCreateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecUserId().toString());
        stmtCreateByPKey.setString(argIdx++,
                (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
        stmtCreateByPKey.setLong(argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
        stmtCreateByPKey.setString(argIdx++, "SVCT");
        stmtCreateByPKey.setString(argIdx++, Description);
        stmtCreateByPKey.execute();
        resultSet = (ResultSet) stmtCreateByPKey.getObject(1);
        if (resultSet == null) {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "crt_svctype() did not return a result set");
        }
        try {
            if (resultSet.next()) {
                CFAstServiceTypeBuff createdBuff = unpackServiceTypeResultSetToBuff(resultSet);
                if (resultSet.next()) {
                    resultSet.last();
                    throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                            "Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
                }
                Buff.setRequiredServiceTypeId(createdBuff.getRequiredServiceTypeId());
                Buff.setRequiredDescription(createdBuff.getRequiredDescription());
                Buff.setRequiredRevision(createdBuff.getRequiredRevision());
                Buff.setCreatedByUserId(createdBuff.getCreatedByUserId());
                Buff.setCreatedAt(createdBuff.getCreatedAt());
                Buff.setUpdatedByUserId(createdBuff.getUpdatedByUserId());
                Buff.setUpdatedAt(createdBuff.getUpdatedAt());
            } else {
                throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                        "Expected a single-record response, " + resultSet.getRow() + " rows selected");
            }
        } catch (SQLException e) {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "crt_svctype() did not return a valid result set");
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
            }
            resultSet = null;
        }
        if (stmtCreateByPKey != null) {
            try {
                stmtCreateByPKey.close();
            } catch (SQLException e) {
            }
            stmtCreateByPKey = null;
        }
    }
}