Example usage for java.sql CallableStatement setObject

List of usage examples for java.sql CallableStatement setObject

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

Introduction

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

Prototype

void setObject(String parameterName, Object x) throws SQLException;

Source Link

Document

Sets the value of the designated parameter with the given object.

Usage

From source file:pingpong.db.DBAccess.java

public TResultSet executeProcedure1(String sql, Object args[]) throws SQLException {
    Connection local_con = null;//from ww  w  .  j  a v  a  2 s. c o  m
    CallableStatement cstmt = null;
    ResultSet rs = null;
    TResultSet ds = null;

    sql = sql.replace(")", ",?)"); //
    log.debug("sql repalce:" + sql + "len:" + args.length);

    int idxParam = (args == null) ? 0 : args.length + 1;
    for (int i = 0; i < args.length; i++)
        log.debug("arg " + i + " : " + args[i]);
    try {
        if (m_con != null) {
            cstmt = m_con.prepareCall(sql);
        } else {
            local_con = getConnection();
            cstmt = local_con.prepareCall(sql);
        }
        if (args != null) {
            for (int i = 0; i < args.length; i++) {
                //log.debug("###########i: "+i +"/"+args[i]+"/"+args.length);
                if (args[i] instanceof Null) {
                    cstmt.setNull(i + 1, ((Null) args[i]).type);
                } else {
                    cstmt.setObject(i + 1, args[i]);
                }

            }

            // For Oracle Procedure return Cursor
            //            cstmt.registerOutParameter(idxParam, OracleTypes.CURSOR);  //            
        }

        // For Oracle Procedure
        //          cstmt.executeQuery();          
        //          rs = (ResultSet) cstmt.getObject(idxParam);
        rs = cstmt.executeQuery();

        return makeTResultSet(rs, 0, -1);
    } catch (SQLException e) {
        state = e.getErrorCode();
        sqlState = e.getSQLState();
        message = e.getMessage();
        throw e;
    } catch (Exception e) {
        message = e.getMessage();
        e.printStackTrace();
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
            }
        }
        if (cstmt != null) {
            try {
                cstmt.close();
            } catch (Exception e) {
            }
        }
        if (local_con != null) {
            try {
                local_con.close();
            } catch (Exception e) {
            }
        }
    }
    return ds;
}

From source file:pingpong.db.DBAccess.java

/**
 * ResultSet?  ?  ? .//from w w w  . j av  a 2s .co  m
 * @param args
 *       -  2? arguments   .
 * @return TResultSet? 
 */
public TResultSet executeProcedure2(String sql, Object args[]) throws SQLException {
    //      String result[] = {"", ""};
    Connection local_con = null;
    CallableStatement cstmt = null;
    ResultSet rs = null;
    TResultSet ds = null;

    //   sql.replace("?)", "?,?)");
    int idxParam = (args == null) ? 0 : args.length;
    try {
        if (m_con != null) {
            cstmt = m_con.prepareCall(sql);
        } else {
            local_con = getConnection();
            cstmt = local_con.prepareCall(sql);
        }
        if (args != null) {
            for (int i = 0; i < args.length - 2; i++) {
                if (args[i] instanceof Null) {
                    log.debug("1###########i:" + i + args[i]);

                    cstmt.setNull(i + 1, ((Null) args[i]).type);
                } else {
                    cstmt.setObject(i + 1, args[i]);
                    log.debug("2###########i:" + i + args[i]);
                }
            }
            cstmt.setString(idxParam - 1, "");
            cstmt.setString(idxParam, "");
            cstmt.registerOutParameter(idxParam - 1, Types.VARCHAR);
            cstmt.registerOutParameter(idxParam, Types.VARCHAR);
            //   cstmt.registerOutParameter(idxParam, OracleTypes.CURSOR);   
            log.debug("###########sql:" + sql + "\nargs:" + args + " len:" + args.length);
            log.debug("###########sql:" + sql + "\nargs:" + args + " len:" + args.length);
        }
        rs = cstmt.executeQuery();
        ds = new TResultSet(rs);
        args[idxParam - 2] = cstmt.getString(idxParam - 1);
        args[idxParam - 1] = cstmt.getString(idxParam);
    } catch (SQLException e) {
        state = e.getErrorCode();
        sqlState = e.getSQLState();
        message = e.getMessage();
        throw e;
    } catch (Exception e) {
        message = e.getMessage();
        e.printStackTrace();
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
            }
        }
        if (cstmt != null) {
            try {
                cstmt.close();
            } catch (Exception e) {
            }
        }
        if (local_con != null) {
            try {
                local_con.close();
            } catch (Exception e) {
            }
        }
    }
    return ds;
}

