We have structured data in a table RBOBJEKTE.
Each record has an RBOBJEKTEID and an OBEROBJEKTID(parentid) which points to an RBOBJEKTEID in the same table.
We show this structured records in a treeview.
The user marks a record an we resolve the structure under the marked objektid.
Then we look into other tables to find contacts or tasks or whatever for these rbobjekteid’s.
Example:
The user marks a building and we look for all floors in the building, all rooms in all floors, all objects in all rooms,…
Our database (MaxDB from SAP) has the ability to do recursive querys.
From other applications we do:
Getting all rbqspruefkopfids from rbqspruefkopf(=quality tests) which have an rbobjekteid from the marked structure
DECLARE RNAME CURSOR FOR WITH RECURSIVE TMPOBJ
(LEVEL, rbobjekteid, oberobjektid)
AS (SELECT 1, ro.rbobjekteid, ro.oberobjektid
FROM rbobjekte ro WHERE rbobjekteid = userstartpoint
UNION ALL SELECT TMPOBJ.LEVEL + 1, ro.rbobjekteid, ro.oberobjektid FROM rbobjekte ro ,TMPOBJ
WHERE ro.oberobjektid = TMPOBJ.rbobjekteid)
SELECT rbqspruefkopfid from tmpobj
join rbqspruefkopf on rbqspruefkopf.rbobjekteid = tmpobj.rbobjekteid
This query gets the wanted qbqspruefkopfids within 1 second even if the structure has more than 10 levels and more than 1000 rbobjekteids.
How can this be done with servoy???
We can rewrite the query but then we have to find out how many level we have beneath the marked object and build a query like this:
select ....
union all select ... (level1)
union all select ... (level2)
union all select ... (level3)
...
This query will cost us between 5 and 40 seconds depending on the levels.
Now the idear was to fire the recursive query with databaseManager.getDatasetbyQuery and then do a controller.loadrecords in the form that shows all quality tests for all objects in the wanted structure.
The databaseManager.getDatasetbyQuery gives us an error (command has to start with select).
How can we fire our recursive query command (rawsql?) and load the found rbqspruefkopfids ???
I have never used MaxDB but what happends when you wrap the query inside a SELECT like so :
SELECT rbqspruefkopf FROM (
DECLARE RNAME CURSOR FOR WITH RECURSIVE TMPOBJ
(LEVEL, rbobjekteid, oberobjektid)
AS (SELECT 1, ro.rbobjekteid, ro.oberobjektid
FROM rbobjekte ro WHERE rbobjekteid = userstartpoint
UNION ALL SELECT TMPOBJ.LEVEL + 1, ro.rbobjekteid, ro.oberobjektid FROM rbobjekte ro ,TMPOBJ
WHERE ro.oberobjektid = TMPOBJ.rbobjekteid)
SELECT rbqspruefkopfid from tmpobj
join rbqspruefkopf on rbqspruefkopf.rbobjekteid = tmpobj.rbobjekteid
) t
The query I gave was not a subselect but more the approach of a view (SELECT id FROM (recursive query) t).
But like I already mentioned I expect it to error.
I guess the only option then is to run the recursive query via rawSQL and put the result in a temp table that you then can query the temp table.
Following my maxdb documention this command creates a tmp table rname:
DECLARE RNAME CURSOR FOR WITH RECURSIVE TMPOBJ
(LEVEL, rbobjekteid, oberobjektid)
AS (SELECT 1, ro.rbobjekteid, ro.oberobjektid FROM rbobjekte ro WHERE rbobjekteid = '61354'
UNION ALL SELECT TMPOBJ.LEVEL + 1, ro.rbobjekteid, ro.oberobjektid FROM rbobjekte ro ,TMPOBJ
WHERE ro.oberobjektid = TMPOBJ.rbobjekteid)
SELECT rbqspruefkopfid from tmpobj
join rbqspruefkopf on rbqspruefkopf.rbobjekteid = tmpobj.rbobjekteid
So with SQL-Studio (a MaxDB tool) i can fire this command and then call:
select * from rname
It outputs the wanted result.
So i tried in servoy:
var l_qb = "DECLARE RNAME CURSOR FOR WITH RECURSIVE TMPOBJ " +
"(LEVEL, rbobjekteid, oberobjektid) " +
" AS (SELECT 1, ro.rbobjekteid, ro.oberobjektid FROM rbobjekte ro WHERE rbobjekteid = '61354'" +
" UNION ALL SELECT TMPOBJ.LEVEL + 1, ro.rbobjekteid, ro.oberobjektid FROM rbobjekte ro ,TMPOBJ " +
" WHERE ro.oberobjektid = TMPOBJ.rbobjekteid)" +
" SELECT rbqspruefkopfid from tmpobj " +
" join rbqspruefkopf on rbqspruefkopf.rbobjekteid = tmpobj.rbobjekteid" ;
databaseManager.startTransaction();
var ok = plugins.rawSQL.executeSQL('maxdb','rbobjekte',l_qb,null);
var result = databaseManager.getDataSetByQuery('maxdb','select * from RNAME',null,-1);
databaseManager.commitTransaction();
application.output(gefundene);
But i got an error!
[-4004] (at 15): Unknown table name or unknown schema:RNAME
I guess the question is where this error comes from. It looks like a DB error but a quick googleling around doesn’t show me this error number.
Perhaps Rob Gansevles from Servoy can give you a better answer on this because he knows how (and if) Servoy can handle these cursors (which are not entirely the same as temp tables).
The error message comes from maxdb, not Servoy.
I guess that the cursor may not be created on the same connection as the select that follows it.
Try the same with a transaction to enforce binding to the same connection.
Also, make sure the cursor is removed (maybe a rollback does that?), otherwise next time the connection is used you may get an exception when creating the cursor.
var l_qb = "DECLARE RNAME CURSOR FOR WITH RECURSIVE TMPOBJ " +
"(LEVEL, rbobjekteid, oberobjektid) " +
" AS (SELECT 1, ro.rbobjekteid, ro.oberobjektid FROM rbobjekte ro WHERE rbobjekteid = '61354'" +
" UNION ALL SELECT TMPOBJ.LEVEL + 1, ro.rbobjekteid, ro.oberobjektid FROM rbobjekte ro ,TMPOBJ " +
" WHERE ro.oberobjektid = TMPOBJ.rbobjekteid)" +
" SELECT rbqspruefkopfid from tmpobj " +
" join rbqspruefkopf on rbqspruefkopf.rbobjekteid = tmpobj.rbobjekteid" ;
databaseManager.startTransaction();
var los = plugins.rawSQL.executeSQL('maxdb','rbobjekte',l_qb);
var gefundene = databaseManager.getDataSetByQuery('maxdb','select * from RNAME',null,-1);
databaseManager.commitTransaction();
Error:[-4004] (at 15): Unknown table name or unknown schema:RNAME
Coming from the Foxpro this one brings me back to a missed a feature in servoy.
We just want to fire an SQL-Command and show the result in a servoy form as we can do with foxpro cursors.
Just for information or navigation. The form can be ‘unbound’. We don’t need insert,update or delete posibilities.
Just displaying the selected records.
For example we have a lot of information forms, that start with an SQL-Command with select … from … join …grouped by …having…
No primary keys, just compressed information that we use for our dashboards,graphs,…
We want to show these informations in a tableview and do special stuff on action,on dblclick,on rightclick,…
In the example above the missing point is, that Servoy expects a ‘Select’ at the beginning of the query.
Without this regimentation everything should work. We would get some rbqspruefkopfid’s (the table key) and could start to do, what we want to.
I guess the scope of the cursor is the statement and it is gone with the next statement.
Another option maybe to create a stored procedure.
The sproc should create the cursor and then do the select.
plugins.rawSQL.executeStoredProcedure() returns the last select done in the sproc as dataset.
But with SQL-Studio (a SQL-Client for MAXDB) i can fire my command:
DECLARE RNAME CURSOR FOR WITH RECURSIVE TMPOBJ
(LEVEL, rbobjekteid, oberobjektid)
AS (SELECT 1, ro.rbobjekteid, ro.oberobjektid
FROM rbobjekte ro WHERE rbobjekteid = userstartpoint
UNION ALL SELECT TMPOBJ.LEVEL + 1, ro.rbobjekteid, ro.oberobjektid FROM rbobjekte ro ,TMPOBJ
WHERE ro.oberobjektid = TMPOBJ.rbobjekteid)
SELECT rbqspruefkopfid from tmpobj
join rbqspruefkopf on rbqspruefkopf.rbobjekteid = tmpobj.rbobjekteid
which shows the wanted result and after that i can do:
select * from rname
which shows the same result.
So “rname” must exist not only for the first command.