Java Database Connectivity (JDBC)

Hi all.

Here is a cute bug. Two issues are shown. One issue is that
the truncate call doesn't return the number of rows affected.
That is relatively minor, and understandable if the DBMS
protocol doesn't return the number of rows for the TRUNCATE
action. Truncate is meant to be a shorthand, so the DBMS
won't be logging or maybe even counting the rows.
The second, more insideous problem is that the prepared
statement for TRUNCATE will silently fail the second time it
is executed. If a new statement is made and used for each
truncate, it works. My guess is that this is really a DBMS
bug, and the DBMS isn't doing everything correctly with the
query plan it is keeping for the truncate statement.

My output is:

The driver is 10.2.0.3.0
The DBMS is Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
PREP insert : 1
PreparedStatement truncate : 0
count(*) returns 0
PREP insert : 1
PreparedStatement truncate : 0
count(*) returns 1

Joe Weinstein at BEA Systems

System.out.println("The driver is " + c.getMetaData().getDriverVersion() );
System.out.println("The DBMS is " + c.getMetaData().getDatabaseProductVersion() );

Statement s = c.createStatement();
try { s.executeUpdate("drop table truncate_test_tb"); } catch (Exception ignore){}
s.executeUpdate("create table truncate_test_tb (foo DATE)");

PreparedStatement prep_ins =
c.prepareStatement("insert into truncate_test_tb values(SYSDATE)");
System.out.println("PREP insert : " + prep_ins.executeUpdate());

PreparedStatement prep_trunc = c.prepareStatement("truncate table truncate_test_tb");
System.out.println("PreparedStatement truncate : " + prep_trunc.executeUpdate());

PreparedStatement prep_sel =
c.prepareStatement("select count(*) from truncate_test_tb");

ResultSet rs = prep_sel.executeQuery();
rs.next();
System.out.println("count(*) returns " + rs.getString(1) );

System.out.println("PREP insert : " + prep_ins.executeUpdate());

System.out.println("PreparedStatement truncate : " + prep_trunc.executeUpdate());

rs = prep_sel.executeQuery();
rs.next();
System.out.println("count(*) returns " + rs.getString(1) );