Searching ntext and text fields through servoy

Classic Servoy
1

I’ve created a new search from where the user clicks a button and it pops up the search form (same as using F3).

I have some fields i need the users to be able to search on. They are very long fields so i made it into a text data type on SQL server 2000. When i try searching on it, i get this error meesage

"com.servoy.j2db.dataprocessing.DataException: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. "

Is there any ideas, interface wise how you guys would get around this limitation?

2

This is a limitation of the database. Try googling for full text index. I think you can turn on some special indexing on the database side that allows you to do that.

3

patrick:
This is a limitation of the database. Try googling for full text index. I think you can turn on some special indexing on the database side that allows you to do that.

Thanks for the info. I set up a full text index for SQL server 2000.

http://www.databasejournal.com/features/mssql/article.php/1438211

however, it still gives me the same error. Is there also somehting I have to do on the Servoy side?

4

I’d look for the query that is being executed by Servoy and try to execute it directly on the DB, to see if your DB is capable of running the query.

Paul

5

Hi Sammy,

I do this type of query all the time in SQL Server 2000 in Servoy. I think you just don’t have the query right. You always have to do a ‘LIKE’ search, you can never do a ‘DISTINCT’ and you’ll always have to programmatically make sure that whatever values the user puts in you will always surround it with percent signs (%) on both sides of their selection.

John

6

Yes, but we are talking about a TEXT column, which is not a varchar, but a large text column (like the CLOB in Oracle). There, things are a bit different. It also sounds like a Servoy generated query, so I would follow Pauls advice and check the actual SQL fired by Servoy.

Probably, it’s what the error says. You can’t do exact searches, but only LIKE searches. So what happens if you search with a wildcar (%) to force Servoy to issue a LIKE query?

7

Yes I do the same search here (i.e. a TEXT column). Thus you can’t do things like DISTINCT that you can with a varchar. What I hadn’t thought about was that the query might be Servoy generated. I presumed Sammy was creating the query himself but on rereading it I think you are both correct in assuming he’s having Servoy generate the statement.

8

john.allen:
Yes I do the same search here (i.e. a TEXT column). Thus you can’t do things like DISTINCT that you can with a varchar. What I hadn’t thought about was that the query might be Servoy generated. I presumed Sammy was creating the query himself but on rereading it I think you are both correct in assuming he’s having Servoy generate the statement.

Allen, you’re right, all i needed was a % in the search criteria in my TEXT column and it works. i think the cataloging is not even needed.

Thanks again for your help everyone!

Long Live The Servoy Nation!!!
sammy

9

Yes the indexing doesn’t have anything to do with it. However it WILL be useful if your database column gets long as well as wide! I have a million and a half records with a TEXT column and SELECTS are very fast.