Load foundset with recursive query

[abeermann]

Hello Everbody

My recursive Query to get rbkontaktids, which should be loaded into a form:

var koquery = " DECLARE RNAME CURSOR FOR WITH RECURSIVE TMPOBJ " ;
koquery += " (rbobjekteid) " ;
koquery += " AS (SELECT ro.rbobjekteid " ;
koquery += " FROM rbobjekte ro " ;
koquery += " WHERE rbobjekteid = ? and aktivkz = '1' " ;
koquery += " UNION ALL SELECT ro.rbobjekteid FROM rbobjekte ro ,TMPOBJ " ;
koquery += " WHERE ro.oberobjektid = TMPOBJ.rbobjekteid ) " ;
koquery += " SELECT rbkontaktid from rbkontakt join tmpobj on rbkontakt.rbobjekteid = TMPOBJ.rbobjekteid " ;
koquery += " where rbkontakt.rbkontaktstatus = 'erledigt' ";
koquery += " order by rbkontakt.rbkontaktstatus desc, rbkontakt.geaendert desc";

var args = new Array();
args[0] = scopes.FM.knotenid;


This one is the old version. It works as expected but it fires two querys: (Performance !? The table rbkontakt has serveral million records!)

var c_kontakte = databaseManager.getDataSetByQuery('maxdb', koquery, args, -1);
forms.Auswahl_Kontakte.foundset.loadRecords(c_kontakte);



As you allow querys beginning with "declare" this new version should fire only one query command and hopefully run much faster.

forms.Auswahl_Kontakte.foundset.loadRecords(koquery,args);
but i get:
com.servoy.j2db.dataprocessing.DataException: [-5016] (at 67): Missing delimiter: )

Any help welcomed
Best regards
Albert

[ROCLASI]

Hi Albert,

Be aware that when you use SQL directly in foundset.loadRecords() that Servoy will wrap this in another SELECT.
I.e.

Code: Select all

SELECT <pkColumn> FROM <tableName>  WHERE <pkColumn> IN (<yourSQL>) ORDER BY <pkColumn>

For that reason your SQL (also CTE's) will not work.

So you can do it the way you did, using a dataset to load into the foundset, or use an in-memory foundset to load these records in.

Hope this helps.