/* Copyright (c) 2001-2014, The HSQL Development Group
* 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 the HSQL Development Group 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 HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
* 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.
*/
package org.hsqldb.test;
import java.io.
CharArrayReader;
import java.io.
IOException;
import java.io.
InputStream;
import java.io.
InputStreamReader;
import java.io.
Reader;
import java.sql.
Blob;
import java.sql.
CallableStatement;
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 javax.sql.rowset.serial.
SerialBlob;
import org.hsqldb.jdbc.
JDBCBlob;
import org.hsqldb.jdbc.
JDBCClob;
import org.hsqldb.lib.
HsqlByteArrayInputStream;
import org.hsqldb.lib.
StopWatch;
public class
TestLobs extends
TestBase {
Connection connection;
Statement statement;
public
TestLobs(
String name) {
// super(name);
// super(name, "jdbc:hsqldb:file:test3", false, false);
super(
name, "jdbc:hsqldb:mem:test3", false, false);
}
protected void
setUp() throws
Exception {
System.
out.
println("SetUp (sub-)test: " +
getName());
super.
setUp();
try {
connection = super.
newConnection();
statement =
connection.
createStatement();
} catch (
Exception e) {
System.
out.
println(
e);
}
}
public void
testBlobA() {
System.
out.
println("Starting (sub-)test: " +
getName());
try {
String ddl0 = "DROP TABLE BLOBTEST IF EXISTS";
String ddl1 =
"CREATE TABLE BLOBTEST(ID IDENTITY, BLOBFIELD BLOB(100000))";
statement.
execute(
ddl0);
statement.
execute(
ddl1);
} catch (
SQLException e) {
e.
printStackTrace();
fail("test failure");
}
try {
String dml0 = "insert into blobtest(blobfield) values(?)";
String dql0 = "select * from blobtest;";
PreparedStatement ps =
connection.
prepareStatement(
dml0);
byte[]
data = new byte[] {
1, 2, 3, 4, 5, 6, 7, 8, 9, 10
};
Blob blob = new
JDBCBlob(
data);
ps.
setBlob(1,
blob);
ps.
executeUpdate();
data[4] = 50;
blob = new
JDBCBlob(
data);
ps.
setBlob(1,
blob);
ps.
executeUpdate();
ps.
close();
ps =
connection.
prepareStatement(
dql0);
ResultSet rs =
ps.
executeQuery();
rs.
next();
Blob blob1 =
rs.
getBlob(2);
rs.
next();
Blob blob2 =
rs.
getBlob(2);
byte[]
data1 =
blob1.
getBytes(1, 10);
byte[]
data2 =
blob2.
getBytes(1, 10);
assertTrue(
data1[4] == 5 &&
data2[4] == 50);
} catch (
SQLException e) {
e.
printStackTrace();
fail("test failure");
}
}
public void
testBlobB() {
System.
out.
println("Starting (sub-)test: " +
getName());
ResultSet rs;
byte[]
ba;
byte[]
baR1 = new byte[] {
(byte) 0xF1, (byte) 0xF2, (byte) 0xF3, (byte) 0xF4, (byte) 0xF5,
(byte) 0xF6, (byte) 0xF7, (byte) 0xF8, (byte) 0xF9, (byte) 0xFA,
(byte) 0xFB
};
byte[]
baR2 = new byte[] {
(byte) 0xE1, (byte) 0xE2, (byte) 0xE3, (byte) 0xE4, (byte) 0xE5,
(byte) 0xE6, (byte) 0xE7, (byte) 0xE8, (byte) 0xE9, (byte) 0xEA,
(byte) 0xEB
};
try {
connection.
setAutoCommit(false);
Statement st =
connection.
createStatement();
st.
executeUpdate("DROP TABLE blo IF EXISTS");
st.
executeUpdate("CREATE TABLE blo (id INTEGER, b blob( 100))");
PreparedStatement ps =
connection.
prepareStatement(
"INSERT INTO blo(id, b) values(2, ?)");
//st.executeUpdate("INSERT INTO blo (id, b) VALUES (1, x'A003')");
ps.
setBlob(1, new
SerialBlob(
baR1));
ps.
executeUpdate();
rs =
st.
executeQuery("SELECT b FROM blo WHERE id = 2");
if (!
rs.
next()) {
assertTrue("No row with id 2", false);
}
java.sql.
Blob blob1 =
rs.
getBlob("b");
System.
out.
println("Size of retrieved blob: " +
blob1.
length());
//System.out.println("Value = (" + rs.getString("b") + ')');
byte[]
baOut =
blob1.
getBytes(1, (int)
blob1.
length());
if (
baOut.length !=
baR1.length) {
assertTrue("Expected array len " +
baR1.length + ", got len "
+
baOut.length, false);
}
for (int
i = 0;
i <
baOut.length;
i++) {
if (
baOut[
i] !=
baR1[
i]) {
assertTrue("Expected array len " +
baR1.length
+ ", got len " +
baOut.length, false);
}
}
rs.
close();
rs =
st.
executeQuery("SELECT b FROM blo WHERE id = 2");
if (!
rs.
next()) {
assertTrue("No row with id 2", false);
}
// ba = rs.getBytes("b"); doesn't convert but throws ClassCast
blob1 =
rs.
getBlob("b");
ba =
blob1.
getBytes(1,
baR2.length);
if (
ba.length !=
baR2.length) {
assertTrue("row2 byte length differs", false);
}
for (int
i = 0;
i <
ba.length;
i++) {
if (
ba[
i] !=
baR1[
i]) {
assertTrue("row2 byte " +
i + " differs", false);
}
}
rs.
close();
connection.
rollback();
// again with stream
ps.
setBinaryStream(1, new
HsqlByteArrayInputStream(
baR1),
baR1.length);
ps.
executeUpdate();
rs =
st.
executeQuery("SELECT b FROM blo WHERE id = 2");
if (!
rs.
next()) {
assertTrue("No row with id 2", false);
}
blob1 =
rs.
getBlob("b");
System.
out.
println("Size of retrieved blob: " +
blob1.
length());
//System.out.println("Value = (" + rs.getString("b") + ')');
baOut =
blob1.
getBytes(1, (int)
blob1.
length());
if (
baOut.length !=
baR1.length) {
assertTrue("Expected array len " +
baR1.length + ", got len "
+
baOut.length, false);
}
for (int
i = 0;
i <
baOut.length;
i++) {
if (
baOut[
i] !=
baR1[
i]) {
assertTrue("Expected array len " +
baR1.length
+ ", got len " +
baOut.length, false);
}
}
rs.
close();
connection.
close();
} catch (
SQLException e) {
e.
printStackTrace();
fail("test failure");
}
}
public void
testClobA() {
System.
out.
println("Starting (sub-)test: " +
getName());
try {
String ddl0 = "DROP TABLE CLOBTEST IF EXISTS";
String ddl1 =
"CREATE TABLE CLOBTEST(ID IDENTITY, CLOBFIELD CLOB(100000))";
statement.
execute(
ddl0);
statement.
execute(
ddl1);
} catch (
SQLException e) {}
try {
String dml0 = "insert into clobtest(clobfield) values(?)";
String dql0 = "select * from clobtest;";
PreparedStatement ps =
connection.
prepareStatement(
dml0);
String data = "Testing clob insert and select ops";
Clob clob = new
JDBCClob(
data);
ps.
setClob(1,
clob);
ps.
executeUpdate();
data =
data.
replaceFirst("insert", "INSERT");
clob = new
JDBCClob(
data);
ps.
setClob(1,
clob);
ps.
executeUpdate();
ps.
close();
ps =
connection.
prepareStatement(
dql0);
ResultSet rs =
ps.
executeQuery();
rs.
next();
Clob clob1 =
rs.
getClob(2);
rs.
next();
Clob clob2 =
rs.
getClob(2);
String string =
rs.
getString(2);
int
data1 =
clob1.
getSubString(1,
data.
length()).
indexOf("insert");
int
data2 =
clob2.
getSubString(1,
data.
length()).
indexOf("INSERT");
assertTrue(
data1 ==
data2 &&
data1 > 0);
assertTrue(
string.
equals(
clob2.
getSubString(1,
data.
length())));
rs.
close();
// test with stream
rs =
ps.
executeQuery();
rs.
next();
InputStream is =
rs.
getAsciiStream(2);
StringBuffer sb = new
StringBuffer();
try {
while (true) {
int
c =
is.
read();
if (
c < 0) {
break;
}
sb.
append((char)
c);
}
} catch (
IOException ie) {}
string =
rs.
getString(2);
assertTrue(
string.
equals(
sb.
toString()));
rs.
next();
rs.
getAsciiStream(2);
} catch (
SQLException e) {
e.
printStackTrace();
fail("test failure");
}
}
public void
testClobB() {
System.
out.
println("Starting (sub-)test: " +
getName());
try {
String ddl0 = "DROP TABLE CLOBTEST IF EXISTS";
String ddl1 =
"CREATE TABLE CLOBTEST(ID IDENTITY, V VARCHAR(10), I INT, CLOBFIELD CLOB(100000))";
statement.
execute(
ddl0);
statement.
execute(
ddl1);
} catch (
SQLException e) {}
try {
String dml0 = "insert into clobtest values(default, ?, ?, ?)";
String dql0 = "select * from clobtest;";
PreparedStatement ps =
connection.
prepareStatement(
dml0);
String data = "Testing clob insert and select ops";
Clob clob = new
JDBCClob(
data);
ps.
setString(1, "test");
ps.
setInt(2, 5);
ps.
setClob(3,
clob);
ps.
executeUpdate();
data =
data.
replaceFirst("insert", "INSERT");
clob = new
JDBCClob(
data);
ps.
setClob(3,
clob);
ps.
executeUpdate();
PreparedStatement ps2 =
connection.
prepareStatement(
dql0);
ResultSet rs =
ps2.
executeQuery();
rs.
next();
Clob clob1 =
rs.
getClob(4);
rs.
next();
Clob clob2 =
rs.
getClob(4);
int
data1 =
clob1.
getSubString(1,
data.
length()).
indexOf("insert");
int
data2 =
clob2.
getSubString(1,
data.
length()).
indexOf("INSERT");
assertTrue(
data1 ==
data2 &&
data1 > 0);
//
Clob clob3 = new
JDBCClob(
data);
Reader reader =
clob3.
getCharacterStream();
ps.
setCharacterStream(3,
reader, (int)
clob3.
length());
ps.
executeUpdate();
//
reader =
clob2.
getCharacterStream();
try {
ps.
setCharacterStream(3,
reader, (int)
clob3.
length());
ps.
executeUpdate();
} catch (
SQLException e) {}
connection.
commit();
} catch (
SQLException e) {
e.
printStackTrace();
fail("test failure");
}
}
public void
testClobC() {
System.
out.
println("Starting (sub-)test: " +
getName());
try {
String ddl0 = "DROP TABLE VARIABLE IF EXISTS";
String ddl1 =
"CREATE TABLE VARIABLE (stateid varchar(128), varid numeric(16,0), "
+ "scalabilitypassivated char(1) DEFAULT 'N', value clob (2G), scopeguid varchar(128),"
+ "primary key (stateid, varid, scalabilitypassivated, scopeguid))";
statement.
execute(
ddl0);
statement.
execute(
ddl1);
} catch (
SQLException e) {}
try {
String dml0 = "INSERT INTO VARIABLE VALUES (?, ?, 'N', ?, ?)";
String dml1 =
"UPDATE VARIABLE SET value = ? WHERE stateid = ? AND "
+ "varid = ? AND scalabilitypassivated = 'N' AND scopeguid = ?";
PreparedStatement ps =
connection.
prepareStatement(
dml0);
//
String resourceFileName = "/org/hsqldb/resources/lob-schema.sql";
InputStreamReader reader = null;
try {
InputStream fis =
getClass().
getResourceAsStream(
resourceFileName);
reader = new
InputStreamReader(
fis, "ISO-8859-1");
} catch (
Exception e) {}
ps.
setString(1, "test-id-1");
ps.
setLong(2, 23456789123456L);
ps.
setCharacterStream(3,
reader, 1000);
ps.
setString(4, "test-scope-1");
ps.
executeUpdate();
try {
InputStream fis =
getClass().
getResourceAsStream(
resourceFileName);
fis =
getClass().
getResourceAsStream(
resourceFileName);
reader = new
InputStreamReader(
fis, "ISO-8859-1");
for (int
i = 0;
i < 100;
i++) {
reader.
read();
}
} catch (
Exception e) {}
//
ps.
setString(1, "test-id-2");
ps.
setLong(2, 23456789123457L);
ps.
setCharacterStream(3,
reader, 100);
ps.
setString(4, "test-scope-2");
ps.
addBatch();
ps.
setString(1, "test-id-3");
ps.
setLong(2, 23456789123458L);
ps.
setCharacterStream(3,
reader, 100);
ps.
setString(4, "test-scope-3");
ps.
addBatch();
int[]
results =
ps.
executeBatch();
//
try {
InputStream fis =
getClass().
getResourceAsStream(
resourceFileName);
fis =
getClass().
getResourceAsStream(
resourceFileName);
reader = new
InputStreamReader(
fis, "ISO-8859-1");
for (int
i = 0;
i < 100;
i++) {
reader.
read();
}
} catch (
Exception e) {}
ps =
connection.
prepareStatement(
dml1);
ps.
setCharacterStream(1,
reader, 500);
ps.
setString(2, "test-id-1");
ps.
setLong(3, 23456789123456L);
ps.
setString(4, "test-scope-1");
ps.
executeUpdate();
ps.
close();
} catch (
SQLException e) {
e.
printStackTrace();
fail("test failure");
}
}
public void
testClobD() {
System.
out.
println("Starting (sub-)test: " +
getName());
try {
String ddl0 = "DROP TABLE VARIABLE IF EXISTS";
String ddl1 =
"CREATE TABLE VARIABLE (stateid varchar(128), varid numeric(16,0), "
+ "scalabilitypassivated char(1) DEFAULT 'N', value clob(20000), scopeguid varchar(128),"
+ "primary key (stateid, varid, scalabilitypassivated, scopeguid))";
statement.
execute(
ddl0);
statement.
execute(
ddl1);
} catch (
SQLException e) {}
try {
String dml0 = "INSERT INTO VARIABLE VALUES (?, ?, 'N', ?, ?)";
String dml1 =
"UPDATE VARIABLE SET value = ? WHERE stateid = ? AND "
+ "varid = ? AND scalabilitypassivated = 'N' AND scopeguid = ?";
PreparedStatement ps =
connection.
prepareStatement(
dml0);
connection.
setAutoCommit(false);
//
JDBCClob dataClob =
new
JDBCClob("the quick brown fox jumps on the lazy dog");
Reader reader = null;
StopWatch sw = new
StopWatch();
sw.
start();
for (int
i = 0;
i < 1000;
i++) {
reader =
dataClob.
getCharacterStream();
ps.
setString(1, "test-id-1" +
i);
ps.
setLong(2, 23456789123456L +
i);
ps.
setCharacterStream(3,
reader,
dataClob.
length());
ps.
setString(4, "test-scope-1" +
i);
ps.
executeUpdate();
connection.
commit();
}
sw.
stop();
System.
out.
println(
sw.
elapsedTimeToMessage("Time for inserts"));
ps =
connection.
prepareStatement(
dml1);
sw.
zero();
sw.
start();
for (int
i = 100;
i < 200;
i++) {
reader =
dataClob.
getCharacterStream();
ps.
setCharacterStream(1,
reader,
dataClob.
length());
ps.
setString(2, "test-id-1" +
i);
ps.
setLong(3, 23456789123456L +
i);
ps.
setString(4, "test-scope-1" +
i);
ps.
executeUpdate();
connection.
commit();
}
connection.
commit();
sw.
stop();
System.
out.
println(
sw.
elapsedTimeToMessage("Time for updates"));
} catch (
SQLException e) {
e.
printStackTrace();
fail("test failure");
}
}
public void
testClobE() {
System.
out.
println("Starting (sub-)test: " +
getName());
try {
String ddl0 = "DROP TABLE VARIABLE IF EXISTS";
String ddl1 =
"CREATE TABLE VARIABLE (stateid varchar(128), varid numeric(16,0), "
+ "scalabilitypassivated char(1) DEFAULT 'N', value clob(2000), scopeguid varchar(128),"
+ "primary key (stateid, varid, scalabilitypassivated, scopeguid))";
statement.
execute(
ddl0);
statement.
execute(
ddl1);
} catch (
SQLException e) {
System.
out.
println(
e.
getMessage());
}
try {
String dml0 = "INSERT INTO VARIABLE VALUES (?, ?, 'N', ?, ?)";
String dml1 =
"UPDATE VARIABLE SET varid = varid + 1 WHERE stateid = ? AND "
+ "varid = ? AND scalabilitypassivated = 'N' AND scopeguid = ?";
PreparedStatement ps =
connection.
prepareStatement(
dml0);
connection.
setAutoCommit(false);
//
JDBCClob dataClob =
new
JDBCClob("the quick brown fox jumps on the lazy dog");
Reader reader = null;
StopWatch sw = new
StopWatch();
sw.
start();
for (int
i = 0;
i < 100;
i++) {
reader =
dataClob.
getCharacterStream();
ps.
setString(1, "test-id-1" +
i);
ps.
setLong(2, 23456789123456L +
i);
ps.
setCharacterStream(3,
reader,
dataClob.
length());
ps.
setString(4, "test-scope-1" +
i);
ps.
executeUpdate();
connection.
commit();
}
sw.
stop();
System.
out.
println(
sw.
elapsedTimeToMessage("Time for inserts"));
ps =
connection.
prepareStatement(
dml1);
sw.
zero();
sw.
start();
for (int
i = 10;
i < 20;
i++) {
ps.
setString(1, "test-id-1" +
i);
ps.
setLong(2, 23456789123456L +
i);
ps.
setString(3, "test-scope-1" +
i);
ps.
executeUpdate();
connection.
commit();
}
connection.
commit();
ResultSet rs =
statement.
executeQuery("SELECT * FROM VARIABLE");
while (
rs.
next()) {
Clob clob =
rs.
getClob(4);
long
dataLength =
dataClob.
length();
long
clobLength =
clob.
length();
assertTrue(
dataLength ==
clobLength);
assertTrue(
dataClob.
getSubString(1, 30).
equals(
clob.
getSubString(1, 30)));
}
rs =
statement.
executeQuery(
"SELECT CAST(SUBSTRING(VALUE FROM 19) AS VARCHAR(100)),STATEID,"
+ "CHARACTER_LENGTH(VALUE),CAST(VALUE AS VARCHAR(100)) FROM "
+ "VARIABLE WHERE VALUE='THE QUICK BROWN FOX JUMPS ON THE LAZY DOG'"
+ "AND STATEID>'TEST-ID-197'");
while (
rs.
next()) {
assertTrue(
rs.
getString(1).
equals("x jumps on the lazy dog"));
}
sw.
stop();
System.
out.
println(
sw.
elapsedTimeToMessage("Time for updates"));
} catch (
SQLException e) {
e.
printStackTrace();
fail("test failure");
}
}
public void
testClobF() {
System.
out.
println("Starting (sub-)test: " +
getName());
try {
String ddl0 = "DROP TABLE CLOBTEST IF EXISTS";
String ddl1 =
"CREATE TABLE CLOBTEST(ID IDENTITY, CLOBFIELD CLOB(100000))";
statement.
execute(
ddl0);
statement.
execute(
ddl1);
} catch (
SQLException e) {}
try {
String dml0 = "insert into clobtest(clobfield) values ?";
String value = "0123456789";
PreparedStatement ps =
connection.
prepareStatement(
dml0);
ps.
setString(1,
value);
ps.
executeUpdate();
String dq1 = "select CHARACTER_LENGTH(clobfield) from clobtest;";
ResultSet rs =
statement.
executeQuery(
dq1);
rs.
next();
int
length =
rs.
getInt(1);
assertTrue(
value.
length() ==
length);
rs.
close();
String dq3 = "delete from clobtest;";
statement.
execute(
dq3);
char[]
testChars = new char[11111];
for (int
i = 0,
j = 32;
i <
testChars.length;
i++,
j++) {
if (
j > 255) {
j = 32;
}
testChars[
i] = (char)
j;
}
ps.
setCharacterStream(1, new
CharArrayReader(
testChars),
testChars.length);
ps.
executeUpdate();
String dq2 = "select clobfield from clobtest;";
rs =
statement.
executeQuery(
dq2);
rs.
next();
Reader reader =
rs.
getCharacterStream(1);
char[]
newChars = new char[
testChars.length];
try {
reader.
read(
newChars);
} catch (
IOException e) {
fail("test failure");
}
for (int
i = 0;
i <
testChars.length;
i++) {
if (
testChars[
i] !=
newChars[
i]) {
fail("test failure");
}
}
} catch (
SQLException e) {
e.
printStackTrace();
fail("test failure");
}
}
public void
testClobG() {
System.
out.
println("Starting (sub-)test: " +
getName());
try {
String ddl0 = "DROP TABLE CLOBTEST IF EXISTS";
String ddl1 =
"CREATE TABLE CLOBTEST(ID IDENTITY, CLOBFIELD CLOB(100000))";
statement.
execute(
ddl0);
statement.
execute(
ddl1);
} catch (
SQLException e) {}
try {
String dml0 = "insert into clobtest(clobfield) values ?";
String value = "0123456789";
PreparedStatement ps =
connection.
prepareStatement(
dml0);
Clob clob =
connection.
createClob();
clob.
setString(1,
value);
ps.
setClob(1,
clob);
ps.
executeUpdate();
String dq1 = "select CHARACTER_LENGTH(clobfield) from clobtest;";
ResultSet rs =
statement.
executeQuery(
dq1);
rs.
next();
int
length =
rs.
getInt(1);
assertTrue(
value.
length() ==
length);
rs.
close();
String dq3 = "delete from clobtest;";
statement.
execute(
dq3);
char[]
testChars = new char[11111];
for (int
i = 0,
j = 32;
i <
testChars.length;
i++,
j++) {
if (
j > 255) {
j = 32;
}
testChars[
i] = (char)
j;
}
ps.
setCharacterStream(1, new
CharArrayReader(
testChars),
testChars.length);
ps.
executeUpdate();
String dq2 = "select clobfield from clobtest;";
rs =
statement.
executeQuery(
dq2);
rs.
next();
Reader reader =
rs.
getCharacterStream(1);
char[]
newChars = new char[
testChars.length];
try {
reader.
read(
newChars);
} catch (
IOException e) {
fail("test failure");
}
for (int
i = 0;
i <
testChars.length;
i++) {
if (
testChars[
i] !=
newChars[
i]) {
fail("test failure");
}
}
} catch (
SQLException e) {
e.
printStackTrace();
fail("test failure");
}
}
public void
testClobH() {
System.
out.
println("Starting (sub-)test: " +
getName());
try {
String ddl1 = "drop procedure PUBLIC.PROC_H if exists";
statement.
execute(
ddl1);
ddl1 = "create procedure PUBLIC.PROC_H(out p1 clob, out p2 int) READS SQL DATA BEGIN ATOMIC SET p1 = 'dafsdfasdfaefafeajfiwejifpjajsidojfakmvkamsdjfadpsjfoajsdifjaos'; SET p2 = 0; end";
String dml0 = "call PUBLIC.PROC_H(?, ?)";
statement.
execute(
ddl1);
CallableStatement ps =
connection.
prepareCall(
dml0);
ps.
registerOutParameter(1, java.sql.
Types.
CLOB);
ps.
registerOutParameter(2, java.sql.
Types.
INTEGER);
ps.
execute();
String string =
ps.
getClob(1).
getSubString(1, 10);
System.
out.
println(
string);
} catch (
SQLException e) {
e.
printStackTrace();
}
}
public void
testBlobH() {
System.
out.
println("Starting (sub-)test: " +
getName());
try {
String ddl1 = "DROP TABLE BLOBTEST IF EXISTS";
String ddl2 = "CREATE TABLE BLOBTEST(A INTEGER, B BLOB)";
statement.
execute(
ddl1);
statement.
execute(
ddl2);
} catch (
SQLException e) {}
try {
String dml0 = "insert into blobtest values(1, ?)";
String dql0 = "select * from blobtest";
PreparedStatement ps =
connection.
prepareStatement(
dml0);
byte[]
data = new byte[] {
1, 2, 3, 4, 5, 6, 7, 8, 9, 10
};
connection.
setAutoCommit(false);
Blob blob =
connection.
createBlob();
blob.
setBytes(1,
data);
ps.
setBlob(1,
blob);
ps.
executeUpdate();
data[4] = 50;
blob = new
JDBCBlob(
data);
ps.
setBlob(1,
blob);
ps.
executeUpdate();
ps.
close();
connection.
commit();
ps =
connection.
prepareStatement(
dql0);
ResultSet rs =
ps.
executeQuery();
rs.
next();
Blob blob1 =
rs.
getBlob(2);
rs.
next();
Blob blob2 =
rs.
getBlob(2);
byte[]
data1 =
blob1.
getBytes(1, 10);
byte[]
data2 =
blob2.
getBytes(1, 10);
assertTrue(
data1[4] == 5 &&
data2[4] == 50);
} catch (
SQLException e) {
e.
printStackTrace();
fail("test failure");
}
}
public void
testBlobI() {
System.
out.
println("Starting (sub-)test: " +
getName());
try {
Statement st =
connection.
createStatement();
st.
executeUpdate("drop table BLOBTEST if exists");
st.
executeUpdate("create table BLOBTEST (BT_BLOB BLOB)");
System.
out.
println("Running insert...");
PreparedStatement insert =
connection.
prepareStatement("insert into BLOBTEST values(?)");
insert.
setBytes(1, new byte[]{});
insert.
executeUpdate();
System.
out.
println("Running update...");
PreparedStatement update =
connection.
prepareStatement(
"update BLOBTEST set BT_BLOB = CONCAT(BT_BLOB,?)");
update.
setBytes(1, new byte[] {
1, 2, 3
});
update.
executeUpdate();
System.
out.
println("Running select...");
PreparedStatement select =
connection.
prepareStatement("select BT_BLOB from BLOBTEST");
ResultSet result =
select.
executeQuery();
System.
out.
println("Results: " +
result.
getFetchSize());
while (
result.
next()) {
byte[]
data =
result.
getBytes(1);
String s =
data == null ? "null"
:
String.
valueOf(
data.length);
System.
out.
println("Result: " +
s);
}
} catch (
SQLException e) {
e.
printStackTrace();
fail("test failure");
}
}
/*
public static void main(String[] args) throws SQLException {
Connection con = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
DriverManager.registerDriver(new org.hsqldb.jdbc.JDBCDriver());
con = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost:7001",
"sa", null);
// con = DriverManager.getConnection("jdbc:hsqldb:mem:mymemdb", "sa", null);
// con.createStatement().execute("drop table aaa");
con.createStatement().execute("create table aaa (a integer, b blob)");
String insert = "insert into aaa values (1, ?)";
st = con.prepareStatement(insert);
byte[] b = {0, 1};
Blob blob = con.createBlob();
blob.setBytes(1, b);
st.setBlob(1, blob);
// this does not work too !!!
// st.setBytes(1, b);
System.out.println("execute");
st.execute();
System.out.println("execute end");
con.commit();
System.out.println("commit");
con.createStatement().execute("shutdown script");
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (st != null) {
st.close();
}
if (con != null) {
con.close();
}
}
}
*/
protected void
tearDown() {
System.
out.
println("TearDown (sub-)test: " +
getName());
try {
statement =
connection.
createStatement();
statement.
execute("SHUTDOWN");
statement.
close();
if (!
isNetwork) {
connection.
close();
}
} catch (
Exception e) {}
super.
tearDown();
}
}