Unable to INSERT INTO [table name]

[Mama M]

net.ucanaccess.jdbc.UcanaccessSQLException: feature not supported
at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:164)
at ProgramManagement.UserDAOImpl.addUser(UserDAOImpl.java:100)
at ProgramManagement.DAOPatternDemo.main(DAOPatternDemo.java:18)
Caused by: java.sql.SQLFeatureNotSupportedException: feature not supported
at org.hsqldb.jdbc.Util.notSupported(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.executeUpdate(Unknown Source)
at net.ucanaccess.jdbc.ExecuteUpdate.executeWrapped(ExecuteUpdate.java:73)
at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:132)
at net.ucanaccess.jdbc.ExecuteUpdate.execute(ExecuteUpdate.java:56)
at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:162)
... 2 more
net.ucanaccess.jdbc.UcanaccessSQLException: feature not supported
at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:164)
at ProgramManagement.UserDAOImpl.addUser(UserDAOImpl.java:100)
at ProgramManagement.DAOPatternDemo.main(DAOPatternDemo.java:29)
Caused by: java.sql.SQLFeatureNotSupportedException: feature not supported
at org.hsqldb.jdbc.Util.notSupported(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.executeUpdate(Unknown Source)
at net.ucanaccess.jdbc.ExecuteUpdate.executeWrapped(ExecuteUpdate.java:73)
at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:132)
at net.ucanaccess.jdbc.ExecuteUpdate.execute(ExecuteUpdate.java:56)
at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:162)
... 2 more

SQL statement used:
String sql = "INSERT INTO User VALUES('" + user.getUserId()+"'"+", '" + user.getUserName()+
"'"+", '" + user.getUserEmail() +"', " + user.getUserTelNo() +", " + user.getUserCellno() +
", " +"'"+ user.getProjectID()+"'"+", '" + user.getUserLoginName()+"')";

try
{   
    String url = "jdbc:ucanaccess://C://Users//mahlatse dau//Documents//School//2014//PRJ//Database5.accdb";
    dbConnection =  DriverManager.getConnection(url, "", "");

    statement = dbConnection.prepareStatement(sql);
    statement.executeUpdate(sql);
    //statement.execute(sql);

    System.out.println("Record inserted into User table for user: "+ user.getUserName());
}
catch(SQLException e)
{
    e.printStackTrace();
}
finally
{
    if (statement !=null)
    {
        try
        {
            statement.close();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }
    if (dbConnection != null)
    {
        try
        {
            dbConnection.close();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }
}
}

[Marco Amadei]

Your use of PreparedStatement doesn't make sense(prepared statements are parametric and typically with ? placeholders).
Anyway, the exception is thrown because you call

statement.executeUpdate(sql);
instead of
statement.executeUpdate();

so you pass the SQL two time (prepareStatement and executeUpdate) using a Statement interface method.
If you don't want to use a prepared statement, you can also use a simple statement

statement = dbConnection.createStatement()
instead of
statement = dbConnection.prepareStatement(sql);

and all will work as well
(and this option is mandatory if the reference type of the "statement" variable is Statement, instead of PreparedStatement).

[Mama M]

Hi Marco,

I am fairly new in java an ucanaccess. Any way

If I go for statement.executeUpdate(), this are the errors I get still:

net.ucanaccess.jdbc.UcanaccessSQLException: data exception: invalid character value for cast
at net.ucanaccess.jdbc.UcanaccessPreparedStatement.executeUpdate(UcanaccessPreparedStatement.java:95)
at ProgramManagement.UserDAOImpl.addUser(UserDAOImpl.java:103)
at ProgramManagement.DAOPatternDemo.main(DAOPatternDemo.java:18)
Caused by: java.sql.SQLDataException: data exception: invalid character value for cast
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.executeUpdate(Unknown Source)
at net.ucanaccess.jdbc.ExecuteUpdate.executeWrapped(ExecuteUpdate.java:71)
at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:132)
at net.ucanaccess.jdbc.ExecuteUpdate.execute(ExecuteUpdate.java:56)
at net.ucanaccess.jdbc.UcanaccessPreparedStatement.executeUpdate(UcanaccessPreparedStatement.java:92)
... 2 more
Caused by: org.hsqldb.HsqlException: data exception: invalid character value for cast
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.Scanner.convertToNumber(Unknown Source)
at org.hsqldb.types.NumberType.convertToType(Unknown Source)
at org.hsqldb.StatementDML.getInsertData(Unknown Source)
at org.hsqldb.StatementInsert.getResult(Unknown Source)
at org.hsqldb.StatementDMQL.execute(Unknown Source)
at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 8 more
net.ucanaccess.jdbc.UcanaccessSQLException: data exception: invalid character value for cast
at net.ucanaccess.jdbc.UcanaccessPreparedStatement.executeUpdate(UcanaccessPreparedStatement.java:95)
at ProgramManagement.UserDAOImpl.addUser(UserDAOImpl.java:103)
at ProgramManagement.DAOPatternDemo.main(DAOPatternDemo.java:29)
Caused by: java.sql.SQLDataException: data exception: invalid character value for cast
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.executeUpdate(Unknown Source)
at net.ucanaccess.jdbc.ExecuteUpdate.executeWrapped(ExecuteUpdate.java:71)
at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:132)
at net.ucanaccess.jdbc.ExecuteUpdate.execute(ExecuteUpdate.java:56)
at net.ucanaccess.jdbc.UcanaccessPreparedStatement.executeUpdate(UcanaccessPreparedStatement.java:92)
... 2 more
Caused by: org.hsqldb.HsqlException: data exception: invalid character value for cast
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.Scanner.convertToNumber(Unknown Source)
at org.hsqldb.types.NumberType.convertToType(Unknown Source)
at org.hsqldb.StatementDML.getInsertData(Unknown Source)
at org.hsqldb.StatementInsert.getResult(Unknown Source)
at org.hsqldb.StatementDMQL.execute(Unknown Source)
at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 8 more

Declarations:
PreparedStatement statement = null;
statement = dbConnection.prepareStatement(sql);
statement.executeUpdate();

[Mama M]

and then I get this error if I don't use PrepareSatement
net.ucanaccess.jdbc.UcanaccessSQLException: data exception: invalid character value for cast
at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:164)
at ProgramManagement.UserDAOImpl.addUser(UserDAOImpl.java:104)
at ProgramManagement.DAOPatternDemo.main(DAOPatternDemo.java:18)
Caused by: java.sql.SQLDataException: data exception: invalid character value for cast
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:132)
at net.ucanaccess.jdbc.ExecuteUpdate.execute(ExecuteUpdate.java:56)
at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:162)

[Marco Amadei]

It's an invalid cast.
You should be sure of both the orde of all columns involved and their types(the default order is "data" not the "display" order, unless you are using the columnOrder=display connection setting).
In other words, you should specify the columns before their values(that's a SQL good practice):
INSERT INTO User(columnName1,columnName2,columnName3,columnName4,columnName5) values(....)

[Mama M]

thank you so much for you assistance. I am able to insert records into my tables using the method suggested above:-)
You are a genius.

[Áxel Costas Pena]

My vote for alter table support!

[Marco Amadei]

My vote for opening another topic in General Discussion.