Example usage for java.sql PreparedStatement setBinaryStream

List of usage examples for java.sql PreparedStatement setBinaryStream

Introduction

In this page you can find the example usage for java.sql PreparedStatement setBinaryStream.

Prototype

void setBinaryStream(int parameterIndex, java.io.InputStream x, long length) throws SQLException;

Source Link

Document

Sets the designated parameter to the given input stream, which will have the specified number of bytes.

Usage

From source file:org.alinous.plugin.derby.DerbyDataSource.java

public void storeBinary(Object connectionHandle, InputStream stream, int length, String table,
        String blobColumn, WhereClause where, PostContext context, VariableRepository provider)
        throws ExecutionException {
    UpdateSentence sentence = new UpdateSentence();
    sentence.setTable(new TableIdentifier(table));
    TypeHelper helper = this.typeHelper.newHelper(false, sentence);

    StringBuffer buff = new StringBuffer();

    buff.append("UPDATE ");
    buff.append(table);/*from  www. j a v a  2 s. c om*/
    buff.append(" SET ");
    buff.append(blobColumn);
    buff.append("=?");

    if (where != null && where.isReady(context, provider, null)) {
        buff.append(" ");
        buff.append(where.extract(context, provider, null, null, helper));
    }

    Connection con = (Connection) connectionHandle;
    boolean lastAutoCommit = false;
    try {
        lastAutoCommit = con.getAutoCommit();
        if (lastAutoCommit == true) {
            con.setAutoCommit(false);
        }

        PreparedStatement statement = con.prepareStatement(buff.toString());
        statement.setBinaryStream(1, stream, length);
        statement.executeUpdate();

        //con.commit();
    } catch (SQLException e) {
        throw new ExecutionException(e, "Failed in storing blob"); // i18n
    } finally {
        try {
            if (lastAutoCommit == true) {
                con.setAutoCommit(lastAutoCommit);
            }
        } catch (SQLException e) {
        }
    }
    // UPDATE table SET blobColumn = ? WHERE...

}

From source file:org.kawanfw.sql.servlet.sql.ServerCallableStatementParameters.java

/**
 * Set the binary stream using the underlying Blob file uploaded by the
 * client side/* w ww .j  av a  2s  . co  m*/
 * 
 * @param preparedStatement
 *            The Prepared Statement to execute
 * @param parameterIndex
 *            the parameter index
 * @param paramValue
 *            the parameter value (the file name)
 * @throws SQLException
 */
private void setBinaryStream(PreparedStatement preparedStatement, int parameterIndex, String paramValue)
        throws SQLException, IOException {
    // Extract the Blob file from the parameter

    debug("before getFileFromParameter()");
    File blobFile = getFileFromParameter(paramValue);
    blobsOrClobs.add(blobFile);

    debug("before new BufferedInputStream(new FileInputStream(blobFile))");

    // Then update the prepared statement binary stream and we are done!
    InputStream in = new BufferedInputStream(new FileInputStream(blobFile));
    long theLength = blobFile.length();

    debug("before preparedStatement.setBinaryStream()");

    // We cast theLength, because the long version may not be implemented by
    // the driver
    preparedStatement.setBinaryStream(parameterIndex, in, (int) theLength);
    this.inList.add(in);

    debug("after preparedStatement.setBinaryStream()");

}

From source file:com.amazon.carbonado.repo.jdbc.OracleSupportStrategy.java

/**
 * @return original blob if too large and post-insert update is required, null otherwise
 * @throws PersistException instead of FetchException since this code is
 * called during an insert operation/*from www  . j a va  2 s. c  om*/
 */
@Override
com.amazon.carbonado.lob.Blob setBlobValue(PreparedStatement ps, int column, com.amazon.carbonado.lob.Blob blob)
        throws PersistException {
    try {
        long length = blob.getLength();
        if (length > BLOB_CHUNK_LIMIT || ((long) ((int) length)) != length) {
            if (mBLOB_empty_lob == null) {
                return super.setBlobValue(ps, column, blob);
            }

            try {
                ps.setBlob(column, (java.sql.Blob) mBLOB_empty_lob.invoke(null));
                return blob;
            } catch (InvocationTargetException e) {
                throw mRepo.toPersistException(e.getCause());
            } catch (Exception e) {
                throw mRepo.toPersistException(e);
            }
        }

        if (blob instanceof OracleBlob) {
            ps.setBlob(column, ((OracleBlob) blob).getInternalBlobForPersist());
            return null;
        }

        ps.setBinaryStream(column, blob.openInputStream(), (int) length);
        return null;
    } catch (SQLException e) {
        throw mRepo.toPersistException(e);
    } catch (FetchException e) {
        throw e.toPersistException();
    }
}

