returned value from executeUpdate().

[Igal Klebanov]

hey guys,

is the returned value from this method currently not implemented or am i doing something wrong?

    private static void doSomething() {
        try {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");

            try (Connection conn = DriverManager.getConnection(connectionString);
                    CallableStatement stmt = conn.prepareCall(callString)) {
                int rowsAffected = stmt.executeUpdate();
                System.out.println(rowsAffected + " rows affected!");
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

despite the called statement actually affecting rows in the db file, it keeps returning 0 instead of true row count.

thanks!

[Gord Thompson]

Hi Igal. No, you aren't missing anything.

UCanAccess is simply passing along the value returned by HSQLDB when it calls executeUpdate on a CallableStatement and that appears to always be zero as illustrated by the test code ...

package com.example.hsqldbtest;

import java.sql.*;

public class HsqldbTestMain {

    public static void main(String[] args) {
        String connectionUrl = "jdbc:hsqldb:mem:memdb";
        try (Connection conn = DriverManager.getConnection(connectionUrl, "SA", "")) {
            try (Statement s = conn.createStatement()) {
                s.execute("CREATE TABLE table1 (id INT PRIMARY KEY, qty INT)");
                s.execute("INSERT INTO table1 (id, qty) VALUES (1, 10)");
                s.execute("INSERT INTO table1 (id, qty) VALUES (2, 20)");
                s.execute(
                        "CREATE PROCEDURE bump_qty() " +
                        "MODIFIES SQL DATA " +
                        "BEGIN ATOMIC " +
                            "UPDATE table1 SET qty = qty + 1 WHERE id = 1; " +
                        "END");
            }

            dumpTable(conn);

            try (CallableStatement cs = conn.prepareCall("{call bump_qty()}")) {
                int i = cs.executeUpdate();
                System.out.printf("%nHSQLDB CallableStatement#executeUpdate returned %d%n%n", i);
            }

            dumpTable(conn);

        } catch (Exception e) {
            e.printStackTrace(System.err);
        }

    }

    private static void dumpTable(Connection conn) throws SQLException {
        try (
                Statement s = conn.createStatement();
                ResultSet rs = s.executeQuery("SELECT id, qty FROM table1 ORDER BY id")) {
            while (rs.next()) {
                System.out.printf("id: %d,  qty: %d%n", rs.getInt(1), rs.getInt(2));
            }
        }

    }

}

 
which produces

id: 1,  qty: 10
id: 2,  qty: 20

HSQLDB CallableStatement#executeUpdate returned 0

id: 1,  qty: 11
id: 2,  qty: 20

 

That's understandable from HSQLDB's perspective because a stored procedure could conceivably contain many DML statements that could affect many different tables, and a single "row(s) affected" value would not be terribly meaningful.