1

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.

2

Re: NBN Exchange addin - new version

This update does not appear on the addin page http://forums.nbn.org.uk/recorderWebsite/Addins.htm, which shows v6.13.0.14.

Murdo

3

Re: NBN Exchange addin - new version

Following a few more tweaks to the addin since Stuart's post the updated version is now available from the addins page of the website:

http://forums.nbn.org.uk/recorderWebsite/Addins.htm

Thanks,
Lynn

4

Re: NBN Exchange addin - new version

Another small fix added:

The way the SiteName is obtained for an exported record is as follows:
1. Attempt to get it from SAMPLE.LOCATION_NAME
2. If we didn't get anything, attempt to link to a LOCATION via SAMPLE.LOCATION_KEY and obtain the preferred name from LOCATION_NAME.ITEM_NAME
3. If we still didn't get anything, attempt to link to a LOCATION via SURVEY_EVENT.LOCATION_KEY and obtain the preferred name from LOCATION_NAME.ITEM_NAME

If the name is obtained in case 1, then it does not involve a link to the LOCATION table, so there is no SiteKey and the value in this column should be left blank. But if it is obtained by cases 2 or 3 where the name does come from a LOCATION entry, then SiteKey ought to be populated with LOCATION_KEY. This wasn't happening - SiteKey was always empty!

This has been fixed. This fix is included in the version posted by Lynn. The .ocx file should be version 6.13.2.24 and dated 21/01/2009 and the nbn_exchange.sql file dated 22/01/2009.

5 (edited by Stuart 23-01-2009 11:44:58)

Re: NBN Exchange addin - new version

A note about installation:

This addin relies heavily on stored procedures and functions which are installed in the SQL Server database (NBNData). These must have the necessary permission to be run by your users. The installation process has to be run by a user with database administration rights in order to grant these permissions.

This is not normally an issue for individuals who are running Recorder 6 on their own machine. They will generally be both the Recorder system manager and the SQL Server database administrator by default.

It will probably be an issue for organisations with the database located on their network. In these cases, the database administrator (DBA) for the SQL Server may well be a different person to the Recorder system manager who will not necessarily have DBA rights..

Installation process:
During the installation of the addin you will see a dialog box entitled "Database login" pop up. It has an explanation:

This process needs to install some stored procedures in your database. To do this, you need to be logged in as a system administrator. Please login using a system administrator account.

and has two options:

    Use my Windows account (the default)
    Use the following SQL Server account: - with prompts for a username and password.

You need to take careful account of this! If you just press [OK], but you are logged in as a user that doesn't have DBA rights, then the stored procedures and functions will get installed, but they won't be granted the necessary permissions to be run by an ordinary user. Later on, when you try to run an export, you will see an error message saying

Execute permission denied on object 'nbn_exchange_create_tables'

So, when you install the addin, make sure that you are logged on as a user with sufficient rights to grant these permissions, or that you know the username ("sa" unless whoever installed SQL Server changed it) and password of the DBA.

To check that the stored procedures and functions have the necessary permissions:
- Use SQL Server management studio to open NBNData
- Open NBNData - Programmability - Stored procedures in the Object explorer
- right-click on one of the stored procedures with a name starting "dbo.nbn_exchange" - nbn_exchange_add_comment is the first one in the list
- choose Properties from the menu
- click Permissions in the "Select a page" list

You should see a list of five "Application roles" in the top pane of the dialog with names starting "R2k_..". The table of permissions in the bottom pane should have the Grant - EXECUTE box ticked for each of these roles.

Note that, if you have the necessary rights to change these permissions, the controls in this dialog will be active and you will be able to tick or untick boxes, etc. If you don't have the necessary rights, the controls will be greyed-out and you can look but not touch.

What to do if the stored procedures and functions don't have the necessary permissions:
- Login to Recorder 6
- Uninstall the NBN Exchchange addin
- close Recorder 6
- log in as a user with database administration rights
- re-install the addin

6

Re: NBN Exchange addin - new version

Hi Stuart
I've managed to install the add-in on the PC that acts as both the server and the database. Should the workstation machine now automatically be able to use it too?
Thanks in advance
David

[color=blue]David Angel[/color]
[color=blue]Data Officer[/color]
[color=blue]The Wildlife Information Centre[/color]

7 (edited by Stuart 01-04-2009 15:10:12)

Re: NBN Exchange addin - new version

The workings of this addin are essentially in two parts:
1 The stored procedures that live in the SQL Server. When you installed the addin, these should have been added to the database and are therefore available to everyone who has access to it (and sufficient permissions).
2. The code for the program that lives in the "NBNExchange.ocx" file and is implemented as a COM object. This has to be available to the particular machine and registered in its registry so that Recorder6 can find and use it.

So in answer to your question: no it isn't available to users from any workstation. Whilst they potentially have access to the stored procedures, the other workstations don't know about the program code. To do this bit (install and register the COM object) you will need to install the addin on each workstation where you want to use it.

8

Re: NBN Exchange addin - new version

I'd hoped that this might cure the mishandling of site names, which led us to getting Mike to write an alternative XML query, but it doesn't work. If there's a location name it returns that whether or not there is a location. Site Key is always left blank. So people will be glad to know the record is from "Meadow to the North", or "Hedgerow" but the site name will be a complete mystery. Sometimes the site name field is left entirely blank even though there is a location.

Graham Hawker
Thames Valley Environmental Records Centre