wood burning stoves 2.0*
The moose likes JDBC and the fly likes Passing Date in Callable Statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Passing Date in Callable Statement" Watch "Passing Date in Callable Statement" New topic
Author

Passing Date in Callable Statement

carina caoor
Ranch Hand

Joined: Jun 23, 2007
Posts: 300

Hi in my application i am trying to pass Date in callable statement but it gives me Class Cast Exception.
Code inside Jsp that calls servlet and passing the from and to date

[code]

xmlHttp.open("POST","../CreateReport?actioncomment="+u1+"&fromdate="+fromdate+"&todate="+todate+"&yygghh="+ran_number,true);


CreateReport.java

[code]
SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd");
String fromdate1=request.getParameter("fromdate");
java.sql.Date fromdate=(java.sql.Date) df.parse(fromdate1);
String todate1=request.getParameter("todate");
java.sql.Date todate=(java.sql.Date) df.parse(todate1);



CallableStatement cstmt = d.connection.prepareCall("{call sp_rs_MasterAuditLog(?,?,?,?,?)}");
cstmt.setString(1,actioncomment);
cstmt.setString(2,"");
cstmt.setString(3,"");
cstmt.setDate(4,fromdate);
cstmt.setDate(5,todate);


When i run my servlet i am getting


java.lang.ClassCastException: java.util.Date
at PlotManagement.CreateReport.doGet(CreateReport.java:35)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:433)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:874)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
at java.lang.Thread.run(Unknown Source)


where 35th line is
java.sql.Date fromdate=(java.sql.Date) df.parse(fromdate1);

Please suggest me something this java.util.Date and java.sql.Date are confusing me ...


Also my procedure has the input variables in Datetime format

Procedure sp_rs_MasterAuditLog
[code]

CREATE PROCEDURE sp_rs_MasterAuditLog
@p_Action VARCHAR(24),
@p_Basin VARCHAR(10),
@p_AreaCode VARCHAR(80),
@p_FromDate DATETIME,
@p_ToDate DATETIME
AS
BEGIN
SET NOCOUNT ON
DECLARE @Error VARCHAR(512)
DECLARE @l_FromDate DATETIME
DECLARE @l_ToDate DATETIME
DECLARE @l_SQL VARCHAR(8000)
DECLARE @l_AuditAction CHAR(1)

[ October 15, 2008: Message edited by: ruquia tabassum ]
[ October 15, 2008: Message edited by: ruquia tabassum ]
Agador Paloi
Ranch Hand

Joined: Jan 24, 2006
Posts: 118
I haven't had to do exactly what you are trying to do but have had to insert date - time strings into an oracle table using prepared statements.

I parse the date string into a java.util.date then set the date into
a java.util.Calendar . I am able to able to create a Timestamp with the
Calendar date time and use that in the prepared statement.

hope this helps
Vinod K Singh
Ranch Hand

Joined: Sep 30, 2008
Posts: 198
Try In oracle date and timestamp both are same.
[ October 15, 2008: Message edited by: Vinod K Singh ]

My Blog
carina caoor
Ranch Hand

Joined: Jun 23, 2007
Posts: 300

In my callable statement i need to send DateTime, my procedure is in MS SQL
java.sql.Date fromdate=(java.sql.Date)(df.parse(fromdate1).getTime()); gives an error which says cannot cast from long to Date so this assumption is not corect, also converting from date to datetime using Calendar is not working.
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Ruquia,
It's not clear to me from the code snippet in your original post, but I assume that "df" is an instance of class DateFormat, in which case method "parse()" returns an instance of java.util.Date and not java.sql.Date.
In order to convert from "java.util.Date" to "java.sql.Date", I do the following:

Note that method "getTime()" returns a "long".

Good Luck,
Avi.
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Vinod,
You said:

In oracle date and timestamp both are same.

No offence, but since Oracle 9i, the database has both a DATE data-type and a TIMESTAMP data-type.
Support for these types in Oracle's JDBC driver has varied from version to version, though.
I'm sure it was not your intention, but it is very frustrating when people post incorrect information.

Good Luck,
Avi.
carina caoor
Ranch Hand

Joined: Jun 23, 2007
Posts: 300

Actually Avi i need to pass a value with datatype as DATETIME in my MS SQL procedure to achieve this i converted the string date into utildate and util date into sql date as sql date do not have a time stamp my rpocedure is not accepting it an its throwing class cast exception ,
Rene Larsen
Ranch Hand

Joined: Oct 12, 2001
Posts: 1179

Show us the code you have made...


Regards, Rene Larsen
Dropbox Invite
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Ruquia,
I am not familiar with MS SQL.
Does the JDBC driver you are using define a mapping between the DATETIME data-type and a java class?

Good Luck,
Avi.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

Originally posted by ruquia tabassum:
Actually Avi i need to pass a value with datatype as DATETIME in my MS SQL procedure to achieve this i converted the string date into utildate and util date into sql date as sql date do not have a time stamp my rpocedure is not accepting it an its throwing class cast exception ,
This thread already has TWO posts showing you an example of how to write the code that does not require casting. Please go back and read them. If you don't, you are just wasting everybody's time including your own.

If you need a timestamp instead of a date (and it sounds like you do), then java.sql.Timestamp is the class you need instead of java.sql.Date. Just make the appropriate changes to the examples that were already posted.
Vinod K Singh
Ranch Hand

Joined: Sep 30, 2008
Posts: 198
Originally posted by Avi Abrami:
Vinod,
You said:

No offence, but since Oracle 9i, the database has both a DATE data-type and a TIMESTAMP data-type.
Support for these types in Oracle's JDBC driver has varied from version to version, though.
I'm sure it was not your intention, but it is very frustrating when people post incorrect information.

Good Luck,
Avi.


Avi, this is information I found on Oracle site, which clearly shows that DATE has time as well-
12 Date: Valid date range from January 1, 4712 BC, to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone.
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1121

Vinod,
Yes, the DATE data-type also has a time component.
I never said it didn't.
On the same Oracle documentation page you referenced, a little further down the page, there also appears the TIMESTAMP data-type.
The important thing for Ruquia is the mapping between the database data-type and the java class.
In fact, it is precisely the cause of the issue he is facing, a "ClassCastException", due to the fact that he is attempting to use an incorrect mapping.
And, as I mentioned, different versions of Oracle's JDBC driver have different mappings for both the DATE data-type and the TIMESTAMP data-type.
Some versions of the driver even support properties to change the mapping.
Hence saying that "java.sql.Date" and "java.sql.Timestamp" both map to the DATE database data-type is not always true.
But since Ruquia doesn't seem to be using an Oracle database, the point is irrelevant for him.
Of-course, if you are using a JDBC driver that maps both "java.sql.Date" and "java.sql.Timestamp" to the DATE data-type, I can understand how you could presume that that this is always the case.
So naturally you had no intention to deliberately mislead Ruquia, you simply made a mistake, which all of us do at some time or other.
So please excuse me if I gave the impression that I was chastising you.

Good Luck,
Avi.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Passing Date in Callable Statement
 
Similar Threads
File Browse in JSP on FireFox
Callable statement in Servlet throws exception
Read a .xls,.xlsx file format using XSSF
Element type "chartLegend" must be declared
converting a string into a date