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();
}
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();
}
}
}