Can't connect or insert to database using ucanaccess method in applet

Question

While connecting one applet to an Access DB using the jdbc:ucanaccess method, I get the following error:

Firstdb.java:44: error: unreported exception SQLException; 
must be caught or declared to be thrown
stmt.executeUpdate(sql);   
                  ^

The code that I used for the applet is as follows (add() and setBounds() are removed from init()):

public class Firstdb extends Applet implements ActionListener {
    TextField t1, t2;
    Label l1;
    Button b1, b2;
    Connection con;
    Statement stmt;

    public void init() {
        try {
            con = DriverManager.getConnection("jdbc:ucanaccess://H:/test/db.mdb");
            stmt = con.createStatement();
        } catch (Exception e) {
        }
    }

    public void actionPerformed(ActionEvent ae) {
        String sql;

        if (ae.getSource() == b1) {
            sql = "insert into user (Uname) values(" + t1.getText() + ")"; 
            stmt.executeUpdate(sql);
        } else if (ae.getSource() == b2) {
            //do something
        }
    }
}

Note: java version "1.8.0_141"

Why am I getting this error?

Answer 1

Your code has two fatal flaws:

1. value is not a valid SQL keyword. It should be values. [Fixed in subsequent edit to question.]
2. Your dynamic SQL is generating command text with invalid syntax (unquoted string literal).

Also, user is a reserved word (function name), so if you need to use it as a table name you really should enclose it in square brackets.

The proper solution to issue #2 above is to use a parameterized query, e.g.,

sql = "insert into [user] ([Uname]) values (?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, t1.getText());
ps.executeUpdate();

Answer 2

The message

Firstdb.java:44: error: unreported exception SQLException; 
must be caught or declared to be thrown
stmt.executeUpdate(sql);   
                  ^

looks like a compile error so the question is, how do you compile/deploy the applet-classes and where exactly do you see that error message. IDEs like Eclipse are creating class-files even in case of compile errors containing and outputting the error message that you can also see in the IDE. Maybe you fail to update the applet classes and end up testing with the same old classes instead of the changed ones leading to the same message over and over again.

If you deploy the applet as part of a web app, the HTTP server might cache the class-file if the web app has been deployed as WAR-file. Also, browsers tend to cache class-files quite aggressively so you should make sure that the browser's cache is empty each time you start a new test.

Answer 3

It is a compile error which means that you either need to surround your stmt.executeUpdate(sql); with try-catch statement, or your method should throw an SQLException:

 public void actionPerformed(ActionEvent ae) {
    try {
        String sql;

        if (ae.getSource() == b1) {
            sql="insert into user (Uname) values("+t1.getText()+")"; 
            stmt.executeUpdate(sql);
        } else if (ae.getSource() == b2) {
            //do something
        }
    catch (SQLException e) {
        // do something
    }
}

or

public void actionPerformed(ActionEvent ae) throws SQLException {
    String sql;

    if (ae.getSource() == b1) {
        sql="insert into user (Uname) values("+t1.getText()+")"; 
        stmt.executeUpdate(sql);
    } else if (ae.getSource() == b2) {
        //do something
    }
}

EDIT: By the way, I don't see if you are loading or registering Oracle JDBC driver class for UCanAccess before opening the database connection:

// Step 1: Loading or registering Oracle JDBC driver class
try {
     Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
} catch(ClassNotFoundException cnfex) {
     System.out.println("Problem in loading or "
            + "registering MS Access JDBC driver");
     cnfex.printStackTrace();
}

So do you have it or no? If no, you should add it before getting your connection: con = DriverManager.getConnection("jdbc:ucanaccess://H:/test/db.mdb");