1 Reply by Lizzy Peat

  • Lizzy Peat
  • Lizzy Peat
  • Member
  • Offline
  • From: Hampshire (HBIC)
  • Registered: 17-05-2006
  • Posts: 38

Re: Taxon Filter Error

We are currently using Version 6.7.2.77
(This error did not occur in previous versions)

Clicking on a species under surveys, clicking on the filter icon and then
doing taxon name is equal to meles meles
produces the following error;

Exception occurred in application Recorder 6 at 02/11/2006 11:33:31.
Version : 6.7.2.77

Exception path:
EOleException : Line 2: Incorrect syntax near '='

Last event\actions:
  TfrmSurveyDetails created
  TfrmSurveyDetails destroyed
  TfrmTaxonOccurrences created
  TfrmTaxonOccurrences destroyed
  TfrmSurveyDetails created
  TfrmSurveyDetails destroyed
  TfrmTaxonOccurrences created
  TfrmTaxonOccurrences destroyed
  TfrmSurveyDetails created

Operating System : Windows 2000  5.00.2195  Service Pack 4
Physical Memory available : 523,252 KB

DLLs loaded:
  advapi32.dll (5.0.2195.7038)
  comctl32.dll (5.81.4916.400)
  comdlg32.dll (5.0.3700.6693)
  gdi32.dll (5.0.2195.7073)
  HHCtrl.ocx (5.2.3790.309)
  kernel32.dll (5.0.2195.7006)
  mpr.dll (5.0.2195.6824)
  MS5.Dll (5.0.0.12)
  MS5User.Dll (5.0.0.4)
  odbc32.dll (3.520.9053.0)
  ole32.dll (5.0.2195.7059)
  oleaut32.dll (2.40.4522.0)
  shell32.dll (5.0.3900.7080)
  user32.dll (5.0.2195.7032)
  version.dll (5.0.2195.6623)
  winmm.dll (5.0.2161.1)
  winspool.drv (5.0.2195.6659)

Information has been saved to the file F:\LRC\Recorder6Server\LastError.txt

Similarly
Doing taxon name is Not equal to meles meles produces-
EOleException : Line 2: Incorrect syntax near '<'
Blah blah

Taxon name starts with mele produces-
EOleException : Incorrect syntax near the keyword 'LIKE'
Blah blah

Taxon name contains mele produces-
EOleException : Incorrect syntax near the keyword 'LIKE'
Blah blah

Best,

Lizzy Peat
HBIC

Lizzy Peat's Website

2 Reply by cporter

  • cporter
  • Member
  • Offline
  • From: Ulster Museum, Belfast, NI
  • Registered: 26-05-2006
  • Posts: 15

Re: Taxon Filter Error

Hi,

I've mentioned this before on the forum.  I get the same error with any of the taxon filter conditions. 

Exception occurred in application Recorder 6 at 06/11/2006 08:22:47.
Version : 6.7.2.77

Exception path:
EOleException : Line 2: Incorrect syntax near '='

Last event\actions:
  TfrmSurveyDetails created
  TfrmSurveyDetails destroyed
  TfrmSurveyDetails created
  TfrmSurveyDetails destroyed
  TfrmSampleDetails created
  TfrmSampleDetails destroyed
  TfrmTaxonOccurrences created
  TfrmTaxonOccurrences destroyed
  TfrmSurveyDetails created

Operating System : Windows XP  5.01.2600  Service Pack 2
Physical Memory available : 1,047,004 KB

DLLs loaded:
  advapi32.dll (5.1.2600.2180)
  comctl32.dll (5.82.2900.2180)
  comdlg32.dll (6.0.2900.2180)
  gdi32.dll (5.1.2600.2180)
  HHCtrl.ocx (5.2.3790.1280)
  kernel32.dll (5.1.2600.2180)
  mpr.dll (5.1.2600.2180)
  MS5.Dll (5.0.0.12)
  MS5User.Dll (5.0.0.4)
  odbc32.dll (3.525.1117.0)
  ole32.dll (5.1.2600.2595)
  oleaut32.dll (5.1.2600.2180)
  shell32.dll (6.0.2900.2620)
  user32.dll (5.1.2600.2622)
  version.dll (5.1.2600.2180)
  winmm.dll (5.1.2600.2180)
  winspool.drv (5.1.2600.2180)

Information has been saved to the file R:\LastError.txt

Any Ideas?

Thanks,
Catherine

Catherine

cporter's Website

3 Reply by Sarah Shaw

  • Sarah Shaw
  • Veteran
  • Offline
  • Registered: 23-06-2006
  • Posts: 267

Re: Taxon Filter Error

Hi

Although I have reported some problems with the filter, including unhandled errors returned with rich text fields such as descriptions or comments, and problems with what the filter returns in certain situations (which should be fixed in the next version), I haven't come across this problem, nor can I reproduce this using my copy of 6.7.2.77.

I've emailed Dorset Software to ask if they have any idea's on what could be causing this error.

Kind regards,

Sarah

Sarah Shaw
Biodiversity Information Assistant
JNCC

Sarah Shaw
Biodiversity Information Assistant
JNCC

4 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Taxon Filter Error

Hi Sarah

