TubDataStore: How can i set array to bind variables into my SQL query?


#1

I have SQL script like:
SELECT ID FROM org_unit WHERE … START WITH parentid IN ( :dep: )CONNECT BY PRIOR id=parentid GROUP BY ID;
:dep: is array of integer.
I try run this script using TubDataStore.
How can i run this SQL script??
I find help page about TubDataStore and i know how run this script with bind variables. But when i try set array to :dep: bind var i get error: ORA-00932: inconsistent datatypes. When i concat array to string with coma separators (like: 1234,4123) - i get same error.


#2

We do not recommend do use a database level hierarchical query. Instead UB have a tree mixin ( this mixin is available in org_unit). It build a materialized path for every row in org_unit and store it to the mi_treePath attribute. So to select all child for some row (for example with ID=1) you can use a Repository

UB.Repository('org_unit').attrs('ID').where('mi_treePath', 'startsWith', '/1/').select()

In case you need to select starting from a several roots - execute several queries