Java Database Connectivity (JDBC)

With MS ACCESS as Database u can't use preparedStatement.
u can do it with any other DB like Oracle,DB2 which make 'query execution plan' before executing it.

JDBC driver version can be obtained by calling "getMajorVersion() " and "getMinorVersion() " on the Driver object.
Refer JavaDoc for Driver interface for getting more methods that give info @ driver.

thanks

Hi,
Look, first of all, it is better to switch for another kind of databases, as a perspictive, I do recommend th MySql engine.
If you are using MS Access, that means you are running your application on a Windows platform, then, you have not to use any JDBC- ODBC drivers, because Access is an ODBC database.
You have to define your database in the Windows platform without using any kind of drivers, I do not know what Windows version you are using, but I will suppose you are using 2k version:

1. Go to Control Panel, Administrative Tools, Data Sources (ODBC).
2. Because you are building an application, go to System DNS tab.
3. Click : Add, and pick up Microsoft Access Driver (*.mdb)
4. Type in Data Source Name, the database name you like to, (e.g. mydatabase), you will use this name in your code to access the real database.
5. Click on the button: Select, and point to your mdb file.
6. That's it!
Good luck, and do not forget my advice, use MySql Engine www.mysql.com, it is now the most used over the world. Another thing, MySql is one of the good friends of Java :)

This is completely incorrect. I have used PreparedStatement with MS Access and the JDBC-ODBC bridge with no problem.

I expect your problem would go away if you used a separate PreparedStatement to execute that query, instead of trying to use an existing one that already has an update built into it.

MySQL might be a fine alternative, but it's not ANSI-SQL compliant. It leaves out some very important functionality (e.g., no referential integrity support for foreign keys).
ODBC is a connectivity technology. You can connect to Access using ODBC, but in this case he's using JDBC via the bridge driver. But that doesn't mean Access is an "ODBC" database.
This step is also totally unnecessary if you use a connection string like this:

String url  = "jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\\path\\to\\your\\data.mdb";

I don't believe this unless you've got some market share numbers to back it up. Oracle is the leader in RDBMS market share.
MySQL has a JDBC driver to allow Java to access it, but so do most other viable database vendors. It's no more or less Java-friendly than anyone else.

DrClap is quite correct - you can use PreparedStatements with Access.

You're getting this exception because you're trying to have Access return the generated keys from your INSERT, using the executeUpdate() method that returns a ResultSet. That's a JDBC 3.0 function, and the JDBC-ODBC bridge only supports JDBC 1.0.

I just tried to do this earlier this week, and I got that exception, too. The bridge driver doesn't support that method.

The MySQL JDBC driver, however, does.

It doesn't mean you can't get the generated key. You can do it like this:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

public class AutoIncrementTester
{
    public static final String DEFAULT_DRIVER   = "sun.jdbc.odbc.JdbcOdbcDriver";
    public static final String DEFAULT_URL      = "jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=Person.mdb";
    public static final String DEFAULT_USERNAME = "admin";
    public static final String DEFAULT_PASSWORD = "";

    public static void main(String [] args)
    {
        if (args.length > 1)
        {
            Connection connection = null;

            try
            {
                Class.forName(DEFAULT_DRIVER);
                connection  = DriverManager.getConnection(DEFAULT_URL, DEFAULT_USERNAME, DEFAULT_PASSWORD);
                int pk      = AutoIncrementTester.insert(connection, args[0], args[1]);
                System.out.println("pk: " + pk + " first: " + args[0] + " last: " + args[1]);
            }
            catch (SQLException e)
            {
                e.printStackTrace();
                System.err.println("SQL state: " + e.getSQLState());
                System.err.println("SQL error: " + e.getErrorCode());
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
            finally
            {
                try { if (connection != null) connection.close(); } catch (Exception e) {}
            }
        }
    }

    public static int insert(Connection connection, final String first, final String last)
        throws SQLException
    {
        String sql = "INSERT INTO PERSON(FIRST_NAME, LAST_NAME) VALUES(?, ?)";

        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, first);
        statement.setString(2, last);
        int numRowsAffected = statement.executeUpdate();

        statement.close();

        int pk = getPrimaryKey(connection);

        return pk;
    }

    public static int getPrimaryKey(Connection connection) throws SQLException
    {
        int pk = -1;

        String sql = "SELECT @@IDENTITY";
        Statement statement = connection.createStatement();
        ResultSet rs    = statement.executeQuery(sql);
        while (rs.next())
        {
            pk = rs.getInt(1);
        }

        rs.close();
        statement.close();

        return pk;
    }
}

%

Hi duffy,
Thank you for the explaination, that was very kind of you. You must be an expert.
This is not a good thing to say, this is a scientific forum and not a place to attack people.

Good luck.

Not as expert as some folks here, but I happened to think about the OP's problem just the other day.
Exactly. Scientists base conclusions on data, and I'm asking you to provide some.

Here's mine:

http://websphereadvisor.com/doc/14246
I'm not attacking anyone, just asking you to substantiate an unsupported claim. I also want to make sure that you don't mislead someone who might not know enough to make an informed choice.

MySQL has open source and for-fee versions. I'm betting you're referring to the open source version of the code. Those numbers will be hard to come by. Every download doesn't necessarily end up in a production app. Lots of folks, including me, have downloaded MySQL for education purposes.

%

Besides which, market share is irrelevant. The OP may have good reasons for using MS Access, and converting from Access to MySQL is not something you can do in an afternoon (speaking as somebody who has actually done that conversion). And for example, Internet Explorer has the largest market share among browsers but that doesn't mean that people should be exhorted to use it.

I agree with DrClap's comment.

I just wanted to respond to the phrase "it is now the most used over the world". I believe it's hyperbole.

%

"Internet Explorer has the largest market share among browsers but that doesn't mean that people should be exhorted to use it."

By the by, I downloaded Firefox a couple of weeks ago and installed it at home. Very nice, indeed. No more IE for me on my home desktop.

%