Tuesday, December 25, 2012

JDBC example using MS Access

Lets see a simple JDBC example using MS Access.

We are using below mentioned particulars
  • MS Access 2007
  • Java 1.6
Step 1. Create Database using MS Access

Create a New blank database, Name the file EmployeeMgmt.mdb and save it in your desire location. Please see the screen shot below.

Create the columns empId and empName in table employee as shown below. Also add few records which we will be fetching through Java code i.e JDBC.

Step 2. Create DSN

Now when the database and table is created, we will create DSN name which is required to connect using JDBC ODBC driver.

Settings -> Control Panel -> Administrative Tools

Click Data sourses (ODBC)

you will get 'ODBC Data Source Administrator' window as shown below. Click 'System DSN' and add.

Select 'Microsoft Access Driver (*.mdb)' as shown below and click Finish

Here add your desire DSN in our case 'EmployeeDSN', Also don't forget to select the EmployeeMgmt.mdb file as shown below.

You can view your DSN which u have just created, if u wish to edit it you can - by clicking Configure.

Step 3. The Java code

So now the DSN name is created, we will see the actual Java code which will fetch the data using JDBC. Also note that as we are using Java 1.6, 'sun.jdbc.odbc.JdbcOdbcDriver' comes with it so no need to add any JAR file.

Please see the self explanatory Java code below.

Output :

Compile and run the code.

Below are the known errors and exceptions

1. Exception in thread "main" java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6957)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)
at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3073)
at sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.java:323)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:174)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:207)

Solution: Check for correct DSN name

2. java.sql.SQLException: No data found
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7138)
at sun.jdbc.odbc.JdbcOdbc.SQLGetDataString(JdbcOdbc.java:3907)
at sun.jdbc.odbc.JdbcOdbcResultSet.getDataString(JdbcOdbcResultSet.java:5698)
at sun.jdbc.odbc.JdbcOdbcResultSet.getString(JdbcOdbcResultSet.java:354)

Solution: I observed above error when column number was repeated - rset.getString(1)

3. java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]Invalid descriptor index
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6957)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)
at sun.jdbc.odbc.JdbcOdbc.SQLColAttributes(JdbcOdbc.java:2613)
at sun.jdbc.odbc.JdbcOdbcResultSet.getColAttribute(JdbcOdbcResultSet.java:5444)
at sun.jdbc.odbc.JdbcOdbcResultSet.getColumnType(JdbcOdbcResultSet.java:6055)
at sun.jdbc.odbc.JdbcOdbcResultSet.getMaxCharLen(JdbcOdbcResultSet.java:5472)
at sun.jdbc.odbc.JdbcOdbcResultSet.getString(JdbcOdbcResultSet.java:278)

Solution: When there is invalid column number