Google-like searching

Classic Servoy
1

Hi, I am trying to implement a Google-like searching using an editable field with dynamic DefaultComboBoxModel for the dropdown items.

What actually happens is that every character that is typed in by the user, the system queries the top 5 records in the database that matches the entered pattern. The method databaseManager.getDataSetByQuery has a max_return_rows parameter and indication of specific columns to be retrieved, so the process is very efficient.

My SELECT statement uses database dependent functions like “::TEXT” and “to_char” so I thought I could use foundsets to make it database independent. However, when I moved my codes to foundsets the performance went down due to the minimum limit of 200 records per query.

I have 2 questions regarding my issue:

  1. Does Servoy have a way of limiting the foundset query to 5 records only?
  2. I used Postgre’s to_char method to align pattern matching of the system format to the database format. Does Servoy have a method to determine the database’s date format at runtime?

Thanks.

2

Any advice on how to make this requirement effecient and database independent using Servoy?

3

Hi Erik

erikd:
Hi, I am trying to implement a Google-like searching using an editable field with dynamic DefaultComboBoxModel for the dropdown items…

did you get any further with this?
I am looking to do something like this and so was wondering if you could share anything about how you were doing it (e.g. are you using the KeyListener plug-in or something…?)
If there is any code based on your original, back-end specific, code you can share, I’d really appreciate it (I will be doing this with a M$ SQL Server on live system, but testing on local PostgreSQL)

Thanks

Rafi

4

As for the database independant queries: Servoy 6.1 offers a QueryBuilder object. With that you can construct an SQL statement object in a object oriented fashion, so without string concatenation.

The object can then be used to either create a DataSet or a FoundSet, in the same way in the API where you normally would pass your SQL String.

This way, you can create custom SQL queries that are database independant.

Paul

5

pbakker:
As for the database independant queries: Servoy 6.1 offers a QueryBuilder object. With that you can construct an SQL statement object in a object oriented fashion, so without string concatenation.
Paul

Thanks for this.

Is the syntax in HQL format?

6

rafig:
did you get any further with this?
I am looking to do something like this and so was wondering if you could share anything about how you were doing it (e.g. are you using the KeyListener plug-in or something…?)
If there is any code based on your original, back-end specific, code you can share, I’d really appreciate it (I will be doing this with a M$ SQL Server on live system, but testing on local PostgreSQL)

Hi Rafi, we are currently using 6.0.x and we are still using native SQL using Java Swing beans with listeners. We are not using the KeyListener plugin. For the back-end, I reckon the QueryBuilder should be a big help if it works like HQL.

7

erikd:

rafig:
did you get any further with this?
I am looking to do something like this and so was wondering if you could share anything about how you were doing it (e.g. are you using the KeyListener plug-in or something…?)
If there is any code based on your original, back-end specific, code you can share, I’d really appreciate it (I will be doing this with a M$ SQL Server on live system, but testing on local PostgreSQL)

Hi Rafi, we are currently using 6.0.x and we are still using native SQL using Java Swing beans with listeners. We are not using the KeyListener plugin. For the back-end, I reckon the QueryBuilder should be a big help if it works like HQL.

Thanks for the reply
Can’t use 6.1 until it is released as this would be for a production system…
Rafi