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);
   }
%>
 

[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:

String myquery  = "INSERT INTO FAQ (category, question, answer, sequence, UserId, created) VALUES (?,?,?,?,?,?)"

(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.

[robtyketto]

Im thinking on my feet here ..

if(parameters.hasMoreElements()) {
 
    String myquery  = "INSERT INTO FAQ (\"category\", \"question\", \"answer\", \"sequence\", \"UserId\", \"created\") VALUES (?,?,?,?,?,?)";
     PreparedStatement mystatement = conn3.prepareStatement(myquery);
 
    mystatement.setString(1,request.getParameter("category"));
    mystatement.setString(2,request.getParameter("question"));    
    mystatement.setString(3,request.getParameter("answer"));
    mystatement.setInt(4,request.getParameter("sequence"));
    mystatement.setObject(5,session.getAttribute("theName"));
    mystatement.setDate(6,d);
 
    ResultSet myresults = mystatement.execute();
  }

When running I get THREE errors for the fields

Sequence (Integer in Database and INPUT TYPE = "Int)
theName (Session attribute the USER who logs in, STRING in Database)
D (Java Date, DATE/TIME in Database)

The exact errors are below :-

org.apache.jasp er.JasperExcept ion: Unable to compile class for JSP

An error occurred at line: 96 in the jsp file: /examples/wk465682AddFAQ. jsp
Generated servlet error:
The method setInt(int, int) in the type PreparedStateme nt is not applicable for the arguments (int, String)

An error occurred at line: 96 in the jsp file: /examples/wk465682AddFAQ. jsp
Generated servlet error:
The method setDate(int, java.sql.Date) in the type PreparedStateme nt is not applicable for the arguments (int, java.util.Date)

An error occurred at line: 96 in the jsp file: /examples/wk465682AddFAQ. jsp
Generated servlet error:
Type mismatch: cannot convert from boolean to ResultSet

No Idea which one is boolean, confused.com ??

Thanks for all your help, youre a lifesaver!

[BigDaddyLH]

If you feel you're in over your head, it's the fault of your course. At the very least, one should learn things one subject at a time. That's so obvious as to be self-evident. Now why do they have you mixing JSP with JDBC? You should have learned the JDBC first, in isolation. JDBC should never appear on a JSP, but that's a topic for another day...

These are all straightforward errors messages.

mystatement.set Int(4,request.g etParameter("se quence"));

You need to parse the String into an integer:

int sequenceValue = Integer.parseInt(request.getParameter("sequence"));
mystatement.setInt(4,request.sequenceValue);

mystatement.set Date(6,d);

You need to turn a java.util.Date into a java.sql.Date. A few glances at the API suggests:

java.sql.Date sqlDate = new java.sql.Date(d.getTime());
mystatement.setDate(6,sqlDate);

ResultSet myresults = mystatement.exe cute();

Only SELECT statements generate result sets. An update statement will return the
number of rows inserted, which will always be one, if it succeeds, so the
return value is not that useful. The preferred method to call is executeUpdate() ;

mystatement.executeUpdate();

[robtyketto]

:-)

I changed the string to int conversion to one line as it didnt work before to:-

mystatement.setInt(4,Integer.parseInt(request.getParameter("sequence")));

All running, it's resetting the time to 00:00:00 so I just need to check that out!!!

Thanks again for your help.

As for the module its a 12 wk module called Java and the Web, it states you dont have to have had any prior experience of Java to take it.

Its all about a dynamic website using a backend database.

Cheers
Rob

[BigDaddyLH]

I changed the string to int conversion to one line as it didnt work before to:-

mystatement.setInt(4,Integer.parseInt(request.getParameter("sequence")));

That's fine. It's a matter of taste if you should break a Java statement into smaller statements.

All running, it's resetting the time to 00:00:00 so I just need to check that out!!!

That's my fault. java.sql.Date corresponds to a SQL datatype that just specifies the day, not the time on that day as well. The solution is to use java.sql.Timest amp, in a similar way:

Timestamp ts = new Timestamp(d.getTime());
mystatement.setTimestamp(6, ts);

And double check that your ACCESS column type can hold the time as well as the date, but it should be okay.

[chaarmann]

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

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

besides using prepared staements, maybe you want to know why your code doesn't work the way you tried.:
your code doesn't work, because you are inserting the date-string directly, without using any sql-conversion functions that will define the format. Like TO_DATE(dateStr ing, 'DD/MM/YYYY') in Oracle-database or str_to_date(dat eString, '%m/%d/%Y') in mySql-database. (I actually must look up the syntax of the formatting string for access-database).
So if you are not giving the date format, your database tries to convert the string by itself, using a "default" format
So most likey your administrator has defined a default data format for the database that is not the same as the data-string you have given.