Menu ▾ ▴

v3.0.7: executeQuery() and execute() throwing NullPointerException.

Help
Igal Klebanov
2016-10-27
2016-10-28

  • Igal Klebanov

    Igal Klebanov - 2016-10-27

    hey guys,

    been having this issue.. exception is thrown, but record is still added to table.

    have a strong feeling it's not on my end. didn't have this issue in previous build.

    ordinary "INSERT INTO (...) VALUES (...) query with parameters stored as qryInsEmployee on accdb:

    INSERT INTO TblEmployees ( LastName, FirstName, Title, BirthDate, HireDate, Address, City, Country, HomePhone, Extension, Photo )
VALUES ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11]);

    java method:
    (similar method for update query doesn't have any issues).

    private static final String connectionString = 
            "jdbc:ucanaccess://src/Tirgul05_north2000.accdb;COLUMNORDER=DISPLAY";
private static final String SQL_INS_EMPLOYEE = 
            "{ call qryInsEmployee(?,?,?,?,?,?,?,?,?,?,?) }";

public static boolean addEmployee(String lastName, String firstName, 
            String title, Date birthDate, Date hireDate, String address, 
            String city, String country, String homePhone, String extension, 
            String photo) {
        try {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");

            try (Connection conn = DriverManager.getConnection(connectionString);
                    CallableStatement stmt = conn.prepareCall(SQL_INS_EMPLOYEE)) {
                int i = 1;

                stmt.setString(i++, lastName);  // can't be null
                stmt.setString(i++, firstName); // can't be null
                if (title != null)
                    stmt.setString(i++, title);
                else
                    stmt.setNull(i++, java.sql.Types.VARCHAR);
                if (birthDate != null)
                    stmt.setDate(i++, new java.sql.Date(birthDate.getTime()));
                else
                    stmt.setNull(i++, java.sql.Types.DATE);
                if (hireDate != null)
                    stmt.setDate(i++, new java.sql.Date(hireDate.getTime()));
                else
                    stmt.setNull(i++, java.sql.Types.DATE);
                if (address != null)
                    stmt.setString(i++, address);
                else
                    stmt.setNull(i++, java.sql.Types.VARCHAR);
                if (city != null)
                    stmt.setString(i++, city);
                else
                    stmt.setNull(i++, java.sql.Types.VARCHAR);
                if (country != null)
                    stmt.setString(i++, country);
                else
                    stmt.setNull(i++, java.sql.Types.VARCHAR);
                if (homePhone != null)
                    stmt.setString(i++, homePhone);
                else
                    stmt.setNull(i++, java.sql.Types.VARCHAR);
                if (extension != null)
                    stmt.setString(i++, extension);
                else
                    stmt.setNull(i++, java.sql.Types.VARCHAR);
                if (photo != null)
                    stmt.setString(i++, photo);
                else
                    stmt.setNull(i++, java.sql.Types.VARCHAR);

//                stmt.executeUpdate();
                stmt.execute();
                return true;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return false;
    }

    exception thrown when reaches execute() or executeUpdate().

    passed all parameters as not null - no luck.

    java.lang.NullPointerException
    at net.ucanaccess.commands.InsertCommand.persist(InsertCommand.java:153)
    at net.ucanaccess.jdbc.UcanaccessConnection.flushIO(UcanaccessConnection.java:315)
    at net.ucanaccess.jdbc.UcanaccessConnection.commit(UcanaccessConnection.java:205)
    at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:161)
    at net.ucanaccess.jdbc.Execute.execute(Execute.java:46)
    at net.ucanaccess.jdbc.UcanaccessPreparedStatement.execute(UcanaccessPreparedStatement.java:228)
    at net.ucanaccess.jdbc.UcanaccessCallableStatement.execute(UcanaccessCallableStatement.java:633)
    at Main.addEmployee(Main.java:103)
    at FrmEmployees.btnSaveOnClick(FrmEmployees.java:498)
    at FrmEmployees.access$800(FrmEmployees.java:7)
    at FrmEmployees$9.actionPerformed(FrmEmployees.java:234)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2348)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
    at java.awt.Component.processMouseEvent(Component.java:6533)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3324)
    at java.awt.Component.processEvent(Component.java:6298)
    at java.awt.Container.processEvent(Container.java:2236)
    at java.awt.Component.dispatchEventImpl(Component.java:4889)
    at java.awt.Container.dispatchEventImpl(Container.java:2294)
    at java.awt.Component.dispatchEvent(Component.java:4711)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4888)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4525)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4466)
    at java.awt.Container.dispatchEventImpl(Container.java:2280)
    at java.awt.Window.dispatchEventImpl(Window.java:2746)
    at java.awt.Component.dispatchEvent(Component.java:4711)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)
    at java.awt.EventQueue.access$500(EventQueue.java:97)
    at java.awt.EventQueue$3.run(EventQueue.java:709)
    at java.awt.EventQueue$3.run(EventQueue.java:703)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:86)
    at java.awt.EventQueue$4.run(EventQueue.java:731)
    at java.awt.EventQueue$4.run(EventQueue.java:729)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.7 null
    at net.ucanaccess.jdbc.UcanaccessPreparedStatement.execute(UcanaccessPreparedStatement.java:230)
    at net.ucanaccess.jdbc.UcanaccessCallableStatement.execute(UcanaccessCallableStatement.java:633)
    at Main.addEmployee(Main.java:103)
    at FrmEmployees.btnSaveOnClick(FrmEmployees.java:498)
    at FrmEmployees.access$800(FrmEmployees.java:7)
    at FrmEmployees$9.actionPerformed(FrmEmployees.java:234)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2348)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
    at java.awt.Component.processMouseEvent(Component.java:6533)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3324)
    at java.awt.Component.processEvent(Component.java:6298)
    at java.awt.Container.processEvent(Container.java:2236)
    at java.awt.Component.dispatchEventImpl(Component.java:4889)
    at java.awt.Container.dispatchEventImpl(Container.java:2294)
    at java.awt.Component.dispatchEvent(Component.java:4711)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4888)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4525)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4466)
    at java.awt.Container.dispatchEventImpl(Container.java:2280)
    at java.awt.Window.dispatchEventImpl(Window.java:2746)
    at java.awt.Component.dispatchEvent(Component.java:4711)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)
    at java.awt.EventQueue.access$500(EventQueue.java:97)
    at java.awt.EventQueue$3.run(EventQueue.java:709)
    at java.awt.EventQueue$3.run(EventQueue.java:703)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:86)
    at java.awt.EventQueue$4.run(EventQueue.java:731)
    at java.awt.EventQueue$4.run(EventQueue.java:729)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)
    Suppressed: java.lang.IllegalStateException: Timer already cancelled.
        at java.util.Timer.sched(Timer.java:397)
        at java.util.Timer.schedule(Timer.java:193)
        at net.ucanaccess.jdbc.DBReference$MemoryTimer.decrementActiveConnection(DBReference.java:107)
        at net.ucanaccess.jdbc.DBReference$MemoryTimer.access$4(DBReference.java:93)
        at net.ucanaccess.jdbc.DBReference.decrementActiveConnection(DBReference.java:373)
        at net.ucanaccess.jdbc.UcanaccessConnection.close(UcanaccessConnection.java:184)
        at Main.addEmployee(Main.java:105)
        ... 39 more
