Link to home
Start Free TrialLog in
Avatar of nateforrest1
nateforrest1

asked on

[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());
            }
Avatar of Data-Man
Data-Man
Flag of United States of America image

If your code is retrieving more rows than exists, subtract 1 from maxCount.

Am I way off base?  

Mike
ASKER CERTIFIED SOLUTION
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of nateforrest1
nateforrest1

ASKER

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.  
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
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.  
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.

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!