Re: NBN Exchange addin - new version
I have done some work on the NBN Exchange format export filter for Recorder 6 and, hopefully, fixed things that have been reported here. The new version is 6.13.2.22 and dated 16/01/2009. Since there are quite a few changes in the Stored Procedures and User Defined Functions in the SQL Server database, it is important that you uninstall the existing version, restart Recorder 6 and then install the new version from scratch. That way, the database updates will be applied properly.
Here are some details of the fixes I have made.
Fixed so that SAMPLE.LOCATION_NAME is returned in the SiteName field if no LOCATION is specified in a record.
This change affects the Stored Procedure nbn_exchange_basic_update. It adds "OR LEN(SiteName)=0" added to the WHERE clause, as suggested by Graham French so that it happens whether the SiteName field is either NULL or empty
Strip RTF codes from comments
This change affects Stored Procedure nbn_exchange_add_comment. Comments are passed through the Rec6 user defined function ufn_RtfToPlaintext. Note that this is SLOW!
Strip Return and Linefeed characters from comments
This change affects Stored Procedure nbn_exchange_add_comment. A new user defined function nbn_exchange_strip_LFCR is added to do this
These two changes have quite a performance hit. To minimise this, the stored procedure has been modified to works as follows:
- queries the records to be exported for cases WHERE comments start "{\rtf" and passes them through ufn_RtfToPlaintext
- queries records to be exported where the export comment field is still null and WHERE comments contains CR or LF
and passes them through nbn_exchange_strip_LFCR
- simply copies in the comment for any remaining records where the output comment field is still null.
Hopefully, the third case is the majority and avoids using these slow text modification functions unless they are really necessary.
Fix for problems where the addin fails to export anything.
It seems that the Recorder interface function IExportFilter6.ExportFileWithFilteredInvalids is not reporting the LastExportError from the addin if it returns False (as IExportFilter.ExportFile does). Consequently exceptions in the addin are "silent". If the addin encountered an error, it just terminated without Recorder reporting any error message. Fixed so that the addin shows its own error message if an exception occurs internally. So, these cases where the addin did nothing, occurred because of an internal error - and it is these errors that really need to be tracked down. I have found a couple, explained below, but there may be others.
One reason this happened was due to an unusual condition where nbn_exchange_date_to_string attempted to convert a NULL date to a string and got stuck in an infinite loop. This (eventually) caused a time-out exception. The user defined function nbn_exchange_date_to_string has been modified to break out of this loop. The way dates are handled by Stored Procedure nbn_exchange_basic_update has been modified to ensure NULL dates are exported as expected.
Another reason (Craig Slawson's problem) was that one of his measurement fields was called "Substrate" - which duplicates an existing field name. This caused an SQL error when trying to add the duplicate column name to the temporary table. Such duplicated names are now suffixed with a digit - starting at 2. So in this case, the measurement will be labeled "Substrate2" in the export file.
Fixed problems where the exporter times out during large exports.
The routine to call the Stored Procedures now sets the CommandTimeout parameter to zero (it was previously using the default setting of 30 secs). Zero means "never timeout".
Records attached to user-added species were being exported.
This causes problems when attempting to import at the NBN Gateway end because the TAXON_VERSION_KEY is not in the NBN Taxon Dictionary. Fixed so that rows where the TAXON_VERSION entry is not system supplied are no longer exported.