aspose file tools*
The moose likes JDBC and the fly likes Problem in updating/deleting data from MS Access Database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem in updating/deleting data from MS Access Database" Watch "Problem in updating/deleting data from MS Access Database" New topic
Author

Problem in updating/deleting data from MS Access Database

Preetish Madalia
Ranch Hand

Joined: Dec 12, 2007
Posts: 37
Guys, i am not able to update/delete data from in a table in MS Access Database.


import java.sql.Connection;
import java.util.Date;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.util.Calendar;


public class JdbcConnection {

public static final String INSERT_QUERY =
"insert into USER_SUMMARY(user_id,login_time,login_date,LOGIN_STATUS) values(?,?,?,?)";
public static final String SELECT_QUERY =
"select * from user_summary";

public static final String FIND_USER=
"select user_id from user_summary where login_status = 'Y'";

public static final String DELETE_USER_STATUS =
"DELETE user_summary.* FROM user_summary where user_id=?";




public static void main(String[] args) {

try {
Driver d = (Driver)Class.forName
("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
Connection conn = DriverManager.getConnection(
"jdbcdbcriver={Microsoft Access Driver (*.mdb)};DBQ=E:/DATABASE/Database3.mdb"
);
/*System.out.println("Begining conn");
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String filename = "E:/DATABASE/Database1.mdb";
String database = "jdbcdbcriver={Microsoft Access Driver (*.mdb)};DBQ=";
database+= filename.trim() + ";PWD=test}";
Connection conn = DriverManager.getConnection( database ,"Preetish_Madalia","preetish"); */

System.out.println("Connection Obtained : " + conn );



PreparedStatement ps3 = conn.prepareStatement(DELETE_USER_STATUS);
ps3.setInt(1,1);

int result2 = ps3.executeUpdate();
System.out.println(result2);


} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch(SQLException sqe){
sqe.printStackTrace();
System.out.println(sqe);
} catch(Exception e){
e.printStackTrace();
}


}

}


The above code runs fine.. also the value of executeUpdate() comes out to be one(i.e. one row is to be deleted) . However, the data is not getting deleted from the table.
Is there any setting we need to set in order to allow the deletion/updation of data in the database when we fire a query from our program.

Pls. look into this problem.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30537
    
150

Preetish,

I haven't used Access, but does the following query work at the command line? I'm not sure what user_summary.* would do. user_summary is the schema. How can * be the table name?
"DELETE user_summary.* FROM user_summary where user_id=?"


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
The correct format for a SQL delete (even in MS Access) is:

DELETE FROM table WHERE conditional statement

If you omit the where clause you will delete all rows. If you actually want to delete all rows, you should use a Truncate Table DDL command instead.
[ December 14, 2007: Message edited by: Paul Campbell ]
D Rog
Ranch Hand

Joined: Feb 07, 2004
Posts: 472

I believe that difference between delete and truncate will be the first returns a number of affected rows. It can be useful in some cases.


Retire your iPod and start with HD Android music player Kamerton | Minimal J2EE container is here | Light weight full J2EE stack | and build tool | Co-author of "Windows programming in Turbo Pascal"
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by D Rog:
I believe that difference between delete and truncate will be the first returns a number of affected rows. It can be useful in some cases.


It may give you a count, but there are more efficient ways to get that information.

If you need the number of rows in a table (and again we are talking about a delete table omitting the where clause - i.e., all rows deleted). You should query the information schema to retrieve number of rows in a table (in oracle it is found in the all_tables view).


� Delete table is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow.

� Truncate table also deletes all the rows in a table, but it won�t log the deletion of each row, instead it logs the de-allocation of the data pages of the table, which makes it faster. Of course, truncate table cannot be rolled back.

� Truncate table is functionally identical to delete statement with no �where clause� both remove all rows in the table. But truncate table is faster and uses fewer system and transaction log resources than delete.

� Truncate table removes all rows from a table, but the table structure and its columns, constraints, indexes etc., remains as it is.

� If you want to remove table definition and its data, use the drop table statement.

� You cannot use truncate table on a table referenced by a foreign key constraint; instead, use delete statement without a where clause. Because truncate table is not logged, it cannot activate a trigger.

� Truncate table may not be used on tables participating in an indexed view.
[ December 15, 2007: Message edited by: Paul Campbell ]
Preetish Madalia
Ranch Hand

Joined: Dec 12, 2007
Posts: 37
Hi,
Actually I got this query from the microsoft access itself.
I constructed it in MS Access and ran it there its working fine there.
But, when I am trying to use the same in my code its not working.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

I don't see anywhere in your code where you commit or close the connection. Typically Access doesn't display updates (including inserts and deletes) to other applications until the code that did the update commits the change. If you don't commit the change explicitly, or implicitly by closing the connection, then Access doesn't do it.
Preetish Madalia
Ranch Hand

Joined: Dec 12, 2007
Posts: 37
Thanks Paul,
I got it.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Problem in updating/deleting data from MS Access Database