Problem in updating/deleting data from MS Access Database

[Preetish Madalia]

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(
"jdbc:odbc:Driver={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 = "jdbc:odbc:Driver={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]

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=?"

[Paul Campbell]

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]

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.

[Paul Campbell]

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]

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]

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]

Thanks Paul,
I got it.