I'm not familiar with MaxDB but can you excute both statemnts as one batch? Something like
TEXT TO lcSql NOSHOW
DECLARE XNAME CURSOR FOR WITH RECURSIVE TMPOBJ
(LEVEL, rbobjekteid, oberobjektid) AS (SELECT 1, ro.rbobjekteid, ro.oberobjektid FROM rbobjekte ro
WHERE oberobjektid = '10091691'
UNION ALL SELECT TMPOBJ.LEVEL + 1, ro.rbobjekteid, ro.oberobjektid FROM rbobjekte ro ,TMPOBJ
WHERE ro.oberobjektid = TMPOBJ.rbobjekteid)
select distinct rbobjekteid from tmpobj
select * from xyz where xyz.rbobjekteid in (select rbobjekteid from xname)
ENDTEXT
lnResult = sqlexec(connr,lcSql)
>
>I want to fire 2 sql commands to my database (MaxDB)
>
>1)
>DECLARE XNAME CURSOR FOR WITH RECURSIVE TMPOBJ
>(LEVEL, rbobjekteid, oberobjektid) AS (SELECT 1, ro.rbobjekteid, ro.oberobjektid FROM rbobjekte ro
> WHERE oberobjektid = '10091691'
> UNION ALL SELECT TMPOBJ.LEVEL + 1, ro.rbobjekteid, ro.oberobjektid FROM rbobjekte ro ,TMPOBJ
> WHERE ro.oberobjektid = TMPOBJ.rbobjekteid)
>select distinct rbobjekteid from tmpobj
>
>2)
>select * from xyz where xyz.rbobjekteid in (select rbobjekteid from xname)
>
>the maxdb gurus from SAP told me that the first recursive one creates a named resultset "rname"
>which can be used by the second command
>
>When fireing these commands one after another from sql studio everything works as expected !!!!!
>
>When calling from foxpro (via odbc)
>l_cmd ="declare xname ...."
>= sqlexec(connr,l_cmd)
>l_cmd = "select * from xyz where ..."
>= sqlexec(connr,l_cmd)
>the first command is ok, the second gives me an error "unknown table xname"
>
>Do i miss something with foxpro cursor handling ??? ODBC connections ???? .... ????
--sb--