Super! I’m happy it makes sense. Can you keep me updated what will be the solution?
Thank you for following my descriptions and regards
Birgit
When you have a single-key pk and you use a custom query, Servoy will do something like ‘where pk in (select x …)’.
Unfortunately, in case of combined pk you can’t do something like that in sql.
Instead we insert the query result in a temp table and join with that:insert into temp (pk1, pk2) select x, y …
select tab.pk1, tab.pk2 from tab join temp on temp.pk1 = tab.pk1 and temp.pk2 = tab.pk2.
The temp table is dropped automatically.
The oracle-error you get happens during the insert, when the custom select does not return the same number and type of columns as the temp table columns.
We cannot check on that before because we don’t want to parse sql, thus allowing any valid sql for that database.
We (also )have a problem when using loadRecords() with combined pk’s.
In the Servoy log i can see that Servoy whants to create a temp table in Oracle but that fails… with the following exception:
java.sql.SQLException: ORA-01031: insufficient privileges
is there a workaround ? because we cannot change the privileges of the Oracle DB.
We have no rights to fire CREATE TABLE statements.
Thanks in advance.
Maurice,
Unfortunately, there is no way to express this in sql without parsing the sql (which we don’t to allow all possible custom sql the specific db supports).
If your table has a single pk column, you can use custom sql without getting this error.
You can also try to define a relation that equals the custom sql and select the data via a related find/search.
Rob