From source file:org.wso2.carbon.user.core.tenant.JDBCTenantManager.java

public void updateTenantRealmConfig(org.wso2.carbon.user.api.Tenant tenant) throws UserStoreException {

    Connection dbConnection = null;
    PreparedStatement prepStmt = null;
    try {//from   www.  j  a va  2  s  .c  o  m
        dbConnection = getDBConnection();
        String sqlStmt;
        String realmConfigString = null;

        if (tenant.getRealmConfig() != null) {
            realmConfigString = RealmConfigXMLProcessor.serialize(tenant.getRealmConfig()).toString();
            if (realmConfigString != null && realmConfigString.trim().length() > 0) {
                sqlStmt = TenantConstants.UPDATE_TENANT_CONFIG_SQL;
                prepStmt = dbConnection.prepareStatement(sqlStmt);
                InputStream is = null;
                try {
                    is = new ByteArrayInputStream(realmConfigString.getBytes());
                    prepStmt.setBinaryStream(1, is, is.available());
                    prepStmt.setInt(2, tenant.getId());
                    prepStmt.executeUpdate();
                    dbConnection.commit();
                    tenantCacheManager.clearCacheEntry(new TenantIdKey(tenant.getId()));
                    RealmCache.getInstance().clearFromCache(tenant.getId(), "primary");
                } catch (IOException e) {
                    log.error("Error occurs while reading realm configuration", e);
                } finally {
                    if (is != null) {
                        try {
                            is.close();
                        } catch (IOException e) {
                            log.error(e);
                        }
                    }
                }
            }
        }

    } catch (SQLException e) {

        DatabaseUtil.rollBack(dbConnection);

        String msg = "Error in updating tenant realm configuration with " + "tenant domain: "
                + tenant.getDomain().toLowerCase() + ".";
        if (log.isDebugEnabled()) {
            log.debug(msg, e);
        }
        throw new UserStoreException(msg, e);
    } finally {
        DatabaseUtil.closeAllConnections(dbConnection, prepStmt);
    }
}

From source file:org.castor.jdo.engine.SQLTypeInfos.java

/**
 * Set given value on given PreparedStatement at given index with given SQL type.
 * //from  w w  w . j  av  a  2  s  .  c om
 * @param stmt The PreparedStatement to set value on.
 * @param index The index of the value in the PreparedStatement.
 * @param value The value to set.
 * @param sqlType The SQL type of the value.
 */
public static void setValue(final PreparedStatement stmt, final int index, final Object value,
        final int sqlType) {
    try {
        if (value == null) {
            stmt.setNull(index, sqlType);
        } else {
            // Special processing for BLOB and CLOB types, because they are mapped
            // by Castor to java.io.InputStream and java.io.Reader, respectively,
            // while JDBC driver expects java.sql.Blob and java.sql.Clob.
            switch (sqlType) {
            case Types.FLOAT:
            case Types.DOUBLE:
                stmt.setDouble(index, ((Double) value).doubleValue());
                break;
            case Types.REAL:
                stmt.setFloat(index, ((Float) value).floatValue());
                break;
            case Types.TIME:
                final Time time = value instanceof java.util.Date ? new Time(((java.util.Date) value).getTime())
                        : null;
                stmt.setTime(index, time != null ? time : (Time) value, getCalendar());
                break;
            case Types.DATE:
                final Date date = value instanceof java.util.Date ? new Date(((java.util.Date) value).getTime())
                        : null;
                stmt.setDate(index, date != null ? date : (Date) value);
                break;
            case Types.TIMESTAMP:
                final Timestamp timestamp = value instanceof java.util.Date
                        ? new Timestamp(((java.util.Date) value).getTime())
                        : null;
                stmt.setTimestamp(index, timestamp != null ? timestamp : (Timestamp) value, getCalendar());
                break;
            case Types.BLOB:
                try {
                    InputStream stream;
                    if (value instanceof byte[]) {
                        stream = new ByteArrayInputStream((byte[]) value);
                    } else {
                        stream = (InputStream) value;
                    }
                    stmt.setBinaryStream(index, stream, stream.available());
                } catch (IOException ex) {
                    throw new SQLException(ex.toString());
                }
                break;
            case Types.CLOB:
                if (value instanceof String) {
                    stmt.setCharacterStream(index, new StringReader((String) value),
                            Math.min(((String) value).length(), Integer.MAX_VALUE));
                } else {
                    stmt.setCharacterStream(index, ((Clob) value).getCharacterStream(),
                            (int) Math.min(((Clob) value).length(), Integer.MAX_VALUE));
                }
                break;
            default:
                stmt.setObject(index, value, sqlType);
                break;
            }
        }
    } catch (SQLException ex) {
        LOG.error("Unexpected SQL exception: ", ex);
    }
}

