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

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

[CODE=Java]int sequenceValue = Integer.parseIn t(request.getPa rameter("sequen ce"));
mystatement.set Int(4,request.s equenceValue);[/CODE]

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:

[CODE=Java]java.sql.Date sqlDate = new java.sql.Date(d .getTime());
mystatement.set Date(6,sqlDate) ;[/CODE]

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() ;

[CODE=Java]mystatement.exe cuteUpdate();[/CODE]

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

[CODE=Java]Timestamp ts = new Timestamp(d.get Time());
mystatement.set Timestamp(6, ts);[/CODE]

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

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.