Home > OS >  I am trying to insert a simple query into a Microsoft database and i am using UCanAccess
I am trying to insert a simple query into a Microsoft database and i am using UCanAccess

Time:09-30

MY CODE:

String databaseURL  = "jdbc:ucanaccess://Orders.accdb";
        
        try {
            Connection connection = DriverManager.getConnection(databaseURL);
            
            System.out.println("Connected to MS Access database");
            
            String sql = "INSERT INTO Orders (Order Date, Completion Date, Starting Date, Machine, Client, Job Name, Comment) VALUES"
                          "('9/30/2021', '9/30/2021', '9/30/2021', 'Okay', 'Okay', 'Okay', 'Yes you are Okay')";
            
            Statement statement = connection.createStatement();
            int rows =  statement.executeUpdate(sql);
            
            if (rows > 0 ){
                System.out.println("A new thing has been inserted.");
            }
            
            connection.close();
        } catch (SQLException ex) {
            Logger.getLogger(OrdersTestCode.class.getName()).log(Level.SEVERE, null, ex);
        }

MY ERROR

2021-09-30T13:50:45.315 0200  SEVERE  null
net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::5.0.1 user lacks privilege or object not found: ORDER
    at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:230)
    at orderstestcode.OrdersTestCode.main(OrdersTestCode.java:29)
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: ORDER
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.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:65)
    at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:264)
    at net.ucanaccess.jdbc.ExecuteUpdate.execute(ExecuteUpdate.java:48)
    at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:228)
    ... 1 more
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: ORDER
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.ParserDQL.readSimpleColumnName(Unknown Source)
    at org.hsqldb.ParserDQL.readSimpleColumnNames(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)
    ... 7 more

I have put in a breakpoint and it crashes at the int Rows line. I am certain that it might be my SQL statement but im probably wrong. Can someone pls help me figure out what is the problem?

CodePudding user response:

Try with the correct Access SQL syntax:

String sql = "INSERT INTO Orders ([Order Date], [Completion Date], [Starting Date], Machine, Client, [Job Name], Comment) VALUES "
                   "(#9/30/2021#, #9/30/2021#, #9/30/2021#, 'Okay', 'Okay', 'Okay', 'Yes you are Okay')";
  • Related