Oracle JDBC fails to return results

[edvedafi]

We have a PreparedStatment that fails to return result sets on rare random occasions. We are run at batch process 15 times a month. Once every 6-7 months one of these Prepared Statement will not return results. When we query the database the data is defiantly there. If we rerun the process then it finds the data and everything is fine, sometimes we need to rerun a couple of times before it works. It is always the same prepared statement that fails to return results, but it never throws an exception.

Recently in our test environment this has been happening once every 10 times that we run our process. The only difference between the two environments is that in production we are using the 1.4.2 Sun JVM. In test we are using Java 1.5.0_07. Both are running Oracle 10g.

After studying the results the only thing I have spotted that is out of the ordinary is a timestamp that has nanos where it shouldn't. We are querying using a DATE field; the value we are looking for is a constant that is derived from:

Calendar cal1 = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
Calendar cal2 = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
cal1.set(1970,1,1);
cal2.set(1800,1,1);
long millis = cal2.getTimeInMillis() - cal1.getTimeInMillis() + 21600000;
java.sql.Timestamp ts = new java.sql.Timestamp(millis);

Then we do ps.setTimestamp(ts);

When we crash we print out ts, and it always looks something like: 1800-01-01 00:00:00.008. The value in nanos is always different, but shouldn't it be zero? What could cause it to get a value from the above code?

I haven’t been able to find anything else that looks consistent from one crash to the next. Could this be our problem, or does oracle ignore this value in nanos? Is there anything else that I should be looking for that would cause a PreparedStatment to just not find the data one time and work perfectly the next? Again it works most of the time, and I don't see anything else that looks consistent from one to the next.

Any Advice is appreciated. Thanks,

Jason

 

[stefanwagner]

Without seeing a relation to your problem -
You know that this:

cal1.set (1970, 1, 1);

will set the Month to February?
Month-indicees are starting at 0 in Java.

When I set the Calendar with the statement above, the time used is the actual machine time.
Therefore, two statements return the same value, or a different one, depending on the speed of the machine, and the granularity of time precision.


seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung

 

[edvedafi]

Thanks for you quick response. Sorry about the delay in mine, I was out of the office for the holiday.

I agree the February thing is kinda wierd ( I'm working with someelse's code), but I don't think that is the problem here. This code should just calculate a constant that indicates 1/1/1800 00:00:00.000. So since both calendars use 1 it would be the same. I think that just creating the constant as a timestamp with a constant long milli value should work.

 Timestamp ts = new java.sql.Timestamp(-5364640800000l)

I cannot be certian since the problem only happens randomly I don't know if it just has not occured yet.

Based on your response what I am think might have been the problem is:

Calendar cal1 = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
Calendar cal2 = Calendar.getInstance(TimeZone.getTimeZone("GMT"));

When we do this cal1 and cal2 could theroretically have different millisecond values if for some reason there was a slow down on the machine. Does this sound reasonable? Could this be the problem?

Thanks

Jason

 

[timw]

Recently in our test environment this has been happening once every 10 times that we run our process

This doesn't sound so random to me, though.

Tim

 

[edvedafi]

Yeah I agree It has been pretty frequent lately.

What I meant by random was that there is no consistancy of when it happens. The proccess is a very long batch proccess and can take up 9 hours to run. So with several people running the proccess we may see the problem once every 3 or 4 days.

Hopefully using this constant will solve the problem, but I really will not know for sure until I keep running for a week or so. Only my version of the code has the change made to it and typically I only run it once or twice a week. So there is really no way to be certain that my code has fixed the problem. It is totally possible that I have just gotten lucky and not had it happen when I was running it.

Jason

 

[edvedafi]

May be this is not our problem. My supervisor was trying to use:

GregorianCalendar gcOne = new GregorianCalendar();
gcOne.set( 1800,0,1,00,00,00);
java.sql.Timestamp ts = new java.sql.Timestamp(gcOne.getTimeInMillis());

As his way of comming up with the timestamp. This was also producing the same problem of no data returned. In this case ts.toString() produced 1800-01-01 00:00:00.97. Why would the .97 be on there?

We did notice that it seams to happen more frequently as there is more of a load on the server. The server runs just one instance of Oracle and our batch process. The frequency of the problem increases when we have multiple people running the process at the same time. However we have had some instances where we have 4 or 5 runs going and not had this problem occur, so having a heavy load does not garuntee the problem will occur.

Thanks,

Jason

 

[stefanwagner]

To avoid the delay of 8 millis, you could also use clone:

Calendar cal1 = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
Calendar cal2 = cal1.clone ();

That would be the least invasive change to the code, without asking why February is used, and what funny magic values there are used for calculation.

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung