Thanks Knut for your clarification. Please bear in mind that I don't know
much about cursor states. But my strong feeling is that it should behave
similar in both embedded and n/w server, other than the difference in the
SQL states. But please find the attached test (which is a miniature of the
CurrentOfTest.java), where I expect that cursor.getCursorName() should
return null. And it works as expected in the embedded mode. But in N/w
server it has a cursor name. Is that the correct behaviour?? If so please
give me some more inputs..
You can run the test in embedded mode as
java TestCursor embedded
in n/w server ( have to start the server seperately)
java TestCursor NetworkServer
Thanks
Manjula
On 2/7/07, Knut Anders Hatlen <[EMAIL PROTECTED]> wrote:
Manjula Kutty <[EMAIL PROTECTED]> writes:
> While converting the currentof.java I came across this. I thought this
test
> will be a good candidate to run both on embedded and client/server
modes.
> But the test passes only on embedded mode. In the client/server mode the
> test fails with "junit.framework.ComparisonFailure: Unexpected SQL
state.
> expected:<24000> but was:<XCL07>" . The test is doing negative test of
> trying to delete cursor past last row caught. In the embedded mode it
does
> what is expected, but in client/server mode the cursor is getting
closed. So
> getting a different exception than expected. I ran the old
> currentof.javatest in the client/server mode and also got the same
> error. So this test is
> expected to run only in embedded mode? And this cursor feature is not
> implemented in client/server mode?
This sounds like a side effect of DERBY-821. If you move past the last
row with a forward-only cursor, the cursor will be closed on the
server. This is an optimization so that the network client doesn't
have to send a message to the server when it later closes the
cursor. Apart from the different messages and SQL states, embedded and
client/server behave the same way in this case, as neither of them
deletes a row.
--
Knut Anders
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 java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Properties;
public class TestCursor{
public static void main(String[] args) throws Exception
{
String type = args[0];
Connection conn = null;
System.out.println("Test started with " + type + " driver");
if(type.equalsIgnoreCase("embedded")){
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
conn =
DriverManager.getConnection("jdbc:derby:testdb;create=true");
}
else{
Class.forName("org.apache.derby.jdbc.ClientDriver");
conn =
DriverManager.getConnection("jdbc:derby://localhost:1527/testdb;create=true");
}
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
stmt.executeUpdate("create table t (i int, c char(50))");
stmt.executeUpdate("create table s (i int, c char(50))");
stmt.executeUpdate("insert into t values (1956, 'hello
world')");
stmt.executeUpdate("insert into t values (456, 'hi yourself')");
stmt.executeUpdate("insert into t values (180, 'rubber
ducky')");
stmt.executeUpdate("insert into t values (3, 'you are the
one')");
stmt.close();
conn.commit();
testUpdate(conn);
}
public static void testUpdate(Connection conn) throws SQLException{
PreparedStatement select = null;
PreparedStatement update = null;
ResultSet cursor = null;
int startCount, endCount;
boolean caught;
// TEST: Update of cursor declared READ ONLY
caught = false;
try {
select = conn.prepareStatement("select I, C
from t for read only");
cursor = select.executeQuery(); // cursor is
now open
if (cursor.getCursorName() == null)
{
caught = true;
System.out.println("PASS: update of
read-only cursor caught");
}
} catch (SQLException se) {
System.out.println("Exception:" +
se.getMessage());
se.printStackTrace();
throw se;
} finally {
if (! caught)
System.out.println("FAIL: update of
read-only cursor not caught");
System.out.println("Cursor is not null,
Cursor name :"+ cursor.getCursorName());
}
cursor.close();
select.close();
// TEST: Update of cursor declared FETCH ONLY
caught = false;
try {
select = conn.prepareStatement("select I, C
from t for fetch only");
cursor = select.executeQuery(); // cursor is
now open
if (cursor.getCursorName() == null)
{
caught = true;
System.out.println("PASS: update of
fetch-only cursor caught");
}
} catch (SQLException se) {
System.out.println("Exception:" +
se.getMessage());
se.printStackTrace();
} finally {
if (! caught)
System.out.println("FAIL: update of
fetch-only cursor not caught");
System.out.println("Cursor is not null,
Cursor name :"+ cursor.getCursorName());
}
cursor.close();
select.close();
// TEST: Update of cursor with a union
caught = false;
try {
select = conn.prepareStatement("select I, C
from t union all select I, C from t");
cursor = select.executeQuery(); // cursor is
now open
if (cursor.getCursorName() == null)
{
System.out.println("PASS: update of
union cursor caught");
caught = true;
}
} catch (SQLException se) {
System.out.println("Exception:" +
se.getMessage());
se.printStackTrace();
throw se;
} finally {
if (! caught)
System.out.println("FAIL: update of
union cursor not caught");
System.out.println("Cursor is not null,
Cursor name :"+ cursor.getCursorName());
}
cursor.close();
select.close();
// TEST: Update of cursor with a join
caught = false;
try {
select = conn.prepareStatement("select t1.I,
t1.C from t t1, t t2 where t1.I = t2.I");
cursor = select.executeQuery(); // cursor is
now open
if (cursor.getCursorName() == null)
{
System.out.println("PASS: update of
join cursor caught");
caught = true;
}
} catch (SQLException se) {
System.out.println("Exception:" +
se.getMessage());
se.printStackTrace();
throw se;
} finally {
if (! caught)
System.out.println("FAIL: update of
join cursor not caught");
System.out.println("Cursor is not null,
Cursor name :"+ cursor.getCursorName());
}
cursor.close();
select.close();
// TEST: Update of cursor with a derived table
caught = false;
try {
select = conn.prepareStatement("select I, C
from (select * from t) t1");
cursor = select.executeQuery(); // cursor is
now open
if (cursor.getCursorName() == null)
{
System.out.println("PASS: update of
derived table cursor caught");
caught = true;
}
} catch (SQLException se) {
System.out.println("Exception:" +
se.getMessage());
se.printStackTrace();
throw se;
} finally {
if (! caught)
System.out.println("FAIL: update of
derived table cursor not caught");
System.out.println("Cursor is not null,
Cursor name :"+ cursor.getCursorName());
}
cursor.close();
select.close();
// TEST: Update of cursor with a values clause
caught = false;
try {
select = conn.prepareStatement("values (1, 2,
3)");
cursor = select.executeQuery(); // cursor is
now open
if (cursor.getCursorName() == null)
{
caught = true;
System.out.println("PASS: update of
values clause cursor caught");
}
} catch (SQLException se) {
System.out.println("Exception:" +
se.getMessage());
se.printStackTrace();
throw se;
} finally {
if (! caught)
System.out.println("FAIL: update of
values clause cursor not caught");
System.out.println("Cursor is not null,
Cursor name :"+ cursor.getCursorName());
}
cursor.close();
select.close();
// TEST: Update of cursor with a subquery
caught = false;
try {
select = conn.prepareStatement("select I, C
from t where I in (select I from t)");
cursor = select.executeQuery(); // cursor is
now open
if (cursor.getCursorName() == null)
{
caught = true;
System.out.println("PASS: update of
subquery cursor caught");
}
} catch (SQLException se) {
System.out.println("Exception:" +
se.getMessage());
se.printStackTrace();
throw se;
} finally {
if (! caught)
System.out.println("FAIL: update of
subquery cursor not caught");
System.out.println("Cursor is not null,
Cursor name :"+ cursor.getCursorName());
}
cursor.close();
select.close();
if(update != null)
update.close();
}
}
