SQL INSERT system date into table

Question

Hi,

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 + " ')  ");      

Answer 1

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 + " '" ')  ");      

Answer 2

Try this:

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

Answer 3

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.

Answer 4

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

Answer 5

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