Example usage for java.sql CallableStatement setString

List of usage examples for java.sql CallableStatement setString

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. CallableStatement
  6. setString

Introduction

In this page you can find the example usage for java.sql CallableStatement setString.

Prototype

void setString(String parameterName, String x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given Java String value.

Usage

From source file:com.mobilewallet.admin.dao.QuestionDAO.java

public int approveQuestion(long userId, long q_id, String is_admin_approved) {
    Connection connection = null;
    CallableStatement pstmt = null;
    ResultSet rs = null;/*  w ww . ja  va  2  s .  c o  m*/
    int approved = 0;
    try {
        connection = dataSource.getConnection();
        pstmt = connection.prepareCall("{call approve_question(?,?,?,?)}");
        pstmt.setLong(1, userId);
        pstmt.setLong(2, q_id);
        pstmt.setString(3, is_admin_approved);

        pstmt.registerOutParameter(4, java.sql.Types.INTEGER);
        pstmt.execute();
        approved = pstmt.getInt(4);
    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {

        try {
            if (rs != null) {
                rs.close();
            }
        } catch (Exception ex) {

        }
        try {
            if (pstmt != null) {
                pstmt.close();
            }
        } catch (Exception ex) {

        }
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (Exception ex) {

        }
    }
    return approved;
}

From source file:exifIndexer.MetadataReader.java

public static void walk(String path, boolean is_recursive) {

    File root = new File(path);
    File[] list = root.listFiles();
    String filePath;//from w w w. j  a v  a  2 s .  co  m
    String fileName;
    String fileExt;
    String valueName;
    String tagName;
    String catName;
    Metadata metadata;
    long fileSize;
    long fileLastModified;
    java.util.Date utilDate;
    java.sql.Date sqlDate;

    String sql = "{ ? = call INSERTIMAGE(?,?,?,?,?) }";
    String sqlMetaData = "{ call INSERTMETADATA (?,?,?,?) }";

    CallableStatement statement;
    CallableStatement statementMeta;
    long result;

    if (list == null) {
        return;
    }

    for (File f : list) {
        if (f.isDirectory() && is_recursive) {
            walk(f.getAbsolutePath(), true);
        } else {

            filePath = FilenameUtils.getFullPath(f.getAbsolutePath());
            fileName = FilenameUtils.getBaseName(f.getName());
            fileExt = FilenameUtils.getExtension(f.getName());
            utilDate = new java.util.Date(f.lastModified());
            sqlDate = new java.sql.Date(utilDate.getTime());

            fileSize = f.length();

            try {
                metadata = ImageMetadataReader.readMetadata(f.getAbsoluteFile());
                try {
                    DBHandler db = new DBHandler();
                    db.openConnection();
                    Connection con = db.getCon();
                    // llamada al metodo insertar imagen SQL con (filePath,fileName,fileExtension,fileSize, fileLastModified)
                    statement = con.prepareCall(sql);
                    statement.setString(2, filePath);
                    statement.setString(3, fileName);
                    statement.setString(4, fileExt);
                    statement.setLong(5, fileSize);
                    statement.setDate(6, sqlDate);
                    statement.registerOutParameter(1, java.sql.Types.NUMERIC);
                    statement.execute();
                    result = statement.getLong(1);

                    // llamada al metodo insertar metadatos SQL con (idImg,valueName, tagName, catName)
                    for (Directory directory : metadata.getDirectories()) {
                        for (Tag tag : directory.getTags()) {

                            valueName = tag.getDescription();
                            tagName = tag.getTagName();
                            catName = directory.getName();

                            if (isNull(valueName) || isNull(tagName) || isNull(catName) || valueName.equals("")
                                    || tagName.equals("") || catName.equals("") || valueName.length() > 250
                                    || tagName.length() > 250 || catName.length() > 500) {
                                System.out.println("Exif row omitted.");
                                System.out.println("Omitting: [" + catName + "] " + tagName + " " + valueName);
                            } else {
                                statementMeta = con.prepareCall(sqlMetaData);
                                statementMeta.setLong(1, result);
                                statementMeta.setString(2, valueName);
                                statementMeta.setString(3, tagName);
                                statementMeta.setString(4, catName);
                                statementMeta.executeUpdate();
                            }
                        }
                    }
                    db.closeConnection();
                } catch (SQLException ex) {
                    System.err.println("Error with SQL command. \n" + ex);
                }
            } catch (ImageProcessingException e) {
                System.out.println("ImageProcessingException " + e);
            } catch (IOException e) {
                System.out.println("IOException " + e);
            }

        }

    }

}

From source file:edu.harvard.i2b2.crc.dao.setfinder.QueryInstanceSpringDao.java

/**
 * Update query instance message/*w  w w.  ja v  a2  s.  c  om*/
 * 
 * @param queryInstanceId
 * @param message
 * @param appendMessageFlag
 * @return 
 */
public void updateMessage(String queryInstanceId, String message, boolean appendMessageFlag)
        throws I2B2DAOException {

    String messageUpdate = "";
    if (appendMessageFlag) {
        String concatOperator = "";
        if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)) {
            concatOperator = "||";
            messageUpdate = " MESSAGE = nvl(MESSAGE,'') " + concatOperator + " ? ";
        } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) {
            concatOperator = "||";
            messageUpdate = " MESSAGE = ? ";

        } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) {
            // Cast(notes as nvarchar(4000))
            //messageUpdate = " message.write (?, NULL, 0) ";

            Connection conn = null;
            try {
                conn = getDataSource().getConnection();
                CallableStatement callStmt = conn
                        .prepareCall("{call " + getDbSchemaName() + "UPDATE_QUERYINSTANCE_MESSAGE(?,?,?)}");
                callStmt.setString(1, message);
                callStmt.setString(2, queryInstanceId);
                callStmt.registerOutParameter(3, java.sql.Types.VARCHAR);
                // callStmt.setString(2, tempPatientMappingTableName);
                callStmt.execute();
                this.getSQLServerProcedureError(dataSourceLookup.getServerType(), callStmt, 3);
            } catch (SQLException sqlEx) {
                sqlEx.printStackTrace();
                throw new I2B2DAOException("SQLException occured" + sqlEx.getMessage(), sqlEx);
            } catch (Exception ex) {
                ex.printStackTrace();
                throw new I2B2DAOException("Exception occured" + ex.getMessage(), ex);
            } finally {
                if (conn != null) {
                    try {
                        conn.close();
                    } catch (SQLException sqlEx) {
                        sqlEx.printStackTrace();
                        log.error("Error while closing connection", sqlEx);
                    }
                }
            }
            return;
            //////

        }

    } else {
        messageUpdate = " MESSAGE = ?";
    }
    String sql = "UPDATE " + getDbSchemaName() + "QT_QUERY_INSTANCE set " + messageUpdate
            + " where query_instance_id = ? ";
    jdbcTemplate.update(sql, new Object[] {

            (message == null) ? "" : message, Integer.parseInt(queryInstanceId) });

}

From source file:com.cisco.iwe.services.util.EmailMonitor.java

/**
 * /*from   www  . ja  v a 2s .  co m*/
 * @throws Exception
 */
/* This sends notification to the user via mail in case the user has uploaded an expense receipt type apart from .jpg,.jpeg,.bmp,.png,.txt,.doc,.pdf formats. */
public void sendNotification() throws Exception {
    CallableStatement stmt = null;
    Connection conn = null;
    try {
        conn = DataBaseUtil.getDevConnection();
        stmt = conn.prepareCall(EmailParseConstants.sendNotificationQuery);
        stmt.setString(1, EmailParseConstants.genericMailAccount);
        stmt.setString(2, EmailParseConstants.emailHostName);
        stmt.setString(3, EmailParseConstants.mailPortNumber);
        stmt.executeUpdate();
    } finally {
        try {
            if (stmt != null) {
            }
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
    }
}

From source file:DAO.Poll_Tbl_pkg.Poll_TblJDBCTemplate.java

public int create2(int uid, String cid_json, String title, String description, String qtn_json, String ans_json,
        String poll_link, String start_ts, String end_ts, int reward, String poll_type) {
    System.out.println("reached create2");
    CallableStatement st;
    int pid = 0;/* www  . j ava2  s.com*/
    try {

        con = conn.getDataSource().getConnection();
        System.out.println("15 dec 10am");
        String sql = "{call createPoll2 (?, ? , ? , ? ,? ,? ,? ,? ,? ,? ,? ,?)}";
        st = con.prepareCall(sql);

        //Bind IN parameter first, then bind OUT parameter
        st.setInt(1, uid);
        st.setString(2, cid_json);
        st.setString(3, title);
        st.setString(4, description);
        st.setString(5, qtn_json);
        st.setString(6, ans_json);
        st.setString(7, poll_link);
        st.setString(8, start_ts);
        st.setString(9, end_ts);
        st.setInt(10, reward);
        st.setString(11, poll_type);
        st.registerOutParameter(12, java.sql.Types.INTEGER);

        //Use execute method to run stored procedure.
        System.out.println("Executing stored procedure...");
        st.execute();

        pid = st.getInt(12);
        System.out.println("PID mila balle balle" + pid);

        con.close();

        return pid;
    } catch (Exception e) {
        System.out.println("createPoll2 procedure error=" + e);
        return pid;
    }

}

From source file:com.jfootball.dao.hibernate.PlayerDaoImpl.java

public void endSeasonJob() {
    logger.info("Execute endSeason job");

    Session session = hibernateTemplate.getSessionFactory().getCurrentSession();
    SessionFactoryImpl sessionFactory = (SessionFactoryImpl) session.getSessionFactory();

    try {//ww w .j  av a  2  s .c  o m
        Connection conn = sessionFactory.getConnectionProvider().getConnection();
        CallableStatement cstmt = conn.prepareCall("{ call endSeasonBatch(?) }");

        GregorianCalendar gc = new GregorianCalendar();
        gc.setTime(new Date(System.currentTimeMillis()));

        int year = gc.get(Calendar.YEAR);

        cstmt.setString("param_year", "30/06/" + year); // current year.

        cstmt.execute();

        logger.info("EndSeason job executed");
    } catch (SQLException e) {
        logger.error(e);
    }

    logger.info("EndSeason job finished");
}

From source file:com.cws.us.pws.dao.impl.CareersReferenceDAOImpl.java

/**
 * @see com.cws.us.pws.dao.interfaces.ICareersReferenceDAO#getCareerData(String, String) throws SQLException
 *//*from   w w  w . java 2  s. c o m*/
@Override
public List<Object> getCareerData(final String reqId, final String lang) throws SQLException {
    final String methodName = ICareersReferenceDAO.CNAME
            + "#getCareerData(final int reqId, final String lang) throws SQLException";

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

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

    try {
        sqlConn = this.dataSource.getConnection();

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

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

        stmt = sqlConn.prepareCall("{ CALL getCareerData(?, ?) }");
        stmt.setString(1, reqId);
        stmt.setString(2, lang);

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

        if (!(stmt.execute())) {
            throw new SQLException("PreparedStatement is null. Cannot execute.");
        }

        resultSet = stmt.getResultSet();

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

        if (resultSet.next()) {
            resultSet.beforeFirst();
            results = new ArrayList<Object>();

            while (resultSet.next()) {
                results.add(resultSet.getString(1)); // REQ_ID
                results.add(resultSet.getDate(2)); // POST_DATE
                results.add(resultSet.getDate(3)); // UNPOST_DATE
                results.add(resultSet.getString(4)); // JOB_TITLE
                results.add(resultSet.getString(5)); // JOB_SHORT_DESC
                results.add(resultSet.getString(6)); // JOB_DESCRIPTION
            }

            if (DEBUG) {
                DEBUGGER.debug("results: {}", results);
            }
        }
    } 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();
        }
    }

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

    return results;
}

