Hi,

It seems JDBC driver does not handle if a INSERT SQL statement
performed by executeUpdate() is actually a SELECT, which is rewritten
by the rule system. 

Exception in thread "main" postgresql.stat.result
	at org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:199)
	at org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:183)
	at test.main(test.java:10)

I'm not sure whether the JDBC driver or PostgreSQL backend should be
blamed though.
--
Tatsuo Ishii

Here is a test case:

create table daily_log (
    log_date timestamp default current_timestamp,
    log_memo varchar
);
create table daily_log01 (
    log_date timestamp default current_timestamp,
    log_memo varchar
);
create table daily_log02 (
    log_date timestamp default current_timestamp,
    log_memo varchar
);

create function insert_daily_log(timestamp,varchar) returns void as '
    declare
        in_log_date alias for $1;
        in_log_memo alias for $2;
    begin
        if 1=0 then
            insert into daily_log01 (log_date,log_memo)
                values (in_log_date,in_log_memo);
        elsif 1=1 then
            insert into daily_log02 (log_date,log_memo)
                values (in_log_date,in_log_memo);
        end if;
        return;
    end;
' language plpgsql;

create rule insert_daily_log_rule as on insert to daily_log
       do instead select insert_daily_log(NEW.log_date,NEW.log_memo);

import java.sql.*;

public class test {
    public static void main(String[] args) throws Exception {
        Class.forName("org.postgresql.Driver");
        Connection conn = DriverManager.getConnection(
            "jdbc:postgresql://localhost/testdb", "dummy", "");
        conn.setAutoCommit(false);
        Statement stat = conn.createStatement();
        stat.executeUpdate(
            "insert into daily_log (log_memo) values ('hoge')");
/* this will not raise an error BTW
        stat.executeQuery(
            "insert into daily_log (log_memo) values ('hoge')");
*/
        conn.commit();
        stat.close();
        conn.close();
    }
}

Thu, 29 Jan 2004 13:44:59 +0900 (JST)
Tatsuo Ishii <t-ishii@sra.co.jp>
rule and JDBC


On Thu, 29 Jan 2004, Tatsuo Ishii wrote:

> Hi,
>
> It seems JDBC driver does not handle if a INSERT SQL statement
> performed by executeUpdate() is actually a SELECT, which is rewritten
> by the rule system.


The JDBC spec says an exception should be thrown if "the given SQL
statement produces a ResultSet object" which it does.  As you note using
executeQuery works, but won't if there isn't a rule.  Perhaps using plain
execute() would be the most appropriate thing to do.

Kris Jurka


Thu, 29 Jan 2004 00:00:11 -0500 (EST)
Kris Jurka <books@ejurka.com>
Re: rule and JDBC
> > It seems JDBC driver does not handle if a INSERT SQL statement
> > performed by executeUpdate() is actually a SELECT, which is rewritten
> > by the rule system.
> 
> 
> The JDBC spec says an exception should be thrown if "the given SQL
> statement produces a ResultSet object" which it does.  As you note using
> executeQuery works, but won't if there isn't a rule.  Perhaps using plain
> execute() would be the most appropriate thing to do.
> 
> Kris Jurka

Got it. With regard to this PostgreSQL JDBC driver confirms the JDBC
spec.
--
Tatsuo Ishii

Thu, 29 Jan 2004 14:07:10 +0900 (JST)
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: rule and JDBC