Find whitespace

[m.vanklink]

I’m trying to find something ending with whitespace. I have a foundset which has a field called name. One of the records in this foundset has the name-field filled with 'Bob ’ (Bob followed by a space). If I execute the following code foundset.getSize() is 0

foundset.find()
foundset.name = 'Bob '
foundset.search()

Wildcard search does find the record, foundset.name = ‘Bob%’ or foundset.name = ‘Bob %’ give me the record but could give me more (unwanted) results.

Does anybody know whether this is a (known) bug or not?

I’m using Servoy 5.2.13

[omar]

Hi Michel,

Normally, you don’t include the whitespace in your search. Just search for ‘Bob’. I assume you database field is longer than 4 characters, so even if you have entered 'Bob_ ’ it will be in the database as ‘Bob______’ (where _ is whitespace) which is why the record is not found because ‘Bob_’ and ‘Bob______’ are not the same. By default the whitespace is trimmed off when you do a search.

Searching for ‘Bob’ should find just Bob records and not Bobby which is what you want right?

[jd2p]

Hey m.vanklink,

I filed this as a bug SVY-2803 in the support system. Some of us have had that same exact issue. The workarounds currently are: changing the field type to VARCHAR (I’m asumming your field is CHAR type, since that type pads values with blanks) or using rawSQL and perform a TRIM on the field, example>

Select fieldabc from tableabc where TRIM(name) = 'Bob'

Of course the TRIM function depends on the the backend database you-re using.

Regards,

JD

[m.vanklink]

I’m not using a CHAR field, it’s a VARCHAR field. But when I store ‘Bob ’ or ’ Bob’ or ’ Bob ’ in the database I am not able to find/search it anymore without using wildcards. This seems to be caused by the fact that servoy trims the search-arguments and that is exactly what SVY-2803 is about.

[omar]

Of course TRIM is used in the find/search by Servoy otherwise you would be counting field lengths all day and filling out search criteria with the exact number of blanks. The problem is not that it can’t find 'Bob ’ but that you are searching for 'Bob ’ instead of ‘Bob’. Searching for ‘Bob’ will find 'Bob ’ but not ‘Bobby’ and ‘Bob%’ finds both. I don’t see the problem…

[m.vanklink]

Searching for ‘Bob’ does not find ‘Bob ’ nor ’ Bob’ nor ’ Bob ', I am talking about a VARCHAR here. For VARCHAR storing ‘Bob’ in a field is something else than storing 'Bob ’ in a field, I don’t know how this is with CHAR there it could be the same.

[omar]

Hi,

I am afraid using varchar is the problem. Use char instead if possible. Varchar is generally slower than char. Also varchar uses 1 or 2 extra bytes of storage in addition to the actual data for length or termination. But the main reason to use char should be that searching is a lot easier in Servoy

I did a test and created a test table in PostgreSQL with a varchar field and got the same behaviour as you did. I then changed the fieldtype to char (don’t forget to reload the table structure in Servoy) and everything worked normal again.

I am not sure if this is a bug regarding the use of varchar fields in Sevoy or that it is inherent to the fieldtype.

Kind regards,

[m.vanklink]

Using CHAR is not an option for us. Thans for your contribution.

[ROCLASI]

The datatype has nothing to do with this problem.

I have the following table in PostgreSQL

                                    Table "public.foo"
   Column |         Type          |                      Modifiers                      
  --------+-----------------------+-----------------------------------------------------
   my_id  | bigint                | not null default nextval('foo_my_id_seq'::regclass)
   name   | character varying(30) |

Where I inserted a value 'Bob ’ in the name column.
When I run the following query in a query tool I get the expected result back:

SELECT my_id FROM foo WHERE name = 'Bob ';

When I put Servoy (6.1) on top of this table and do a manual search on that form I don’t get anything back.
As already said in this thread Servoy does seem to strip the trailing spaces from your search criteria.
See the trace:

