hy
i am struggeling with the folowing problem:
i have a Query and i remove the doubles with “group by” …all good
but then when i look at the result i get a list with max 1000 records i don’t know what i am doing wrong
var aQuery = "SELECT deelnemersid FROM deelnemers WHERE deelnemersid IN "+ IDList + "GROUP BY email;";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), aQuery, null,10000)
forms.maildeelnemers.controller.showRecords(dataset)
strange I think but what happens when you change 10000 into -1. This removes the limit…
Some remarks:
when i change 10000 into -1 noting happens still the 1000 records
the querry works i get only the unique records and when the records are less than 1000 all is fine.
the first step is finding the records then i do this
var array = databaseManager.getFoundSetDataProviderAsArray(foundset,'deelnemersid');
application.closeFormDialog( true);
forms.mail.sentmaildeelnemers(array);
in sentmaildeelnemers
var arDeelnemers = arguments[0]
var IDList = "(" + arDeelnemers.join(',') + ")";
var aQuery = "SELECT deelnemersid FROM deelnemers WHERE deelnemersid IN "+ IDList + "GROUP BY email;";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), aQuery, null,-1)
forms.maildeelnemers.controller.showRecords(dataset)
thx
What happens when you use the created query to query direct on the database (without Servoy)?
BTW why don’t you copy the foundset from one form to the other?
Please read my post:
- Look at the sample code of controller.loadRecords. Loading a dataset with more than 200 primary keys is not support (due to limitations in the IN statement), you will have to use controller.loadRecords(aQuery) instead.
I think I have read somewhere that in Servoy 3 this limit has been raised to 1000. That’s why you get only 1000 records.
And then again:
- The IN statement is less than ideal (if not impossible) for too many records. How long is your IDList?
If you do an IN statement with more than 1000 records, you are issuing a query like
SELECT PK FROM table WHERE pk = 1 OR pk = 2 OR pk = 3 ... OR pk = 1000.
So you are asking your database for more than 1000 ORs. That is not a good way to query…
if i run de querry in the db i get 3847 records
BTW why don’t you copy the foundset from one form to the other?
would that make any difference?
sure would that make a difference, correct size (see post Patrick), less coding, no additional db queries etc.
i wil try to copy the foundset
i did it this way because i wanted to remove the duplicates by using GROUP BY email
is there an other way in servoy to do this?
You have to give us a little insight into your table setup.
But yes, you can use your query and do this:
var aQuery = "SELECT deelnemers.deelnemersid FROM deelnemers WHERE deelnemers.deelnemersid IN "+ IDList + "GROUP BY deelnemers.email;";
forms.maildeelnemers.controller.loadRecords(aQuery);
But as I said before, the IN-part is not nice. Explain your table/problem a bit better and we can help you with the query.
wel
i use 2 tabels
“deelnemers” with the field “email”
and 1 table
“mail” with the contents of the mail (body subject images…)
i call the form “maildeelnemers” from “mail” with
var vraag = plugins.dialogs.showQuestionDialog( 'mail aan deelnemers', ' heb je de mail al gemaakt?', 'ja', 'nee maar ik ga het nu doen.');
if (vraag == "ja") {
application.showFormInDialog(forms.maildeelnemers,-1,-1,-1,-1,"Wie wil je mailen?")
}
i make my selection and use the method
var array = databaseManager.getFoundSetDataProviderAsArray(foundset,'deelnemersid');
application.closeFormDialog( true);
forms.mail.sentmaildeelnemers(array);
and then i want to send the mail with “sentmaildeelnemers”
var arDeelnemers = arguments[0]
var bijlage = attachment;
var ond = onderwerp;
var msgText = tekst;
var IDList = "(" + arDeelnemers.join(',') + ")";
var aQuery = "SELECT deelnemersid FROM deelnemers WHERE deelnemersid IN "+ IDList + "GROUP BY email;";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), aQuery, null,-1)
//Show me the result
forms.maildeelnemers.controller.showRecords(dataset)
// send the mail
voila
when i stay under 1000 records everyting works fine
I had the same problem with the 1000 record limit (which was not present in Servoy 2.x).
To avoid it, do not use databaseManager.getDataSetByQuery, use instead controller.loadRecords(query): this will remove the 1000-record limit.
However, with this you must retrieve only Pk columns, use the ORDER BY clause and you cannot use the GROUP BY clause (see the example in Developer)
I had the same problem with the 1000 record limit (which was not present in Servoy 2.x).
Correct. In Servoy 2.x the limit was 200
Regarding the original question. What do you mean by
i make my selection and use the method
Especially: what kind of selection do you make here?
Patrick,
I am sorry, but this is not correct: 200 records were loaded in memory, but the SQL query returned the correct number of records. Now, on 3.0.1, the same query returns 1000, even if the are many more in the search.
If you are interested, please have a look at this thread 3.0: after an SQL query cannot load more than 1000 records - Classic Servoy - Servoy Community where I describe the problem.
With Servoy 2.x you just had to loop through the records and load 200 at a time to have a look at all the records found by the query, with 3.0 this is not possible.
I am sure of this because a method that was working perfectly in 2.2.5 returned strange results.
Sure, my SQL query was much simpler than that of fritsyrules, and I frankly don’t know if in Servoy 2.x the results would have been different.
Maybe half correct? This is the sample code of controller.loadRecords() in Servoy 2.x:
//2) to load a primary key dataset (max ~200 records due to limits with SQL IN statement)
//var dataset = databaseManager.getDataSetByQuery(…);
//controller.loadRecords(dataset);
Maybe this restriction was only mentioned in the sample code and never really applied by Servoy? I have never tested this, but as I stated earlier in this thread, the main limitation of this approach in my eyes is the IN statement in general. It is just not so great to load 8132 primary keys in an IN statement. So I guess what Servoy means is this:
If the amount of records is not too big, you can load the dataset directly. If you expect any number of records, load the query instead.
I think we are both correct: I was referring to a “simple” query, without any IN or GROUP BY clause. I’m sure that in this case the 200 record limit was enforced.
But, if the problem was with the IN clause, e.g. with the SQL as stated in the documentation example, I don’t understand how this limit has now been pushed to 1000.
Wait a minute. The remark “due to limits with SQL IN statement” relates to something else: you can fire any query to the database using getDataSetByQuery. You can use the resulting dataset for HTML rendering, writing text files etc. There should be NO limit to the amount of data in such a dataset. BUT: if you want to use controller.loadRecords(dataset), Servoy takes your dataset and constructs an IN statement using the primary keys in your dataset. That’s why there is this limit. It is not you issuing a query using IN or GROUP BY or whatever, it’s Servoy doing that when you use controller.loadRecords().
When you use loadRecords(dataset), my understanding is the servoy has to “wrap” your dataset in its own query, something like:
select primary_key from table1 where primary_key in (dataset)
Thus it limits the amount of records it loads.
When you use loadRecords(query), servoy doesnt have to “wrap” the query as you are being implicit in the pks you want to display and their order.
That’s my understanding anyway…
Exactly! That’s what I am trying to say for a few days now.
I think we are confusing a few things.
A select statement with a IN ( array with values ) is subject to a limit. Probably just because the select statement can only be so long, string-wise and depending on the back-end database.
SELECT theVALUE FROM theTable WHERE theOtherValue in (1,2,3,4,5,6, ... , 10000,10001);
But a select with a sub-select doesn’t have that limit.
SELECT theVALUE FROM theTable WHERE theOtherValue in (SELECT theValue2 FROM theTable2);
Now a DataSet object knows what SQL and parameters it used to get the set of data so Servoy can use that info to use the sub-select technique. So no limit there.
As for Servoy 2.2.x I have used datasets before to load a controller. The effect it had was that unlike the way Servoy normally only loads 200 records (until you scroll pass the 200 records) it would load the full dataset. I have done this with over 20,000 records.
I haven’t tested this yet with 3.x but it should work the same.
As for using controller.loadRecords(SQL).
This will load the data like Servoy always does…with batches of 200.
Hope this helps.