The filtering code is driven by the contents of the Usable_Field table. I know that Charles Roper reported filtering errors which were down to differences between the contents of this table and the official version. My guess is that some particular upgrade sequence has cause the errors to occur.

If you can have a look at the difference between a working and non-working installation in this table then it might give us some clues. The Calculation_SQL field is the place to start looking - the following SQL will bring back the relevant rows:

select * from usable_field where apply_to='f'

Cheers,

John van Breda
Biodiverse IT

johnvanbreda's Website

5 Reply by Sarah Shaw

  • Sarah Shaw
  • Veteran
  • Offline
  • Registered: 23-06-2006
  • Posts: 267

Re: Taxon Filter Error

Hi John

Thanks for that.

Catherine has kindly provided a copy of the results using the query you provided and I have compared that with the results from our Usable_Field table in V6.7.2a here.

The results showed that 3 rows within Catherine's table were different from ours. These were rows with Usable_Field_key = 'ABABABAB00000089', 'ABABABAB00000088' and 'ABABABAB00000086'. It appears that the text has been truncated somehow.

We therefore provided the following SQL to update 'ABABABAB00000086':

UPDATE Usable_Field
SET Calculation_SQL='SELECT TXO.Taxon_Occurrence_Key AS ItemKey FROM Taxon T, Taxon_Version TV, Taxon_Occurrence TXO, Taxon_List_Item TLI, Taxon_Determination TD WHERE TLI.Taxon_List_Item_Key = TD.Taxon_List_Item_Key AND TXO.Taxon_Occurrence_Key = TD.Taxon_Occurrence_Key AND TV.Taxon_Version_Key = TLI.Taxon_Version_Key AND T.Taxon_Key = TV.Taxon_Key AND Preferred = True AND Item_Name'
WHERE Usable_Field_Key='ABABABAB00000086'

Having run this Catherine reports that the filter now works, however, she gets a timeout error when she tries to open one of the surveys containing the search criteria.

Catherine thinks this may be a problem with her system and is going to do some further investigating, but I wondered if you have any suggestions as to why this might be occurring?

Also, do you think it is worth Lizzy trying to run the above SQL?

Thanks for your help,

Sarah

Sarah Shaw
Biodiversity Information Assistant
JNCC

Sarah Shaw
Biodiversity Information Assistant
JNCC

6 Reply by Sarah Shaw

  • Sarah Shaw
  • Veteran
  • Offline
  • Registered: 23-06-2006
  • Posts: 267

Re: Taxon Filter Error

Hi

Just a quick update - Catherine has tracked down and resolved the problem with the time out.

Regards,

Sarah

Sarah Shaw
Biodiversity Information Assistant
JNCC

7 Reply by cporter

  • cporter
  • Member
  • Offline
  • From: Ulster Museum, Belfast, NI
  • Registered: 26-05-2006
  • Posts: 15

Re: Taxon Filter Error

Yes, thanks to the Update Sarah sent me, the problem with the Taxon Filter seems to be fixed

Thanks again,
Catherine

Catherine

cporter's Website

8 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Taxon Filter Error

Sarah

There is no harm in running this SQL so I don't see why Lizzy shouldn't try it. In fact if you want it could be added to the 6.9.3 upgrade kit as a standard script so that it is run against all databases.

Cheers

John

John van Breda
Biodiverse IT

johnvanbreda's Website

9 Reply by Sarah Shaw

  • Sarah Shaw
  • Veteran
  • Offline
  • Registered: 23-06-2006
  • Posts: 267

Re: Taxon Filter Error

Hi

Thanks for that John. I'll discuss adding them with Steve.

I'll also look into the differences with the two other rows concerned and provide some SQL to update those when I can.

Many thanks,

Sarah

Sarah Shaw
Biodiversity Information Assistant
JNCC

Sarah Shaw
Biodiversity Information Assistant
JNCC

10 Reply by Darwyn Sumner (LERC)

  • Darwyn Sumner (LERC)
  • Veteran
  • Offline
  • Registered: 07-06-2006
  • Posts: 176

Re: Taxon Filter Error

We have 62 records arising from the "select * from usable_field where apply_to='f'". I've passed this list on to Sarah for comparison with a clean copy.
What exactly is going on here? Our Usable_field table is bound to be different from Sarah's as the table is used to make additional fields available for reporting purposes.

11 Reply by Darwyn Sumner (LERC) (edited by Darwyn Sumner (LERC) 29-11-2006 16:25:31)

  • Darwyn Sumner (LERC)
  • Veteran
  • Offline
  • Registered: 07-06-2006
  • Posts: 176

Re: Taxon Filter Error

Lizzy says "Oh look, isn't that amazing, it works"
(This is after Sarah sent me that fix with full instructions - by email)

12 Reply by Sarah Shaw

  • Sarah Shaw
  • Veteran
  • Offline
  • Registered: 23-06-2006
  • Posts: 267

Re: Taxon Filter Error

Hi John

I've now emailed Darwyn and Catherine the necessary SQL to update all the rows that appear to have been truncated.

I've discussed this with Steve and we are going to add this to the upgrade as an additional script.

Can I check with you that the next script name we should use is '0000003T.sql'?

Many thanks,

Sarah

Sarah Shaw
Biodiversity Information Assistant
JNCC

Sarah Shaw
Biodiversity Information Assistant
JNCC