?
Solved

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

Posted on 2004-09-20
7
Medium Priority
?
2,544 Views
Last Modified: 2008-01-09
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
Comment
Question by:nateforrest1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 18

Expert Comment

by:Data-Man
ID: 12105146
If your code is retrieving more rows than exists, subtract 1 from maxCount.

Am I way off base?  

Mike
0
 
LVL 58

Accepted Solution

by:
Julian Hansen earned 1500 total points
ID: 12105562
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
 

Author Comment

by:nateforrest1
ID: 12105694
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 18

Expert Comment

by:Data-Man
ID: 12105768
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
 

Author Comment

by:nateforrest1
ID: 12105800
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
 
LVL 58

Expert Comment

by:Julian Hansen
ID: 12105885
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
 

Author Comment

by:nateforrest1
ID: 12106467
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question