Inserting data via ResultSet.insertRow()

[Frank]

Hi,
I'm trying to insert data into MS Access file through ResultSet.insertRow() command but it fails. I used three methods to insert data, they are listed below. While insert() and insertPS() works fine, insertRS() fails on irs.insertRow(). The table article has 4 columns (volume, number, title, page) and there is a primary key on columns (volume, number). Variable tableModel holds connection to database and provides with statements. Connection is setAutoCommit(false) that's why I commit the transaction.

public int insert() throws SQLException {
    String insert = "INSERT INTO article (volume, number, title, page) VALUES ("
            + "'Volume 28, Issue 4, Pages 315-658 (October 2012)', "
            + "1, "
            + "'Growing old in rural places', "
            + "'Pages 315-317'"
            + ")";

    Statement is = tableModel.getStatement(
            ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

    int numOfRows = is.executeUpdate(insert);
    is.getConnection().commit();
    return numOfRows;
}

public int insertPS() throws SQLException {
    PreparedStatement ps = tableModel.getPreparedStatement(
            "INSERT INTO article (volume, number, title, page) VALUES (?, ?, ?, ?)",
            ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

    ps.setString(1, "Volume 28, Issue 4, Pages 315-658 (October 2012)");
    ps.setInt(2, 1);
    ps.setString(3, "Growing old in rural places");
    ps.setString(4, "Pages 315-317");

    int num = ps.executeUpdate();
    ps.getConnection().commit();
    return num;
}

public void insertRS() throws SQLException {
    Statement insertStatement = tableModel.getStatement(
            ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

    ResultSet irs = insertStatement.executeQuery(
            "SELECT article.* FROM article");

    irs.moveToInsertRow();
    irs.updateString("volume", "Volume 28, Issue 4, Pages 315-658 (October 2012)");
    irs.updateInt("number", 1);
    irs.updateString("title", "Growing old in rural places");
    irs.updateString("page", "Pages 315-317");
    irs.insertRow();
    insertStatement.getConnection().commit();
}

And here is the printStackTrace() after calling method insertRS():

net.ucanaccess.jdbc.UcanaccessSQLException: java.lang.NullPointerException java.lang.NullPointerException
at net.ucanaccess.jdbc.UcanaccessResultSet.insertRow(UcanaccessResultSet.java:836)
at net.nohel.utest.UCanAccessBuilder.insertRS(UCanAccessBuilder.java:77)
at net.nohel.utest.UCanAccessTest.main(UCanAccessTest.java:33)
Caused by: java.sql.SQLException: java.lang.NullPointerException java.lang.NullPointerException
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCResultSet.performInsert(Unknown Source)
at org.hsqldb.jdbc.JDBCResultSet.insertRow(Unknown Source)
at net.ucanaccess.jdbc.UcanaccessResultSet.insertRow(UcanaccessResultSet.java:834)
... 2 more
Caused by: org.hsqldb.HsqlException: java.lang.NullPointerException
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.result.Result.newErrorResult(Unknown Source)
at org.hsqldb.StatementResultUpdate.execute(Unknown Source)
at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
at org.hsqldb.Session.executeResultUpdate(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 6 more
Caused by: java.lang.NullPointerException
at net.ucanaccess.jdbc.UcanaccessConnection.getCtxConnection(UcanaccessConnection.java:88)
at net.ucanaccess.triggers.TriggerInsert.fire(TriggerInsert.java:33)
at org.hsqldb.TriggerDef.pushPair(Unknown Source)
at org.hsqldb.Table.fireTriggers(Unknown Source)
at org.hsqldb.StatementDML.insertSingleRow(Unknown Source)
at org.hsqldb.StatementResultUpdate.getResult(Unknown Source)
... 10 more

I use insertRS() in different project and I get almost the same result but the cause is different: Instead of java.lang.NullPointerException there is net.ucanaccess.triggers.TriggerException.
I went through the code in Jackcess and HSQLDB but I couldn't find the cause of the failure because I'm not very expert in Java.
I would be very grateful for any help. I expect problem is on my side but now I have no idea where to start.

[Marco Amadei]

I'm so sorry, I read your post just an hour ago (I don't know why it was in the moderate queue, this forum is without moderation) .
Well, I've pached the bug you reported in the svn trunk (patched class net.ucanaccess.jdbc.UcanaccessPreparedStatement and added class net.ucanaccess.jdbc.InsertResultSet ).
Unfortunatly I will post the 2.0.5 only in the first days of may, so if the rebuilding of ucanaccess.jar is problematic for you, you can contact me directly at amadei.mar@gmail.com.
In any case, please, let me know your findings.
Cheers Marco

In any case, please let me know your findings.

[Frank]

Hi Marco,
first of all, thanks a lot for your response. I really appreciate your effort to help me as quickly as posible.
I might be able to build the jar file but I'm not in a hurry so I will wait till May.
Cheers Frank

[Marco Amadei]

I've released the 2.0.4.1. which fixes this bug.
Also I added a specific junit test in the net.ucanaccess.test.CrudTest class.
Thanks!
Marco

[Frank]

Hi Marco,
I've downloaded UCanAccess 2.0.5 and tested it. Unfortunately I have come across two issues:

1) the method insertRS() works fine only if all columns are set! The column page (in my example) is nullable so the following code

  irs.moveToInsertRow();

  irs.updateString("volume", "Volume 28, Issue 4, Pages 315-658 (October 2012)");

  irs.updateInt("number", 1);

  irs.updateString("title", "Growing old in rural places");

  //irs.updateString("page", "Pages 315-317");

  irs.insertRow(); // line 90

throws this exception:

net.ucanaccess.jdbc.UcanaccessSQLException: invalid cursor state: all column must be set before insert
at net.ucanaccess.jdbc.UcanaccessResultSet.insertRow(UcanaccessResultSet.java:836)
at net.nohel.utest.UCanAccessBuilder.insertRS(UCanAccessBuilder.java:90)
at net.nohel.utest.UCanAccessTest.main(UCanAccessTest.java:30)
Caused by: java.sql.SQLException: invalid cursor state: all column must be set before insert
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCResultSet.performInsert(Unknown Source)
at org.hsqldb.jdbc.JDBCResultSet.insertRow(Unknown Source)
at net.ucanaccess.jdbc.InsertResultSet.executeWrapped(InsertResultSet.java:37)
at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:121)
at net.ucanaccess.jdbc.InsertResultSet.execute(InsertResultSet.java:32)
at net.ucanaccess.jdbc.UcanaccessResultSet.insertRow(UcanaccessResultSet.java:834)
... 2 more
Caused by: org.hsqldb.HsqlException: invalid cursor state: all column must be set before insert
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
... 9 more

I mention that metod insertPS() works fine both with all columns set and with nullable columns not set.

2) the method insert() doesn't work at all. It throws

