List of usage examples for java.sql ResultSet getBinaryStream
java.io.InputStream getBinaryStream(String columnLabel) throws SQLException;
ResultSet object as a stream of uninterpreted bytes. From source file:org.kawanfw.test.api.client.InsertAndUpdateBlobTestPsqlOID.java
/** * Test that the blob was were correctly inserted * // w ww.ja v a 2 s .c om * @param connection */ public void selectBlobTest(Connection connection, String originalFileName, String shaHexa) throws Exception { int customer_id; int item_id; String description; BigDecimal cost_price; Date date_placed; Timestamp date_shipped; boolean is_delivered; int quantity; String sql = "select * from orderlog_2 where customer_id >= ? and item_id >= ? "; PreparedStatement prepStatement = connection.prepareStatement(sql); int i = 1; prepStatement.setInt(i++, 1); prepStatement.setInt(i++, 1); ResultSet rs = prepStatement.executeQuery(); MessageDisplayer.display(""); InputStream in = null; OutputStream out = null; SqlUtil sqlUtil = new SqlUtil(connection); while (rs.next()) { File originalBlobFile = SqlTestParms.getFileFromUserHome(originalFileName); // Do the rs.getBinaryStream("jpeg_image") first (INGRES constraint) File file = createTempFile(originalBlobFile.toString()); try { in = rs.getBinaryStream("jpeg_image"); if (in != null) { out = new BufferedOutputStream(new FileOutputStream(file)); IOUtils.copy(in, out); } else { MessageDisplayer.display("jpeg_image column is null!"); } } finally { IOUtils.closeQuietly(in); IOUtils.closeQuietly(out); } i = 1; customer_id = rs.getInt(i++); item_id = rs.getInt(i++); description = rs.getString(i++); cost_price = rs.getBigDecimal(i++); date_placed = rs.getDate(i++); date_shipped = rs.getTimestamp(i++); // pass the image i++; if (sqlUtil.isIngres()) { is_delivered = (rs.getInt(i++) == 1) ? true : false; } else { is_delivered = rs.getBoolean(i++); } quantity = rs.getInt(i++); MessageDisplayer.display(""); MessageDisplayer.display("customer_id : " + customer_id); MessageDisplayer.display("item_id : " + item_id); MessageDisplayer.display("description : " + description); MessageDisplayer.display("cost_price : " + cost_price); MessageDisplayer.display("date_placed : " + date_placed); MessageDisplayer.display("date_shipped: " + date_shipped); MessageDisplayer.display("jpeg_image : " + "content stored in file: " + file); MessageDisplayer.display("is_delivered: " + is_delivered); MessageDisplayer.display("quantity : " + quantity); // Compute the hash of the file Sha1Util sha1 = new Sha1Util(); String shaHexaNew = sha1.getHexFileHash(file); Assert.assertEquals(shaHexa, shaHexaNew); file.delete(); MessageDisplayer.display(""); MessageDisplayer.display("Ok, SHA-1 value of read file " + file + " is same as inserted file " + SqlTestParms.getFileFromUserHome(originalFileName)); } prepStatement.close(); rs.close(); MessageDisplayer.display("Select done!"); }
From source file:com.nabla.wapp.report.server.handler.UpgradeReportHandler.java
@Override protected void update(UpgradeReport record, IUserSessionContext ctx) throws DispatchException, SQLException { final Connection conn = ctx.getWriteConnection(); final PreparedStatement stmt = StatementFormat.prepare(ctx.getReadConnection(), "SELECT * FROM import_data WHERE id=?;", record.getFileId()); try {/*from ww w . j ava2 s. co m*/ final ResultSet rs = stmt.executeQuery(); try { if (!rs.next()) throw new ActionException(CommonServerErrors.NO_DATA); if (!ctx.getSessionId().equals(rs.getString("userSessionId"))) throw new ActionException(CommonServerErrors.ACCESS_DENIED); final ConnectionTransactionGuard guard = new ConnectionTransactionGuard(conn); try { Database.executeUpdate(conn, "DELETE FROM report_resource WHERE report_id=?;", record.getReportId()); Database.executeUpdate(conn, "DELETE FROM report_name_localized WHERE report_id=?;", record.getReportId()); if (ReportZipFile.acceptContentType(rs.getString("content_type"))) { final ReportZipFile zip = new ReportZipFile(rs.getBinaryStream("content")); try { final ZipArchiveEntry design = zip.getReportDesign(); if (design == null) throw new DispatchException(ReportErrors.ADD_REPORT_NO_REPORT_DESIGN_FOUND); reportManager.upgradeReport(conn, record.getReportId(), design.getName(), zip.getInputStream(design)); for (Enumeration<ZipArchiveEntry> iter = zip .getEntries(ReportManager.PROPERTIES_FILE_EXTENSION); iter.hasMoreElements();) { final ZipArchiveEntry e = iter.nextElement(); reportManager.loadLocaleReportName(conn, record.getReportId(), e.getName(), zip.getInputStream(e)); } for (Enumeration<ZipArchiveEntry> iter = zip .getEntries(ReportManager.RESOURCE_FILE_EXTENSIONS); iter.hasMoreElements();) { final ZipArchiveEntry e = iter.nextElement(); reportManager.loadReportResource(conn, record.getReportId(), e.getName(), zip.getInputStream(e)); } } finally { zip.close(); } } else reportManager.upgradeReport(conn, record.getReportId(), FilenameUtils.getBaseName(rs.getString("file_name")), rs.getBinaryStream("content")); guard.setSuccess(); } finally { guard.close(); } } finally { rs.close(); } } finally { stmt.close(); } Database.executeUpdate(conn, "DELETE FROM import_data WHERE id=?;", record.getFileId()); }
From source file:com.demandware.vulnapp.challenge.impl.SQLIChallenge.java
/** * Given a statement, pull up to MAX_RESULTS from the results of the query * @param ps prepared and executed statement (this method will not close this) * @return String containing a formatted output string * @throws SQLException//from www .j a v a2 s . c o m */ private String generateOutputForChallengeQuery(PreparedStatement ps) throws SQLException { StringBuilder sb = new StringBuilder(); ResultSet rs = ps.getResultSet(); int i = 0; while (i < MAX_RESULTS && rs.next()) { try { sb.append("<tr>"); sb.append("<td>"); String name = rs.getString(1); sb.append(name); sb.append("</td>"); sb.append("<td>"); String blurb = rs.getString(2); sb.append(blurb); sb.append("</td>"); String picData = ""; try { Base64InputStream pic = new Base64InputStream(rs.getBinaryStream(3), true); picData = IOUtils.toString(pic); } catch (Exception e) { picData = e.getMessage(); } sb.append("<td>"); sb.append("<img src=\"data:image/jpg;base64,").append(picData).append("\"/>"); sb.append("</td>"); sb.append("</tr>"); } catch (Exception e) { e.printStackTrace(); } } return sb.toString(); }
From source file:gov.nih.nci.cadsr.cadsrpasswordchange.test.TestPasswordReset.java
public void testEncryptionDecryptionWithOracleFunctions() { //TBD - this approach is currently not used, we had issue with "java.sql.SQLException: ORA-28232: invalid input length for obfuscation toolkit //ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT_FFI", line 84 //ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT", line 255 //ORA-06512: at "SBREXT.DECRYPT", line 7" //while invoking the database functions String key = "1234567890123456"; String text = "testtest"; //data has to be in a multiples of 8 bytes String encryptedText = null;// ww w .j av a 2 s . co m byte[] encryptedBytes = null; String decryptedText = null; byte[] decryptedBytes = null; PreparedStatement stmt = null; ResultSet rs = null; ByteArrayInputStream in = null; try { Connection conn = getConnection(ADMIN_ID, ADMIN_PASSWORD); String sql = "select sbrext.encrypt('" + key + "', ?) from dual"; stmt = conn.prepareStatement(sql); stmt.setBytes(1, text.getBytes()); rs = stmt.executeQuery(); while (rs.next()) { // encryptedText = rs.getString(1); in = (ByteArrayInputStream) rs.getBinaryStream(1); encryptedBytes = toBytes(in); } String sql2 = "select sbrext.decrypt('" + key + "', ?) from dual"; stmt = conn.prepareStatement(sql2); stmt.setBytes(1, encryptedBytes); rs = stmt.executeQuery(); while (rs.next()) { // decryptedText = rs.getString(1); in = (ByteArrayInputStream) rs.getBinaryStream(1); decryptedBytes = toBytes(in); } } catch (Exception e) { e.printStackTrace(); } assertEquals(text.getBytes(), decryptedBytes); }
From source file:org.sakaiproject.content.impl.db.test.CheckBlobSafety.java
@Test public void testBlob() { try {// www . java 2 s .c o m Random r = new Random(); int blockSize = 4095; // use an odd size to get byte boundaries int nblocks = 512; int maxSize = blockSize * nblocks; byte[] b = new byte[maxSize]; byte[] bin = new byte[maxSize]; log.info("Loading Random Data " + maxSize); r.nextBytes(b); log.info("Loaded Random Data"); log.info("Got Connection"); PreparedStatement pstout = null; PreparedStatement pstin = null; InputStream instream = null; ResultSet rs = null; try { pstout = con.prepareStatement(p.getProperty("insert.statement")); pstin = con.prepareStatement(p.getProperty("select.statement")); for (int i = 1; i < nblocks; i += 5) { int size = blockSize * i; pstout.clearParameters(); pstout.setBinaryStream(1, new ByteArrayInputStream(b), size); pstout.setInt(2, i); pstout.executeUpdate(); log.info("Loaded record " + i + " of size " + (size) + " bytes"); con.commit(); i++; } for (int i = 1; i < nblocks; i += 5) { int size = blockSize * i; pstin.clearParameters(); pstin.setInt(1, i); rs = pstin.executeQuery(); if (rs.next()) { instream = rs.getBinaryStream(1); DataInputStream din = new DataInputStream(instream); din.readFully(bin, 0, size); for (int j = 0; j < size; j++) { Assert.assertEquals("Byte Missmatch record " + i + " offset " + j, b[j], bin[j]); } log.info("Checked Record " + i + " of size " + size + " bytes"); din.close(); instream.close(); rs.close(); i++; } else { Assert.assertEquals("Didnt get any record at " + i, true, false); } con.commit(); } } finally { try { pstin.close(); } catch (SQLException e) { } try { pstout.close(); } catch (SQLException e) { } try { instream.close(); } catch (Exception ex) { } try { rs.close(); } catch (Exception ex) { } } } catch (Exception ex) { log.error("Failed ", ex); } }
From source file:org.apache.jackrabbit.core.data.db.DbDataStore.java
/** * Open the input stream. This method sets those fields of the caller * that need to be closed once the input stream is read. * * @param inputStream the database input stream object * @param identifier data identifier// ww w . j a v a 2 s . co m * @throws DataStoreException if the data store could not be accessed, * or if the given identifier is invalid */ InputStream openStream(DbInputStream inputStream, DataIdentifier identifier) throws DataStoreException { ResultSet rs = null; try { // SELECT ID, DATA FROM DATASTORE WHERE ID = ? rs = conHelper.query(selectDataSQL, identifier.toString()); if (!rs.next()) { throw new DataStoreException("Record not found: " + identifier); } InputStream stream = rs.getBinaryStream(2); if (stream == null) { stream = new ByteArrayInputStream(new byte[0]); DbUtility.close(rs); } else if (copyWhenReading) { // If we copy while reading, create a temp file and close the stream File temp = moveToTempFile(stream); stream = new BufferedInputStream(new TempFileInputStream(temp)); DbUtility.close(rs); } else { stream = new BufferedInputStream(stream); inputStream.setResultSet(rs); } return stream; } catch (Exception e) { DbUtility.close(rs); throw convert("Retrieving database resource ", e); } }
From source file:org.apache.gora.sql.store.SqlStore.java
protected byte[] getBytes(ResultSet resultSet, int columnIndex, Schema schema, Column column) throws SQLException, IOException { switch (column.getJdbcType()) { case BLOB://from ww w.j av a 2 s. com Blob blob = resultSet.getBlob(columnIndex); return IOUtils.readFully(blob.getBinaryStream()); case BINARY: case VARBINARY: return resultSet.getBytes(columnIndex); case LONGVARBINARY: return IOUtils.readFully(resultSet.getBinaryStream(columnIndex)); } return null; }
From source file:org.accada.epcis.repository.query.QueryOperationsBackendSQL.java
/** * {@inheritDoc}/*from w w w . j av a 2 s . c o m*/ */ public Map<String, QuerySubscriptionScheduled> fetchSubscriptions(final QueryOperationsSession session) throws SQLException, ImplementationExceptionResponse { String query = "SELECT * FROM subscription"; LOG.debug("SQL: " + query); Statement stmt = session.getConnection().createStatement(); QuerySubscriptionScheduled storedSubscription; GregorianCalendar initrectime = new GregorianCalendar(); ResultSet rs = stmt.executeQuery(query); Map<String, QuerySubscriptionScheduled> subscribedMap = new HashMap<String, QuerySubscriptionScheduled>(); while (rs.next()) { try { String subscrId = rs.getString("subscriptionid"); ObjectInput in = new ObjectInputStream(rs.getBinaryStream("params")); QueryParam[] paramArray = (QueryParam[]) in.readObject(); // convert QueryParam[] to QueryParams QueryParams params = new QueryParams(); for (int i = 0; i < paramArray.length; i++) { params.getParam().add(paramArray[i]); } String dest = rs.getString("dest"); in = new ObjectInputStream(rs.getBinaryStream("sched")); Schedule sched = (Schedule) in.readObject(); initrectime.setTime(rs.getTimestamp("initialrecordingtime")); boolean exportifempty = rs.getBoolean("exportifempty"); String queryName = rs.getString("queryname"); String trigger = rs.getString("trigg"); if (trigger == null || trigger.length() == 0) { storedSubscription = new QuerySubscriptionScheduled(subscrId, params, dest, Boolean.valueOf(exportifempty), initrectime, new GregorianCalendar(), sched, queryName); } else { storedSubscription = new QuerySubscriptionTriggered(subscrId, params, dest, Boolean.valueOf(exportifempty), initrectime, new GregorianCalendar(), queryName, trigger, sched); } subscribedMap.put(subscrId, storedSubscription); } catch (SQLException e) { // sql exceptions are passed on throw e; } catch (Exception e) { // all other exceptions are caught String msg = "Unable to restore subscribed queries from the database."; LOG.error(msg, e); ImplementationException iex = new ImplementationException(); iex.setReason(msg); iex.setSeverity(ImplementationExceptionSeverity.ERROR); throw new ImplementationExceptionResponse(msg, iex, e); } } return subscribedMap; }
From source file:com.nabla.dc.server.ImageService.java
private boolean exportImage(final String imageId, final HttpServletResponse response) throws IOException, SQLException, InternalErrorException { final Connection conn = db.getConnection(); try {/*from w ww . ja v a 2 s .c o m*/ final PreparedStatement stmt = StatementFormat.prepare(conn, "SELECT * FROM image WHERE id=?;", imageId); try { final ResultSet rs = stmt.executeQuery(); try { if (!rs.next()) { if (log.isDebugEnabled()) log.debug("failed to find report ID= " + imageId); return false; } if (log.isTraceEnabled()) log.trace("exporting image " + imageId); response.reset(); response.setBufferSize(DEFAULT_BUFFER_SIZE); response.setContentType(rs.getString("content_type")); response.setHeader("Content-Length", String.valueOf(rs.getInt("length"))); response.setHeader("Content-Disposition", MessageFormat.format("inline; filename=\"{0}\"", rs.getString("name"))); // to prevent images to be downloaded every time user hovers one image final Calendar cal = Calendar.getInstance(); cal.setTime(new Date()); cal.add(Calendar.MONTH, 2); response.setDateHeader("Expires", cal.getTime().getTime()); final BufferedInputStream input = new BufferedInputStream(rs.getBinaryStream("content"), DEFAULT_BUFFER_SIZE); try { final BufferedOutputStream output = new BufferedOutputStream(response.getOutputStream(), DEFAULT_BUFFER_SIZE); try { final byte[] buffer = new byte[DEFAULT_BUFFER_SIZE]; int length; while ((length = input.read(buffer)) > 0) output.write(buffer, 0, length); } finally { output.close(); } } finally { input.close(); } } finally { rs.close(); } } finally { stmt.close(); } } finally { conn.close(); } return true; }
From source file:org.fosstrak.epcis.repository.query.QueryOperationsBackendSQL.java
/** * {@inheritDoc}//from w w w . ja v a2s. c o m */ public Map<String, QuerySubscriptionScheduled> fetchSubscriptions(final QueryOperationsSession session) throws SQLException, ImplementationExceptionResponse { String query = "SELECT * FROM subscription"; LOG.debug("SQL: " + query); Statement stmt = session.getConnection().createStatement(); QuerySubscriptionScheduled storedSubscription; GregorianCalendar initrectime = new GregorianCalendar(); ResultSet rs = stmt.executeQuery(query); Map<String, QuerySubscriptionScheduled> subscribedMap = new HashMap<String, QuerySubscriptionScheduled>(); while (rs.next()) { try { String subscrId = rs.getString("subscriptionid"); ObjectInput in = new ObjectInputStream(rs.getBinaryStream("params")); QueryParams params = (QueryParams) in.readObject(); String dest = rs.getString("dest"); in = new ObjectInputStream(rs.getBinaryStream("sched")); Schedule sched = (Schedule) in.readObject(); initrectime.setTime(rs.getTimestamp("initialrecordingtime")); boolean exportifempty = rs.getBoolean("exportifempty"); String queryName = rs.getString("queryname"); String trigger = rs.getString("trigg"); if (trigger == null || trigger.length() == 0) { storedSubscription = new QuerySubscriptionScheduled(subscrId, params, dest, Boolean.valueOf(exportifempty), initrectime, new GregorianCalendar(), sched, queryName); } else { storedSubscription = new QuerySubscriptionTriggered(subscrId, params, dest, Boolean.valueOf(exportifempty), initrectime, new GregorianCalendar(), queryName, trigger, sched); } subscribedMap.put(subscrId, storedSubscription); } catch (SQLException e) { // sql exceptions are passed on throw e; } catch (Exception e) { // all other exceptions are caught String msg = "Unable to restore subscribed queries from the database."; LOG.error(msg, e); ImplementationException iex = new ImplementationException(); iex.setReason(msg); iex.setSeverity(ImplementationExceptionSeverity.ERROR); throw new ImplementationExceptionResponse(msg, iex, e); } } return subscribedMap; }