List of usage examples for java.sql CallableStatement getLong
long getLong(String parameterName) throws SQLException;
BIGINT parameter as a long in the Java programming language. From source file:org.apache.hadoop.metrics2.sink.SqlSink.java
public long getMetricRecordID(String recordTypeContext, String recordTypeName, String nodeName, String sourceIP, String clusterName, String serviceName, String tagPairs, long recordTimestamp) { CallableStatement cstmt = null; long result;//from w ww . j a v a 2 s.com logger.trace("Params: recordTypeContext = " + recordTypeContext + ", recordTypeName = " + recordTypeName + ", nodeName = " + nodeName + ", sourceIP = " + sourceIP + ", tagPairs = " + tagPairs + ", clusterName = " + clusterName + ", serviceName = " + serviceName + ", recordTimestamp = " + recordTimestamp); if (recordTypeContext == null || recordTypeName == null || nodeName == null || sourceIP == null || tagPairs == null) return -1; int colid = 1; try { if (ensureConnection()) { String procedureCall = String.format("{call %s(?, ?, ?, ?, ?, ?, ?, ?, ?)}", getGetMetricsProcedureName()); cstmt = conn.prepareCall(procedureCall); cstmt.setNString(colid++, recordTypeContext); cstmt.setNString(colid++, recordTypeName); cstmt.setNString(colid++, nodeName); cstmt.setNString(colid++, sourceIP); cstmt.setNString(colid++, clusterName); cstmt.setNString(colid++, serviceName); cstmt.setNString(colid++, tagPairs); cstmt.setLong(colid++, recordTimestamp); cstmt.registerOutParameter(colid, java.sql.Types.BIGINT); cstmt.execute(); result = cstmt.getLong(colid); if (cstmt.wasNull()) return -1; return result; } } catch (Exception e) { if (DEBUG) logger.info("Error during getMetricRecordID call sproc: " + e.toString()); flush(); } finally { if (cstmt != null) { try { cstmt.close(); } catch (SQLException se) { if (DEBUG) logger.info("Error during getMetricRecordID close cstmt: " + se.toString()); } /* * We don't close the connection here because we are likely to be * writing * metric values next and it is more efficient to share the connection. */ } } return -1; }
From source file:org.apache.ojb.broker.util.sequence.SequenceManagerStoredProcedureImpl.java
/** * Calls the stored procedure stored procedure throws an * error if it doesn't exist./*from ww w . j av a 2 s . c o m*/ * @param broker * @param cld * @param sequenceName * @return * @throws LookupException * @throws SQLException */ protected long buildNextSequence(PersistenceBroker broker, ClassDescriptor cld, String sequenceName) throws LookupException, SQLException, PlatformException { CallableStatement cs = null; try { Connection con = broker.serviceConnectionManager().getConnection(); cs = getPlatform().prepareNextValProcedureStatement(con, PROCEDURE_NAME, sequenceName); cs.executeUpdate(); return cs.getLong(1); } finally { try { if (cs != null) cs.close(); } catch (SQLException ignore) { // ignore it } } }
From source file:org.executequery.databasemediators.spi.DefaultStatementExecutor.java
/** <p>Executes the specified procedure. * * @param the SQL procedure to execute * @return the query result/*from w w w . j a v a 2 s .c o m*/ */ public SqlStatementResult execute(DatabaseExecutable databaseExecutable) throws SQLException { if (!prepared()) { return statementResult; } ProcedureParameter[] param = databaseExecutable.getParametersArray(); Arrays.sort(param, new ProcedureParameterSorter()); String procQuery = null; boolean hasOut = false; boolean hasParameters = (param != null && param.length > 0); List<ProcedureParameter> outs = null; List<ProcedureParameter> ins = null; if (hasParameters) { // split the params into ins and outs outs = new ArrayList<ProcedureParameter>(); ins = new ArrayList<ProcedureParameter>(); int type = -1; for (int i = 0; i < param.length; i++) { type = param[i].getType(); if (type == DatabaseMetaData.procedureColumnIn || type == DatabaseMetaData.procedureColumnInOut) { // add to the ins list ins.add(param[i]); } else if (type == DatabaseMetaData.procedureColumnOut || type == DatabaseMetaData.procedureColumnResult || type == DatabaseMetaData.procedureColumnReturn || type == DatabaseMetaData.procedureColumnUnknown || type == DatabaseMetaData.procedureColumnInOut) { // add to the outs list outs.add(param[i]); } } char QUESTION_MARK = '?'; String COMMA = ", "; // init the string buffer StringBuilder sb = new StringBuilder("{ "); if (!outs.isEmpty()) { // build the out params place holders for (int i = 0, n = outs.size(); i < n; i++) { sb.append(QUESTION_MARK); if (i < n - 1) { sb.append(COMMA); } } sb.append(" = "); } sb.append(" call "); if (databaseExecutable.supportCatalogOrSchemaInFunctionOrProcedureCalls()) { String namePrefix = null; if (databaseExecutable.supportCatalogInFunctionOrProcedureCalls()) { namePrefix = databaseExecutable.getCatalogName(); } if (databaseExecutable.supportSchemaInFunctionOrProcedureCalls()) { namePrefix = databaseExecutable.getSchemaName(); } if (namePrefix != null) { sb.append(namePrefix).append('.'); } } sb.append(databaseExecutable.getName()).append("( "); // build the ins params place holders for (int i = 0, n = ins.size(); i < n; i++) { sb.append(QUESTION_MARK); if (i < n - 1) { sb.append(COMMA); } } sb.append(" ) }"); // determine if we have out params hasOut = !(outs.isEmpty()); procQuery = sb.toString(); } else { StringBuilder sb = new StringBuilder(); sb.append("{ call "); if (databaseExecutable.getSchemaName() != null) { sb.append(databaseExecutable.getSchemaName()).append('.'); } sb.append(databaseExecutable.getName()).append("( ) }"); procQuery = sb.toString(); } //Log.debug(procQuery); // null value literal String NULL = "null"; // clear any warnings conn.clearWarnings(); Log.info("Executing: " + procQuery); CallableStatement cstmnt = null; try { // prepare the statement cstmnt = conn.prepareCall(procQuery); stmnt = cstmnt; } catch (SQLException e) { handleException(e); statementResult.setSqlException(e); return statementResult; } // check if we are passing parameters if (hasParameters) { // the parameter index counter int index = 1; // the java.sql.Type value int dataType = -1; // the parameter input value String value = null; // register the out params for (int i = 0, n = outs.size(); i < n; i++) { //Log.debug("setting out at index: " + index); cstmnt.registerOutParameter(index, outs.get(i).getDataType()); index++; } try { // register the in params for (int i = 0, n = ins.size(); i < n; i++) { ProcedureParameter procedureParameter = ins.get(i); value = procedureParameter.getValue(); dataType = procedureParameter.getDataType(); // try infer a type if OTHER if (dataType == Types.OTHER) { // checking only for bit/bool for now if (isTrueFalse(value)) { dataType = Types.BOOLEAN; } else if (isBit(value)) { dataType = Types.BIT; value = value.substring(2, value.length() - 1); } } if (MiscUtils.isNull(value) || value.equalsIgnoreCase(NULL)) { cstmnt.setNull(index, dataType); } else { switch (dataType) { case Types.TINYINT: byte _byte = Byte.valueOf(value).byteValue(); cstmnt.setShort(index, _byte); break; case Types.SMALLINT: short _short = Short.valueOf(value).shortValue(); cstmnt.setShort(index, _short); break; case Types.LONGVARCHAR: case Types.CHAR: case Types.VARCHAR: cstmnt.setString(index, value); break; case Types.BIT: case Types.BOOLEAN: boolean _boolean = false; if (NumberUtils.isNumber(value)) { int number = Integer.valueOf(value); if (number > 0) { _boolean = true; } } else { _boolean = Boolean.valueOf(value).booleanValue(); } cstmnt.setBoolean(index, _boolean); break; case Types.BIGINT: long _long = Long.valueOf(value).longValue(); cstmnt.setLong(index, _long); break; case Types.INTEGER: int _int = Integer.valueOf(value).intValue(); cstmnt.setInt(index, _int); break; case Types.REAL: float _float = Float.valueOf(value).floatValue(); cstmnt.setFloat(index, _float); break; case Types.NUMERIC: case Types.DECIMAL: cstmnt.setBigDecimal(index, new BigDecimal(value)); break; /* case Types.DATE: case Types.TIMESTAMP: case Types.TIME: cstmnt.setTimestamp(index, new Timestamp( BigDecimal(value)); */ case Types.FLOAT: case Types.DOUBLE: double _double = Double.valueOf(value).doubleValue(); cstmnt.setDouble(index, _double); break; default: cstmnt.setObject(index, value); } } // increment the index index++; } } catch (Exception e) { statementResult.setOtherErrorMessage(e.getClass().getName() + ": " + e.getMessage()); return statementResult; } } /* test creating function for postgres: CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false) RETURNS text AS $$ SELECT CASE WHEN $3 THEN UPPER($1 || ' ' || $2) ELSE LOWER($1 || ' ' || $2) END; $$ LANGUAGE SQL IMMUTABLE STRICT; */ try { cstmnt.clearWarnings(); boolean hasResultSet = cstmnt.execute(); Map<String, Object> results = new HashMap<String, Object>(); if (hasOut) { // incrementing index int index = 1; // return value from each registered out String returnValue = null; for (int i = 0; i < param.length; i++) { int type = param[i].getType(); int dataType = param[i].getDataType(); if (type == DatabaseMetaData.procedureColumnOut || type == DatabaseMetaData.procedureColumnResult || type == DatabaseMetaData.procedureColumnReturn || type == DatabaseMetaData.procedureColumnUnknown || type == DatabaseMetaData.procedureColumnInOut) { switch (dataType) { case Types.TINYINT: returnValue = Byte.toString(cstmnt.getByte(index)); break; case Types.SMALLINT: returnValue = Short.toString(cstmnt.getShort(index)); break; case Types.LONGVARCHAR: case Types.CHAR: case Types.VARCHAR: returnValue = cstmnt.getString(index); break; case Types.BIT: case Types.BOOLEAN: returnValue = Boolean.toString(cstmnt.getBoolean(index)); break; case Types.INTEGER: returnValue = Integer.toString(cstmnt.getInt(index)); break; case Types.BIGINT: returnValue = Long.toString(cstmnt.getLong(index)); break; case Types.REAL: returnValue = Float.toString(cstmnt.getFloat(index)); break; case Types.NUMERIC: case Types.DECIMAL: returnValue = cstmnt.getBigDecimal(index).toString(); break; case Types.DATE: case Types.TIME: case Types.TIMESTAMP: returnValue = cstmnt.getDate(index).toString(); break; case Types.FLOAT: case Types.DOUBLE: returnValue = Double.toString(cstmnt.getDouble(index)); break; } if (returnValue == null) { returnValue = "NULL"; } results.put(param[i].getName(), returnValue); index++; } } } if (!hasResultSet) { statementResult.setUpdateCount(cstmnt.getUpdateCount()); } else { statementResult.setResultSet(cstmnt.getResultSet()); } useCount++; statementResult.setOtherResult(results); } catch (SQLException e) { statementResult.setSqlException(e); } catch (Exception e) { statementResult.setMessage(e.getMessage()); } return statementResult; }
From source file:org.nuclos.server.dblayer.impl.standard.StandardSqlDBAccess.java
protected <T> T getCallableResultValue(CallableStatement stmt, int index, Class<T> javaType) throws SQLException { Object value;/*from w w w . jav a2 s .com*/ if (javaType == String.class) { value = stmt.getString(index); } else if (javaType == NuclosPassword.class) { value = new NuclosPassword(ServerCryptUtil.decrypt(stmt.getString(index))); } else if (javaType == Double.class) { value = stmt.getDouble(index); } else if (javaType == Long.class) { value = stmt.getLong(index); } else if (javaType == Integer.class) { value = stmt.getInt(index); } else if (javaType == Boolean.class) { value = stmt.getBoolean(index); } else if (javaType == BigDecimal.class) { value = stmt.getBigDecimal(index); } else if (javaType == java.util.Date.class) { value = stmt.getDate(index); } else if (javaType == byte[].class) { value = stmt.getBytes(index); } else if (javaType == NuclosScript.class) { final XStreamSupport xs = XStreamSupport.getInstance(); final XStream xstream = xs.getXStream(); try { value = xstream.fromXML(stmt.getString(index)); } finally { xs.returnXStream(xstream); } } else { throw new IllegalArgumentException("Class " + javaType + " not supported by readField"); } return stmt.wasNull() ? null : javaType.cast(value); }
From source file:org.plasma.sdo.jdbc.service.StoredProcSequenceGenerator.java
private Long getSeqNum(String seqName) { CallableStatement cstmt1 = null; try {/*from w ww.jav a 2s .co m*/ if (conn == null) initialize(); cstmt1 = (CallableStatement) conn.prepareCall("{ call GET_SQNC_NMBR (?, ?) }"); cstmt1.registerOutParameter(2, Types.NUMERIC); cstmt1.setString(1, seqName); cstmt1.execute(); long id = cstmt1.getLong(2); return new Long(id); } catch (Throwable t) { throw new RuntimeException(t); } finally { if (cstmt1 != null) try { cstmt1.close(); } catch (Throwable t2) { } } }
From source file:org.wso2.carbon.dataservices.core.description.query.SQLQuery.java
private ParamValue getOutparameterValue(CallableStatement cs, String type, int ordinal) throws DataServiceFault { try {//from w w w .j a va 2 s . c o m Object elementValue; if (type.equals(DBConstants.DataTypes.STRING)) { elementValue = cs.getString(ordinal); return new ParamValue(elementValue == null ? null : elementValue.toString()); } else if (type.equals(DBConstants.DataTypes.DOUBLE)) { elementValue = cs.getDouble(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Double) elementValue)); } else if (type.equals(DBConstants.DataTypes.BIGINT)) { elementValue = cs.getLong(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Long) elementValue)); } else if (type.equals(DBConstants.DataTypes.INTEGER)) { elementValue = cs.getInt(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Integer) elementValue)); } else if (type.equals(DBConstants.DataTypes.TIME)) { elementValue = cs.getTime(ordinal); return new ParamValue(elementValue == null ? null : this.convertToTimeString((Time) elementValue)); } else if (type.equals(DBConstants.DataTypes.DATE)) { elementValue = cs.getDate(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Date) elementValue)); } else if (type.equals(DBConstants.DataTypes.TIMESTAMP)) { elementValue = cs.getTimestamp(ordinal, calendar); return new ParamValue( elementValue == null ? null : this.convertToTimestampString((Timestamp) elementValue)); } else if (type.equals(DBConstants.DataTypes.BLOB)) { elementValue = cs.getBlob(ordinal); return new ParamValue(elementValue == null ? null : this.getBase64StringFromInputStream(((Blob) elementValue).getBinaryStream())); } else if (type.equals(DBConstants.DataTypes.CLOB)) { elementValue = cs.getClob(ordinal); return new ParamValue(elementValue == null ? null : deriveValueFromClob((Clob) elementValue)); } else if (type.equals(DBConstants.DataTypes.STRUCT)) { elementValue = cs.getObject(ordinal); return new ParamValue(elementValue == null ? null : (Struct) elementValue); } else if (type.equals(DBConstants.DataTypes.ARRAY)) { Array dataArray = cs.getArray(ordinal); ParamValue paramValue = new ParamValue(ParamValue.PARAM_VALUE_ARRAY); if (dataArray != null) { this.processSQLArray(dataArray, paramValue); } return paramValue; } else if (type.equals(DBConstants.DataTypes.NUMERIC)) { elementValue = cs.getBigDecimal(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((BigDecimal) elementValue)); } else if (type.equals(DBConstants.DataTypes.BIT)) { elementValue = cs.getBoolean(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Boolean) elementValue)); } else if (type.equals(DBConstants.DataTypes.TINYINT)) { elementValue = cs.getByte(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Byte) elementValue)); } else if (type.equals(DBConstants.DataTypes.SMALLINT)) { elementValue = cs.getShort(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Short) elementValue)); } else if (type.equals(DBConstants.DataTypes.REAL)) { elementValue = cs.getFloat(ordinal); return new ParamValue( elementValue == null ? null : ConverterUtil.convertToString((Float) elementValue)); } else if (type.equals(DBConstants.DataTypes.BINARY)) { elementValue = cs.getBlob(ordinal); return new ParamValue(elementValue == null ? null : this.getBase64StringFromInputStream(((Blob) elementValue).getBinaryStream())); } else { throw new DataServiceFault("Unsupported data type: " + type); } } catch (SQLException e) { throw new DataServiceFault(e, "Error in getting sql output parameter values."); } }
From source file:org.wso2.ws.dataservice.DBUtils.java
private static String setOutparameterValue(CallableStatement cs, Query query, String resultSetFieldName) throws SQLException, AxisFault { // This could be an out parameter //Procedure returns both result & out parameters String elementValue = ""; Param param = query.getParam(resultSetFieldName); if (param != null) { if ("OUT".equals(param.getType()) || "INOUT".equals(param.getType())) { if (param.getSqlType().equals(DBConstants.DataTypes.STRING)) { elementValue = cs.getString(param.getOrdinal()); } else if (param.getSqlType().equals(DBConstants.DataTypes.DOUBLE)) { elementValue = String.valueOf(cs.getDouble(param.getOrdinal())); } else if (param.getSqlType().equals(DBConstants.DataTypes.BIGINT)) { elementValue = String.valueOf(cs.getLong(param.getOrdinal())); } else if (param.getSqlType().equals(DBConstants.DataTypes.INTEGER)) { elementValue = String.valueOf(cs.getInt(param.getOrdinal())); } else if (param.getSqlType().equals(DBConstants.DataTypes.TIME)) { elementValue = String.valueOf(cs.getTime(param.getOrdinal())); } else if (param.getSqlType().equals(DBConstants.DataTypes.DATE)) { elementValue = String.valueOf(cs.getDate(param.getOrdinal())); } else if (param.getSqlType().equals(DBConstants.DataTypes.TIMESTAMP)) { elementValue = String.valueOf(cs.getTimestamp(param.getOrdinal())); } else { log.error("Unsupported data type : " + param.getSqlType()); throw new AxisFault("Unsupported data type : " + param.getSqlType()); }/*from w w w . ja v a 2 s. c o m*/ } } return elementValue; }