From source file:Statement.Statement.java

private void load() {
    //Display dong Fix va Other Cost
    try {/*from  w  w  w.  j  a v  a2 s .c  o  m*/
        PreparedStatement st = cnn
                .prepareStatement("select Fixed,Other from Expense where ShopID = ? and Date = ?");
        st.setString(1, code);
        st.setString(2, date);
        ResultSet rs = st.executeQuery();

        while (rs.next()) {
            txtFix.setText(customFormat("VND ###,###,###", rs.getInt(1)));
            fix = rs.getInt(1);
            txtOther.setText(customFormat("VND ###,###,###", rs.getInt(2)));
            other = rs.getInt(2);
        }
    } catch (SQLException e) {
        System.err.println(e.getMessage());
    }
    //Display dong Revenue
    String query = "{call Revenue_Shop_Date(?,?,?)}";
    try {
        CallableStatement cst = cnn.prepareCall(query);
        cst.setString(1, code);
        cst.setString(2, date);
        cst.registerOutParameter(3, INTEGER);
        cst.execute();

        txtRev.setText(customFormat("VND ###,###,###", cst.getInt(3)));
        rev = cst.getInt(3);
    } catch (Exception e) {
    }
    //Display dong Profit
    cost = fix + other;
    profit = rev - cost;
    txtProfit.setText(customFormat("VND ###,###,###", profit));

    //Display comment
    try {

        PreparedStatement st1 = cnn.prepareStatement("select Goal from Shop where ShopID = ?");
        st1.setString(1, code);
        ResultSet rs1 = st1.executeQuery();

        while (rs1.next()) {
            int goal = rs1.getInt(1);
            if (rev >= goal) {
                comment.setText("Congrats! The Shop has achieved the goal");
            } else {
                comment.setText("The Shop has failed the goal");
            }
        }

    } catch (SQLException e) {
        System.err.println(e.getMessage());
    }
}

From source file:com.cws.us.pws.dao.impl.CareersReferenceDAOImpl.java

/**
 * @see com.cws.us.pws.dao.interfaces.ICareersReferenceDAO#getCareerList(String) throws SQLException
 */// w  ww.ja v a 2  s . co  m
@Override
public List<Object[]> getCareerList(final String lang) throws SQLException {
    final String methodName = ICareersReferenceDAO.CNAME
            + "#getCareerList(final String lang) throws SQLException";

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

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

    try {
        sqlConn = this.dataSource.getConnection();

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

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

        stmt = sqlConn.prepareCall("{ CALL getCareersList(?) }");
        stmt.setString(1, lang);

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

        if (!(stmt.execute())) {
            throw new SQLException("PreparedStatement is null. Cannot execute.");
        }

        resultSet = stmt.getResultSet();

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

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

            while (resultSet.next()) {
                Object[] data = new Object[] { resultSet.getString(1), // REQ_ID
                        resultSet.getString(2), // POST_DATE
                        resultSet.getString(3), // UNPOST_DATE
                        resultSet.getString(4), // JOB_TITLE
                        resultSet.getBigDecimal(5), // JOB_SHORT_DESC
                        resultSet.getString(6), // JOB_DESCRIPTION
                };

                results.add(data);
            }

            if (DEBUG) {
                DEBUGGER.debug("results: {}", results);
            }
        }
    } 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();
        }
    }

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

    return results;
}

From source file:dbProcs.Getter.java

/**
 * @param ApplicationRoot The current running context of the application
 * @param classId The identifier of the class
 * @return String Array with Class information with the format of {name, year}
 *//*from ww w  . ja va 2 s  . c o  m*/
public static String[] getClassInfo(String ApplicationRoot, String classId) {
    String[] result = new String[2];
    log.debug("*** Getter.getClassInfo (Single Class) ***");
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        CallableStatement callstmt = conn.prepareCall("call classFind(?)");
        callstmt.setString(1, classId);
        log.debug("Gathering classFind ResultSet");
        ResultSet resultSet = callstmt.executeQuery();
        log.debug("Opening Result Set from classFind");
        resultSet.next();
        result[0] = resultSet.getString(1);//Name
        result[1] = resultSet.getString(2);//Year
    } catch (SQLException e) {
        log.error("Could not execute query: " + e.toString());
        result = null;
    }
    log.debug("*** END getClassInfo");
    return result;
}