Thread: executeUpdate("SELECT INTO")

executeUpdate("SELECT INTO")

From
Joseph Shraibman
Date:
How come executeUpdate() always returns 1 when the sql is SELECT ...
INTO TEMP tablename ?  I'm using pg 7.4.7

Re: executeUpdate("SELECT INTO")

From
Oliver Jowett
Date:
Joseph Shraibman wrote:
> How come executeUpdate() always returns 1 when the sql is SELECT ...
> INTO TEMP tablename ?  I'm using pg 7.4.7

I just tried the CVS driver against 7.4.6 and 8.0.0 servers, and both
return 0 in this case, regardless of the actual number of rows inserted.
I don't have a 7.4.7 build to hand, but I'd expect it to behave the same
as 7.4.6.

Returning 0 is ok according to the JDBC spec, which says that
executeUpdate returns "either the row count for INSERT, UPDATE or DELETE
statements, or 0 for SQL statements that return nothing". We can't
return a row count here anyway, as the server doesn't tell us the number
of rows inserted by a SELECT INTO.

If you're seeing a return value of 1, that does seem wrong. Can you
provide some test code showing the problem? We'd also need to know
exactly which driver build you are using.

The test code I used is attached.

-O
import java.sql.*;

// Run with one argument: a JDBC url to connect to.
public class TestSelectInto {
    public static void main(String[] args) throws Exception {
        Class.forName("org.postgresql.Driver");
        Connection c = DriverManager.getConnection(args[0]);

        Statement s = c.createStatement();

        s.executeUpdate("CREATE TEMP TABLE t1 (i integer); INSERT INTO t1(i) VALUES (1); INSERT INTO t1(i) VALUES
(2)");
        int count = s.executeUpdate("SELECT i INTO TEMP TABLE t2 FROM t1");
        System.out.println("Update count: " + count);

        s.close();
        c.close();
    }
}