/**
* Copyright (c) 2013, impossibl.com
* All rights reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions are met:
*
* * Redistributions of source code must retain the above copyright notice,
* this list of conditions and the following disclaimer.
* * Redistributions in binary form must reproduce the above copyright
* notice, this list of conditions and the following disclaimer in the
* documentation and/or other materials provided with the distribution.
* * Neither the name of impossibl.com nor the names of its contributors may
* be used to endorse or promote products derived from this software
* without specific prior written permission.
*
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
* IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
* ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE
* LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
* CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
* SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
* INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
* CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
* ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
* POSSIBILITY OF SUCH DAMAGE.
*/
/*-------------------------------------------------------------------------
*
* Copyright (c) 2004-2011, PostgreSQL Global Development Group
*
*
*-------------------------------------------------------------------------
*/
package com.impossibl.postgres.jdbc;
import java.io.ByteArrayOutputStream;
import java.io.DataOutputStream;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Reader;
import java.io.Writer;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.Arrays;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.junit.runners.JUnit4;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;
/**
* @author Michael Barker <mailto:mike@middlesoft.co.uk>
*
*/
@RunWith(JUnit4.class)
public class BlobTest {
private Connection conn;
@Before
public void before() throws Exception {
conn = TestUtil.openDB();
TestUtil.createTable(conn, "blobtest", "ID INT PRIMARY KEY, DATA OID");
TestUtil.createTable(conn, "testblob", "ID NAME, LO OID");
conn.setAutoCommit(false);
}
@After
public void after() throws SQLException {
conn.setAutoCommit(true);
TestUtil.dropTable(conn, "blobtest");
TestUtil.dropTable(conn, "testblob");
TestUtil.closeDB(conn);
}
@Test
public void testSetNull() throws Exception {
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO testblob(lo) VALUES (?)");
pstmt.setBlob(1, (Blob) null);
pstmt.executeUpdate();
pstmt.setNull(1, Types.BLOB);
pstmt.executeUpdate();
pstmt.setObject(1, null, Types.BLOB);
pstmt.executeUpdate();
pstmt.setClob(1, (Clob) null);
pstmt.executeUpdate();
pstmt.setNull(1, Types.CLOB);
pstmt.executeUpdate();
pstmt.setObject(1, null, Types.CLOB);
pstmt.executeUpdate();
pstmt.close();
}
@Test
public void testSet() throws SQLException {
Statement stmt = conn.createStatement();
stmt.execute("INSERT INTO testblob(id,lo) VALUES ('1', lo_creat(-1))");
ResultSet rs = stmt.executeQuery("SELECT lo FROM testblob");
assertTrue(rs.next());
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO testblob(id, lo) VALUES(?,?)");
Blob blob = rs.getBlob(1);
pstmt.setString(1, "setObjectTypeBlob");
pstmt.setObject(2, blob, Types.BLOB);
assertEquals(1, pstmt.executeUpdate());
blob = rs.getBlob(1);
pstmt.setString(1, "setObjectBlob");
pstmt.setObject(2, blob);
assertEquals(1, pstmt.executeUpdate());
blob = rs.getBlob(1);
pstmt.setString(1, "setBlob");
pstmt.setBlob(2, blob);
assertEquals(1, pstmt.executeUpdate());
Clob clob = rs.getClob(1);
pstmt.setString(1, "setObjectTypeClob");
pstmt.setObject(2, clob, Types.CLOB);
assertEquals(1, pstmt.executeUpdate());
clob = rs.getClob(1);
pstmt.setString(1, "setObjectClob");
pstmt.setObject(2, clob);
assertEquals(1, pstmt.executeUpdate());
clob = rs.getClob(1);
pstmt.setString(1, "setClob");
pstmt.setClob(2, clob);
assertEquals(1, pstmt.executeUpdate());
rs.close();
stmt.close();
pstmt.close();
}
/*
* Tests uploading a blob to the database
*/
@Test
public void testUploadBlob() throws Exception {
assertTrue(uploadFileBlob("pom.xml") > 0);
assertTrue(compareBlobs());
}
/*
* Tests uploading a clob to the database
*/
@Test
public void testUploadClob() throws Exception {
assertTrue(uploadFileClob("pom.xml") > 0);
assertTrue(compareClobs());
}
@Test
public void testGetBytesOffsetBlob() throws Exception {
assertTrue(uploadFileBlob("pom.xml") > 0);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT lo FROM testblob");
assertTrue(rs.next());
Blob lob = rs.getBlob(1);
byte[] data = lob.getBytes(2, 4);
assertEquals(data.length, 4);
assertEquals(data[0], '!');
assertEquals(data[1], '-');
assertEquals(data[2], '-');
assertEquals(data[3], '\n');
stmt.close();
rs.close();
}
@Test
public void testGetBytesOffsetClob() throws Exception {
assertTrue(uploadFileClob("pom.xml") > 0);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT lo FROM testblob");
assertTrue(rs.next());
Clob lob = rs.getClob(1);
String data = lob.getSubString(2, 4);
assertEquals(data.length(), 4);
assertEquals(data.charAt(0), '!');
assertEquals(data.charAt(1), '-');
assertEquals(data.charAt(2), '-');
assertEquals(data.charAt(3), '\n');
stmt.close();
rs.close();
}
@Test
public void testMultipleStreamsBlob() throws Exception {
assertTrue(uploadFileBlob("pom.xml") > 0);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT lo FROM testblob");
assertTrue(rs.next());
Blob lob = rs.getBlob(1);
byte[] data = new byte[2];
InputStream is = lob.getBinaryStream();
assertEquals(data.length, is.read(data));
assertEquals(data[0], '<');
assertEquals(data[1], '!');
is.close();
is = lob.getBinaryStream();
assertEquals(data.length, is.read(data));
assertEquals(data[0], '<');
assertEquals(data[1], '!');
is.close();
rs.close();
stmt.close();
}
@Test
public void testMultipleStreamsClob() throws Exception {
assertTrue(uploadFileClob("pom.xml") > 0);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT lo FROM testblob");
assertTrue(rs.next());
Clob lob = rs.getClob(1);
char[] data = new char[2];
Reader r = lob.getCharacterStream();
assertEquals(data.length, r.read(data));
assertEquals(data[0], '<');
assertEquals(data[1], '!');
r.close();
r = lob.getCharacterStream();
assertEquals(data.length, r.read(data));
assertEquals(data[0], '<');
assertEquals(data[1], '!');
r.close();
rs.close();
stmt.close();
}
@Test
public void testParallelStreamsBlob() throws Exception {
assertTrue(uploadFileBlob("pom.xml") > 0);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT lo FROM testblob");
assertTrue(rs.next());
Blob lob = rs.getBlob(1);
InputStream is1 = lob.getBinaryStream();
InputStream is2 = lob.getBinaryStream();
while (true) {
int i1 = is1.read();
int i2 = is2.read();
assertEquals(i1, i2);
if (i1 == -1)
break;
}
is1.close();
is2.close();
rs.close();
stmt.close();
}
@Test
public void testParallelStreamsClob() throws Exception {
assertTrue(uploadFileClob("pom.xml") > 0);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT lo FROM testblob");
assertTrue(rs.next());
Clob lob = rs.getClob(1);
Reader is1 = lob.getCharacterStream();
Reader is2 = lob.getCharacterStream();
while (true) {
int i1 = is1.read();
int i2 = is2.read();
assertEquals(i1, i2);
if (i1 == -1)
break;
}
is1.close();
is2.close();
rs.close();
stmt.close();
}
private long uploadFileBlob(String file) throws Exception {
FileInputStream fis = new FileInputStream(file);
int oid = LargeObject.creat((PGConnectionImpl) conn, LargeObject.INV_WRITE);
LargeObject lo = LargeObject.open((PGConnectionImpl) conn, oid);
OutputStream os = new BlobOutputStream(null, lo.dup());
int s = fis.read();
while (s > -1) {
os.write(s);
s = fis.read();
}
os.close();
lo.close();
fis.close();
// Insert into the table
Statement st = conn.createStatement();
st.executeUpdate(TestUtil.insertSQL("testblob", "id,lo", "'" + file + "'," + oid));
conn.commit();
st.close();
return oid;
}
private long uploadFileClob(String file) throws Exception {
FileReader fr = new FileReader(file);
int oid = LargeObject.creat((PGConnectionImpl) conn, LargeObject.INV_WRITE);
LargeObject lo = LargeObject.open((PGConnectionImpl) conn, oid);
ClobWriter cw = new ClobWriter(null, lo.dup());
int ch = fr.read();
while (ch > -1) {
cw.write(ch);
ch = fr.read();
}
cw.close();
lo.close();
fr.close();
// Insert into the table
Statement st = conn.createStatement();
st.executeUpdate(TestUtil.insertSQL("testblob", "id,lo", "'" + file + "'," + oid));
conn.commit();
st.close();
return oid;
}
/*
* Helper - compares the blobs in a table with a local file. This uses the
* jdbc java.sql.Blob api
*/
private boolean compareBlobs() throws Exception {
boolean result = true;
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(TestUtil.selectSQL("testblob", "id,lo"));
assertNotNull(rs);
while (rs.next()) {
String file = rs.getString(1);
Blob blob = rs.getBlob(2);
FileInputStream fis = new FileInputStream(file);
InputStream bis = blob.getBinaryStream();
int f = fis.read();
int b = bis.read();
int c = 0;
while (f >= 0 && b >= 0 & result) {
result = (f == b);
f = fis.read();
b = bis.read();
c++;
}
result = result && f == -1 && b == -1;
if (!result)
assertTrue("Blob compare failed at " + c + " of " + blob.length(), false);
bis.close();
fis.close();
}
rs.close();
st.close();
return result;
}
/*
* Helper - compares the clobs in a table with a local file.
*/
private boolean compareClobs() throws Exception {
boolean result = true;
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(TestUtil.selectSQL("testblob", "id,lo"));
assertNotNull(rs);
while (rs.next()) {
String file = rs.getString(1);
Clob clob = rs.getClob(2);
FileReader fr = new FileReader(file);
Reader cr = clob.getCharacterStream();
int f = fr.read();
int b = cr.read();
int c = 0;
while (f >= 0 && b >= 0 & result) {
result = (f == b);
f = fr.read();
b = cr.read();
c++;
}
result = result && f == -1 && b == -1;
if (!result)
assertTrue("Clob compare failed at " + c + " of " + clob.length(), false);
cr.close();
fr.close();
}
rs.close();
st.close();
return result;
}
/**
* Test the writing and reading of a single byte.
*
* @throws SQLException
*/
@Test
public void test1Byte() throws SQLException {
byte[] data = {(byte) 'a'};
readWriteBlob(data);
}
/**
* Test the writing and reading of a single char.
*
* @throws SQLException
*/
@Test
public void test1Char() throws SQLException {
String data = "a";
readWriteClob(data);
}
/**
* Test the writing and reading of a few bytes.
*
* @throws SQLException
*/
@Test
public void testManyBytes() throws SQLException {
byte[] data = "aaaaaaaaaa".getBytes();
readWriteBlob(data);
}
/**
* Test the writing and reading of a few chars.
*
* @throws SQLException
*/
@Test
public void testManyChars() throws SQLException {
String data = "aaaaaaaaaa";
readWriteClob(data);
}
/**
* Test writing a single byte with an offset.
*
* @throws SQLException
*/
@Test
public void test1ByteOffset() throws SQLException {
byte[] data = {(byte) 'a'};
readWriteBlob(10, data);
}
/**
* Test writing a single char with an offset.
*
* @throws SQLException
*/
@Test
public void test1CharOffset() throws SQLException {
String data = "a";
readWriteClob(10, data);
}
/**
* Test the writing and reading of a few bytes with an offset.
*
* @throws SQLException
*/
@Test
public void testManyBytesOffset() throws SQLException {
byte[] data = "aaaaaaaaaa".getBytes();
readWriteBlob(10, data);
}
/**
* Test the writing and reading of a few chars with an offset.
*
* @throws SQLException
*/
@Test
public void testManyCharsOffset() throws SQLException {
String data = "aaaaaaaaaa";
readWriteClob(10, data);
}
/**
* Tests all of the byte values from 0 - 255.
*
* @throws SQLException
*/
@Test
public void testAllBytes() throws SQLException {
byte[] data = new byte[256];
for (int i = 0; i < data.length; i++) {
data[i] = (byte) i;
}
readWriteBlob(data);
}
/**
* Tests random values across entire code point range
*
* @throws SQLException
* @throws IOException
*/
@Test
public void testRangeChars() throws SQLException, IOException {
ByteArrayOutputStream bos = new ByteArrayOutputStream();
DataOutputStream dos = new DataOutputStream(bos);
for (int i = 0; i < 256; i++) {
dos.writeInt(i * 16 * 1024 * 1024);
}
readWriteClob(new String(bos.toByteArray(), PGClob.CHARSET));
}
@Test
public void testTruncateBlob() throws SQLException {
byte[] data = new byte[100];
for (byte i = 0; i < data.length; i++) {
data[i] = i;
}
readWriteBlob(data);
PreparedStatement ps = conn.prepareStatement("SELECT ID, DATA FROM blobtest WHERE ID = 1");
ResultSet rs = ps.executeQuery();
assertTrue(rs.next());
Blob blob = rs.getBlob("DATA");
assertEquals(100, blob.length());
blob.truncate(50);
assertEquals(50, blob.length());
blob.truncate(150);
assertEquals(150, blob.length());
data = blob.getBytes(1, 200);
assertEquals(150, data.length);
for (byte i = 0; i < 50; i++) {
assertEquals(i, data[i]);
}
for (int i = 50; i < 150; i++) {
assertEquals(0, data[i]);
}
rs.close();
ps.close();
}
@Test
public void testTruncateClob() throws SQLException {
char[] chars = new char[100];
for (char i = 0; i < chars.length; i++) {
chars[i] = i;
}
String data = new String(chars);
readWriteClob(data);
PreparedStatement ps = conn.prepareStatement("SELECT ID, DATA FROM blobtest WHERE ID = 1");
ResultSet rs = ps.executeQuery();
assertTrue(rs.next());
Clob clob = rs.getClob("DATA");
assertEquals(100, clob.length());
clob.truncate(50);
assertEquals(50, clob.length());
clob.truncate(150);
assertEquals(150, clob.length());
data = clob.getSubString(1, 200);
assertEquals(150, data.length());
for (char i = 0; i < 50; i++) {
assertEquals(i, data.charAt(i));
}
for (int i = 50; i < 150; i++) {
assertEquals(0, data.charAt(i));
}
rs.close();
ps.close();
}
/**
*
* @param data
* @throws SQLException
*/
private void readWriteBlob(byte[] data) throws SQLException {
readWriteBlob(1, data);
}
/**
*
* @param data
* @throws SQLException
*/
private void readWriteClob(String data) throws SQLException {
readWriteClob(1, data);
}
/**
*
* @param offset
* @param data
* @throws SQLException
*/
private void readWriteBlob(int offset, byte[] data) throws SQLException {
PreparedStatement ps = conn.prepareStatement("INSERT INTO blobtest VALUES (1, lo_creat(-1))");
ps.executeUpdate();
ps.close();
ps = conn.prepareStatement("SELECT ID, DATA FROM blobtest WHERE ID = 1");
ResultSet rs = ps.executeQuery();
assertTrue(rs.next());
Blob b = rs.getBlob("DATA");
b.setBytes(offset, data);
rs.close();
ps.close();
ps = conn.prepareStatement("SELECT ID, DATA FROM blobtest WHERE ID = 1");
rs = ps.executeQuery();
assertTrue(rs.next());
b = rs.getBlob("DATA");
byte[] rspData = b.getBytes(offset, data.length);
assertTrue("Request should be the same as the response", Arrays.equals(data, rspData));
rs.close();
ps.close();
}
/**
*
* @param offset
* @param data
* @throws SQLException
*/
private void readWriteClob(int offset, String data) throws SQLException {
PreparedStatement ps = conn.prepareStatement("INSERT INTO blobtest VALUES (1, lo_creat(-1))");
ps.executeUpdate();
ps.close();
ps = conn.prepareStatement("SELECT ID, DATA FROM blobtest WHERE ID = 1");
ResultSet rs = ps.executeQuery();
assertTrue(rs.next());
Clob b = rs.getClob("DATA");
b.setString(offset, data);
rs.close();
ps.close();
ps = conn.prepareStatement("SELECT ID, DATA FROM blobtest WHERE ID = 1");
rs = ps.executeQuery();
assertTrue(rs.next());
b = rs.getClob("DATA");
String rspData = b.getSubString(offset, data.length());
assertEquals("Request should be the same as the response", data, rspData);
rs.close();
ps.close();
}
/**
* Test the writing and reading of a single byte.
*
* @throws SQLException
* @throws IOException
*/
@Test
public void test1ByteStream() throws SQLException, IOException {
byte[] data = {(byte) 'a'};
readWriteBlobStream(data);
}
/**
* Test the writing and reading of a single char.
*
* @throws SQLException
* @throws IOException
*/
@Test
public void test1CharStream() throws SQLException, IOException {
String data = "a";
readWriteClobStream(data);
}
/**
* Test the writing and reading of a few bytes.
*
* @throws SQLException
* @throws IOException
*/
@Test
public void testManyBytesStream() throws SQLException, IOException {
byte[] data = "aaaaaaaaaa".getBytes();
readWriteBlobStream(data);
}
/**
* Test the writing and reading of a few chars.
*
* @throws SQLException
* @throws IOException
*/
@Test
public void testManyCharsStream() throws SQLException, IOException {
String data = "aaaaaaaaaa";
readWriteClobStream(data);
}
/**
* Test writing a single byte with an offset.
*
* @throws SQLException
* @throws IOException
*/
@Test
public void test1ByteOffsetStream() throws SQLException, IOException {
byte[] data = {(byte) 'a'};
readWriteBlobStream(10, data);
}
/**
* Test writing a single char with an offset.
*
* @throws SQLException
* @throws IOException
*/
@Test
public void test1CharOffsetStream() throws SQLException, IOException {
String data = "a";
readWriteClobStream(10, data);
}
/**
* Test the writing and reading of a few bytes with an offset.
*
* @throws SQLException
* @throws IOException
*/
@Test
public void testManyBytesOffsetStream() throws SQLException, IOException {
byte[] data = "aaaaaaaaaa".getBytes();
readWriteBlobStream(10, data);
}
/**
* Test the writing and reading of a few chars with an offset.
*
* @throws SQLException
* @throws IOException
*/
@Test
public void testManyCharsOffsetStream() throws SQLException, IOException {
String data = "aaaaaaaaaa";
readWriteClobStream(10, data);
}
/**
* Tests all of the byte values from 0 - 255.
*
* @throws SQLException
* @throws IOException
*/
@Test
public void testAllBytesStream() throws SQLException, IOException {
byte[] data = new byte[256];
for (int i = 0; i < data.length; i++) {
data[i] = (byte) i;
}
readWriteBlobStream(data);
}
/**
* Tests random values across entire code point range
*
* @throws SQLException
* @throws IOException
*/
@Test
public void testRangeCharsStream() throws SQLException, IOException {
ByteArrayOutputStream bos = new ByteArrayOutputStream();
DataOutputStream dos = new DataOutputStream(bos);
for (int i = 0; i < 256; i++) {
dos.writeInt(i * 16 * 1024 * 1024);
}
readWriteClobStream(new String(bos.toByteArray(), PGClob.CHARSET));
}
private void readWriteBlobStream(byte[] data) throws SQLException, IOException {
readWriteBlobStream(1, data);
}
private void readWriteClobStream(String data) throws SQLException, IOException {
readWriteClobStream(1, data);
}
/**
* Reads then writes data to the blob via a stream.
*
* @param offset
* @param data
* @throws SQLException
* @throws IOException
*/
private void readWriteBlobStream(int offset, byte[] data) throws SQLException, IOException {
PreparedStatement ps = conn.prepareStatement("INSERT INTO blobtest VALUES (1, lo_creat(-1))");
ps.executeUpdate();
ps.close();
ps = conn.prepareStatement("SELECT ID, DATA FROM blobtest WHERE ID = 1");
ResultSet rs = ps.executeQuery();
assertTrue(rs.next());
Blob b = rs.getBlob("DATA");
OutputStream out = b.setBinaryStream(offset);
out.write(data);
out.flush();
out.close();
rs.close();
ps.close();
ps = conn.prepareStatement("SELECT ID, DATA FROM blobtest WHERE ID = 1");
rs = ps.executeQuery();
assertTrue(rs.next());
b = rs.getBlob("DATA");
InputStream in = b.getBinaryStream();
byte[] rspData = new byte[data.length];
in.skip(offset - 1);
in.read(rspData);
in.close();
assertTrue("Request should be the same as the response", Arrays.equals(data, rspData));
rs.close();
ps.close();
}
/**
* Reads then writes data to the clob via a stream.
*
* @param offset
* @param data
* @throws SQLException
* @throws IOException
*/
private void readWriteClobStream(int offset, String data) throws SQLException, IOException {
PreparedStatement ps = conn.prepareStatement("INSERT INTO blobtest VALUES (1, lo_creat(-1))");
ps.executeUpdate();
ps.close();
ps = conn.prepareStatement("SELECT ID, DATA FROM blobtest WHERE ID = 1");
ResultSet rs = ps.executeQuery();
assertTrue(rs.next());
Clob c = rs.getClob("DATA");
Writer out = c.setCharacterStream(offset);
out.write(data);
out.flush();
out.close();
rs.close();
ps.close();
ps = conn.prepareStatement("SELECT ID, DATA FROM blobtest WHERE ID = 1");
rs = ps.executeQuery();
assertTrue(rs.next());
c = rs.getClob("DATA");
Reader in = c.getCharacterStream();
char[] rspData = new char[data.length()];
in.skip(offset - 1);
in.read(rspData);
in.close();
assertEquals("Request should be the same as the response", data, new String(rspData));
rs.close();
ps.close();
}
@Test
public void testPatternBlob() throws SQLException {
byte[] data = "abcdefghijklmnopqrstuvwxyx0123456789".getBytes();
byte[] pattern = "def".getBytes();
PreparedStatement ps = conn.prepareStatement("INSERT INTO blobtest VALUES (1, lo_creat(-1))");
ps.executeUpdate();
ps.close();
ps = conn.prepareStatement("SELECT ID, DATA FROM blobtest WHERE ID = 1");
ResultSet rs = ps.executeQuery();
assertTrue(rs.next());
Blob b = rs.getBlob("DATA");
b.setBytes(1, data);
rs.close();
ps.close();
ps = conn.prepareStatement("SELECT ID, DATA FROM blobtest WHERE ID = 1");
rs = ps.executeQuery();
assertTrue(rs.next());
b = rs.getBlob("DATA");
long position = b.position(pattern, 1);
byte[] rspData = b.getBytes(position, pattern.length);
assertTrue("Request should be the same as the response", Arrays.equals(pattern, rspData));
rs.close();
ps.close();
}
@Test
public void testPatternClob() throws SQLException {
String data = "abcdefghijklmnopqrstuvwxyx0123456789";
String pattern = "def";
PreparedStatement ps = conn.prepareStatement("INSERT INTO blobtest VALUES (1, lo_creat(-1))");
ps.executeUpdate();
ps.close();
ps = conn.prepareStatement("SELECT ID, DATA FROM blobtest WHERE ID = 1");
ResultSet rs = ps.executeQuery();
assertTrue(rs.next());
Clob c = rs.getClob("DATA");
c.setString(1, data);
rs.close();
ps.close();
ps = conn.prepareStatement("SELECT ID, DATA FROM blobtest WHERE ID = 1");
rs = ps.executeQuery();
assertTrue(rs.next());
c = rs.getClob("DATA");
long position = c.position(pattern, 1);
String rspData = c.getSubString(position, pattern.length());
assertEquals("Request should be the same as the response", pattern, rspData);
rs.close();
ps.close();
}
@Test
public void testFreeBlob() throws SQLException {
Statement stmt = conn.createStatement();
stmt.execute("INSERT INTO blobtest VALUES (1, lo_creat(-1))");
ResultSet rs = stmt.executeQuery("SELECT data FROM blobtest");
assertTrue(rs.next());
Blob blob = rs.getBlob(1);
blob.free();
try {
blob.length();
fail("Should have thrown an Exception because it was freed.");
}
catch (SQLException sqle) {
// Ok
}
rs.close();
stmt.close();
}
@Test
public void testFreeClob() throws SQLException {
Statement stmt = conn.createStatement();
stmt.execute("INSERT INTO blobtest VALUES (1, lo_creat(-1))");
ResultSet rs = stmt.executeQuery("SELECT data FROM blobtest");
assertTrue(rs.next());
Clob clob = rs.getClob(1);
clob.free();
try {
clob.length();
fail("Should have thrown an Exception because it was freed.");
}
catch (SQLException sqle) {
// Ok
}
rs.close();
stmt.close();
}
@Test
public void testEOFBlob() throws SQLException, IOException {
Statement stmt = conn.createStatement();
stmt.execute("INSERT INTO blobtest VALUES (1, lo_creat(-1))");
ResultSet rs = stmt.executeQuery("SELECT data FROM blobtest");
assertTrue(rs.next());
Blob blob = rs.getBlob(1);
InputStream in = blob.getBinaryStream();
assertEquals(-1, in.read());
assertEquals(-1, in.read(new byte[4], 0, 4));
rs.close();
stmt.close();
}
@Test
public void testEOFClob() throws SQLException, IOException {
Statement stmt = conn.createStatement();
stmt.execute("INSERT INTO blobtest VALUES (1, lo_creat(-1))");
ResultSet rs = stmt.executeQuery("SELECT data FROM blobtest");
assertTrue(rs.next());
Clob clob = rs.getClob(1);
Reader in = clob.getCharacterStream();
assertEquals(-1, in.read());
assertEquals(-1, in.read(new char[4], 0, 4));
rs.close();
stmt.close();
}
@Test
public void testWrapperBlob() throws SQLException {
conn.setAutoCommit(false);
PreparedStatement stmt = conn.prepareStatement("INSERT INTO blobtest VALUES (1, ?)");
final Blob blob = conn.createBlob();
Blob wrapper = new Blob() {
@Override
public long length() throws SQLException {
return blob.length();
}
@Override
public byte[] getBytes(long pos, int length) throws SQLException {
return blob.getBytes(pos, length);
}
@Override
public InputStream getBinaryStream() throws SQLException {
return blob.getBinaryStream();
}
@Override
public long position(byte[] pattern, long start) throws SQLException {
return blob.position(pattern, start);
}
@Override
public long position(Blob pattern, long start) throws SQLException {
return blob.position(pattern, start);
}
@Override
public int setBytes(long pos, byte[] bytes) throws SQLException {
return blob.setBytes(pos, bytes);
}
@Override
public int setBytes(long pos, byte[] bytes, int offset, int len) throws SQLException {
return blob.setBytes(pos, bytes, offset, len);
}
@Override
public OutputStream setBinaryStream(long pos) throws SQLException {
return blob.setBinaryStream(pos);
}
@Override
public void truncate(long len) throws SQLException {
blob.truncate(len);
}
@Override
public void free() throws SQLException {
blob.free();
}
@Override
public InputStream getBinaryStream(long pos, long length) throws SQLException {
return blob.getBinaryStream(pos, length);
}
};
stmt.setBlob(1, wrapper);
stmt.execute();
stmt.close();
conn.commit();
}
@Test
public void testWrapperClob() throws SQLException {
conn.setAutoCommit(false);
PreparedStatement stmt = conn.prepareStatement("INSERT INTO blobtest VALUES (1, ?)");
final Clob clob = conn.createClob();
Clob wrapper = new Clob() {
@Override
public long length() throws SQLException {
return clob.length();
}
@Override
public String getSubString(long pos, int length) throws SQLException {
return clob.getSubString(pos, length);
}
@Override
public Reader getCharacterStream() throws SQLException {
return clob.getCharacterStream();
}
@Override
public InputStream getAsciiStream() throws SQLException {
return clob.getAsciiStream();
}
@Override
public long position(String searchstr, long start) throws SQLException {
return clob.position(searchstr, start);
}
@Override
public long position(Clob searchstr, long start) throws SQLException {
return clob.position(searchstr, start);
}
@Override
public int setString(long pos, String str) throws SQLException {
return clob.setString(pos, str);
}
@Override
public int setString(long pos, String str, int offset, int len) throws SQLException {
return clob.setString(pos, str, offset, len);
}
@Override
public OutputStream setAsciiStream(long pos) throws SQLException {
return clob.setAsciiStream(pos);
}
@Override
public Writer setCharacterStream(long pos) throws SQLException {
return clob.setCharacterStream(pos);
}
@Override
public void truncate(long len) throws SQLException {
clob.truncate(len);
}
@Override
public void free() throws SQLException {
clob.free();
}
@Override
public Reader getCharacterStream(long pos, long length) throws SQLException {
return clob.getCharacterStream(pos, length);
}
};
stmt.setClob(1, wrapper);
stmt.execute();
stmt.close();
conn.commit();
}
}