Caused by: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.7 null
    at net.ucanaccess.jdbc.UcanaccessConnection.commit(UcanaccessConnection.java:212)
    at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:161)
    at net.ucanaccess.jdbc.Execute.execute(Execute.java:46)
    at net.ucanaccess.jdbc.UcanaccessPreparedStatement.execute(UcanaccessPreparedStatement.java:228)
    ... 41 more
Caused by: net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.7 null
    at net.ucanaccess.jdbc.UcanaccessConnection.flushIO(UcanaccessConnection.java:348)
    at net.ucanaccess.jdbc.UcanaccessConnection.commit(UcanaccessConnection.java:205)
    ... 44 more
Caused by: java.lang.NullPointerException
    at net.ucanaccess.commands.InsertCommand.persist(InsertCommand.java:153)
    at net.ucanaccess.jdbc.UcanaccessConnection.flushIO(UcanaccessConnection.java:315)
    ... 45 more

    thanks!

     

  • Gord Thompson

    Gord Thompson - 2016-10-28

    I am unable to recreate your issue under UCanAccess 3.0.7 using the attached database file and the following code:

    CallableStatement st = conn.prepareCall("{ call qryInsEmployee(?,?,?,?,?,?,?,?,?,?,?) }");
st.setString(1, "Thompson");
st.setString(2, "Gord");
st.setString(3, "Mr");
st.setDate(4, java.sql.Date.valueOf("1901-01-01"));
st.setDate(5, java.sql.Date.valueOf("2011-01-01"));
st.setString(6, "123 Main St");
st.setString(7, "Mytown");
st.setString(8, "Canada");
st.setString(9, "416-555-1212");
st.setString(10, "x123");
byte[] data = new byte[] { 1, 2, 3 };
st.setBytes(11, data);
st.execute();

     
    It would be helpful if you could post a small sample database file that could be used to reliably recreate the issue.

     
    zzz4Igal.accdb

  • Igal Klebanov

    Igal Klebanov - 2016-10-28

    Hey Gord,

    a 13th attachment field (that was not mentioned in the query) was causing this. upon it's removal from the table in the accdb file, everything works.

    so my question is this:

    how do you set an actual attachment type parameter into the statement? what setX method do you use?
    how do you setNull an attachment type parameter? what java.sql.Types do you use?

    thanks!

     

    • Gord Thompson

      Gord Thompson - 2016-10-28

      If you're asking how to manipulate Attachments from a saved query in Access (that could in turn be invoked by a CallableStatement in Java), I'm not sure it's even possible. The Access Database Engine tends to throw errors like "An INSERT INTO query cannot contain a multi-valued field." whenever we try to run DML statements that include "complex" fields (i.e., fields that can have more then one value, like an Attachment field or a multi-value Lookup field).

      However, if you're asking how to insert Attachments using a PreparedStatement then you have to use setObject to pass an array of net.ucanaccess.complex.Attachment objects.

       

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.