From source file:pingpong.db.DBAccess.java

public TResultSet executeProcedure3(String sql, Object args[]) throws SQLException {
    Connection local_con = null;//w w w .  j a  va2s  .  c o  m
    CallableStatement cstmt = null;
    ResultSet rs = null;
    TResultSet ds = null;

    sql = sql.replace("?)", "?,?)"); //
    log.debug("sql repalce:" + sql);
    int idxParam = (args == null) ? 0 : args.length + 1;
    try {
        if (m_con != null) {
            cstmt = m_con.prepareCall(sql);
        } else {
            local_con = getConnection();
            cstmt = local_con.prepareCall(sql);
        }
        if (args != null) {
            for (int i = 0; i < args.length - 2; i++) {
                log.debug("###########i: " + i + "/" + args[i] + "/" + args.length);
                if (args[i] instanceof Null) {
                    cstmt.setNull(i + 1, ((Null) args[i]).type);
                } else {
                    cstmt.setObject(i + 1, args[i]);
                }
            }

            cstmt.setString(idxParam - 2, "");
            cstmt.setString(idxParam - 1, "");
            cstmt.registerOutParameter(idxParam - 2, Types.VARCHAR);
            cstmt.registerOutParameter(idxParam - 1, Types.VARCHAR);
            // For Oracle
            //            cstmt.registerOutParameter(idxParam, OracleTypes.CURSOR);  //

            log.debug("###########sql:" + sql + "\nargs:" + args + " len:" + args.length);
        }

        //OracleCallableStatement ? getCursor() method  REF CURSOR 
        //JDBC ResultSet variable ? .         
        //          cstmt.executeQuery();          
        //          rs = (ResultSet) cstmt.getObject(idxParam);
        rs = cstmt.executeQuery();
        ds = new TResultSet(rs);

        args[idxParam - 3] = cstmt.getString(idxParam - 2); //call by refenene  000
        args[idxParam - 2] = cstmt.getString(idxParam - 1); //O.K
        log.debug(cstmt.getString(idxParam - 2) + "," + cstmt.getString(idxParam - 1));
    } catch (SQLException e) {
        state = e.getErrorCode();
        sqlState = e.getSQLState();
        message = e.getMessage();
        throw e;
    } catch (Exception e) {
        message = e.getMessage();
        e.printStackTrace();
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
            }
        }
        if (cstmt != null) {
            try {
                cstmt.close();
            } catch (Exception e) {
            }
        }
        if (local_con != null) {
            try {
                local_con.close();
            } catch (Exception e) {
            }
        }
    }
    return ds;
}

From source file:us.daveread.basicquery.BasicQuery.java

/**
 * Setup a callable statement for execution.
 * /* w  w  w .j  av a 2  s.  co m*/
 * @param stmt
 *          The callable statement
 * @param params
 *          The list of parameters
 */
