UX improvement: Unable to expand a certain source physical file in Object Browser (due to invalid data in result set)

[krisbaehr]

I'm trying to view a list of members in a specific problematic source physical file via the object browser; every other source file works fine. When I try to expand this one, the arrow toggles downward but never displays the members and never displays an error. I can produce an error though by performing a right-click > Search Source File:

Error searching source members: Error: Syntax of date, time, or timestamp value not valid. (220007).

I'm unsure if it's a problem with the source file or one of its members. I can view and open these members in RDi with no issues.

Could the error message tell me more about where the invalid data was found? Maybe it could gracefully handle this situation and list the members anyway?

Thanks a bunch and keep up the great work!

[worksofliam]

@krisbaehr Please share the contents of your Code for IBM i output channel after the list doesn't appear.

[krisbaehr]

Note: the source physical files in this library are ready only. That might be the reason for the unauthorized message.

[krisbaehr]

I ran that SQL statement. I bet it's these rows.

[sebjulliand]

Good thinking @krisbaehr , thanks!
@chrjorgensen do you think we'd need coalesce for these two fields?

[chrjorgensen]

@sebjulliand coalesce could be a solution - if the columns contains nulls...! They could contain anything currently, just not a valid date. The ACS output does not indicate null, since the CREATED column has +++++++++ - I would expect NULL or whatever replacement value @krisbaehr has set in his ACS preferences for null results output.

When I run SQL statement values extract(epoch from (cast(null as timestamp))), I get null as output and no SQL error 22007 - which again to me indicates that the CREATE timestamp on these two members does not contain null.

I think this is a case of bad data on the server - and that the solution should be to fix the bad data instead of us trying to make Code for IBM i handle the bad data.

@krisbaehr
I would suggest to run RCLDBXREF OPTION(*FIX) LIB(<lib>) on your system to see if it would fix the bad data. If this does not help or you're not able to run the command, you could copy the members with bad create timestamp into new members and remove the original ones.

[krisbaehr]

@chrjorgensen Would it be better to have the app handle situations like this gracefully? If others run into the same issue, they may think it's broken or something and consider not using it. Some will dig further and see the fix through this thread.

The rows are being returned from the query, but with a bad creation date. When processing the date into a variable in the native language, a try-catch or something similar may allow it to be processed.

I will try replacing those members and provide an update.

Cheers!

[chrjorgensen]

@krisbaehr There is no way to present any data from the SQL, since the server returns zero rows and a SQL state of 22007. We could of course forward this error message into a pop-up in Code for IBM i from the IBM i output channel, but I don't know if that would make a user more "forgiving" knowing there is an invalid date, time or timestamp somewhere and dig further into the problem...

I wonder how that bad date was created - and how it shows up in other interfaces like PDM or DSPFD?

[krisbaehr]

@chrjorgensen Gotcha. Here's one of them.

[krisbaehr]

Here's a modified SQL statement that will help others find members with invalid creation and change timestamps. As soon as a where clause or even order by clause is added to try to target these, the SQL statement breaks. This might be the best we can do.

select b.system_table_schema, b.system_table_name, b.system_table_member, b.source_type, b.partition_text, b.create_timestamp, b.last_source_update_timestamp from qsys2.systables as a join qsys2.syspartitionstat as b on (b.table_schema = a.table_schema and b.table_name = a.table_name) where cast(a.system_table_schema as char(10) for bit data) = 'WRKLIB' -- library/schema and cast(a.system_table_name as char(10) for bit data) = 'QRPGLESRC'; -- source physical file

[krisbaehr]

We used PDM to perform a copy/replace for those members, which populated the missing creation dates for those members. I am now able to expand this source physical file in the Object Browser.

Thanks for the help and for being so responsive!

[worksofliam]

@krisbaehr Glad to hear you got it fixed!

I think our goal is to improve the error message displayed to the user, and potentially how to fix this issue (from what we learned here) instead of masking issue anyway.

[zkarj735]

We just hit the same problem. I saw no errors in the VSCode interface, I just saw a source file that would not expand. I stumbled into the fact that one member had a "blank" creation date (it was the first member listed in DSPFD) and suspected it was the issue. I took a copy of it elsewhere, then deleted it and then the source file expanded OK in VSCode.

So yes, bad data is the cause, but that's at least two cases so far, and the optics are that VSCode simply refuses to expand the contents of the source file. I would expect some kind of error pop-up with whatever the plugin knows, such as "SQL failed with error XXX".

I only found this issue after we found and fixed the problem.