PreparedStatement, ACCESS and jdbcodbc

From: Paul Cooper (paul.cooper_at_Nobasspam.ac.uk)
Date: 11/10/04

  • Next message: Paul Cooper: "Re: PreparedStatement, ACCESS and jdbcodbc" 
    Date: Wed, 10 Nov 2004 11:38:21 +0000

    Dear All,

    I have a situation where I am using MS Access as the database package
    for an application I am developing. This is an interim situation for
    development only - I will be moving it to PostgreSQL in due course,
    but cannot at present. I am using the JdbcOdbc driver, with the
    connection set up as follows:

    //
    String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
    String dbUrl = "jdbc:odbc:Driver={MicroSoft Access
    Driver(*.mdb)};DBQ=C:/femgis/fm.mdb";
    String user = "";
    String pass = "";
    Class.forName(driverName);
    Connection dbConn = DriverManager.getConnection(dbUrl, user, pass);

    This connection works fine in all other contexts I am using it in.

    Anyway, I am executing an insert statement to generate records that
    include an key automatically generated by the DBMS. I would like to
    retrieve the key for further processing.

    First, my INSERT statement works fine using Statement.execute(String).
    However, I can find no means of returning the autogenerated value!

    I have tried using
    Statement.execute(String,Statement.RETURN_GENERATED_KEYS), followed by
    Resultset rs = myStatement.getGeneratedKeys(), and various attemtps
    with preparedStatements, for example:

    myStmt.execute(sqlString,Statement.RETURN_GENERATED_KEYS);
    ResultSet thisRs = myStmt.getGeneratedKeys();
    System.out.println(thisRs.getString(1));

    but every time I get the following stack trace:

    java.lang.UnsupportedOperationException
            at
    sun.jdbc.odbc.JdbcOdbcStatement.getGeneratedKeys(JdbcOdbcStatement.java:1460)
            at
    FeLocationForm$okButtonListener.actionPerformed(FeLocationForm.java:599)
            at
    javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1764)

    Is what I am attempting simply impossible with the development set up
    I am using? Is it a consequence of the JdbcOdbc bridge, and if so is
    there a better JDBC driver for Access (free, as I have no need to
    build this into my final solution)?

    Many thanks

    Paul

  • Next message: Paul Cooper: "Re: PreparedStatement, ACCESS and jdbcodbc"