private void setupCall(CallableStatement stmt, List<StatementParameter> params) {
    int paramIndex;
    StatementParameter param;

    paramIndex = 0;

    try {
        for (paramIndex = 0; paramIndex < params.size(); ++paramIndex) {
            param = (StatementParameter) params.get(paramIndex);

            if (param.getType() == StatementParameter.IN) {
                switch (param.getDataType()) {
                case java.sql.Types.VARCHAR:
                    stmt.setString(paramIndex + 1, param.getDataString());
                    break;
                case java.sql.Types.INTEGER:
                    stmt.setInt(paramIndex + 1, Integer.parseInt(param.getDataString()));
                    break;
                default:
                    stmt.setObject(paramIndex + 1, param.getDataString());
                    break;
                }
            } else {
                stmt.registerOutParameter(paramIndex + 1, param.getType());
            }
        }
    } catch (Throwable any) {
        LOGGER.error("Failed to register output parameter at index [" + paramIndex + "]", any);
        messageOut(Resources.getString("errFailRegisterOutParam", paramIndex + "", any.getMessage()));
    }
}

From source file:weave.servlets.AdminService.java

/**
 * @param conn An active SQL connection used to test the query.
 * @param query SQL query which may contain '?' marks for parameters.
 * @param params Optional list of parameters to pass to the SQL query.  May be null.
 * @return The Weave dataType metadata value to use, based on the result of the SQL query.
 *//*from   w ww  .  j a  v a2s . c om*/
private String testQueryAndGetDataType(Connection conn, String query, Object[] params) throws RemoteException {
    CallableStatement cstmt = null;
    Statement stmt = null;
    ResultSet rs = null;
    DataType dataType = null;
    try {
        String dbms = conn.getMetaData().getDatabaseProductName();
        if (!dbms.equalsIgnoreCase(SQLUtils.SQLSERVER) && !dbms.equalsIgnoreCase(SQLUtils.ORACLE))
            query += " LIMIT 1";

        if (params == null || params.length == 0) {
            // We have to use Statement when there are no parameters, because CallableStatement
            // will fail in Microsoft SQL Server with "Incorrect syntax near the keyword 'SELECT'".
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
        } else {
            cstmt = conn.prepareCall(query);
            for (int i = 0; i < params.length; i++)
                cstmt.setObject(i + 1, params[i]);
            rs = cstmt.executeQuery();
        }

        dataType = DataType.fromSQLType(rs.getMetaData().getColumnType(2));
    } catch (SQLException e) {
        throw new RemoteException("Unable to execute generated query:\n" + query, e);
    } finally {
        SQLUtils.cleanup(rs);
        SQLUtils.cleanup(cstmt);
        SQLUtils.cleanup(stmt);
    }

    return dataType.toString();
}

From source file:weave.utils.SQLUtils.java

/**
 * @param conn An existing SQL Connection
 * @param schemaName A schema name accessible through the given connection
 * @param tableName A table name existing in the given schema
 * @param newColumnValues The values to be inserted into that table
 * @throws SQLException If the query fails.
 *//*w  ww. j a  v  a  2  s  . c  o m*/
public static void insertRow(Connection conn, String schemaName, String tableName,
        Map<String, Object> newColumnValues) throws SQLException {
    CallableStatement pstmt = null;
    String query = "";
    int i = 0;
    try {
        // build list of quoted column names, question marks, and array of values in correct order
        Set<Entry<String, Object>> entrySet = newColumnValues.entrySet();
        String columnNames = "";
        String questionMarks = "";
        Object[] values = new Object[entrySet.size()];

        for (Entry<String, Object> entry : entrySet) {
            if (i > 0) {
                columnNames += ",";
                questionMarks += ",";
            }
            columnNames += quoteSymbol(conn, entry.getKey());
            questionMarks += "?";
            values[i] = entry.getValue();

            // constrain oracle double values to float range
            if (isOracleServer(conn) && values[i] instanceof Double)
                values[i] = ((Double) values[i]).floatValue();

            i++;
        }

        query = String.format("INSERT INTO %s (%s) VALUES (%s)", quoteSchemaTable(conn, schemaName, tableName),
                columnNames, questionMarks);

        // prepare call and set string parameters
        pstmt = conn.prepareCall(query);
        for (i = 0; i < values.length; i++)
            pstmt.setObject(i + 1, values[i]);

        pstmt.execute();
    } catch (SQLException e) {
        System.out.println(query);
        System.out.println(newColumnValues);
        throw e;
    } finally {
        SQLUtils.cleanup(pstmt);
    }
}