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=?";
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.
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=?"
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 ]
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 ]
posted 10 years ago
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.
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.