From source file:org.apache.jackrabbit.core.persistence.db.DatabasePersistenceManager.java

/**
 * Executes the given SQL statement with the specified parameters.
 * If a <code>SQLException</code> is encountered and
 * <code>autoReconnect==true</code> <i>one</i> attempt is made to re-establish
 * the database connection and re-execute the statement.
 *
 * @param sql    statement to execute/*from w ww .  j  a va 2s  .  c o m*/
 * @param params parameters to set
 * @return the <code>Statement</code> object that had been executed
 * @throws SQLException if an error occurs
 */
protected Statement executeStmt(String sql, Object[] params) throws SQLException {
    int trials = autoReconnect ? 2 : 1;
    while (true) {
        PreparedStatement stmt = (PreparedStatement) preparedStatements.get(sql);
        try {
            for (int i = 0; i < params.length; i++) {
                if (params[i] instanceof SizedInputStream) {
                    SizedInputStream in = (SizedInputStream) params[i];
                    stmt.setBinaryStream(i + 1, in, (int) in.getSize());
                } else {
                    stmt.setObject(i + 1, params[i]);
                }
            }
            stmt.execute();
            resetStatement(stmt);
            return stmt;
        } catch (SQLException se) {
            if (--trials == 0) {
                // no more trials, re-throw
                throw se;
            }
            log.warn("execute failed, about to reconnect... {}", se.getMessage());

            // try to reconnect
            if (reestablishConnection()) {
                // reconnect succeeded; check whether it's possible to
                // re-execute the prepared stmt with the given parameters
                for (int i = 0; i < params.length; i++) {
                    if (params[i] instanceof SizedInputStream) {
                        SizedInputStream in = (SizedInputStream) params[i];
                        if (in.isConsumed()) {
                            // we're unable to re-execute the prepared stmt
                            // since an InputStream paramater has already
                            // been 'consumed';
                            // re-throw previous SQLException
                            throw se;
                        }
                    }
                }

                // try again to execute the statement
                continue;
            } else {
                // reconnect failed, re-throw previous SQLException
                throw se;
            }
        }
    }
}

From source file:org.accada.epcis.repository.query.QueryOperationsBackendSQL.java

/**
 * {@inheritDoc}//w ww .  j a  v a2  s .  c o  m
 */
