Menu

returned value from executeUpdate().

Help
2016-10-12
2016-10-12
  • Igal Klebanov

    Igal Klebanov - 2016-10-12

    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

    Gord Thompson - 2016-10-12

    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.

     

Log in to post a comment.

Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.