I encountered a problem with databaseManager.getDataSetByQuery.
See this example:
The following query works in the ‘MySQL monitor’ command line client:
select country_name from proceedings_countries where country_name is not null limit 10;
But it does not work in Servoy using databaseManager.getDataSetByQuery. Now, if I run the same query but I add the PK column, proceeding_country_id, it works.
These work:
[attachment=2]Capture1.JPG[/attachment]
[attachment=1]Capture2.JPG[/attachment] This does not work:
[attachment=0]Capture3.JPG[/attachment]
This is the error message shown in the Console:
For input string: “Dominican Republic” Wrapped java.lang.RuntimeException: Error setting form foundset, For input string: “Dominican Republic” (C:\Users\JCarlos\workspace\srms_workspace\fcpacw_queries_testing\forms\matters_all_queries.js#25)
** at C:\Users\JCarlos\workspace\srms_workspace\fcpacw_queries_testing\forms\matters_all_queries.js:25 (executeQuery)**
This of course happens every time a query does not include the PK column in the query. Is this an expected behavior? And if so, what’s the way around?
I bet your query is executed fine and you’ll get a result.
What goes wrong is trying to load records in a foundset using the dataset.
That case you need to include the pk on the first column.
Most of the time I try to avoid loading records this way as Servoy’s queries are getting slower.
Try using foundset.loadRecords(query, [args]) and you’ll be better off…
also this case you’re forced to pass the pk…
mboegem:
I bet your query is executed fine and you’ll get a result.
What goes wrong is trying to load records in a foundset using the dataset.
That case you need to include the pk on the first column.
Most of the time I try to avoid loading records this way as Servoy’s queries are getting slower.
Try using foundset.loadRecords(query, [args]) and you’ll be better off…
also this case you’re forced to pass the pk…
I thought that by using the dataset (databaseManager.getDataSetByQuery) you don’t let Servoy to do the query (or its hibernate-mapping perhaps), instead you pass the query to the database directly. I don’t see how Servoy can be blamed for being slow in this case.
Also, while using the dataset, the query works if I pass the pk. It seems that I am forced to pass the pk using databaseManager.getDataSetByQuery too.
Anyway, thank you very much for your response and advice. I’ll try it using the foundset object and method you pointed out here. I’ll post the results soon.
Marc is right, when you load records in a foundset via sql, either directly via custom sql or via a dataset from databaseManager.getDataSetByQuery(), the query has to return the pks.
A nicer way is to do this the Servoy way, in stead us using queries just tell the foundset which conditions you want.
Using find:
if (foundset.find())
{
foundset.country_name = '!^'
foundset.search()
}
or better, since 6.1 use the query builder:
var query = foundset.getQuery()
query.where.add(query.columns.country_name.not.eq(null))
foundset.loadRecords(query)
Excellent! I wasn’t aware of the query builder in 6.1. I probably missed that point when 6.1 was introduced. And yet, while using the dataset, the query works if I pass the pk. I saw that.
I also agree with you. I have used foundset for all my queries in Servoy. I wouldn’t dare to use databaseManager.getDataSetByQuery for simple queries (I am too lazy, I let Servoy do the work).
Now I am trying to create complex reports that will end up presenting data in heat-maps and score boxes of statistics. I put this simple query above as an example (because I didn’t know the requirement for pk). The real queries are pretty complex. In many instances I have to create temporary tables, and create session variables with the SET @variable_name statement in MySQL.
I am glad to learn that foundset.getQuery() is more efficient than the databaseManager.getDataSetByQuery. I wonder why? Do you know why is it more efficient?[/size]
Now, I have another question. Do you know how to pass various expressions (I don’t think this is a good name for it) as a single query? See the related/new post here: [url viewtopic.php?f=22&t=19099][/url]
Why is it the foundset path to get data (using foundset.getQuery()) more efficient than the dataset path to get the same thing (databaseManager.getDataSetByQuery)?
Can anyone tell me if it’s really worthy to change my very complex SQL queries (with multiple joins, subqueries and calculations) into the sql-like structure of the new Query Builder API?
Foundsets use a cache (the famous 200 record batch limit)
Datasets don’t, they will load everything. When you have a table with thousands of records, it’s easy to understand why it’s best to use foundsets.
Now the QueryBuilder is good when you want to build database agnostic code. Otherwise I don’t think you need to worry about it.
If you plan is to use MySQL then just stick to SQL queries as usual, they will still work fine.
ptalbot:
Foundsets use a cache (the famous 200 record batch limit)
Datasets don’t, they will load everything. When you have a table with thousands of records, it’s easy to understand why it’s best to use foundsets.
Now it is clear as water!
ptalbot:
Now the QueryBuilder is good when you want to build database agnostic code. Otherwise I don’t think you need to worry about it.
If you plan is to use MySQL then just stick to SQL queries as usual, they will still work fine.
