goldcougar:
Each database has its own limits for how many parameters can be stuffed into the IN part of the statement. I believe MySQL has a max of 200.
Scott,
Servoy automatically creates different sql when an in-clause with more than 200 values is used.
So using Servoy you do not run in that limit.
Rob
Rob, Servoy behaves really strange (if not scary).
This is what I do:
var query = "SELECT servcard.servcardid FROM servcard JOIN assignment ON servcard.servcardid = assignment.servcardid WHERE (servcard.employeeid = '" + globals.userName + "' OR assignment.employeeid = '" + globals.userName + "') ORDER BY servcard.servcardid DESC";
databaseManager.addTableFilterParam(serverName, tableName, "servcardid", "IN", query, filterName);
This does not pop any errors and seems to work because the foundset is reduced to 30 records.
However, the query result must be 256 records (it is if I run databaseManager.getDataSetByQuery() or apply the query externally).
That’s not it yet.
I use an aggregate to count the total number of records in the table. It changes correctly when I apply filters not using “IN” but after I run the above code the number is 2 (30 records showing on the form and in debug window).
And this is not it.
If I stop debugger on the line where the filter is applied, execute it with ctrl+shift+I and re-evaluate the ‘foundset’ expression in my expressions window, this is what I get in place of my foundset :
[attachment=1]1.jpg[/attachment]
Now my foundset has 165 records (as opposed to 30 that it shows on the form). The aggregate count number is still 2.
The scary part is that records 1 to 4 and 17 to 165 have become null.
The selectedIndex is 180. No idea where this figure comes from…
[attachment=0]2.jpg[/attachment]
If I execute the same line again the number of records in the foundset changes to 150 or 195 or some other figure.
Applying the filter returns true all the time.
The aggregate remains 2 all the time.
That’s a mess.
What am I doing wrong?
Please help.
The null records in the debugger view are no problem, records may be removed from memory, they will be retrieved again when needed.
For the sql exception, can you create a sample solution that shows this?
I guess that this only happens in the foxpro server?
Please submit a case and include the driver and a foxpro file.
Servoy automatically creates different sql when an in-clause with more than 200 values is used.
So using Servoy you do not run in that limit.
Rob
Is this something new? back in 4.x if you use an array of more than 200 values with mysql if failed with errors. were there release notes on this change?
So, what’s the optimal way to delete nowdays when table filters are present?
Both works:
databaseManager.removeTableFilterParam //only filter related to the table where record is about to be deleted
foundset.deleteRecord
databaseManager.addTableFilterParam //add back all the filters, initial setup
and
plugins.rawSQL.executeSQL
I use Servoy 5.2.8
Currently I use 1st option (remove/re-add) as want to keep the foundset
Remove/add filters will fire reload events for that table through the entire client, all foundsets on that table will reload twice.
Note that table filters are designed for limiting access to certain records.
Records that do not match the filter should never be seen by the user and should also not be deletable.
If the user does need access sometimes,I guess best way is to use foundset filters, so only some foundsets are filtered.
If you do need to use table filters, the rawsql approach is probably best for delete.
