Hello,
when doing this:
var query = 'SELECT table.pk FROM schema.table ORDER BY table.pk'
controller.loadRecords(query)
I am getting the following error:
Foundset table name is not the first table in query
I suppose I am getting this because “table” is textually different from “schema.table”. From a database point of view, however, they are the same! In order to be able to use queries with databases that use schemas, this is a no go. Can this be fixed/loosened?
Thanks
Patrick
Not sure but is table an example name or your real name? If the last is the case I can imagine it is a name you are not allowed to use!?
It’s an example/a pattern…
that wasn’t the complete example that you use yes?
i think you did something like this:
var query = ‘SELECT table.pk FROM schema.table, table2, schema.table3 ORDER BY table.pk’
controller.loadRecords(query)
But i don’t think this will work currently in the 2.2 release.
We have to build full schema support for that.
How big is the foundset you are trying to select?
Can you try to select it with the databasemanager.getDataSetByQuery()
and that do loadRecords(dataset)
Yes, this is what I want to do:
var query = 'SELECT table.pk FROM schema.table, schema.table2, schema.table3 WHERE .... ORDER BY table.pk'
controller.loadRecords(query)
Why does Servoy need schema support for that? If you just allow me to use that query (instead of throwing the error above), I’d be fine. The query works and it complies to the restrictions that come with loadRecords.
and one more:
How big is the foundset you are trying to select?
I don’t know.
Can you try to select it with the databasemanager.getDataSetByQuery()
and that do loadRecords(dataset)
No. The limitation of 200 records doesn’t work here…
our table names don’t have any schema name in them. That kind of support must first be build
If you say use this query then that query (that string) isn’t really used. It is analysed and broken down to what we internally are using.
That table part isn’t even used. Just checked.
We just have to have schema support in our table objects. I don’t see another way currently. Will discuss this interally
Understand. Thanks.
Does this
That table part isn’t even used
also mean, that I don’t even have to try something like:
SELECT table.pk FROM table, schema.table2 WHERE table.column = schema.table2.column ORDER BY table.pk
patrick:
No. The limitation of 200 records doesn’t work here…
I don’t get it! Is there a limitation of 200 records also here?
I tried that in developer, but can’t find the limitation. dataset of 300 records, loads just fine!
Not exactly. But there is “some” limit. I might get some 55.000 records and not even all db systems can handle an IN-statement with that many arguments
Do you know what the limit exactly is?
tried it with 5000 records, and this works fine!
there is a limit for that. Because we are generating an in query for it to do the the finds.
(it can work if you don’t do finds after that anymore just browsing data, but doing a search again in that set will not work with 50k data)
this:
SELECT table.pk FROM table, schema.table2 WHERE table.column = schema.table2.column ORDER BY table.pk
should work in think!
Because then the main table is not a schema table and can be found just perfectly.
So that should work.
by the way what you did before:
SELECT table.pk FROM schema.table ORDER BY table.pk
how could that work? Because then you need a form build on schema.table but that is currently not really possible. Because we can’t load data from a non default schema table.
this:
SELECT table.pk FROM table, schema.table2 WHERE table.column = schema.table2.column ORDER BY table.pkshould work in think!
Because then the main table is not a schema table and can be found just perfectly.
So that should work.
Will the query also be sent to the database exactly like that? Before you said, the FROM-Clause isn’t even used. If Servoy sends a “FROM table, table2” instead of “FROM table, schema.table2” then Servoy might be happy, but I don’t get any data…
looking at the code that should work (i can’t test it currently)
the main table must be without schema. But the ‘related’ tables are used as they are specificied.