|
|
Re: Help need integrating program with MS Access
|
Posted: Apr 14, 2005 11:49 PM
|
|
This is a big topic, I can just give you the basics. Don't expect anyone to update your program, because as long as you don't fully understand it, you won't get far. You have to try it for yourself.
First you need a JDBC-Access Bridge. I purchased one to connect to the MSDB via ADO, but I know there are free bridges for accessing via ODBC.
You need to study the basics of the SQL language and the API of the "java.sql" package, espescially of Connection, Statement, ResultSet, maybe also SQLException.
Reading data ============ 1. You get the Connection object from the bridge. 2. Once you have that, create a Statement, 3. then a ResultSet
Connection con = [driver].createConnection or whatever.
Statement stmt = con.createStatement (optionalparameters);
ResultSet rs = stmt.executeQuery();
while (rs.next()) { //as long as there is more data.
//readdata
}
stmt.close();
con.close();
this is an example of a sql select command:
select name, id, numfield, somethingelse from tablename where name = 'noname' or numfield = 14;
It's allway the same sceme: SELECT fields FROM table WHERE condition ORDER BY fields;
If you pass the string through jdbc, you must elimiate the last character (';')
You can select from multiple tables by JOINing them on specific fields, you can GROUP if you want t use SUM or MAX and much more, but for a beginner this should be enough for now.
Allmost every jdbc-command throws a SQLException, so you must catch it all the times.
Adding data =========== The Sql Syntax is like:
insert into tablename(fieldlist) values (valuelist)
Connection con = [driver].createConnection or whatever.
Statement stmt = con.createStatement (optionalparameters);
int nUpdates = stmt.executeUpdate(...);
stmt.close();
con.close();
Updating data ============= Just like adding data, with a different syntax:
update tablename set field1 = value, field2 = value where id = 16 Don't use where if you want to update all rows.
Hints: Create the connection object when your program starts and close it at program exit, because it takes some time to create. Creating a statement is also slow. Try to use it as long as possible before closing it.
I created for my personal use a class wich does all those things. It offers a readSql and a writeSql method. Behind those two methods there are 2 different statements, one is optimized for reading. I create the statements at program start and destroy them at program end. If the connection breaks up during runtime, these methods recreate the connection and statements. So in my program code you won't find stmt.close(), con.close() or But don't forget to close the resultsets after reading all data.
There exists preparedstatement wich works great if you need to update a lot lof lines.
|
|