UCAExc:::3.0.7 unknown token: error in inserting date

Question

I've a table in my .mdb database. I want to insert date in this table. So I wrote this code for this

public void main(String[] args) {
        // TODO Auto-generated method stub

        PreparedStatement state;
        Connection connect;
        DateFormat dateFormat=new SimpleDateFormat("dd/MM/yyyy");
        Calendar cal=Calendar.getInstance();
        String date=dateFormat.format(cal.getTime());
        System.out.println(date);
        try {
            connect = DriverManager.getConnection("jdbc:ucanaccess://C:/Users/FTK1187/Desktop/eArchiveMaster.mdb","FTEC198","");
            String sql="INSERT INTO LastUpdated (LastUpdatedDate) VALUES (#"+date+"#)"; 
            state=connect.prepareStatement(sql);
            state.execute();
            state.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }


    }

And it gives me this error

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.7 unknown token: 
    at net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:528)
    at org.solr.InsertLastUpdate.main(InsertLastUpdate.java:27)
    at org.solr.updateServlet.doGet(updateServlet.java:40)
    at org.solr.updateServlet.doPost(updateServlet.java:59)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:493)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:800)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:806)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1498)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLSyntaxErrorException: unknown token: 
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
    at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
    at net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:525)
    ... 27 more
Caused by: org.hsqldb.HsqlException: unknown token: 
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.ParserBase.read(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.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.compileStatement(Unknown Source)
    at org.hsqldb.Session.compileStatement(Unknown Source)
    at org.hsqldb.StatementManager.compile(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 30 more

The date format is general date in my database. When I'm doing this

System.out.println(date);

it gives me what I want but when I want to insert in my database I get an error. I don't know how to fix it.

Answer 1

There is no need to use SimpleDateFormat and Calendar. To insert a date we can simply use something like

String sql = "INSERT INTO LastUpdated (LastUpdatedDate) VALUES (?)";
state = connect.prepareStatement(sql);
state.setDate(1, java.sql.Date.valueOf(LocalDate.now()));
state.execute();
state.close();

or, since we are inserting the current date, we can just do

String sql = "INSERT INTO LastUpdated (LastUpdatedDate) VALUES (DATE())";
state = connect.prepareStatement(sql);
state.execute();
state.close();