calling a stored update query throws user lacks privilege or object not found : UCanAccess 3.0.6

[Igal Klebanov]

Hello guys,

UCanAccess is truly great.

been having an issue with an update query stored in the access file.

the callable statement throws the exception mentioned in the title.
while the prepared statement in the comments below it, works fine (SQL string was copied from the stored query with [#]'s changed to (?)'s).
also tested the stored query in access and it works.

i've triple-checked to see that the query name in the call statement matches the query name in the file.

can provide the database file if needed.

i want to limit the usage of SQL within the java code for this little project, so it's really important for me that anything that should be callable, will be called.

thanks!

    public boolean editProduct(long productID, String productName, 
            Long supplierID, Long categoryID, String quantityPerUnit, 
            BigDecimal unitPrice, Integer unitsInStock, Integer unitsInOrder, 
            Integer reorderLevel, boolean discontinued) {
        try (Connection conn = DriverManager.getConnection(
                Constants.CONNECTION_URL);
                CallableStatement stmt = conn.prepareCall(
                        "{call qryEditProduct(?,?,?,?,?,?,?,?,?,?)}")
                /*PreparedStatement stmt = conn.prepareStatement("UPDATE Products SET ProductName = (?), SupplierID = (?), CategoryID = (?), QuantityPerUnit = (?), UnitPrice = (?), UnitsInStock = (?), UnitsOnOrder = (?), ReorderLevel = (?), Discontinued = (?)\n" +
"WHERE ProductID = (?);")*/) {
            int i = 1;
            stmt.setString(i++, productName);
            if (supplierID != null)
                stmt.setLong(i++, supplierID);
            else
                stmt.setNull(i++, java.sql.Types.BIGINT);
            if (categoryID != null)
                stmt.setLong(i++, categoryID);
            else
                stmt.setNull(i++, java.sql.Types.BIGINT);
            if (quantityPerUnit != null && quantityPerUnit.length() > 0)
                stmt.setString(i++, quantityPerUnit);
            else
                stmt.setNull(i++, java.sql.Types.VARCHAR);
            if (unitPrice != null)
                stmt.setBigDecimal(i++, unitPrice);
            else
                stmt.setNull(i++, java.sql.Types.NUMERIC);
            if (unitsInStock != null)
                stmt.setInt(i++, unitsInStock);
            else
                stmt.setNull(i++, java.sql.Types.INTEGER);
            if (unitsInOrder != null)
                stmt.setInt(i++, unitsInOrder);
            else
                stmt.setNull(i++, java.sql.Types.INTEGER);
            if (reorderLevel != null)
                stmt.setInt(i++, reorderLevel);
            else
                stmt.setNull(i++, java.sql.Types.INTEGER);
            stmt.setBoolean(i++, discontinued);
            stmt.setLong(i, productID);
            stmt.execute();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

connection string:

    private final static String DB_FILENAME = "Tirgul01_north2000.accdb";
    private final static String DB_FILEPATH = "src/north2000/model/db/";
    protected final static String CONNECTION_URL = "jdbc:ucanaccess://" + DB_FILEPATH + 
            DB_FILENAME + ";COLUMNORDER=DISPLAY";

[Marco Amadei]

hi Igal, as you can see using the UCanAccess console, UCanAccess can't load the qryEditProduct procedure whilst it can load all the other ones. We are working on this.

[Marco Amadei]

It will be fixed in the next release. In the meantime I suggest to declere explicitly all qryEditProduct parameters (in the query declaration in access).

[Igal Klebanov]

thank you for the response. looking forward to the next release. is there an ETA?

[Marco Amadei]

Fixed in the 3.0.7