Re: Debugging db exception with Openbase and raw rows date type
Re: Debugging db exception with Openbase and raw rows date type
- Subject: Re: Debugging db exception with Openbase and raw rows date type
- From: Chuck Hill <email@hidden>
- Date: Fri, 27 Aug 2010 11:58:39 -0400
On Aug 27, 2010, at 11:46 AM, William Hatch wrote:
> Hi Chuck, inline below.
And more.
> On Aug 27, 2010, at 11:32 AM, Chuck Hill wrote:
>
>> Hi Bill,
>>
>> On Aug 27, 2010, at 11:16 AM, William Hatch wrote:
>>> On Aug 27, 2010, at 10:52 AM, Chuck Hill wrote:
>>>> On Aug 26, 2010, at 12:14 PM, William Hatch wrote:
>>>>
>>>>> I hope this is on topic enough; OB list seems to be dead.
>>>>>
>>>>> I've got a utility app using latest wo and wonder, openbase db, that performs raw fetches to then bulk export data in csv. Basically a simple data export/dumper thing. This had been working perfectly for a long time, but a recent run resulted in the following exception, thrown during the call to EOUtilities.rawRowsForSQL(...) :
>>>>>
>>>>
>>>> What exception happens here?
>>>
>>> Ah sorry, cut it off. It's an IllegalArgumentException immediately above the following output
>>>>
>>>>>> at java.sql.Date.valueOf(Date.java:138)
>>>>>> at com.openbase.jdbc.f.getDate(null:Unknown)
>>>>>> at com.webobjects.jdbcadaptor.JDBCColumn._fetchCorrectObject(JDBCColumn.java:288)
>>>>>> at com.webobjects.jdbcadaptor.JDBCColumn._fetchValue(JDBCColumn.java:384)
>>>>>> at com.webobjects.jdbcadaptor.JDBCColumn.fetchValue(JDBCColumn.java:372)
>>>>>> ... skipped 1 stack elements
>>>>>> at com.webobjects.eoaccess.EOUtilities._rawRowsForSQL(EOUtilities.java:636)
>>>>>> at com.webobjects.eoaccess.EOUtilities.rawRowsForSQL(EOUtilities.java:588)
>>>>>> at com.imapp.eo.Judgement.rawRows(Judgement.java:53)
>>>>>> at com.imapp.tasks.JudgementTask.performAction(JudgementTask.java:26)
>>>>>> at er.extensions.ERXLongResponseTask$DefaultImplementation.run(ERXLongResponseTask.java:162)
>>>>>> ... skipped 1 stack elements
>>>>>> at er.extensions.ERXLongResponseTask$WorkerThread.run(ERXLongResponseTask.java:60)
>>>>>
>>>>> which to me, indicates a problem with a date column within the entity referred to. I got around this particular problem by doing some raw queries and identifying and deleting row with bad date values, and the problem vanished. Then it came up again on another table, again with a date type, but this time I'm unable to find any bad data via direct queries. I debugged around my action by setting a breakpoint in the catch block, but that's not helping much. I'm looking for some advice on how to determine and fix, or at least work around the real issue. Anybody else seen this before? Thanks.
>>>>
>>>>
>>>> Is there old jdbc2info in the model? Is the JDBC driver current? Has it changed? Are you using prototypes? How are dates defined? What is the column type in the model?
>>>
>>> Not sure how to check for old jdbc2 info in the model Chuck. What should I look for?
>>
>> Look in index.plist (e.g. in a text editor) for the key jdbc2info.
>
> Yep, there's some entries in there, a bunch of stuff actually.
>
> jdbc2Info = {
> "ANSI92_CONFORMANCE" = ANSI92Intermediate;
> "DBMS_NAME" = OpenBase;
> "DRIVER_NAME" = "OpenBase JDBC Driver";
> "DRIVER_VER" = "0.14";
> "IDENTIFIER_QUOTE_STRING" = " ";
> "NON_NULLABLE_COLUMNS" = T;
> "STRING_FUNCTIONS" = "ASCII,CHAR,CONCAT,DIFFERENCE,INSERT,LCASE,LEFT,LENGTH,LOCATE,LTRIM,REPEAT,REPLACE,RIGHT,RTRIM,SOUNDEX,SPACE,SUBSTRING,UCASE";
> typeInfo = {
>
> ...
>
> date = { createParams = 0; defaultJDBCType = (DATE); isNullable = T; isSearchable = T; maxScale = 0; minScale = 0; precision = 0; };
If that _is_ the problem (and I doubt that it is based on your other descriptions), deleting this will fix the issue as EOF will re-populate this on connection to the database. This is just an optimization cache.
> The thing is, it always worked, and the project itself hasn't changed. It's a pretty old app, actually.
>>
>> Has the OB version changed?
>
> No, neither on the production server, or my local instance.
That would seem to rule out having an out of date jdbc2info.
>>> I did upgrade local wonder somewhat recently, but not the other dependent frameworks. I inherited the model a long time ago, and it never used prototypes. It's at least nine years old. The column type in the model is date, Data Type is Date and class is NSTimestamp. Here's the entry for that column in the entities' .plist file:
>>>
>>> {
>>> allowsNull = Y;
>>> columnName = saleDate;
>>> externalType = date;
>>> name = saledate;
>>> valueClassName = NSCalendarDate;
>>> valueType = D;
>>
>> As in a Date only, no time? All of mine are:
>
> Yeah, Date no time. I could change it with some alters maybe; if that might help.
>>
>>> valueClassName = NSTimestamp;
>>> valueType = T;
>>
>> Using "date only" values in Java WO is problematic.
>
> Bummer...;-)
>>
>>
>>> },
>>>
>>> So, I fixed it on one table by querying for bad date ranges after exploring revealed some crazy dates and formats. I did this from openbases's cli tool. But I'm not seeing any bad, or what would appear to be bad, data in the second table. Not sure if it's a data issue, or what. The app that inputs the data runs on an older version of wo; 5.3.x and I've been copying down the db locally every once in a while and running this exporter utility locally.
>>
>> It does sound like bad data, though not a helpful error messages. How does OB manage to get bad data in a "date" type column?
>
> I don't know, and another fact that reinforces this theory is that the exceptions are getting thrown way up towards newer data. In the application, I'm basically just doing a bunch of raw rows fetches, using the pk value and ordering on it, and incrementing a counter var while we go. I only grab batches of 5000 rows for memory purposes, and I don't get this until I get into new data. That's how I was able to find it in the previous table, but this time, everything looks good. I had to write this little app as Openbase doesn't have any open standard way of exporting data, that I could find anyway, and I needed to do some massaging of various columns to remove tabs, comma's, newlines, boolean translations, etc, prior to dumping them to csv. The primary motivation is moving off of Openbase in the end, so hopefully postgres will work a little better. Anyway, before going down this path, I tried the ERXJDBCUtilities.copy... stuff but couldn't get that to fly. I sure wish it was easier...;-)
>
> Do you think it might be worth trying to convert those columns to datetime, or timestamp?
It may. It may also allow OB itself to point out where the invalid values are as EOF seems unhelpful in this area.
Chuck
--
Chuck Hill Senior Consultant / VP Development
Practical WebObjects - for developers who want to increase their overall knowledge of WebObjects or who are trying to solve specific problems.
http://www.global-village.net/products/practical_webobjects
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden