The ID field is not accessible via this action, in my experience. You’ll need to expose this value in another attribute if you want to access it.
The ‘Execute OQL Statement’ creates new objects for your selected return entity, so you will also run into an issue where your returned objects have different IDs. To put that another way, if you retrieve 5 Task objects, 5 new Task objects will be created as your returned list.
Also, using the ‘*’ wildcard in OQL doesn’t really work unless it’s in an aggregate function. That said, you’ll have to write out which columns you want to return. These columns should have aliases that match the attributes on your return entity, otherwise the columns will show as empty because Mendix can’t route the record values to the return entity.
Thank you all for the quick responses!
Yeah when I do a "SELECT Task.Name" instead of "SELECT * ", I seem to get "new" objects rather than references to existing ones in the DB (see screenshot below – it seems to be getting cropped, please download the complete image, if needed) :
More detailed problem :
1. I actually have a deeplinked URL for one of my pages that displays a table of data.
2. The deeplink URL can accept optional query parameters that may filter, sort, show/hide table columns dynamically.
3. Now what I had been trying to do is - write a custom Java action to build a single large dynamic OQL query. Based on the filter, sort conditions it adds JOIN clauses, WHERE clauses, ORDER BY etc to this dynamic OQL.
^*Performance is critical*, so I need to keep this query lean. :)
4. Lastly I was planning to use the Export Map that will serialize all the data into JSON, which my custom widget can then recieve & process to show the table with the correct columns, with filtered, pre-sorted data. And it can then decide what buttons/widgets are applicable within the each table row.
I think my problem now is #3 is not going to give me the actual objects in the DB, but new objects (hence no associations present).
Can you suggest a workaround to my problem?
I don't think I can afford to iterate over this OQL result one by one and do a "Retrieve from Database" activity where I get each "Task" entity object one by one, for every ID in my OQL.
Is there way to do a "Retrieve from Database" using a list of IDs in one go?
Or do I need to write a custom Java action to build such a XPATH query?
I ran into the same problem and found this post. It's indeed impossible to select the ID from an entity. The documentation in the Java action:
For each column, the action expects an attribute in the result entity with the same name.
If the result is the ID of an object, it expects an association with the same name (without the module prefix).
But I found an easy workaround for this: SELECT CAST(id as STRING) AS Orphanid
So the trick is CASTing the ID to a string. And because you can't name an attribute ‘ID’ (it's a reserved word) you give the attribute just a name, in the example above Orphanid
Yes, OQL does retrieve persistable entities. No idea what makes this statement get treated as an association.
But trial and error gets you a long way. What is the result if you specify (a list of ) fields instead of ‘Select * ...’