0

I am using Java with Microsoft Access through an ODBC driver. When I insert a duplicate entry for primary key it gives me an error: java.sql.SQLException: General error. I want to show a message to the user that this record already exists, but I think that this exception can be thrown by ODBC in some other cases also. So I found that there are error codes against each message (ref), but I found no error code for primary key violation. Can anyone tell me what error code is for primary key violation for ODBC with MS Access?

Here is basic code

String qry = "INSERT INTO customers VALUES ('" + txtReg.getText()
            + "' ,'" + txtName.getText() + "', '" + txtCity.getText() + "' ,'" + txtCell.getText() + "')";

try {

    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con = DriverManager.getConnection("jdbc:odbc:MyDB");

    Statement st = con.createStatement();
    st.executeQuery(qry);
    st.close();
    con.close();

} catch (ClassNotFoundException ex) {
    JOptionPane.showMessageDialog(this, "Error: " + ex, "Error!", JOptionPane.ERROR_MESSAGE);
} catch (SQLException ex) {
    JOptionPane.showMessageDialog(this, "Error: " + ex, "Error!", JOptionPane.ERROR_MESSAGE);
}

These txtName and so on are JTextFields. Here is complete Stack trace

connected
st created

Error code: 0
SQLState: S1000
Messsage: General error

java.sql.SQLException: General error
    at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6986)
    at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)
    at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3110)
    at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:338)
    at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(JdbcOdbcStatement.java:288)
    at gui.InsertFileCode.btnInsertActionPerformed(insertFileCode.java:399)
Shahrzad
  • 1,062
  • 8
  • 26

1 Answers1

3

The problem is in this line st.executeQuery(qry);

executeQuery(query) method is used mostly for SELECT statement and it returns the in form of ResultSet object.

Sence the statement is INSERT statement you have to use executeUpdate(query) , this method is generally used by INSERT, UPDATE, And DELETE statements. When table updated successfully then it returns 1. For example

int result = st.executeUpdate(qry);
System.out.println(result);

UPDATE:

Due to the comments, I've fegured out you have another problem rather than the SQL statement. You must beware of when using java to Ms Access, you're actually connecting to a middleware server, so that, you must expect un-expected exception(s) while running the SQL statement, for example:

CRATE TABLE FOO (ID varchar (50) NOT NULL , NAME varchar (255) DEFAULT NULL)

This query runs on SQLite and MySQL (maybe SQL Server too as I didn't test it), gives Syntex error on Access, as DEFAULT NULL should be removed for running the statement.A nd maybe there are many other problem you have to prepare facing it with Access "database" file.

So, I am telling you to leave it, MS Access is suitable for its users, not for us as a programmer, we have to find best-general way because we must consider that some user uses this application that don't know anything neither about Programming Language nor Database.

Then, what should I do?

I am not an expert in database, but take my advice :

  • If your application need to share its database: MySQL, Oracle and SQL Server used for that purpose.
  • If your application is used only for some purposes and not need to share its records to other users, use an actually serverless database engine such as SQLite. This seems to be the best option for you as it's a file like Access, only needs an external driver for Java, see this.

I think there is a FireFox extension for designing the SQLite database if you search on google maybe you find it.

Community
  • 1
  • 1
Azad
  • 5,047
  • 20
  • 38
  • It did not solved the problem. Still the same error occurs when I give duplicate key value. My problem is to know what is error code for ODBC MS Access when it received a query with PK violation. Thanks for mentioning `finally` but I am already using this, but I did not write here for code simplicity. I am pasting complete stack trace. May be this is helpful. – Shahrzad Jul 09 '13 at 12:18
  • Is `st.executeUpdate(qry)` returns value other than 1 if it find a PK violation? But I debugged the code it jumps to the `SQLException catch block` when it reaches the `st.executeUpdate(qry);` statement. Means this statement is throwing the exception. I pasted the stack trace by using `st.executeUpdate(qry)` – Shahrzad Jul 09 '13 at 12:25
  • Yes, I was confused because you didn't mention your code were for demonstration that's why I corrected it. – Azad Jul 09 '13 at 13:07
  • About the `general error`, it's hard to say what causes this problem, maybe some java exception which is bundled and displayed as general error, or maybe a suitable mistake. There is a lot of things causes this error, sometimes data type, in your case, duplication primary key, that's why it's hard to tell. – Azad Jul 09 '13 at 13:11
  • Then how can I know that there is a violation of primary key?Any approach. – Shahrzad Jul 11 '13 at 10:54
  • 1
    Yes, leave MS-Acees, if your application is large, use MySQL, otherwise, the use SQLite database. See the answer of [**this question**](http://stackoverflow.com/questions/15232755/how-to-connect-with-mysql-database-file-sql-using-directory-path-like-ms-acc) for more detail. – Azad Jul 11 '13 at 13:21
  • +1 Yes, I am also thinking the same that MS Access is so stupid. Thanks for Sqlite. I am building a small standalone application, is this a correct approach to use sqlite, I was using Access due to its file like features and no need for backup from OS drive? Is sqlite have same features? – Shahrzad Jul 12 '13 at 03:54
  • Great! Yes database is not shared b\w any one. You are right problem is with `ODBC`, hhh at same time I found that executing the same query inside Access gives the proper error but not using ODBC, thanks any way. I want that there is never need for backup, even the OS crashes, as I placed the access file in the D: drive (any one other than OS). Can I do same with the sqlite? I searched but did not getting appropriate answer. – Shahrzad Jul 12 '13 at 13:54
  • @aquestion: What do want is save the `.sqlite` file to **D:** drive? Yes, you can. And about how to it, you can ask a different question as maybe it will be useful also for someone else. – Azad Jul 12 '13 at 14:01
  • @ Azad: very helpful, I made a project from your answer – Shahrzad Mar 19 '14 at 14:27