Java dates for JDBC (JSP) String/Date conversions.

[robtyketto]

Greetings,

I have the following code below which allows the date to be added via a JDBC connection as a STRING.

The value of dateString is inserted into the MS ACCESS database.

What is the easiest method to add the current date/time (dd/mm/yy hh:mm:ss) via JDBC in DATE format i.e. convert STRING to DATE or format the DATE without changing its type and insert it.

I'm a newbie to Java/Jsp too.

<%@ page import="java.util.Date, java.text.SimpleDateFormat" %>
<% java.util.Locale locale = request.getLocale(); %>    

<%
	Date d = new Date();
	String dateString = getFormattedDate (d);
%>

<%! 
    String getFormattedDate(Date d)
   {
    SimpleDateFormat simpleDate = new SimpleDateFormat("dd/MM/yy hh:mm:ss");
    return simpleDate.format(d);
   }
%>

[BigDaddyLH]

I'm confused. What are you trying to do?

It sounds like you want to insert a row into a database table, with a Date value in one of its columns.

[CODE=Java]String sql = "INSERT INTO tablename(colum nname1, columnname2) VALUES (?,?)";
PreparedStateme nt ps = con.prepareStat ement(sql);
try {
ps.setDate(1, date);
ps.setString(2, someString);
ps.executeUpdat e();
} finally {
ps.close();
}[/CODE]

[robtyketto]

Sorry, I will include the code for the SQL record insert which shows the Datestring value being passed in.

So to clarify I get the current date and time and insert into a record in my access database.

statement.executeUpdate("INSERT INTO FAQ (\"Id\",\"category\", \"question\", \"answer\", \"sequence\", \"UserId\", \"created\") VALUES ('"+IdParam+"','"+categoryParam+"','"+questionParam+"','"+answerParam+"', '"+sequence+"', '"+ session.getAttribute("theName")+"', '"+[B]dateString[/B]+"')   ");

Currently dateString is a string as in the database model and I want to convert the string into a date or have another method of inserting the current date (dd/mm/yy hh:mm:ss) into the database as a DATE rather than STRING.

Thanks
Rob

[BigDaddyLH]

Here's one of the 10 commandments of JDBC:

Don't use Statement, use PreparedStateme nt,

http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html

[robtyketto]

Thanks I will have a read of that, but at the moment my priority is to change my date in format "dd/mm/yy hh:mm:ss" into a java date format rather than a string.

Doesnt look professional have a date field in my database as TEXT :(

[BigDaddyLH]

What is the source of this dateString? I'm suggesting that if this string is being generated from a Date object, don't! Keep it a Date object and you won't have to parse it.

[robtyketto]

Thanks for the reply.

Its starts of as a date see code below (JSP code)

<%
    Date d = new Date();
    String dateString = getFormattedDate (d);
%>

Because I dont know any better :-( I change it to a string so I can manipulate it, see below:-

<%! 
    String getFormattedDate(Date d)
   {
    SimpleDateFormat simpleDate = new SimpleDateFormat("dd/MM/yy hh:mm:ss");
    return simpleDate.format(d);
   }
%>

As long as I can get a date format of dd/MM/yy hh:mm:ss as a DATE I'll be happy!

[BigDaddyLH]

You want a date string so that you can turn it back into a Date object???

[CODE=Java]Date d = new Date();[/CODE]

Why isn't that good enough?

[robtyketto]

Current date goes into the MS Access database as a string as its setup as Text on the database.

I've just changed the database schema to date/time and used the value of the date i.e

Date d = new Date();

I need to check about the SQL inserts as It' really confusing me as it seem all values require a single quote and a double i.e. '" no matter if string or integer ???

newstatement.executeUpdate("INSERT INTO FAQ (\"category\", \"question\", \"answer\", \"sequence\", \"UserId\", \"created\") VALUES ('"+categoryParam+"','"+questionParam+"','"+answerParam+"', '"+sequence+"', '"+ session.getAttribute("theName")+"', '"+d+"' )");

Using the code above it complains of data type mismatch, I will have to find out if i can insert dates in this way using SQL.

Cheers
Rob

[BigDaddyLH]

Trust me, I've said this more than once in this thread: you never want to write code like what you just posted. Use a PreparedStateme nt.

[robtyketto]

Ok, I take your point about the code, it hasn't fallen on deaf ears!

I'm a university student and the code I've used is based on the examples that we are meant to modify.

Though the problem with my JDBC date insert I assume isnt related at all, once i get my code working I can then worry about other improvements.

I'm aiming to achieve some core functionality such as storing dates at the mo.

[BigDaddyLH]

Hmmm... I think that if you do it just with Statement, your SQL syntax depends on the database you're using. I've never touched ACCESS so I don't know the SQL syntax for it. If it were me, I'd say Statement be damned and use a PreparedStateme nt. Why do something inferior just because that was the model?

[robtyketto]

I changed it :)

	String myquery  = "INSERT INTO FAQ (\"category\", \"question\", \"answer\", \"sequence\", \"UserId\", \"created\") VALUES ('"+categoryParam+"','"+questionParam+"','"+answerParam+"', '"+sequence+"', '"+ session.getAttribute("theName")+"', '"+d+"' )";
PreparedStatement mystatement = conn3.prepareStatement(myquery);

At the moment it's not showing any errors but not actually inserting any records, so Im still investigating :)

[robtyketto]

Ahh now I understand what i have to do .. assign values and execute !

[BigDaddyLH]

Yikes! You are misusing Prepared Statement. The string should be:
[CODE=Java]
String myquery = "INSERT INTO FAQ (category, question, answer, sequence, UserId, created) VALUES (?,?,?,?,?,?)"[/CODE]

(note: all those quote marks only make it hard to read!)

Try reading the tutorial on prepared statement again. The last think you want to do is hardcode values into the prepared statement string -- it's whole reason for existence is to be parameterized.