Strange search behavior

Classic Servoy
1

I recently upgraded to

Version R2 2.2rc1-build 319
Java version 1.4.2_04-b05 (Windows XP)

I had the following search method attached to a on show event prior to upgrade:

controller.find()
order_classification = “Flexible Endoscope”
estimate_completion_date = “^”
forms.FlexQCServiceEstimate.os_order_ = “^”
foundset.sort(‘CreatedRecvd_DateTime asc’);
controller.search();
// Count Found Set
globals.Found_Set_Count = forms.FlexQCServiceEstimate.controller.getMaxRecordIndex();

Everything worked fine:

I noticed that the method was changed to the following after the upgrade:

controller.find()
order_classification = “%Flexible Endoscope%”
estimate_completion_date = “^”
forms.FlexQCServiceEstimate.os_order_ = “^”
foundset.sort(‘CreatedRecvd_DateTime asc’);
controller.search();
// Count Found Set
globals.Found_Set_Count = forms.FlexQCServiceEstimate.controller.getMaxRecordIndex

As you can see, wild cards are added. If I remove them I get the following error:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

I am confused as to why I now need to add wildcards, when I don’t want them.

Additionally, I wanted to add another request to my find criteria:

controller.find()
order_classification = ‘%Flexible Endoscope%’
estimate_completion_date = “^”
forms.FlexQCServiceEstimate.os_order_ = “^”
controller.newRecord()
order_classification = ‘%Flex Accessories%’
foundset.sort(‘CreatedRecvd_DateTime asc’);
controller.search();
// Count Found Set
globals.Found_Set_Count = forms.FlexQCServiceEstimate.controller.getMaxRecordIndex();

When I run this method, it returns the correct found set, but it changes the values for classification from Flex Accessories to %Flex Accessories%
but it does not change the classification values for the records where the classification values are Flexible Endoscope.

I am a bit bewildered, can someone shed some light?

thanks,
Erich

2

I can’t believe that an update changed a method?!

3

Ok, lets say that I changed the methods and do not remember doing so. Why would I have to add the wildcards to get results, when the value is exact.

Why would I get an error without the wild cards?

Also, why would wildcards get added to values in the additional find request, the after the search results are displayed?

This is the first time I have added a additional find request to a method, so I may be doing something wrong.

4

I seemed to get everything working, by deleting records.

I Still get the following error if I do not use wildcards when performing a search (manually even).

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

5

And the column CreatedRecvd_DateTime is obviously not a TEXT column, but a datetime?

6

Correct,

If I enter enter Ctrl F, type in ‘Flexible Endoscope’ in the classification field (my only criteria), and search…I get the error

7

I found the issue. In the backend for this field, I have the data type set to text, the length cannot be modified.

In servoy data provider the field displays as text with a length of 2147483647 (?). I changed the backend datatype to varchar, length 50. and I no longer need the wildcards to search within servoy.

So it seems to be a datatype issue

8

That’s why I asked about the field type. TEXT in MS SQL is a text field with a maximum length of 2^31-1. Since you can’t search inside a blob you need this kind of field if you want to store long texts that you can search. The maximum varchar length in MS SQL is 8000, if you need more, you need a TEXT type.

9

Ok, Thanks for the help, I don’t know where servoy got that length from, I guess its a default setting

10

2^31-1 = 2147483647

Servoy knows math :lol:

11

maybe if it gets smarter, it can teach me!