Tutorial :SQL INSERT system date into table



Question:

I have created a table called "myTable" with a column called CURRENT_DATE and it's data type is "DATE". When I programatically update my table, I also want to place a timestamp or system date in the CURRENT_DATE field. A portion of what I am doing is shown below, but it is not working. I would think this would be easy, but... Can you help?

//System date is captured for placement in the CURRENT_DATE field in myTable   java.util.Date currentDate = new java.util.Date();  ...  stmt.executeQuery("INSERT INTO myTable (NAME, CURRENT_DATE) VALUES (' " + userName + " ', ' " + currentDate + " ')  ");        


Solution:1

You really should be doing this as a prepared statement using parameters, it makes things a lot easier and cuts out a few very simple SQL injection threats.

Connection con = ...;  PreparedStatement statement = con.prepareStatement("INSERT INTO myTable (NAME, CURRENT_DATE) VALUES ( ?, ?)");  statement.setString(1, userName);  statement.setDate(2, currentDate );  statement.execute();  

There is plenty of info on how to use prepared statements properly. For example: http://www.jdbc-tutorial.com/jdbc-prepared-statements.htm


Solution:2

If you just want the current date, you can retrieve that from the SQL server, without submitting it as a variable. It varies a little depending on what server you're using, but in MS SQL Server there's a function called getdate().

stmt.executeQuery("INSERT INTO myTable (NAME, CURRENT_DATE) VALUES (' " + userName + " ', getdate()");        

You can also set getdate() as the default value for that field, so that you can omit the field entirely:

stmt.executeQuery("INSERT INTO myTable (NAME) VALUES (' " + userName + " '" ')  ");        


Solution:3

Don't put strings in the SQL. Use a prepared statement and set parameters.


Solution:4

Try this:

stmt.executeQuery("INSERT INTO myTable (NAME, CURRENT_DATE) VALUES (' " + userName + " ', GETDATE())  ");   


Solution:5

For data integrity, this is something that should be done at the DBMS end with an update and insert trigger.

Any other method can be gotten around by malicious (or buggy) code.

How to get the actual value (and how to create the appropriate triggers) depends on the DBMS itself. DB2 provides current date, current time and current timestamp that you can use to insert the server time into the database, which is likely to be more consistent than hundreds of different client times.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »