001: /**
002: *
003: * Derby - Class BLOBTest
004: *
005: * Licensed to the Apache Software Foundation (ASF) under one or more
006: * contributor license agreements. See the NOTICE file distributed with
007: * this work for additional information regarding copyright ownership.
008: * The ASF licenses this file to You under the Apache License, Version 2.0
009: * (the "License"); you may not use this file except in compliance with
010: * the License. You may obtain a copy of the License at
011: *
012: * http://www.apache.org/licenses/LICENSE-2.0
013: *
014: * Unless required by applicable law or agreed to in writing,
015: * software distributed under the License is distributed on an
016: * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND,
017: * either express or implied. See the License for the specific
018: * language governing permissions and limitations under the License.
019: */package org.apache.derbyTesting.functionTests.tests.jdbcapi;
020:
021: import org.apache.derbyTesting.functionTests.util.TestInputStream;
022: import org.apache.derbyTesting.junit.BaseJDBCTestCase;
023:
024: import junit.framework.Test;
025: import junit.framework.TestSuite;
026: import java.sql.PreparedStatement;
027: import java.sql.ResultSet;
028: import java.sql.Statement;
029: import java.sql.Blob;
030: import java.sql.Connection;
031: import java.sql.SQLException;
032: import java.io.IOException;
033: import java.io.InputStream;
034:
035: /**
036: * Tests reading and updating binary large objects (BLOBs).
037: * @author Andreas Korneliussen
038: */
039: final public class BLOBTest extends BaseJDBCTestCase {
040: /**
041: * Constructor
042: * @param name name of test case (method).
043: */
044: public BLOBTest(String name) {
045: super (name);
046: }
047:
048: /**
049: * Tests updating a Blob from a scollable resultset, using
050: * result set update methods.
051: * @exception SQLException causes test to fail with error
052: * @exception IOException causes test to fail with error
053: */
054: public void testUpdateBlobFromScrollableResultSetUsingResultSetMethods()
055: throws SQLException, IOException {
056: final Statement stmt = createStatement(
057: ResultSet.TYPE_SCROLL_INSENSITIVE,
058: ResultSet.CONCUR_UPDATABLE);
059: final ResultSet rs = stmt.executeQuery("SELECT * from "
060: + BLOBDataModelSetup.getBlobTableName());
061: println("Last");
062: rs.last();
063:
064: final int newVal = rs.getInt(1) + 11;
065: final int newSize = rs.getInt(2) / 2;
066: testUpdateBlobWithResultSetMethods(rs, newVal, newSize);
067:
068: println("Verify updated blob using result set");
069: verifyBlob(newVal, newSize, rs.getBlob(3));
070:
071: rs.close();
072: stmt.close();
073: }
074:
075: /**
076: * Tests updating a Blob from a forward only resultset, using
077: * result set update methods.
078: * @exception SQLException causes test to fail with error
079: * @exception IOException causes test to fail with error
080: */
081: public void testUpdateBlobFromForwardOnlyResultSetUsingResultSetMethods()
082: throws SQLException, IOException {
083: final Statement stmt = createStatement(
084: ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
085: final ResultSet rs = stmt.executeQuery("SELECT * from "
086: + BLOBDataModelSetup.getBlobTableName());
087:
088: while (rs.next()) {
089: println("Next");
090: final int val = rs.getInt(1);
091: if (val == BLOBDataModelSetup.bigVal)
092: break;
093: }
094:
095: final int newVal = rs.getInt(1) + 11;
096: final int newSize = rs.getInt(2) / 2;
097: testUpdateBlobWithResultSetMethods(rs, newVal, newSize);
098:
099: rs.close();
100: stmt.close();
101: }
102:
103: /**
104: * Tests updating a Blob from a scollable resultset, using
105: * positioned updates.
106: * @exception SQLException causes test to fail with error
107: * @exception IOException causes test to fail with error
108: */
109: public void testUpdateBlobFromScrollableResultSetUsingPositionedUpdates()
110: throws SQLException, IOException {
111: final Statement stmt = createStatement(
112: ResultSet.TYPE_SCROLL_INSENSITIVE,
113: ResultSet.CONCUR_UPDATABLE);
114: final ResultSet rs = stmt.executeQuery("SELECT * from "
115: + BLOBDataModelSetup.getBlobTableName());
116: println("Last");
117: rs.last();
118:
119: final int newVal = rs.getInt(1) + 11;
120: final int newSize = rs.getInt(2) / 2;
121: testUpdateBlobWithPositionedUpdate(rs, newVal, newSize);
122:
123: rs.relative(0); // Necessary after a positioned update
124:
125: println("Verify updated blob using result set");
126: verifyBlob(newVal, newSize, rs.getBlob(3));
127:
128: rs.close();
129: stmt.close();
130: }
131:
132: /**
133: * Tests updating a Blob from a forward only resultset, using
134: * methods.
135: * @exception SQLException causes test to fail with error
136: * @exception IOException causes test to fail with error
137: */
138: public void testUpdateBlobFromForwardOnlyResultSetUsingPositionedUpdates()
139: throws SQLException, IOException {
140: final Statement stmt = createStatement(
141: ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
142: final ResultSet rs = stmt.executeQuery("SELECT * from "
143: + BLOBDataModelSetup.getBlobTableName());
144: while (rs.next()) {
145: println("Next");
146: final int val = rs.getInt(1);
147: if (val == BLOBDataModelSetup.bigVal)
148: break;
149: }
150:
151: final int newVal = rs.getInt(1) + 11;
152: final int newSize = rs.getInt(2) / 2;
153: testUpdateBlobWithPositionedUpdate(rs, newVal, newSize);
154:
155: rs.close();
156: stmt.close();
157: }
158:
159: /**
160: * Tests updating a Blob from a scollable resultset produced by a
161: * select query with projection. Updates are made using
162: * result set update methods.
163: * @exception SQLException causes test to fail with error
164: * @exception IOException causes test to fail with error
165: */
166: public void testUpdateBlobFromScrollableResultSetWithProjectUsingResultSetMethods()
167: throws SQLException, IOException {
168: final Statement stmt = createStatement(
169: ResultSet.TYPE_SCROLL_INSENSITIVE,
170: ResultSet.CONCUR_UPDATABLE);
171: final ResultSet rs = stmt
172: .executeQuery("SELECT data,val,length from "
173: + BLOBDataModelSetup.getBlobTableName());
174: println("Last");
175: rs.last();
176:
177: final int newVal = rs.getInt(2) + 11;
178: final int newSize = rs.getInt(3) / 2;
179: testUpdateBlobWithResultSetMethods(rs, newVal, newSize);
180:
181: println("Verify updated blob using result set");
182: verifyBlob(newVal, newSize, rs.getBlob(1));
183:
184: rs.close();
185: stmt.close();
186: }
187:
188: /**
189: * Tests updating a Blob from a forward only resultset, produced by
190: * a select query with projection. Updates are made using
191: * result set update methods.
192: * @exception SQLException causes test to fail with error
193: * @exception IOException causes test to fail with error
194: */
195: public void testUpdateBlobFromForwardOnlyResultSetWithProjectUsingResultSetMethods()
196: throws SQLException, IOException {
197: final Statement stmt = createStatement(
198: ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
199: final ResultSet rs = stmt
200: .executeQuery("SELECT data,val,length from "
201: + BLOBDataModelSetup.getBlobTableName());
202:
203: while (rs.next()) {
204: println("Next");
205: final int val = rs.getInt("VAL");
206: if (val == BLOBDataModelSetup.bigVal)
207: break;
208: }
209:
210: final int newVal = rs.getInt("VAL") + 11;
211: final int newSize = BLOBDataModelSetup.bigSize / 2;
212: testUpdateBlobWithResultSetMethods(rs, newVal, newSize);
213:
214: rs.close();
215: stmt.close();
216: }
217:
218: /**
219: * Tests updating a Blob from a scollable resultset, produced by
220: * a select query with projection. Updates are made using
221: * positioned updates
222: * @exception SQLException causes test to fail with error
223: * @exception IOException causes test to fail with error
224: */
225: public void testUpdateBlobFromScrollableResultSetWithProjectUsingPositionedUpdates()
226: throws SQLException, IOException {
227: final Statement stmt = createStatement(
228: ResultSet.TYPE_SCROLL_INSENSITIVE,
229: ResultSet.CONCUR_UPDATABLE);
230: final ResultSet rs = stmt.executeQuery("SELECT data from "
231: + BLOBDataModelSetup.getBlobTableName()
232: + " WHERE val= " + BLOBDataModelSetup.bigVal);
233: println("Last");
234: rs.last();
235:
236: final int newVal = BLOBDataModelSetup.bigVal * 2;
237: final int newSize = BLOBDataModelSetup.bigSize / 2;
238: testUpdateBlobWithPositionedUpdate(rs, newVal, newSize);
239:
240: rs.relative(0); // Necessary after a positioned update
241:
242: println("Verify updated blob using result set");
243: verifyBlob(newVal, newSize, rs.getBlob("DATA"));
244:
245: rs.close();
246: stmt.close();
247: }
248:
249: /**
250: * Tests updating a Blob from a forward only resultset, produced by
251: * a select query with projection. Updates are made using
252: * positioned updates.
253: * @exception SQLException causes test to fail with error
254: * @exception IOException causes test to fail with error
255: */
256: public void testUpdateBlobFromForwardOnlyResultSetWithProjectUsingPositionedUpdates()
257: throws SQLException, IOException {
258: final Statement stmt = createStatement(
259: ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
260: final ResultSet rs = stmt.executeQuery("SELECT data from "
261: + BLOBDataModelSetup.getBlobTableName()
262: + " WHERE val = " + BLOBDataModelSetup.bigVal);
263: rs.next();
264:
265: final int newVal = BLOBDataModelSetup.bigVal * 2;
266: final int newSize = BLOBDataModelSetup.bigSize / 2;
267: testUpdateBlobWithPositionedUpdate(rs, newVal, newSize);
268:
269: rs.close();
270: stmt.close();
271: }
272:
273: /**
274: * Tests updating the Blob using result set update methods.
275: * @param rs result set, currently positioned on row to be updated
276: * @param newVal new value in val column and blob data
277: * @param newSize new size of Blob
278: * @exception SQLException causes test to fail with error
279: * @exception IOException causes test to fail with error
280: */
281: private void testUpdateBlobWithResultSetMethods(final ResultSet rs,
282: final int newVal, final int newSize) throws SQLException,
283: IOException {
284: int val = rs.getInt("VAL");
285: int size = rs.getInt("LENGTH");
286: println("VerifyBlob");
287: verifyBlob(val, size, rs.getBlob("DATA"));
288:
289: println("UpdateBlob");
290: final TestInputStream newStream = new TestInputStream(newSize,
291: newVal);
292:
293: rs.updateInt("VAL", newVal);
294: rs.updateInt("LENGTH", newSize);
295: rs.updateBinaryStream("DATA", newStream, newSize);
296: rs.updateRow();
297:
298: println("Verify updated blob with another query");
299: verifyNewValueInTable(newVal, newSize);
300: }
301:
302: /**
303: * Tests updating the Blob using positioned updates
304: * @param rs result set, currently positioned on row to be updated
305: * @param newVal new value in val column and blob data
306: * @param newSize new size of Blob
307: * @exception SQLException causes test to fail with error
308: * @exception IOException causes test to fail with error
309: */
310: private void testUpdateBlobWithPositionedUpdate(final ResultSet rs,
311: final int newVal, final int newSize) throws SQLException,
312: IOException {
313: final PreparedStatement preparedStatement = getConnection()
314: .prepareStatement(
315: "UPDATE "
316: + BLOBDataModelSetup.getBlobTableName()
317: + " SET val=?, length = ?, data = ? WHERE CURRENT OF "
318: + rs.getCursorName());
319:
320: println("UpdateBlob");
321:
322: final TestInputStream newStream = new TestInputStream(newSize,
323: newVal);
324:
325: preparedStatement.setInt(1, newVal);
326: preparedStatement.setInt(2, newSize);
327: preparedStatement.setBinaryStream(3, newStream, newSize);
328: preparedStatement.executeUpdate();
329:
330: println("Verify updated blob with another query");
331: verifyNewValueInTable(newVal, newSize);
332: }
333:
334: /**
335: * Verifies that the table has row with column val=newVal
336: * and that it its data and size columns are consistent.
337: * @param newVal value expected to be found in the val column of a row
338: * @param newSize expected size of size column and size of blob
339: * @exception SQLException causes test to fail with error
340: * @exception IOException causes test to fail with error
341: */
342: private void verifyNewValueInTable(final int newVal,
343: final int newSize) throws IOException, SQLException {
344: println("Verify new value in table: " + newVal);
345:
346: final Statement stmt = createStatement(
347: ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
348:
349: final ResultSet rs = stmt.executeQuery("SELECT * FROM "
350: + BLOBDataModelSetup.getBlobTableName()
351: + " WHERE val = " + newVal);
352:
353: println("Query executed, calling next");
354:
355: boolean foundVal = false;
356:
357: while (rs.next()) {
358: println("Next called, verifying row");
359:
360: assertEquals("Unexpected value in val column", newVal, rs
361: .getInt(1));
362:
363: verifyBlob(newVal, newSize, rs.getBlob(3));
364: foundVal = true;
365: }
366: assertTrue("No column with value= " + newVal + " found ",
367: foundVal);
368:
369: rs.close();
370: stmt.close();
371: }
372:
373: /**
374: * Verifies that the blob is consistent
375: * @param expectedVal the InputStream for the Blob should return this value
376: * for every byte
377: * @param expecteSize the Blob should have this size
378: * @exception SQLException causes test to fail with error
379: * @exception IOException causes test to fail with error
380: */
381: private void verifyBlob(final int expectedVal,
382: final int expectedSize, final Blob blob)
383: throws IOException, SQLException {
384: final InputStream stream = blob.getBinaryStream();
385: int blobSize = 0;
386: for (int val = stream.read(); val != -1; val = stream.read()) {
387: blobSize++;
388:
389: // avoid doing a string-concat for every byte in blob
390: if (expectedVal != val) {
391: assertEquals("Unexpected value in stream at position "
392: + blobSize, expectedVal, val);
393: }
394: }
395: stream.close();
396: assertEquals("Unexpected size of stream ", expectedSize,
397: blobSize);
398: }
399:
400: /**
401: * The suite decorates the tests of this class with
402: * a setup which creates and populates the data model.
403: */
404: public static Test suite() {
405: TestSuite mainSuite = new TestSuite(BLOBTest.class);
406: return new BLOBDataModelSetup(mainSuite);
407: }
408:
409: /**
410: * The setup creates a Connection to the database.
411: * @exception Exception any exception will cause test to fail with error.
412: */
413: public final void setUp() throws Exception {
414: println("Setup of: " + getName());
415: getConnection().setAutoCommit(false);
416: }
417: }
|