Strange issue with multiple find requests

Classic Servoy
1

Hi,
one of my clients just pointed out a strange issue that seems to occur when trying to do a ‘find’ with multiple requests.
The problem only seems to happen with certain columns
What happens is the client goes into ‘find’ mode, enters a criteria into a field, then does ‘New (Record) Request’, enters a new value in the same field, repeats etc. and then starts search and the results are only the result from the FIRST and LAST requests, not the ones in between… :?
I set Servoy to output the SQL & Params to help me :-

SQL = select products103.product_id from products products103 where products103.comments like ? or products103.comments like ? or products103.comments like ? order by products103.product_id asc
SQL Params = [%a%,%b%,%c%]
SQL = select products103.product_id from products products103 where products103.title like ? or products103.title like ? order by products103.product_id asc
SQL Params = [%a%,%c%]
SQL = select products103.product_id from products products103 where products103.subtitle like ? or products103.subtitle like ? order by products103.product_id asc
SQL Params = [%a%,%c%]
SQL = select products103.product_id from products products103 where products103.product_id between ? and ? order by products103.product_id desc
SQL Params = [2000,2002]
SQL = select products103.product_id from products products103 where products103.product_id = ? or products103.product_id = ? order by products103.product_id desc
SQL Params = [2000,2002]

In the 1st one above, I am doing 3 requests into the ‘comments’ field - this works
In 2nd, I also tried same 3 params/requests into the ‘title’ field - doesn’t work
in 3rd, same 3 in ‘subtitle’ - doesn’t work
In 4th I tried 1 request with a range of product_id’s, this returned 3 results
in 5th I tried doing those product_id’s as 3 individual requests [2000,2001,2002] - doesn’t work

I obviously don’t have time to test with every column in every table in the solution, but this seems to be a serious issue and I can’t see anything I am doing to make it not work (title,comments & subtitle are all varchars of 255 or less)

Client is running Servoy 5.2.11, as was I
I then updated to 5.2.15 to see if it was a bug that was fixed, still doesn’t work.
Back end is MS SQL Server 2005
Driver is jTDS (net.sourceforge.jtds.jdbc.Driver)
Driver URL is jdbc:jtds:sqlserver://x.x.x.x:1433/abrsm_pubs;SelectMethod=cursor

This is potentially a very worrying issue, as if user’s are doing searches and trusting the results, but those results are not correct…

Please can anyone advise me as to what might be going wrong??

Thanks

Rafi

2

[^^^BUMP!^^^]
Anyone???

3

Rafi, the best way, is to create a reproducable case and file it in the support-system!

4

Hi Harjo,

Harjo:
Rafi, the best way, is to create a reproducable case and file it in the support-system!

I’m not sure it’s something I can make a reproducible cut down solution to file as I think it might be a whole load of inter-connected issues, but was really just trying to see if anyone had had a similar issue before I tried to take it further.

Thanks

Rafi

5

rafig:

SQL = select products103.product_id from products products103 where products103.comments like ? or products103.comments like ? or products103.comments like ? order by products103.product_id asc

SQL Params = [%a%,%b%,%c%]
SQL = select products103.product_id from products products103 where products103.title like ? or products103.title like ? order by products103.product_id asc
SQL Params = [%a%,%c%]
SQL = select products103.product_id from products products103 where products103.subtitle like ? or products103.subtitle like ? order by products103.product_id asc
SQL Params = [%a%,%c%]
SQL = select products103.product_id from products products103 where products103.product_id between ? and ? order by products103.product_id desc
SQL Params = [2000,2002]
SQL = select products103.product_id from products products103 where products103.product_id = ? or products103.product_id = ? order by products103.product_id desc
SQL Params = [2000,2002]




Not sure, but LIKE is case sensitive. ILIKE isn't.
Also the 5th can be written as 
... where products103.product_id IN (2000,2002) ...
6

Hi Christian

swingman:
Not sure, but LIKE is case sensitive. ILIKE isn’t.
Also the 5th can be written as
… where products103.product_id IN (2000,2002) …

All the SQL was what Servoy generated when a user does a ‘find by example’ (i.e. entering search criteria in data entry form)…
Thanks
Rafi

7

Sorry, red herring then…

8

Hi Rafi,

Did you run the SQL also in a query editor to see if it does give the correct result?
Here is the combined SQL/criteria:

SELECT product_id FROM products WHERE title LIKE '%a%' OR title LIKE '%c%' ORDER BY product_id ASC;
SELECT product_id FROM products WHERE subtitle LIKE '%a%' OR subtitle LIKE '%c%' ORDER BY product_id ASC;

Hope this helps.

9

Hi Robert

ROCLASI:
Hi Rafi,

Did you run the SQL also in a query editor to see if it does give the correct result?
Here is the combined SQL/criteria:
Hope this helps.

Thanks for the reply.
The issue is that Servoy is NOT creating the correct SQL query from what the user entered into the fields on the form, so in this case, the user did
request 1 : in the ‘title’ field they entered ‘%a%’
request 2 : in the ‘title’ field they entered ‘%b%’
request 3 : in the ‘title’ field they entered ‘%c%’
but Servoy only generated the SQL from the first and last requests (in this case 1 & 3), however many are entered in between…
Thanks

Rafi

10

Rafi,

Something seems to go wrong in creating and/or filling the find records.
From the generated sql it seems that the second record is not created or is not filled for the title search.
And for the comments search it seems ok.

Basically, Servoy just generates conditions for each record and ORs them together into one condition.

Try to inspect the records before the search() call.
Are there really 3 records and does each have a different title value?

Rob

11

Hi Rob,

rgansevles:
Rafi,

Something seems to go wrong in creating and/or filling the find records.
From the generated sql it seems that the second record is not created or is not filled for the title search.
And for the comments search it seems ok.

Basically, Servoy just generates conditions for each record and ORs them together into one condition.

Try to inspect the records before the search() call.
Are there really 3 records and does each have a different title value?

Rob

I will have to check when I am next at client, or get a chance to log in remotely…
I don’t have the ‘page controller/navigation thing’ visible as the form is normally in a frame (I am using the old CRM framework like on the demo.servoy.com site) which I do a ‘controller.show()’ on before going into find mode…

Thanks

Rafi