AWT-EventQueue-0	INFO	com.servoy.j2db.util.Debug	Find executed, time: 6 thread: AWT-EventQueue-0, sql: SELECT public:foo#foo2.1073741908=my_id<-5,0,0> FROM public:foo#foo2 SV:S ((public:foo#foo2.1073741909=name<12,30,0>)=([Bob])) ORDER BY public:foo#foo2.1073741908=my_id<-5,0,0> ASC
pool-4-thread-2	INFO	com.servoy.j2db.util.Debug	QuerySet { prepares = <null>, select = select my_id from foo where name = ? order by my_id asc limit ? [[Bob,200]] <0^200>, cleanups = <null> }	
pool-4-thread-2	INFO	com.servoy.j2db.util.Debug	SELECT public:foo#foo26.1073741908=my_id<-5,0,0> FROM public:foo#foo26 SV:S ((public:foo#foo26.1073741909=name<12,30,0>)=([Bob])) ORDER BY public:foo#foo26.1073741908=my_id<-5,0,0> ASC FILTER null
pool-4-thread-2	INFO	com.servoy.j2db.util.Debug	Analysing filters null
pool-4-thread-2	INFO	com.servoy.j2db.util.Debug	questiondata[1] = 201 ,type: java.lang.Integer
pool-4-thread-2	INFO	com.servoy.j2db.util.Debug	questiondata[0] = 'Bob' ,type: java.lang.String ,overrideType: 12
pool-4-thread-2	INFO	com.servoy.j2db.util.Debug	used sql select my_id from foo where name = ? order by my_id asc limit ? questiondata.length 2
pool-4-thread-2	INFO	com.servoy.j2db.util.Debug	QuerySet { prepares = <null>, select = select my_id from foo where name = ? order by my_id asc limit ? [[Bob,201]] <0^201>, cleanups = <null> }
pool-4-thread-2	INFO	com.servoy.j2db.util.Debug	SELECT public:foo#foo25.1073741908=my_id<-5,0,0> FROM public:foo#foo25 SV:S ((public:foo#foo25.1073741909=name<12,30,0>)=([Bob])) ORDER BY public:foo#foo25.1073741908=my_id<-5,0,0> ASC FILTER null
pool-4-thread-2	INFO	com.servoy.j2db.util.Debug	Analysing filters null	2E35BB30-3F88-4D5E-9D48-D7202102791D	test
pool-4-thread-2	INFO	com.servoy.j2db.util.Debug	sql select SELECT public:foo#foo25.1073741908=my_id<-5,0,0> FROM public:foo#foo25 SV:S ((public:foo#foo25.1073741909=name<12,30,0>)=([Bob])) ORDER BY public:foo#foo25.1073741908=my_id<-5,0,0> ASC
AWT-EventQueue-0	INFO	com.servoy.j2db.util.Debug	Search called for form 'foo'

[ROCLASI]

In any case Servoy is aware of it and working on it.
https://support.servoy.com/browse/SVY-2803
Seems they plan to fix it in 6.0.8 and 6.1.1.

[omar]

Hi Robert,

I immediately believe you, but then how do you explain my testresults? With varchar using find/search ‘Bob’ is not found. With char he is. Try it yourself.

[ROCLASI]

Hi Omar,

That is how Pg works with CHAR datatypes:

Values of type character are physically padded with spaces to the specified width n, and are stored and displayed that way. However, the padding spaces are treated as semantically insignificant. Trailing spaces are disregarded when comparing two values of type character, and they will be removed when converting a character value to one of the other string types. Note that trailing spaces are semantically significant in character varying and text values, and when using pattern matching, e.g. LIKE, regular expressions.

http://www.postgresql.org/docs/9.1/stat … acter.html

Hope this explain things better.

[omar]

Thanks Robert, so the datatype does matter right? This explains why it works with char and not with varchar or am I missing something?

[ROCLASI]

Hi Omar,

omar:
Thanks Robert, so the datatype does matter right? This explains why it works with char and not with varchar or am I missing something?

The datatype does matter in the difference of behavior you saw.

But what I said was that it didn’t had anything to do with the actual problem put forward by Michel. Servoy trims the search input where it shouldn’t.
Moving to a CHAR datatype would only fix the symptom but won’t fix the real issue which lies with Servoy, which seems to become fixed in the next release.

[omar]

Ok, thx Robert, I understand