Solved

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

Posted on 2004-09-20
7
2,221 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
  • 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 51

Accepted Solution

by:
Julian Hansen earned 500 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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 51

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now