List of usage examples for java.sql CallableStatement getInt
int getInt(String parameterName) throws SQLException;
INTEGER parameter as an int in the Java programming language. From source file:net.sourceforge.msscodefactory.cfinternet.v2_1.CFInternetSybase.CFInternetSybaseTenantTable.java
public int nextTSecGroupIdGen(CFInternetAuthorization Authorization, CFInternetTenantPKey PKey) { final String S_ProcName = "nextTSecGroupIdGen"; if (!schema.isTransactionOpen()) { throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName, "Not in a transaction"); }//from ww w .j ava2 s . c o m Connection cnx = schema.getCnx(); long Id = PKey.getRequiredId(); CallableStatement stmtSelectNextTSecGroupIdGen = null; try { String sql = "{ call sp_next_tsecgroupidgen( ?" + ", " + "?" + " ) }"; stmtSelectNextTSecGroupIdGen = cnx.prepareCall(sql); int argIdx = 1; stmtSelectNextTSecGroupIdGen.registerOutParameter(argIdx++, java.sql.Types.INTEGER); stmtSelectNextTSecGroupIdGen.setLong(argIdx++, Id); stmtSelectNextTSecGroupIdGen.execute(); int nextId = stmtSelectNextTSecGroupIdGen.getInt(1); return (nextId); } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } finally { if (stmtSelectNextTSecGroupIdGen != null) { try { stmtSelectNextTSecGroupIdGen.close(); } catch (SQLException e) { } stmtSelectNextTSecGroupIdGen = null; } } }
From source file:org.castor.cpa.persistence.sql.keygen.SequenceDuringKeyGenerator.java
/** * {@inheritDoc}//from w w w . j ava2 s . c o m */ public Object executeStatement(final Database database, final CastorConnection conn, final Identity identity, final ProposedEntity entity) throws PersistenceException { CastorStatement stmt = conn.createStatement(); CallableStatement cstmt = null; try { SQLColumnInfo[] ids = _engine.getColumnInfoForIdentities(); stmt.prepareStatement(_insert); String statement = stmt.toString(); statement += " RETURNING "; statement += _factory.quoteName(ids[0].getName()); statement += " INTO ?"; statement = "{call " + statement + "}"; stmt.setStatement(conn.getConnection().prepareCall(statement)); if (LOG.isTraceEnabled()) { LOG.trace(Messages.format("jdo.creating", _engineType, stmt.toString())); } bindFields(entity, stmt); if (LOG.isTraceEnabled()) { LOG.trace(Messages.format("jdo.creating", _engineType, stmt.toString())); } // generate key during INSERT. cstmt = (CallableStatement) stmt.getStatement(); int sqlType = ids[0].getSqlType(); cstmt.registerOutParameter(stmt.getParameterSize() + 1, sqlType); if (LOG.isDebugEnabled()) { LOG.debug(Messages.format("jdo.creating", _engineType, cstmt.toString())); } cstmt.execute(); // first skip all results "for maximum portability" // as proposed in CallableStatement javadocs. while (cstmt.getMoreResults() || (cstmt.getUpdateCount() != -1)) { // no code to execute } // identity is returned in the last parameter. // workaround for INTEGER type in Oracle getObject returns BigDecimal. Object temp; if (sqlType == java.sql.Types.INTEGER) { temp = new Integer(cstmt.getInt(stmt.getParameterSize() + 1)); } else { temp = cstmt.getObject(stmt.getParameterSize() + 1); } return new Identity(ids[0].toJava(temp)); } catch (SQLException except) { LOG.fatal(Messages.format("jdo.storeFatal", _engineType, stmt.toString()), except); throw new PersistenceException(Messages.format("persist.nested", except), except); } finally { //close statement try { if (cstmt != null) { cstmt.close(); } } catch (SQLException e) { LOG.warn("Problem closing JDBC statement", e); } try { stmt.close(); } catch (SQLException e) { LOG.warn("Problem closing JDBC statement", e); } } }
From source file:org.executequery.databasemediators.spi.DefaultStatementExecutor.java
/** <p>Executes the specified procedure. * * @param the SQL procedure to execute * @return the query result/* w w w .j av a 2s. co 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.exist.xquery.modules.oracle.ExecuteFunction.java
@Override public Sequence eval(Sequence[] args, Sequence contextSequence) throws XPathException { if (args.length == 5 || args.length == 6) { // was a connection and PL/SQL statement specified? if (args[0].isEmpty() || args[1].isEmpty()) { return (Sequence.EMPTY_SEQUENCE); }//from w w w .j a va2 s. co m // get the Connection long connectionUID = ((IntegerValue) args[0].itemAt(0)).getLong(); Connection connection = SQLModule.retrieveConnection(context, connectionUID); if (connection == null) { return (Sequence.EMPTY_SEQUENCE); } // get the PL/SQL statement String plSql = args[1].getStringValue(); // get the input parameters (if any) Element parameters = null; if (!args[2].isEmpty()) { parameters = (Element) args[2].itemAt(0); } // was a result set position specified? int resultSetPos = 0; if (!args[3].isEmpty()) { resultSetPos = ((IntegerValue) args[3].itemAt(0)).getInt(); } boolean haveReturnCode = false; int plSqlSuccess = 1; // default value of 1 for success if (args.length == 6) { // a return code is expected so what is the value indicating success? plSqlSuccess = ((IntegerValue) args[5].itemAt(0)).getInt(); haveReturnCode = true; } CallableStatement statement = null; ResultSet resultSet = null; try { MemTreeBuilder builder = context.getDocumentBuilder(); int iRow = 0; statement = connection.prepareCall(plSql); if (haveReturnCode) { statement.registerOutParameter(1, Types.NUMERIC); } if (resultSetPos != 0) { statement.registerOutParameter(resultSetPos, OracleTypes.CURSOR); } if (!args[2].isEmpty()) { setParametersOnPreparedStatement(statement, parameters); } statement.execute(); if (haveReturnCode) { int returnCode = statement.getInt(1); if (returnCode != plSqlSuccess) { LOG.error(plSql + " failed [" + returnCode + "]"); return (Sequence.EMPTY_SEQUENCE); } } if (resultSetPos != 0) { // iterate through the result set building an XML document builder.startDocument(); builder.startElement(new QName("result", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); builder.addAttribute(new QName("count", null, null), String.valueOf(-1)); resultSet = (ResultSet) statement.getObject(resultSetPos); ResultSetMetaData rsmd = resultSet.getMetaData(); int iColumns = rsmd.getColumnCount(); while (resultSet.next()) { builder.startElement(new QName("row", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); builder.addAttribute(new QName("index", null, null), String.valueOf(resultSet.getRow())); // get each tuple in the row for (int i = 0; i < iColumns; i++) { String columnName = rsmd.getColumnLabel(i + 1); if (columnName != null) { String colValue = resultSet.getString(i + 1); String colElement = "field"; if (((BooleanValue) args[4].itemAt(0)).effectiveBooleanValue() && columnName.length() > 0) { // use column names as the XML node /** * Spaces in column names are replaced with * underscore's */ colElement = SQLUtils.escapeXmlAttr(columnName.replace(' ', '_')); } builder.startElement( new QName(colElement, OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); if (!((BooleanValue) args[4].itemAt(0)).effectiveBooleanValue() || columnName.length() <= 0) { String name; if (columnName.length() > 0) { name = SQLUtils.escapeXmlAttr(columnName); } else { name = "Column: " + String.valueOf(i + 1); } builder.addAttribute(new QName("name", null, null), name); } builder.addAttribute( new QName("type", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), rsmd.getColumnTypeName(i + 1)); builder.addAttribute(new QName("type", Namespaces.SCHEMA_NS, "xs"), Type.getTypeName(SQLUtils.sqlTypeToXMLType(rsmd.getColumnType(i + 1)))); if (resultSet.wasNull()) { // Add a null indicator attribute if the value was SQL Null builder.addAttribute( new QName("null", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), "true"); } if (colValue != null) { builder.characters(SQLUtils.escapeXmlText(colValue)); } builder.endElement(); } } builder.endElement(); iRow++; } builder.endElement(); // Change the root element count attribute to have the correct value NodeValue node = (NodeValue) builder.getDocument().getDocumentElement(); Node count = node.getNode().getAttributes().getNamedItem("count"); if (count != null) { count.setNodeValue(String.valueOf(iRow)); } builder.endDocument(); // return the XML result set return (node); } else { // there was no result set so just return an empty sequence return (Sequence.EMPTY_SEQUENCE); } } catch (SQLException sqle) { LOG.error("oracle:execute() Caught SQLException \"" + sqle.getMessage() + "\" for PL/SQL: \"" + plSql + "\"", sqle); //return details about the SQLException MemTreeBuilder builder = context.getDocumentBuilder(); builder.startDocument(); builder.startElement(new QName("exception", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); boolean recoverable = false; if (sqle instanceof SQLRecoverableException) { recoverable = true; } builder.addAttribute(new QName("recoverable", null, null), String.valueOf(recoverable)); builder.startElement(new QName("state", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); String sqlState = sqle.getSQLState(); if (sqlState != null) { builder.characters(sqle.getSQLState()); } else { builder.characters("null"); } builder.endElement(); builder.startElement(new QName("message", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); builder.characters(sqle.getMessage()); builder.endElement(); builder.startElement(new QName("stack-trace", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); ByteArrayOutputStream bufStackTrace = new ByteArrayOutputStream(); sqle.printStackTrace(new PrintStream(bufStackTrace)); builder.characters(new String(bufStackTrace.toByteArray())); builder.endElement(); builder.startElement(new QName("oracle", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); builder.characters(SQLUtils.escapeXmlText(plSql)); builder.endElement(); int line = getLine(); int column = getColumn(); builder.startElement(new QName("xquery", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null); builder.addAttribute(new QName("line", null, null), String.valueOf(line)); builder.addAttribute(new QName("column", null, null), String.valueOf(column)); builder.endElement(); builder.endElement(); builder.endDocument(); return (NodeValue) builder.getDocument().getDocumentElement(); } finally { release(connection, statement, resultSet); } } else { throw new XPathException("Invalid number of arguments [" + args.length + "]"); } }
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 ww . jav a 2 s. co m 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.nuxeo.ecm.core.storage.sql.jdbc.JDBCRowMapper.java
/** * Clean up soft-deleted rows.//ww w . ja v a 2 s. c o m * <p> * Rows deleted more recently than the beforeTime are left alone. Only a * limited number of rows may be deleted, to prevent transaction during too * long. * @param max the maximum number of rows to delete at a time * @param beforeTime the maximum deletion time of the rows to delete * * @return the number of rows deleted * @throws StorageException */ public int cleanupDeletedRows(int max, Calendar beforeTime) throws StorageException { if (max < 0) { max = 0; } String sql = sqlInfo.getSoftDeleteCleanupSql(); if (logger.isLogEnabled()) { logger.logSQL(sql, Arrays.<Serializable>asList(beforeTime, Long.valueOf(max))); } try { if (sql.startsWith("{")) { // callable statement boolean outFirst = sql.startsWith("{?="); int outIndex = outFirst ? 1 : 3; int inIndex = outFirst ? 2 : 1; CallableStatement cs = connection.prepareCall(sql); try { cs.setInt(inIndex, max); dialect.setToPreparedStatementTimestamp(cs, inIndex + 1, beforeTime, null); cs.registerOutParameter(outIndex, Types.INTEGER); cs.execute(); int count = cs.getInt(outIndex); logger.logCount(count); return count; } finally { cs.close(); } } else { // standard prepared statement with result set PreparedStatement ps = connection.prepareStatement(sql); try { ps.setInt(1, max); dialect.setToPreparedStatementTimestamp(ps, 2, beforeTime, null); ResultSet rs = ps.executeQuery(); countExecute(); if (!rs.next()) { throw new StorageException("Cannot get result"); } int count = rs.getInt(1); logger.logCount(count); return count; } finally { closeStatement(ps); } } } catch (Exception e) { checkConnectionReset(e); throw new StorageException("Could not purge soft delete", e); } }
From source file:org.openbmp.db_rest.resources.Orr.java
/** * Run query to get the IGP for the given peer/routerId * * @param peerHashId BGP peer hash id * @param routerId Router ID (IPv4 or IPv6) printed form * @param protocol Either 'ospf' or 'isis' * @param max_age Maximum allowable age for cached IGP SPF * * * @returns Results are returned as a Map list of column definitions * NULL will be returned if there was an error getting the RIB. */// w w w . ja va 2 s. c o m Map<String, List<DbColumnDef>> getIGPRib(String peerHashId, String routerId, String protocol, int max_age) { String tableName = null; String spfProc = null; String route_type_field = ""; if (protocol.equalsIgnoreCase("ospf")) { tableName = "igp_ospf_" + routerId.replace('.', '_'); spfProc = "{call ls_ospf_spf(?, ?, ?,?)}"; route_type_field = "ospf_route_type"; } else if (protocol.equalsIgnoreCase("isis")) { tableName = "igp_isis_" + routerId.replace('.', '_'); spfProc = "{call ls_isis_spf(?, ?, ?,?)}"; route_type_field = "isis_type"; } StringBuilder query = new StringBuilder(); // first call stored procedure to generate the IGP/SPF table. Connection conn = null; CallableStatement cs = null; try { conn = mysql_ds.getConnection(); cs = conn.prepareCall(spfProc); cs.setString(1, peerHashId); cs.setString(2, routerId); cs.setInt(3, max_age); cs.registerOutParameter(4, Types.INTEGER); cs.execute(); System.out.println(" SPF iterations = " + cs.getInt(3)); // WARNING: If changing the order or adding/removing columns to the below, make sure // to update getIgpPrefixMetric() and mergeIgpWithBgp() - they expect column 6 to be the metric query.append("select igp.prefix as prefix,igp.prefix_len,\n"); query.append(" concat('" + protocol + "') as protocol,\n"); query.append(" l.neighbor_addr as NH,\n"); query.append(" concat('') as ORR,\n"); query.append( " " + route_type_field + " as Type,igp.metric,n.igp_router_id as src_router_id,\n"); query.append(" nei.igp_router_id as nei_router_id,\n"); query.append(" igp.path_router_ids,igp.path_hash_ids,\n"); query.append(" l.neighbor_addr as neighbor_addr,\n"); query.append(" igp.peer_hash_id,p.router_hash_id\n"); query.append(" FROM " + tableName + " igp JOIN ls_nodes n ON (igp.src_node_hash_id = n.hash_id)\n"); query.append(" JOIN bgp_peers p ON (igp.peer_hash_id = p.hash_id)\n"); query.append( " JOIN ls_nodes nei ON (igp.nh_node_hash_id = nei.hash_id and nei.peer_hash_id = '" + peerHashId + "')\n"); query.append(" LEFT JOIN ls_links l ON (igp.nh_node_hash_id = l.remote_node_hash_id and\n"); query.append( " igp.root_node_hash_id = l.local_node_hash_id and l.peer_hash_id = '" + peerHashId + "')\n"); query.append(" WHERE best = TRUE "); query.append(" GROUP BY igp.prefix,igp.prefix_len,l.neighbor_addr\n"); System.out.println("QUERY: \n" + query.toString() + "\n"); } catch (SQLException e) { if (e.getSQLState().equals("45000")) { System.out.println("Procedure returned error " + e.getErrorCode() + " : " + e.getMessage()); } else { System.out.println("Error in query " + e.getErrorCode() + " : " + e.getMessage()); e.printStackTrace(); } } finally { try { if (cs != null) cs.close(); if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return DbUtils.select_DbToMap(mysql_ds, query.toString()); }
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 ww w . java2 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()); }/* w w w . ja v a 2 s .c o m*/ } } return elementValue; }
From source file:org.xenei.bloomgraph.bloom.sql.MySQLCommands.java
@Override public void tripleInsert(final Connection connection, final int pageId, final PageSearchItem candidate) throws SQLException, IOException { CallableStatement stmt = null; try {/* w w w .j a va 2 s . c o m*/ final String simpleProc = "{ call add_triple(?,?,?,?,?,?,?,?) }"; stmt = connection.prepareCall(simpleProc); stmt.setInt(1, pageId); stmt.setInt(2, candidate.getTripleFilter().getHammingWeight()); stmt.setDouble(3, candidate.getTripleFilter().getApproximateLog(3)); stmt.setInt(4, candidate.getTriple().hashCode()); stmt.setBlob(5, DBIO.asInputStream(candidate.getTripleFilter().getByteBuffer())); stmt.setBlob(6, DBIO.asInputStream(candidate.getSerializable().getByteBuffer())); stmt.setBlob(7, DBIO.asInputStream(candidate.getPageFilter().getByteBuffer())); stmt.registerOutParameter(8, java.sql.Types.INTEGER); stmt.execute(); candidate.getSerializable().setIndex(stmt.getInt(8)); } finally { DbUtils.closeQuietly(stmt); } }