Problem passing date parameters with Oracle 10g

[ozwolverine]

Hello Everybody, this is my problem.

 

My Web application, has some pages that receive two dates, Once I have them I create the HashMap for the parameters

(

Code:

HashMap<String, Object> parameters = new HashMap<String, Object>(); ), Then I export my report to PDF ( 

Code:

[code]JasperRunManager.runReportToPdfFile(File_Name, namePDF, parameters, new JREmptyDataSource());

 

If I run this report I don't get any results, because Oracle needs the dates to be passed in this format: "dd-Mmm-yy" or "dd-Mmm-yyyy". How could I create this date format from java to be sure Oracle recongnizes it as a valid Date?

 

I have tried things like:

 

Code:

[code] SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", new Locale("en", "US"«»));

 

 

but SimpleDateFormat returns a String not a Date. Is there any way to do a cast since my jasper report to recognize the dates?

 

Thanks a lot,

 

Johnny

[bklawans]

Are you getting any errors from Oracle when you pass the date in as Date objects? I've done this with Oracle 9i without any trouble.

 

If you pass the parameters as Date's, JasperReports simply tells the JDBC driver that there is a date param and set the Object you passed. The driver converts the date to a format the db engine understands, so the locale's date format shouldn't matter.

 

Converting to a string manually should be OK, as long as you do it in a way that gets called before the query is executed. Declare a new parameter of type String that has a default value of the expression that uses a SimpleDataFormat to convert to a string, and use that parameter as part of your query.

 

The exact error you are getting from Oracle would be a big help.

 

-Barry

[ozwolverine]

Hello Barry, thanks for answering,

 

bklawans wrote:

Are you getting any errors from Oracle when you pass the date in as Date objects?

When I pass a Date (java.util.Date) without any prior formatting, I just get no results on the report.

 

If you pass the parameters as Date's, JasperReports simply tells the JDBC driver that there is a date param and set the Object you passed. The driver converts the date to a format the db engine understands, so the locale's date format shouldn't matter.

This is very strange, if I run the query directly in SQLPlus Console (like this: "SELECT NOMBRECOMPLETO_USUARIOEMPRESA, ID_USUARIOEMPRESA, EMAIL_USUARIOEMPRESA, RAZONSOCIAL_EMPRESA, ID_TIPOUSUARIO

FROM usuarioEmpresa ue, Empresa e

WHERE ue.NIT_EMPRESA = e.NIT_EMPRESA AND ue.FECHAREG_USUARIOEMPRESA between '01-Jul-2006' and '27-Aug-2006'" ), I get results. But, if I try the query with a different date format like:

'Mon Aug 28 00:00:00 COT 2006'

 

I get:

 

ERROR at line 3:

ORA-01858: a non-numeric character was found where a numeric was expected

 

But If I run it from my web app I get:

 

 

 

Converting to a string manually should be OK, as long as you do it in a way that gets called before the query is executed. Declare a new parameter of type String that has a default value of the expression that uses a SimpleDataFormat to convert to a string, and use that parameter as part of your query.

 

I will try this, I'm formatting with SimpleDateFormat from my java app, I will change it to the Default Value Expression for parameter option.

 

 

The exact error you are getting from Oracle would be a big help.

When I send a complete java.util.Date I don't get any error message, but when I tried using a String I got

an strange error message I will post it as soon as I have access to the application.

 

Thanks a lot for your useful help,

 

Johnny

[Code_Slave]

the format of the oracle date types was changed I think from 91 to 93.

Basically oracle was not mapping the dates correctly, because oracle stores a different precision for dates.

 

There was something about not using oracle "date" but instead using oracle "timestamp" which more clearly reflects a mapping with java.

 

I had a pig of a job sorting thru this mess sometime last year, and i appear not to be the only one, looking at the replies oracle have made, & the clarification they posted with the java ODBC drivers

 

I was getting all sorts of oracle errors, trying to convert from oracle to java & visa versa.

 

For your mapping on your query, you have to tell oracle the format of the date you are using, it is not a mind reader.

 

Have a look at the oracle sql function to_date() you can pass in 1 or 2 peramaters.

if you pass in one, it assumes that the date format is the SAME as the current setting of your database

 

or you can do

to_date ('01/26/47','mm/dd/yy')

by changing the format string and telling oracle how to parse the date you are passing you can use various formats.

 

Aslong as they comply with the agreed formats understood by oracle.

Post edited by: code_slave, at: 2006/09/04 02:00

[ozwolverine]

Hello code_slave, thanks for your answer.

 

 

The problem is that I modified my SQL query to:

SELECT NOMBRECOMPLETO_USUARIOEMPRESA, ID_USUARIOEMPRESA, EMAIL_USUARIOEMPRESA, RAZONSOCIAL_EMPRESA, ID_TIPOUSUARIO FROM usuarioEmpresa ue, Empresa e WHERE ue.NIT_EMPRESA = e.NIT_EMPRESA AND ue.FECHAREG_USUARIOEMPRESA between to_date($P{fechaInicial},'DD-MON-YY') and to_date($P{fechaFinal},'DD-MON-YY')

 

Passing the parameters (fechaInicial and fechaFinal) as Strings, but I stil cannot get results, even if I have records in my db. Do you think I should change my db types from date to timestamp?

 

Thanks a lot,

Johnny

[bklawans]

code_slave wrote:

the format of the oracle date types was changed I think from 91 to 93.

 

Thanks for the info - I'm still running 9.1 and wasn't aware of this change.

 

-Barry

[Code_Slave]

Well, I can only repeat what oracle has said :

 

refer to

http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm

 

What is going on with DATE and TIMESTAMP?

 

it a bit out of date 2004, but still relevent.

 

********

Prior to 9.2, the Oracle JDBC drivers mapped the DATE SQL type to java.sql.Timestamp. This made a certain amount of sense because the Oracle DATE SQL type contains both date and time information as does java.sql.Timestamp. The more obvious mapping to java.sql.Date was somewhat problematic as java.sql.Date does not include time information. It was also the case that the RDBMS did not support the TIMESTAMP SQL type, so there was no problem with mapping DATE to Timestamp.

********

Personally i still use date, because i have legacy code (10 year old foxpro), still accessing the data on a 9i database.

 

 

I use jasper reports extensivly with ireport designer, and have little problem with the oracle "date" , but remember it gets truncated.(time info is stripped)

 

 

There is an excellent free program from oracle called SQLDEVELOPER, its written in java, which allows you to "crack" open your database and view/ modify tables & run Queries

 

O.K

lets crack open the query & take it thru in stages.

 

Start by stripping your query down, substitute in your own value for the table & col, (i do not have access to your tables , so i have to use one of my own)

 

1.select * from supplier where supplier.born between to_date('11-12-2001','DD-MM-YY') and to_date('11-12-2006','DD-MM-YY')

 

O.K this gives me a valid result and we can see that both arguments require strings.

 

2.select * from supplier where born between to_date('11-12-2001','DD-MON-YY') and to_date('11-12-2006','DD-MON-YY')

 

2 gives me an error because i passed in a number instead of month (note the format 'DD-MON-YY' )

 

3.select * from supplier where born between to_date('11-Dec-2001','DD-MON-YY') and to_date('11-Dec-2006','DD-MON-YY')

 

O.K this gave me an answer which was the same as 1, so we see that the syntax is ok

 

4. select * from supplier where born between to_date('11-December-2001','DD-MON-YY') and to_date('11-December-2006','DD-MON-YY')

 

this also gave me the correct results, so we se we can use either format for month (Dec or December)

lets try something different (i'm assuminy your Italian)

 

5.select * from supplier where born between to_date('11-Dicembre-2001','DD-MON-YY') and to_date('11-Dicembre-2006','DD-MON-YY')

O.K here i got an error ORA-01843 not a valid month (because my db is set to English)

 

therefore we can summise:

1. if you are getting absolutly no errors, then your query is correct insofar as syntax.

2. strip your query down to the basics as I have & test it.

3. Ensure that your ,'fechaInicial and fechaFinal' , are clean and contain the correct values, (don't assume they are correct, print them out)

 

4. temporarily substitute known strings for 'fechaInicial and fechaFinal'

I.E HARD CODE THEM.

 

5. the date/timestamp has no effect on your query , because, you are not using java directly to read/write the database., you are doing a simple query.

(so put that issue out of your mind for now)

 

try the following :

SELECT *

FROM usuarioEmpresa ue

WHERE ue.FECHAREG_USUARIOEMPRESA between to_date('01-01-1901','DD-MM-YY') and to_date('01-01-2010,'DD-MM-YY')

 

this will test the syntax of your query, then gradually build it up to:

 

SELECT NOMBRECOMPLETO_USUARIOEMPRESA, ID_USUARIOEMPRESA, EMAIL_USUARIOEMPRESA, RAZONSOCIAL_EMPRESA, ID_TIPOUSUARIO

FROM usuarioEmpresa ue, Empresa e

WHERE ue.NIT_EMPRESA = e.NIT_EMPRESA AND ue.FECHAREG_USUARIOEMPRESA between to_date('01-01-1901','DD-MM-YY') and to_date('01-01-2010','DD-MM-YY')

 

 

and if you do that , your problem will illuminate it's self.

also note that because i do not 100% know your home language or your database settings i have used the format "DD-MM-YY", which is all numerical

Post edited by: code_slave, at: 2006/09/04 23:15

[ozwolverine]

Hello code_slave,

 

You have brought very good ideas on how to test and solve my problem.

 

I will follow your suggestions and let you know the results.

 

thanks a lot,

 

Johnny