Load foundset with recursive query

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

Load foundset with recursive query

Postby abeermann » Fri May 20, 2016 10:26 am

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
abeermann
 
Posts: 105
Joined: Fri Nov 26, 2010 12:46 pm

Re: Load foundset with recursive query

Postby ROCLASI » Fri May 20, 2016 1:41 pm

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.
Robert Ivens
ROCLASI Software Solutions / JBS Group, Partner
SAN Developer / Servoy Valued Professional / Servoy Certified Developer
Twitter: @roclasi / @servoyforge
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5274
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium


Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 2 guests