Hi,
I'm very new to the junit testing. So I picked lang/currentof.java as my
first test to convert to junit. I did it with the very little knowledge
I have and also with the help of the Wiki. I got the test running to
some extend but then I get some errors on the testUpdate. The error says
"Invalid cursor state" . I tried using printf to see how the control
goes and I could see the number of rows expected and got are 0. So it
should come out of the verifycount method, but it is not. There may be
3 reasons for this. Either I didn't understand the test fully or did
something wrong while converting or there may be bug . Can someone
please review the attached code(I know it is not a very clean code) and
let me know what went wrong??
One more question where will the test database gets created? Even the
test fails will the teardown method deletes all tables? If so is there a
mechanism to access the database or copy the database instance when the
test went wrong?
Thanks in advance
Manjula
package org.apache.derbyTesting.functionTests.tests.lang;
import java.sql.*;
import junit.framework.Test;
import junit.framework.TestSuite;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
public class CurrentOfJunit extends BaseJDBCTestCase{
private static boolean passed = false;
public CurrentOfJunit(String name)
{
super(name);
}
public static Test suite() {
TestSuite suite = new TestSuite();
if (usingEmbedded()) {
suite.addTestSuite(CurrentOfJunit.class);
return new CleanDatabaseTestSetup(suite);
}
return suite;
}
protected void setUp() throws SQLException
{
getConnection().setAutoCommit(false);
Statement stmt = createStatement();
stmt.executeUpdate("create table t (i int, c char(50))");
stmt.executeUpdate("create table s (i int, c char(50))");
stmt.close();
commit();
}
protected void tearDown() throws Exception
{
Statement stmt = createStatement();
verifyCount("drop table t",
stmt.executeUpdate("drop table t"),
0);
verifyCount("drop table s",
stmt.executeUpdate("drop table s"),
0);
stmt.close();
commit();
super.tearDown();
}
public void rowCount(boolean first) throws SQLException{
Statement stmt = createStatement();
if(!first){
verifyBoolean(
stmt.execute("delete from t"),
false);
}
verifyCount("insert into t values (1956, 'hello
world')",
stmt.executeUpdate("insert into t values (1956,
'hello world')"),
1);
verifyCount("insert into t values (456, 'hi yourself')",
stmt.executeUpdate("insert into t values (456, 'hi
yourself')"),
1);
verifyCount("insert into t values (180, 'rubber
ducky')",
stmt.executeUpdate("insert into t values (180,
'rubber ducky')"),
1);
verifyCount("insert into t values (3, 'you are the
one')",
stmt.executeUpdate("insert into t values (3, 'you
are the one')"),
1);
}
public void testDelete() throws SQLException {
PreparedStatement select, delete;
Statement select2, delete2;
ResultSet cursor;
int startCount, endCount;
boolean caught;
rowCount(true);
startCount = countRows("select i, c from t for read only");
// because there is no order by (nor can there be)
// the fact that this test prints out rows may someday
// be a problem. When that day comes, the row printing
// can (should) be removed from this test.
select = prepareStatement("select i, c from t for update");
cursor = select.executeQuery(); // cursor is now open
// would like to test a delete attempt before the cursor
// is open, but finagling to get the cursor name would
// destroy the spirit of the rest of the tests,
// which want to operate against the generated name.
// TEST: cursor and target table mismatch
caught = false;
try {
delete = prepareStatement("delete from s where current
of "+
cursor.getCursorName());
} catch (SQLException se) {
String m = se.getSQLState();
assertTrue("42X28" == m);
caught= true;
}
// TEST: find the cursor during compilation
delete = prepareStatement("delete from t where current of "+
cursor.getCursorName());
// TEST: delete before the cursor is on a row
caught = false;
try {
delete.executeUpdate();
} catch (SQLException se) {
String m = se.getSQLState();
assertTrue("24000" == m);
caught= true;
}
// TEST: find the cursor during execution and it is on a row
nextRow(cursor);
verifyCount("delete, ok", delete.executeUpdate(),
1);
caught = false;
// skip a row and delete another row so that two rows will
// have been removed from the table when we are done.
nextRow(cursor); // skip this row
nextRow(cursor);
verifyCount("<delete after skipping>",
delete.executeUpdate(),
1);
// TEST: delete past the last row
nextRow(cursor); // skip this row
verifyBoolean(cursor.next(), false); // past last row now
caught = false;
try {
delete.executeUpdate(); // no current row / closed
} catch (SQLException se) {
String m = se.getSQLState();
//System.out.println("Sql state got :"+ m);
assertTrue("24000" == m);
caught= true;
}
// TEST: delete off a closed cursor
// Once this is closed then the cursor no longer exists.
cursor.close();
caught = false;
try {
delete.executeUpdate();
} catch (SQLException se) {
String m = se.getSQLState();
//System.out.println("Sql state got :"+ m);
//why assertTrue is failing here?
assertFalse("XCL07" == m ||"42X30" == m);
//assertTrue("42X30" == m);
caught= true;
//if (!caught)
// throw se;
}
endCount = countRows ("select i, c from t for read only");
verifyCount("startCount-endCount", startCount-endCount,2);
// TEST: no cursor with that name exists
delete2 = createStatement();
caught = false;
try {
delete2.execute("delete from t where current of
nosuchcursor");
} catch (SQLException se) {
String m = se.getSQLState();
assertTrue("42X30" == m);
caught= true;
//if (!caught)
// throw se;
}
delete.close();
delete2.close();
select.close();
// TEST: attempt to do positioned delete before cursor execute'd
// TBD
}
public void testUpdate() throws SQLException {
PreparedStatement select = null;
PreparedStatement update = null;
Statement select2, update2;
ResultSet cursor = null;
int startCount, endCount;
boolean caught;
// these are basic tests without a where clause on the select.
// all rows are in and stay in the cursor's set when updated.
// because there is no order by (nor can there be)
// the fact that this test prints out rows may someday
// be a problem. When that day comes, the row printing
// can (should) be removed from this test.
endCount = countRows ("select i, c from t for read only");
// TEST: Updated column not found in for update of list
caught = false;
try {
select = prepareStatement("select I, C from t for
update of I");
cursor = select.executeQuery(); // cursor is now open
update = prepareStatement(
"update t set C =
'abcde' where current of " +
cursor.getCursorName());
} catch (SQLException se) {
String m = se.getSQLState();
assertTrue("42X31" == m);
caught= true;
}
cursor.close();
select.close();
// TEST: Update of cursor declared READ ONLY
caught = false;
try {
select = prepareStatement("select I, C from t for read
only");
cursor = select.executeQuery(); // cursor is now open
assertTrue (cursor.getCursorName() == null);
caught=true;
} catch (SQLException se) {
String m = se.getSQLState();
//JDBCDisplayUtil.ShowSQLException(System.out,se);
throw se;
}
cursor.close();
select.close();
// TEST: Update of cursor declared FETCH ONLY
caught = false;
try {
select = prepareStatement("select I, C from t for fetch
only");
cursor = select.executeQuery(); // cursor is now open
assertTrue(cursor.getCursorName() == null);
caught = true;
} catch (SQLException se) {
String m = se.getSQLState();
//JDBCDisplayUtil.ShowSQLException(System.out,se);
throw se;
}
cursor.close();
select.close();
// TEST: Update of cursor with a union
caught = false;
try {
select = prepareStatement("select I, C from t union all
select I, C from t");
cursor = select.executeQuery(); // cursor is now open
assertTrue(cursor.getCursorName() == null);
caught = true;
} catch (SQLException se) {
//JDBCDisplayUtil.ShowSQLException(System.out,se);
String m = se.getSQLState();
throw se;
}
/*finally {
if (! caught)
System.out.println("FAIL: update of union
cursor not caught");
}*/
cursor.close();
select.close();
// TEST: Update of cursor with a join
caught = false;
try {
select = prepareStatement("select t1.I, t1.C from t t1,
t t2 where t1.I = t2.I");
cursor = select.executeQuery(); // cursor is now open
assertTrue (cursor.getCursorName() == null);
caught=true;
} catch (SQLException se) {
String m = se.getSQLState();
//JDBCDisplayUtil.ShowSQLException(System.out,se);
throw se;
} /*finally {
if (! caught)
System.out.println("FAIL: update of join cursor
not caught");
}*/
cursor.close();
select.close();
// TEST: Update of cursor with a derived table
caught = false;
try {
select = prepareStatement("select I, C from (select *
from t) t1");
cursor = select.executeQuery(); // cursor is now open
assertTrue(cursor.getCursorName() == null);
caught=true;
} catch (SQLException se) {
String m = se.getSQLState();
//JDBCDisplayUtil.ShowSQLException(System.out,se);
throw se;
} finally {
if (! caught)
System.out.println("FAIL: update of derived
table cursor not caught");
}
cursor.close();
select.close();
// TEST: Update of cursor with a values clause
caught = false;
try {
select = prepareStatement("values (1, 2, 3)");
cursor = select.executeQuery(); // cursor is now open
assertTrue (cursor.getCursorName() == null);
caught=true;
} catch (SQLException se) {
String m = se.getSQLState();
//JDBCDisplayUtil.ShowSQLException(System.out,se);
throw se;
} /*finally {
if (! caught)
System.out.println("FAIL: update of values
clause cursor not caught");
}*/
cursor.close();
select.close();
// TEST: Update of cursor with a subquery
caught = false;
try {
select = prepareStatement("select I, C from t where I
in (select I from t)");
cursor = select.executeQuery(); // cursor is now open
assertTrue (cursor.getCursorName() == null);
caught=true;
} catch (SQLException se) {
//JDBCDisplayUtil.ShowSQLException(System.out,se);
throw se;
} /*finally {
if (! caught)
System.out.println("FAIL: update of subquery
cursor not caught");
}*/
cursor.close();
select.close();
select = prepareStatement("select I, C from t for update");
cursor = select.executeQuery(); // cursor is now open
// would like to test a update attempt before the cursor
// is open, but finagling to get the cursor name would
// destroy the spirit of the rest of the tests,
// which want to operate against the generated name.
// TEST: cursor and target table mismatch
caught = false;
try {
update = prepareStatement("update s set i=1 where
current of "+
cursor.getCursorName());
} catch (SQLException se) {
//JDBCDisplayUtil.ShowSQLException(System.out,se);
String m = se.getSQLState();
assertTrue ("42X29"==m);
caught=true;
} /*finally {
if (! caught)
System.out.println("FAIL: update table and
cursor table mismatch not caught");
}*/
// TEST: find the cursor during compilation
update = prepareStatement(
"update t set i=i+10, c='Gumby was here' where current of "+
cursor.getCursorName());
// TEST: update before the cursor is on a row
caught = false;
try {
verifyCount("update before the cursor",
update.executeUpdate(), 0); // no current row / closed
} catch (SQLException se) {
String m = se.getSQLState();
//JDBCDisplayUtil.ShowSQLException(System.out,se);
assertTrue ("24000"==m);
caught = true;
}
/*finally {
if (! caught)
System.out.println("FAIL: No error from update
on cursor before first row");
}*/
// TEST: find the cursor during execution and it is on a row
nextRow(cursor);
System.out.println("you are here");
verifyCount("update on row", update.executeUpdate(), 1);
// TEST: update an already updated row; expect it to succeed.
// will it have a cumulative effect?
verifyCount("2nd update on row", update.executeUpdate(), 1);
// skip a row and update another row so that two rows will
// have been removed from the table when we are done.
nextRow(cursor); // skip this row
nextRow(cursor);
verifyCount( "update after skipping", update.executeUpdate(),
1);
// TEST: update past the last row
nextRow(cursor); // skip this row
verifyBoolean(cursor.next(), false); // past last row now
caught = false;
try {
verifyCount("update: no current row",
update.executeUpdate(), 0); // no current row / closed
} catch (SQLException se) {
String m = se.getSQLState();
//JDBCDisplayUtil.ShowSQLException(System.out,se);
assertTrue("24000"==m);
caught = true;
} /*finally {
if (! caught)
System.out.println("FAIL: No error from update
on cursor past last row");
}*/
// TEST: update off a closed cursor
cursor.close();
select.close();
caught = false;
try {
verifyCount("update on closed cursor",
update.executeUpdate(),
0);
} catch (SQLException se) {
String m = se.getSQLState();
//JDBCDisplayUtil.ShowSQLException(System.out,se);
assertTrue ("XCL07"==m ||"42X30"==m);
caught = true;
} /*finally {
if (! caught)
System.out.println("FAIL: No error from update
on closed cursor");
}*/
update.close();
// TEST: no cursor with that name exists
update2 = createStatement();
caught = false;
try {
update2.execute("update t set i=1 where current of
nosuchcursor");
} catch (SQLException se) {
String m = se.getSQLState();
//JDBCDisplayUtil.ShowSQLException(System.out,se);
assertTrue("42X30"==m);
caught = true;
} /*finally {
if (! caught)
System.out.println("FAIL: No error from update
on nonexistent cursor");
}*/
endCount = countRows ("select i, c from t for read only");
//System.out.println("Have "+endCount+" rows in table at end");
// TEST: attempt to do positioned update before cursor execute'd
// TBD
// TEST closing a cursor will close the related update
bug4395("CS4395"); // Application provided cursor name
bug4395(null); // system provided cursor name
System.out.println("PASS: update test complete");
}
private static void verifyCount(String text, int count, int expect)
throws SQLException {
if (count!=expect) {
assertFalse (expect == count);
//throw new SQLException("Wrong number of rows
returned");
}
else
assertTrue(expect==count);
}
private static void verifyBoolean(boolean got, boolean expect) throws
SQLException {
if (got!=expect) {
assertFalse (expect == got);
//throw new SQLException("Wrong boolean returned");
}
else
assertTrue(expect==got);
}
static void nextRow(ResultSet r) throws SQLException {
verifyBoolean(r.next(), true);
}
private int countRows(String query) throws SQLException {
Statement select = createStatement();
ResultSet counter = select.executeQuery(query);
int count = 0;
while (counter.next()) {
count++;
}
counter.close();
select.close();
return count;
}
private void bug4395(String cursorName) throws SQLException {
System.out.println("bug4395 Cursor Name " + (cursorName == null
? "System Generated" : "Application Defined"));
PreparedStatement select = prepareStatement("select I, C from t
for update");
if (cursorName != null)
select.setCursorName(cursorName);
ResultSet cursor = select.executeQuery(); // cursor is now open
// TEST: find the cursor during compilation
cursorName = cursor.getCursorName();
PreparedStatement update = prepareStatement("update t set
i=i+?, c=? where current of "+
cursorName);
nextRow(cursor);
update.setInt(1, 10);
update.setString(2, "Dan was here");
verifyCount("update: valid update", update.executeUpdate(), 1);
cursor.close();
// now prepare the a cursor with the same name but only column
I for update
PreparedStatement selectdd = prepareStatement("select I, C from
t for update of I");
selectdd.setCursorName(cursorName);
cursor = selectdd.executeQuery();
nextRow(cursor);
try {
update.setInt(1, 7);
update.setString(2, "no update");
update.executeUpdate();
} catch (SQLException se) {
String m = se.getSQLState();
//JDBCDisplayUtil.ShowSQLException(System.out,se);
assertTrue ("42X31"==m);
}
cursor.close();
cursor = selectdd.executeQuery();
nextRow(cursor);
cursor.close();
}
}
