0

I have an SQL which looks into a dimension table (which stores every dates until year 2020) and then shall retrieve the todays row. I watched into the table, todays date is in there. The problem is, that SQL does not return any result.

I am thinking of a problem related to the use of java.sql.PreparedStatement.setDate method. In past i think this was working fine, now I did some kine of regression test and it failed. The differences to the past are having Oracle 12 DB now instead of 11 in past and running it on CentOS 6.5 instead of AIX.

On search I found this topic here: Using setDate in PreparedStatement

As far as I can see, I am doing as suggested.

Heres the java code and the query:

public static String SELECT_DATUM = "SELECT TIME_ID, DATE, DAY_NAME, WEEK_NAME, MONTH_NAME, YEAR_NAME, SORTING, RELATIONDATE, VALID_TO, VALID_FROM FROM DIM_TIME WHERE DATE = :date";

java.util.Calendar now = Calendar.getInstance();
now.clear(Calendar.HOUR);
now.clear(Calendar.MINUTE);
now.clear(Calendar.SECOND);
now.clear(Calendar.MILLISECOND);

Date tmpDate = now.getTime();

Date tmpDate2 = new Date(((java.util.Date)tmpDate ).getTime());

statement.setDate(1, tmpDate2 );

I notice that getTime() is called twice. But I dont think its that bad. I also noticed some displaying formats: in Database the date-colums shows me the date like this: '08.11.2015' in java while debugging tmpDate2 shows me a date like this: '2015-11-08' in java while debugging tmpDate shows me a date like this 'Sun Nov 08 12:00:00 CET 2015'

But again, these are just display formattings while it is a dateobject in background and a date-type in database. I would expect that je JDBC driver would map this itself without formattings, that why we are using setDate method and not setString.

What am I doing wrong? What could I do for further debugging to get it? I would like see the resulting SQL query which is finally executed with the parameter.

I tried this sql on db isntance:

SELECT * FROM v$sql s WHERE s.sql_text LIKE '%select time%' ;

but only getting this then: "... where date = trunc(:1 )" On this row at least I can see that it was using the right schema I expected it to use and where I checked whether todays date is available.

Edit: something I found out: I saw another code using the same function but giving an GregorianCalendar instead Calendar. When using new GregorienCalandar(); instead of Calendar.getInstance(); Theres no difference. But when I assign a date and dont let the system take the current time, then it works: Using new GregorianCalendar(2015, Calendar.NOVEMBER, 8); Would retrieve the row I want from SQL.

Community
  • 1
  • 1
Kaspatoo
  • 1,223
  • 2
  • 11
  • 28
  • Try this: `SELECT TIME_ID, DATE, DAY_NAME, WEEK_NAME, MONTH_NAME, YEAR_NAME, SORTING, RELATIONDATE, VALID_TO, VALID_FROM FROM DIM_TIME WHERE DATE = TRUNC (:date)` – Zsigmond Lőrinczy Nov 08 '15 at 12:49
  • Do you use a `java.sql.Date`? – thokuest Nov 08 '15 at 12:52
  • new Date(((java.util.Date)tmpDate ).getTime()); tmpDate is java.util.Date new Date creates java.sql.Date – Kaspatoo Nov 08 '15 at 14:04
  • new Date(((java.util.Date)tmpDate ).getTime()); tmpDate is java.util.Date new Date creates java.sql.Date statement.setDate(1, tmpDate2 ); statement is PreparedStatement and setDates takes in that case java.sql.Date – Kaspatoo Nov 08 '15 at 14:11
  • Why don't you use simply java.sql.Data ? java.sql.Data is derived from java.util.Data, see javadoc here: http://docs.oracle.com/javase/7/docs/api/java/sql/Date.html. Just use `java.sql.Date tmpDate = now.getTime();` without all these confusing casts and conversions. – krokodilko Nov 08 '15 at 14:17
  • @Zsigmond Lőrinczy: for this I am doing trunc on java, why does this not work? using trunc on SQL works! thanks – Kaspatoo Nov 08 '15 at 14:21
  • @Kaspatoo: that's what debugging is good for. For example: `SELECT DUMP(:date,10),DUMP(TRUNC(:date),10) FROM dual` – Zsigmond Lőrinczy Nov 08 '15 at 16:29
  • 1
    You probably want: `WHERE trunc(DATE) = :date` (btw: `date` is a **horrible** name for a column) –  Nov 08 '15 at 16:34

1 Answers1

0

Zsigmond Lőrinczy posted this answer as comment:

Try this: SELECT TIME_ID, DATE, DAY_NAME, WEEK_NAME, MONTH_NAME, YEAR_NAME, SORTING, RELATIONDATE, VALID_TO, VALID_FROM FROM DIM_TIME WHERE DATE = TRUNC (:date) – 3 hours ago

This works for my problem.

I am writing this as reponse to check it later as answer on this question if hes not going to write his own response (to get the reputation-points).

But I am wondering how I could get the same by preparing on java. The code uses the clear-methods, which where released into an own method named 'trunc'. I think the programmer intendet to do this instead of TRUNC in SQL. I am wondering if it werent possible to do so in java and if yes, how?

Edit: And I am wondering why a TRUNC is needed at all. Because the column in Database is of type Date an not Timestampt. So wouldnt there be an automatically trunc? I would expect this. Why do I need a trunc on SQL?

Kaspatoo
  • 1,223
  • 2
  • 11
  • 28
  • "*Why do I need a trunc on SQL*" - because in Oracle a `DATE` also contains a time: https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#sthref152 –  Nov 08 '15 at 16:35
  • on this documentation: http://docs.oracle.com/cd/E11882_01/java.112/e16548/apxref.htm#JJDBC28920 I found: This is because if you use the setDate method, then the time component of the Oracle DATE data will be lost and if you use the setTimestamp method, then the index on the DATE column will not be used. Did this change in Oracle 12? – Kaspatoo Nov 08 '15 at 16:50
  • But the value _stored_ in the database probably contains a time –  Nov 08 '15 at 16:56
  • but then TRUNC() wouldn work, because time-part is truncated. If in the database theres a value with time, then I would not match the value without (because its truncated) time. – Kaspatoo Nov 08 '15 at 16:58
  • `trunc()` doesn't "remove" the time, it just sets it to `00:00:00` –  Nov 08 '15 at 19:06
  • edited: well then setting fields to '0' within java should be enough to get it working but query still returns no result. So there must be more truncated than only the time (timezone, era, further fields). So the question now is: what is trunc within an sql doing and what would I need to do on java to achieve the same? I may dont want to change the sql because it is used by other code, too and trunc may would have negative effects, so it would be better to do 'trunc' on java. – Kaspatoo Nov 09 '15 at 07:59
  • edited: The previous programmer seems to have thought that using clear would achieve this. And may in combination with what I found in the oracle docs (see link above) it seem to work at least on oracle 11g. May it changed on 12 but I cant find sth on this. – Kaspatoo Nov 09 '15 at 08:02