• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2712
  • Last Modified:

[SQL0181] Value in date, time, or timestamp string not valid.

I am getting the following exception when looping through my resultset when I encounter the following error:
Billing.buildItemTable(): SQLException2 = [SQL0181] Value in date, time, or timestamp string not valid.

The while loop runs successfully over 240+ times before the exception is thrown.  The last logging statement I see is "row added!" so it seems that the code is throwing an exception when trying list.next().    I first thought maybe the date value I was getting from the database was invalid, but I'm containing that exception in its own try / catch block.  I've looked in the database (DB2) and cannot see anything wrong with the next row's data.  

Here's  the code:

            try {
                  while(counter < maxCount && list.next()) {
                        counter++;

                        // Format the date.
                        try {
                              System.out.println("getting date...");
                              Date date = list.getDate("TRANSACTION_DATE");
                              System.out.println("date = " + date);
                              sDate = DATEFORMAT.format(date );
                        }
                        catch(SQLException sqle) {
                              System.out.println("Billing.buildItemTable(): SQLException1 = " + sqle.getMessage());
                        }
            
                        String transType = list.getString("TYPE").trim();
                        System.out.println("transType = " + transType);

                        int sequence = list.getInt("SEQUENCE_NUMBER");
                        System.out.println("sequence = " + sequence);

                        String transNumber = list.getString("NUMBER");
                        System.out.println("transNumber = " + transNumber);
            
                        String po = list.getString("PO");
                        System.out.println("po = " + po);

                        String description = list.getString("DESCRIPTION");
                        System.out.println("description = " + description);

                        // Add the item.
                        System.out.println("adding row...");
                        items.addRow(sDate, transNumber, po, description);
                        System.out.println("row added!");
                  }
            }
            catch(SQLException sqle) {
                  System.out.println("Billing.buildItemTable(): SQLException2 = " + sqle.getMessage());
            }
0
nateforrest1
Asked:
nateforrest1
  • 3
  • 2
  • 2
1 Solution
 
Data-ManCommented:
If your code is retrieving more rows than exists, subtract 1 from maxCount.

Am I way off base?  

Mike
0
 
Julian HansenCommented:
The obvious explanation is that it is something wrong with the data. If you did 240 successful loops then by induction you can say that your code is correct. The code is static and with exception of the counter increment and the list.next nothing changes. I doubt it is the counter statement - 240 items is not about to cause an overflow and one would think that list.next() has been written in such a way that it can handle end of list conditions et al gracefully.

That means the only thing left is data - I would do a bit of data analysis on the input query to see what potential funnies there are there.

0
 
nateforrest1Author Commented:
Actually there are 431 rows in the ResultSet, but it keeps throwing the Exception on the same row.  I have about 10 large customers that were giving me the same exception so I did some testing and found that the Exception was thrown each time the ResultSet reached the 287th row.  I don't think there is anything magical about this number, i.e. I limited the columns I was bringing back in my ResultSet and I was able to get all the rows.  

I started thinking maybe this is an out of memory exception, so I increased the amount of memory: java -Xms256M -Xmx256m com.test.Billing and added a Runtime object to monitor the memory being used.  

      Runtime rt = Runtime.getRuntime();
      System.out.println("Free/total memory:");
      System.out.println(rt.freeMemory()+"   "+rt.totalMemory());      

However, the Runtime object showed plenty of free memory available.  
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Data-ManCommented:
I agree with julian...sounds like a problem with the data.  I've seen where a hard return (ctrl+enter) in a field can cause problems.  Can you check the data using another method?

mike
0
 
nateforrest1Author Commented:
I first suspected bad data so I changed the query to start in the middle of the first ResultSet and the program was able to return rows past the record in question.  
0
 
Julian HansenCommented:
Good thinking batman.

Well that rules out a particular record as being the problem.

What happens (just as a matter of interest) if you don't try and catch the exceptions i.e. comment out your try / catch? Not suggesting this as a solution but it would be interesting to see what happens.

0
 
nateforrest1Author Commented:
I'm not sure if its something quirky with AS400s, but I spoke to a programmer who had worked here for several years and he said he had seen something like this in the past.  He instructed me to add the following to my host name when setting up JDBC:

;date format=iso

This is an excerpt from IBM's web site:
Applications that require ISO formats or JDBC-defined string conversions must set the toolbox connection properties that control the date and time format to ISO.

This fixes the problem.  So it appears maybe it is some sort of data problem.  I'm still a bit wary of this since I had tested the data for bad data.  Oh well.  Thanks to everyone for all the help!  julianH I'm recommending partial credit to you!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now