Access (.mdb) Working with databases with jdbc
Reference Information. Work on an application that will run on an Apache server hosted by Network Solutions. Friend / Client insisted on using Access database instead of SQL database.
Current issue: Wrote a Java test program to make sure I can connect to the database before diving into my head first by writing the entire backend. When I run this code on the Apache server JVM, the final product will be hosted:
import java.sql.*;
import java.io.*;
public class test {
public static void main(String[] args) {
try {
Class driverClass = Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
DriverManager.registerDriver((Driver) driverClass.newInstance());
// set this to a MS Access DB you have on your machine
String filename = new File(".").getCanonicalPath() + "/ITEMS.mdb";
String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
database+= filename.trim(); // add on to the end
// now we can get the connection from the DriverManager
System.out.println(database);
Connection conn = DriverManager.getConnection( database );
} catch (Exception e) {
System.out.println("Error: " + e.getMessage() + " " + e.getLocalizedMessage());
e.printStackTrace();
}
}
}
I am getting null pointer exception at Connection line conn = DriverManager.getConnection (database)
Here is the stack:
java.lang.NullPointerException
at sun.jdbc.odbc.JdbcOdbcDriver.initialize(JdbcOdbcDriver.java:436)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:153)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:207)
at test.main(test.java:20)
To write this test, I used it as my main source: http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=2691&lngWId=2
source to share
String strconnect="jdbc:odbc:DRIVER=Microsoft Access Driver (*.mdb, *.accdb);DBQ="
Sring filename="some path";
StringBuilder a =new StringBuilder();
a.append(strconnect);
a.append(filename);
String db = a.toString();
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection c= DriverManager.getrConnection(db,"","")
Statement stmt = c.createStament();
String query = "some query";
stmt.executeQuery(query);
c.close();
Never get the canonical path, either give the path to the access file or use JFile Explore and get the path
also don't forget to close the connection
source to share
Since you mention apache, I will assume that the server is running Linux or BSD.
In this case, please read the following:
- Connecting to an Access Database from Linux
- Good Linux ODBC drivers for access are expensive! ($ 850 for one car!)
There are no default drivers for Access provided on Linux and ODBC JDBC won't work if you don't install it.
Unless there is a particular and compelling reason to use an Access database as a backend on a website on a linux server, let your friend know that this is not technically or even economically viable.
If you need a lightweight database on the server, use SQLite : it's free, there is good support for connecting to it from Java on Linux , there is good snap-in (even browser plugins ), and you can always convert that database to Access if your friend wants get an Access backup once in a while.
Otherwise, go for PostgreSQL or MySQL like everyone else (usually for a good reason).
source to share