public void storeSupscriptions(final QueryOperationsSession session, QueryParams queryParams, String dest,
        String subscrId, SubscriptionControls controls, String trigger,
        QuerySubscriptionScheduled newSubscription, String queryName, Schedule schedule)
        throws SQLException, ImplementationExceptionResponse {
    String insert = "INSERT INTO subscription (subscriptionid, "
            + "params, dest, sched, trigg, initialrecordingtime, "
            + "exportifempty, queryname, lastexecuted) VALUES "
            + "((?), (?), (?), (?), (?), (?), (?), (?), (?))";
    PreparedStatement stmt = session.getConnection().prepareStatement(insert);
    LOG.debug("QUERY: " + insert);
    try {
        stmt.setString(1, subscrId);
        LOG.debug("       query param 1: " + subscrId);

        ByteArrayOutputStream outStream = new ByteArrayOutputStream();
        ObjectOutput out = new ObjectOutputStream(outStream);
        out.writeObject(queryParams);
        ByteArrayInputStream inStream = new ByteArrayInputStream(outStream.toByteArray());
        stmt.setBinaryStream(2, inStream, inStream.available());
        LOG.debug("       query param 2: [" + inStream.available() + " bytes]");

        stmt.setString(3, dest.toString());
        LOG.debug("       query param 3: " + dest);

        outStream = new ByteArrayOutputStream();
        out = new ObjectOutputStream(outStream);
        out.writeObject(schedule);
        inStream = new ByteArrayInputStream(outStream.toByteArray());
        stmt.setBinaryStream(4, inStream, inStream.available());
        LOG.debug("       query param 4: [" + inStream.available() + " bytes]");

        stmt.setString(5, trigger);
        LOG.debug("       query param 5: " + trigger);

        Calendar cal = newSubscription.getInitialRecordTime();
        Timestamp ts = new Timestamp(cal.getTimeInMillis());
        String time = ts.toString();
        stmt.setString(6, time);
        LOG.debug("       query param 6: " + time);

        stmt.setBoolean(7, controls.isReportIfEmpty());
        LOG.debug("       query param 7: " + controls.isReportIfEmpty());

        stmt.setString(8, queryName);
        LOG.debug("       query param 8: " + queryName);

        stmt.setString(9, time);
        LOG.debug("       query param 9: " + time);

        stmt.executeUpdate();
    } catch (IOException e) {
        String msg = "Unable to store the subscription to the database: " + e.getMessage();
        LOG.error(msg);
        ImplementationException iex = new ImplementationException();
        iex.setReason(msg);
        iex.setSeverity(ImplementationExceptionSeverity.ERROR);
        throw new ImplementationExceptionResponse(msg, iex, e);
    }
}

From source file:com.flexive.core.storage.genericSQL.GenericBinarySQLStorage.java

/**
 * Set insert parameters for a preview image
 *
 * @param ps                the prepared statement to use
 * @param previewFile       the preview file
 * @param dimensionsPreview dimensions (width, height)
 * @param positionBinary    position in the prepared statement
 * @param positionSize      position of the file size parameter in the prepared statement
 * @return FileInputStream/*from  w ww.j  a  v a  2 s  .c  o  m*/
 * @throws FileNotFoundException if the file does not exist
 * @throws SQLException          on errors
 */
private FileInputStream setPreviewTransferParameters(PreparedStatement ps, File previewFile,
        int[] dimensionsPreview, int positionBinary, int positionSize)
        throws FileNotFoundException, SQLException {
    FileInputStream pin = null;
    if (previewFile != null && previewFile.exists()) {
        pin = new FileInputStream(previewFile);
        ps.setBinaryStream(positionBinary, pin, (int) previewFile.length());
        ps.setInt(positionBinary + 1, dimensionsPreview[0]);
        ps.setInt(positionBinary + 2, dimensionsPreview[1]);
        ps.setInt(positionSize, (int) previewFile.length());
    } else {
        ps.setNull(positionBinary, Types.BINARY);
        ps.setInt(positionBinary + 1, 0);
        ps.setInt(positionBinary + 2, 0);
        ps.setInt(positionSize, 0);
    }
    return pin;
}

From source file:org.fosstrak.epcis.repository.query.QueryOperationsBackendSQL.java

/**
 * {@inheritDoc}//from  w  ww .  j av a  2 s . com
 */
