I am trying to load records from a dataset created by the getDatasetByQuery method in Servoy on a link table (a table that uses two foreign keys to compose the primary key) and I am having no luck. I am getting a good foundset, but the load records method is failing every time. No errors, it just brings back all the records in the link table. I have tried adjusting the dataset, but I am not having any luck.
Is there anyone who has had luck with this at all? Is there a related defect to this problem.
If you start the developer with the -DSTACKTRACE=true option in the servoy_developer.bat, you can see all queries that Servoy fires to the database. If you then debug to that point and have a look at the console, you can see what is actually used when you do controller.loadRecords(). Since your case is a bit unsual (having two columns as primary key), I could imagine nobody has ever tried and it might just not work.
Thanks for the bit of info on debugging. I did not know you could do that. I appreciate the tip.
As far as it being unusual to have two primary key fields in the same table, this should not be a foreign concept and I do not believe it to be unusual. It is required of a many-to-many relationship in relational database design. Normally to make a many-to-many relationship between two tables in a database you must create a third table that has a primary key consisting of the primary key of both tables (hence the term “link table”) This compound key becomes the unique identifier for each row in the table, you must have both values to “identify” the record. I will not go into an example here, but you most certainly find one on the internet if you google many-to-many.
Before anyone else posts that I could assign a arbitrary key (like an auto increment integer) and base each row off that field, I will remind that this is not an efficient way to design tables. To optimize MySQL, you should NEVER use arbitrary keys in a table; always use the “natural” key so that indexing is more effective. This is a MySQL recommendation directly from them, not mine.
The essence of my post was not finding your DB design unusual, but this
I could imagine nobody has ever tried and it might just not work.
I was just saying that most people don’t use those kind of keys.