/* Copyright (c) 2001-2017, 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.
Reader;
import java.sql.
Blob;
import java.sql.
Clob;
import java.sql.
Connection;
import java.sql.
DriverManager;
import java.sql.
PreparedStatement;
import java.sql.
ResultSet;
import java.sql.
SQLException;
import java.sql.
Statement;
import org.hsqldb.jdbc.
JDBCBlob;
import org.hsqldb.jdbc.
JDBCClob;
public class
TestUpdatableResultSets extends
TestBase {
//
Connection connection;
Statement statement;
public
TestUpdatableResultSets(
String name) {
super(
name, true, false);
}
protected void
setUp() throws
Exception {
super.
setUp();
connection = super.
newConnection();
statement =
connection.
createStatement(
ResultSet.
TYPE_FORWARD_ONLY,
ResultSet.
CONCUR_UPDATABLE);
}
public void
testUpdatable() {
try {
statement.
execute("SET DATABASE EVENT LOG SQL LEVEL 3");
statement.
execute("drop table t1 if exists");
statement.
execute(
"create table t1 (i int primary key, v varchar(10), t varbinary(3), b blob(16), c clob(16))");
String insert = "insert into t1 values(?,?,?,?,?)";
String select = "select i, v, t, b, c from t1 where i > ?";
PreparedStatement ps =
connection.
prepareStatement(
insert);
for (int
i = 0;
i < 10;
i++) {
ps.
setInt(1,
i);
ps.
setString(2,
String.
valueOf(
i) + " s");
ps.
setBytes(3, new byte[] {
(byte)
i, ' ', (byte)
i
});
ps.
setBytes(4, new byte[] {
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16
});
ps.
setString(5, "123");
ps.
execute();
}
ps.
close();
connection.
setAutoCommit(false);
ps =
connection.
prepareStatement(
select,
ResultSet.
TYPE_SCROLL_INSENSITIVE,
ResultSet.
CONCUR_UPDATABLE);
ps.
setInt(1, -1);
ResultSet rs =
ps.
executeQuery();
while (
rs.
next()) {
String s =
rs.
getString(2);
rs.
updateString(2,
s +
s);
rs.
updateRow();
}
rs.
close();
rs =
ps.
executeQuery();
while (
rs.
next()) {
String s =
rs.
getString(2);
System.
out.
println(
s);
}
connection.
rollback();
rs =
ps.
executeQuery();
while (
rs.
next()) {
String s =
rs.
getString(2);
System.
out.
println(
s);
}
connection.
commit();
rs =
ps.
executeQuery();
Clob c = new
JDBCClob("123456789abcdef");
if (
rs.
next()) {
rs.
updateClob(5,
c);
rs.
updateRow();
}
connection.
rollback();
rs =
ps.
executeQuery();
Blob b = new
JDBCBlob(new byte[] {
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16
});
if (
rs.
next()) {
rs.
updateBlob(4,
b);
rs.
updateRow();
}
connection.
rollback();
rs =
ps.
executeQuery();
if (
rs.
next()) {
rs.
updateClob(5,
c);
rs.
updateClob(5,
c);
rs.
updateRow();
}
connection.
rollback();
rs =
ps.
executeQuery();
Reader r =
new java.io.
CharArrayReader("123456789abcdef".
toCharArray());
if (
rs.
next()) {
rs.
updateClob(5,
c);
rs.
updateClob(5,
r, 5);
rs.
updateRow();
}
connection.
rollback();
} catch (
SQLException e) {
e.
printStackTrace();
}
}
public void
testDeletable() {
try {
statement.
execute("drop table t1 if exists");
statement.
execute(
"create table t1 (i int primary key, c varchar(10), t varbinary(3))");
String insert = "insert into t1 values(?,?,?)";
String select = "select i, c, t from t1";
PreparedStatement ps =
connection.
prepareStatement(
insert);
for (int
i = 0;
i < 10;
i++) {
ps.
setInt(1,
i);
ps.
setString(2,
String.
valueOf(
i) + " s");
ps.
setBytes(3, new byte[] {
(byte)
i, ' ', (byte)
i
});
ps.
execute();
}
connection.
setAutoCommit(false);
ResultSet rs =
statement.
executeQuery(
select);
while (
rs.
next()) {
String s =
rs.
getString(2);
rs.
deleteRow();
}
rs.
close();
rs =
statement.
executeQuery(
select);
while (
rs.
next()) {
super.
fail("rows not deleted");
}
connection.
rollback();
rs =
statement.
executeQuery(
select);
while (
rs.
next()) {
String s =
rs.
getString(2);
System.
out.
println(
s);
}
connection.
commit();
} catch (
SQLException e) {
e.
printStackTrace();
}
}
public void
testDeletable2() {
try {
Connection c =
DriverManager.
getConnection("jdbc:hsqldb:mem:mytestdb", "SA",
"");
String createSQL =
"create table test (num INTEGER PRIMARY KEY, str VARCHAR(25))";
Statement createStmt =
c.
createStatement();
createStmt.
execute(
createSQL);
createStmt.
close();
String ins = "insert into test (num,str) values (?,?)";
PreparedStatement pStmt =
c.
prepareStatement(
ins);
for (int
i = 0;
i < 100;
i++) {
pStmt.
setInt(1,
i);
pStmt.
setString(2, "String" +
i);
pStmt.
execute();
}
// there should now be 100 rows in the table
String select = "SELECT * FROM test";
PreparedStatement stmt =
c.
prepareStatement(
select,
ResultSet.
TYPE_SCROLL_INSENSITIVE,
ResultSet.
CONCUR_UPDATABLE);
ResultSet rs =
stmt.
executeQuery();
rs.
beforeFirst();
while (
rs.
next()) {
int
num =
rs.
getInt("num");
if ((
num % 7) == 0) {
System.
out.
println("Deleting row:" +
num);
rs.
deleteRow();
}
}
Statement dropStmt =
c.
createStatement();
dropStmt.
execute("drop table test;");
dropStmt.
close();
} catch (
SQLException e) {
e.
printStackTrace();
}
}
public void
testScrollable() {
try {
statement.
execute("drop table t1 if exists");
statement.
execute(
"create table t1 (i int primary key, c varchar(10), t varbinary(3))");
statement.
close();
String insert = "insert into t1 values(?,?,?)";
String select = "select i, c, t from t1";
PreparedStatement ps =
connection.
prepareStatement(
insert);
for (int
i = 0;
i < 10;
i++) {
ps.
setInt(1,
i);
ps.
setString(2,
String.
valueOf(
i) + " s");
ps.
setBytes(3, new byte[] {
(byte)
i, ' ', (byte)
i
});
ps.
execute();
}
connection.
setAutoCommit(false);
statement =
connection.
createStatement(
ResultSet.
TYPE_SCROLL_INSENSITIVE,
ResultSet.
CONCUR_READ_ONLY);
ResultSet srs =
statement.
executeQuery("select * from t1 limit 2");
srs.
afterLast();
while (
srs.
previous()) {
String name =
srs.
getString(2);
float
id =
srs.
getFloat(1);
System.
out.
println(
name + " " +
id);
}
srs.
close();
srs =
statement.
executeQuery("select * from t1 limit 2");
srs.
absolute(3);
while (
srs.
previous()) {
String name =
srs.
getString(2);
float
id =
srs.
getFloat(1);
System.
out.
println(
name + " " +
id);
}
srs.
absolute(2);
while (
srs.
previous()) {
String name =
srs.
getString(2);
float
id =
srs.
getFloat(1);
System.
out.
println(
name + " " +
id);
}
srs.
absolute(-1);
while (
srs.
previous()) {
String name =
srs.
getString(2);
float
id =
srs.
getFloat(1);
System.
out.
println(
name + " " +
id);
}
connection.
commit();
} catch (
SQLException e) {
e.
printStackTrace();
}
}
}