public void storeSupscriptions(final QueryOperationsSession session, QueryParams queryParams, String dest,
        String subscrId, SubscriptionControls controls, String trigger,
        QuerySubscriptionScheduled newSubscription, String queryName, Schedule schedule)
        throws SQLException, ImplementationExceptionResponse {
    String insert = "INSERT INTO subscription (subscriptionid, "
            + "params, dest, sched, trigg, initialrecordingtime, "
            + "exportifempty, queryname, lastexecuted) VALUES "
            + "((?), (?), (?), (?), (?), (?), (?), (?), (?))";
    PreparedStatement stmt = session.getConnection().prepareStatement(insert);
    LOG.debug("QUERY: " + insert);
    try {
        stmt.setString(1, subscrId);
        LOG.debug("       query param 1: " + subscrId);

        ByteArrayOutputStream outStream = new ByteArrayOutputStream();
        ObjectOutput out = new ObjectOutputStream(outStream);
        out.writeObject(queryParams);
        ByteArrayInputStream inStream = new ByteArrayInputStream(outStream.toByteArray());
        stmt.setBinaryStream(2, inStream, inStream.available());
        LOG.debug("       query param 2: [" + inStream.available() + " bytes]");

        stmt.setString(3, dest.toString());
        LOG.debug("       query param 3: " + dest);

        outStream = new ByteArrayOutputStream();
        out = new ObjectOutputStream(outStream);
        out.writeObject(schedule);
        inStream = new ByteArrayInputStream(outStream.toByteArray());
        stmt.setBinaryStream(4, inStream, inStream.available());
        LOG.debug("       query param 4: [" + inStream.available() + " bytes]");

        stmt.setString(5, trigger);
        LOG.debug("       query param 5: " + trigger);

        Calendar cal = newSubscription.getInitialRecordTime();
        Timestamp ts = new Timestamp(cal.getTimeInMillis());
        String time = ts.toString();
        stmt.setString(6, time);
        LOG.debug("       query param 6: " + time);

        stmt.setBoolean(7, controls.isReportIfEmpty());
        LOG.debug("       query param 7: " + controls.isReportIfEmpty());

        stmt.setString(8, queryName);
        LOG.debug("       query param 8: " + queryName);

        stmt.setString(9, time);
        LOG.debug("       query param 9: " + time);

        stmt.executeUpdate();
        session.commit();
    } catch (IOException e) {
        String msg = "Unable to store the subscription to the database: " + e.getMessage();
        LOG.error(msg);
        ImplementationException iex = new ImplementationException();
        iex.setReason(msg);
        iex.setSeverity(ImplementationExceptionSeverity.ERROR);
        throw new ImplementationExceptionResponse(msg, iex, e);
    }
}

From source file:org.rhq.enterprise.server.core.plugin.AgentPluginScanner.java

/**
 * This will write the contents of the given plugin file to the database.
 * This will store both the contents and the MD5 in an atomic transaction
 * so they remain insync.//ww  w.j a  v  a  2  s .  c  o  m
 *
 * When <code>different</code> is <code>false</code>, it means the original
 * plugin and the one currently found on the file system are the same.
 *
 * When <code>different</code> is <code>true</code>, it means the plugin
 * is most likely a different one than the one that originally existed.
 * When this happens, it is assumed that the {@link ProductPluginDeployer} needs
 * to see the plugin on the file system as new and needing to be processed, therefore
 * the MD5, CONTENT and MTIME columns will be updated to ensure the deployer
 * will process this plugin and thus update all the metadata for this plugin.
 *
 * @param name the name of the plugin whose content is being updated
 * @param file the plugin file whose content will be streamed to the database
 * @param different this will be <code>true</code> if the given file has a different filename
 *                  that the plugin's "path" as found in the database.
 *
 *
 * @throws Exception
 */
private void streamPluginFileContentToDatabase(String name, File file, boolean different) throws Exception {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    TransactionManager tm = null;

    String sql = "UPDATE " + Plugin.TABLE_NAME
            + " SET CONTENT = ?, MD5 = ?, MTIME = ?, PATH = ? WHERE DEPLOYMENT = 'AGENT' AND NAME = ?";

    // if 'different' is true, give bogus data so the plugin deployer will think the plugin on the file system is new
    String md5 = (!different) ? MessageDigestGenerator.getDigestString(file) : "TO BE UPDATED";
    long mtime = (!different) ? file.lastModified() : 0L;
    InputStream fis = (!different) ? new FileInputStream(file) : new ByteArrayInputStream(new byte[0]);
    int contentSize = (int) ((!different) ? file.length() : 0);

    try {
        tm = LookupUtil.getTransactionManager();
        tm.begin();
        DataSource ds = LookupUtil.getDataSource();
        conn = ds.getConnection();
        ps = conn.prepareStatement(sql);
        ps.setBinaryStream(1, new BufferedInputStream(fis), contentSize);
        ps.setString(2, md5);
        ps.setLong(3, mtime);
        ps.setString(4, file.getName());
        ps.setString(5, name);
        int updateResults = ps.executeUpdate();
        if (updateResults == 1) {
            log.info("Stored content for plugin [" + name + "] in the db. file=" + file);
        } else {
            throw new Exception("Failed to update content for plugin [" + name + "] from [" + file + "]");
        }
    } catch (Exception e) {
        tm.rollback();
        tm = null;
        throw e;
    } finally {
        JDBCUtil.safeClose(conn, ps, rs);

        try {
            fis.close();
        } catch (Throwable t) {
        }

        if (tm != null) {
            tm.commit();
        }
    }
    return;
}