how to write to ms access database using jsp

Question

I am trying to INSERT some data in ms access database using jsp but it shows SQLException. The query has a subquery. I have tried to run the query in access and the query executes fine. I am not sure why the jsp is throwing exception. I have checked my query over and over again for 3 hours now but still no use. Can anyone help??

    try {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();

        //Creating new statement
        Connection conn = DriverManager.getConnection("jdbc:odbc:accdbJava");

        String uName = ((UserInfo) session.getAttribute("userInfo")).getUserName().trim();

        //Creating statement
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO Message (User_ID, Heading, Body, DatePosted) VALUES ('(SELECT ID FROM User WHERE UserName = '" + uName + "')', '" + messageItem1.getSubject() + "', '" + messageItem1.getMessage() + "', '" + messageItem1.getDatePosted() + "');");

        //Executing the update
        stmt.executeUpdate();

        //Closing connection, statement
        stmt.close();
        conn.close();
    }
    catch(Exception e) {
        e.printStackTrace();
    }

The exception is as follows:

*java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''(SELECT ID FROM User WHERE UserName = 'hrai')''. at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.SQLPrepare(Unknown Source) at sun.jdbc.odbc.JdbcOdbcConnection.prepareStatement(Unknown Source) at sun.jdbc.odbc.JdbcOdbcConnection.prepareStatement(Unknown Source) at PostMessage.addMessageToDatabase(PostMessage.java:118) at PostMessage.doPost(PostMessage.java:55) at javax.servlet.http.HttpServlet.service(HttpServlet.java:637) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489) at java.lang.Thread.run(Unknown Source) *

Answer 1

Try the following changes

note this will work for non-Access databases. for Access see the edit below.

String SQLQuery = "INSERT INTO Message (User_ID, Heading, Body, DatePosted) " +
          "VALUES ((SELECT ID FROM USER WHERE username=?), ?, ?, ?)";

PreparedStatement stmt = conn.prepareStatement(SQLQuery);
stmt.setString(1, uName);
stmt.setString(2, messageItem1.getSubject());
stmt.setString(3,messageItem1.getMessage());
stmt.setDate(4,messageItem1.getDatePosted());

no semi-colon needed at the end of the query. By using the Prepared statement properly you can simplify your query and the string building you do.

On a side note, this is probably all being done inside a scriptlet (<%%>) tag which isn't good practice. You may want to move this code to a back-end java class.

Edit - After seeing your next error, I opened Access to test the query. Your original query suffers from the problem that you have an extra set of single quotes around your inner select.

Values ('(SELECT ID FROM User WHERE UserName = 'foo')'...

that extra set of single quotes around the select gives the missing operator exception. If you remove those than you get the error from my query. So the two queries are near identical.

This query would work with another database (MySQL, SQL Server, etc) but Access doesn't follow all the SQL rules so it can be a bit hit or miss sometimes.

Try the following modification to my original answer to perform your inserts

String SQLQuery = "INSERT INTO MESSAGE (User_ID, Heading, Body, DatePosted) " +
    "SELECT ID, ?, ?, ? " + 
    "FROM USER " + 
    "WHERE username=?";

PreparedStatement stmt = conn.prepareStatement(SQLQuery);

stmt.setString(1, messageItem1.getSubject());
stmt.setString(2,messageItem1.getMessage());
stmt.setDate(3,messageItem1.getDatePosted());
stmt.setString(4, uName);

note that I moved around the arguments some. And there is no VALUES keyword used. This query worked for me in Access