net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: )
at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:162)
at net.nohel.utest.UCanAccessBuilder.insert(UCanAccessBuilder.java:58)
at net.nohel.utest.UCanAccessTest.main(UCanAccessTest.java:31)
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: )
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.executeUpdate(Unknown Source)
at net.ucanaccess.jdbc.ExecuteUpdate.executeWrapped(ExecuteUpdate.java:73)
at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:121)
at net.ucanaccess.jdbc.ExecuteUpdate.execute(ExecuteUpdate.java:56)
at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:160)
... 2 more
Caused by: org.hsqldb.HsqlException: unexpected token: )
at org.hsqldb.error.Error.parseError(Unknown Source)
at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
at org.hsqldb.ParserBase.checkIsIdentifier(Unknown Source)
at org.hsqldb.ParserDQL.readColumnOrFunctionExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadSimpleValueExpressionPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadValueExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadRowElementList(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadValueExpressionOrNull(Unknown Source)
at org.hsqldb.ParserDQL.XreadValueExpressionWithContext(Unknown Source)
at org.hsqldb.ParserDQL.readRow(Unknown Source)
at org.hsqldb.ParserDQL.XreadContextuallyTypedTable(Unknown Source)
at org.hsqldb.ParserDML.compileInsertStatement(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
at org.hsqldb.Session.executeDirectStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 8 more


[Marco Amadei]

Hi Frank,
about point 1:
if page column is null, you must set it to null anyway:
irs.updateString("page",null);(this depends on the mirror hsqldb implementation)

about point 2:

I've just tried the following code, after having created a table article: it works fine and prints "nr row:1":

package net.ucanaccess.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Prova {

public static void main(String[] args) throws ClassNotFoundException, SQLException {
    System.out.println("nr row:" +new Prova().insert());
}

public int insert() throws SQLException, ClassNotFoundException {
    String insert = "INSERT INTO article (volume, number, title, page) VALUES ("
            + "'Volume 28, Issue 4, Pages 315-658 (October 2012)', "
            + "1, "
            + "'Growing old in rural places', "
            + "'Pages 315-317'"
            + ")";  
    Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
    Connection conn= DriverManager.getConnection("jdbc:ucanaccess://c:\\db\\volume.mdb");
    Statement is = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

    int numOfRows = is.executeUpdate(insert);
    is.getConnection().commit();
    return numOfRows;
}

}

Please, let me know your findings.

[Frank]

Hi Marco,

as far as point 2 is concerned, I'm sorry for confusing you. I made a mistake in SQL statement and that's why it didn't work. I tested your code and it works fine. Then I checked my code again and found the mistake.

As far as the point 1 is concerned I'm a bit disappointed. My application converts data from XML documents to a SQL scheme but I know the scheme only during the run time. I cannot use prepared statements and inserting of data via ResultSet is very convenient for me because I don't need to set nullable columns. At least JDBC drivers for MySQL and Oracle (and JDBC ODBC bridge driver as well) don't require a setting nullable columns.

Rewriting my code is possible but I don't want to do it because of one special JDBC driver.
Anyway I will think about other solutions. I woudl be grateful for any ideas.
Thanks a lot for your effort.

[Marco Amadei]

Hi Frank,
as I said, this is the behaviour of hsqldb, which I consider a great project/product.
In fact hsqldb throws the SQLException with the message "all column must be set before insert".
I can't change this behaviour
BUT
if you are handling an updatable ResultSet you could get column names from the ResultSetMetaData and, after having setted all the values got from the xml, set to null the columns which have not been set before. You don't need to change anything, only you have to add 2 lines of code. Notice that ResultSetMetaData are uppercased.
That's not so much for a special driver.
Cheers Marco