RE: oXml DB Question

Thanks.  I figured out I could just:
<db:sql>
      DECLARE=20
        val INTEGER;=20
      BEGIN
        val :=3D =
codex.main.add_search({$in_show},{$in_name},{$in_parent},
{$in_where});=20
      END;
</db:sql>=20

which seems to work.  I do need that return value if possible though.
Any idea on how to get at the val variable from the above statement?  I
have a work around but it would save me "yet another hack" elsewhere in
my code.  Thanks for your help.  I'll give the cleaner syntax you
mentioned a try.

Mike=20

-----Original Message-----
From: Martin Klang [mailto:[email protected]]=20
Sent: Monday, January 30, 2006 6:02 AM
To: Michael Wilson
Cc: o-xml-zRfLyl9bSvv/[email protected]
Subject: Re: [o:XML] oXml DB Question

Hi Michael,

I've little experience of stored procedures so I'll have to investigate
this further, however here's an initial reply that might help.

With db:execute you can run any SQL query. There's no explicit support
for stored procedures (or CallableStatements in JDBC) though you should
be able to call a procedure with something like:
<db:execute sql=3D"{call FOOBAR}"/><!-- call the stored procedure =
'FOOBAR'
--> On Oracle the syntax _may_ be:
<db:execute sql=3D"{exec FOOBAR}"/>

If you want to use the result set you should be able to include a result
template as the db:execute body as usual. For example, if FOOBAR returns
a column called 'FOO', you can do:
<db:execute sql=3D"{call FOOBAR}"><foobar>{$foo}</foobar></db:execute>
This should, theoretically, produce a foobar element for each row in the
result.

Otherwise I'm told that you can wrap a stored procedure in a SQL
function, which can then be used in a normal select statement.

I'm not sure about passing parameters or returning values, I'll have to
look into that.

hope this helps!

/m

On 29 Jan 2006, at 7:15, Michael Wilson wrote:

> I have a stored procedure on my database server.  Using the db=20
> extensions is it possible to call the stored procedure with oXML?  The

> documentation web page mentions the <db:execute> but doesn't give a=20
> complete example.  Such as how do you define the datasource when using

> this method?  Anyway, if anyone is reading this that knows if this is=20
> possible can I get a short example?  Thanks.
>
> Mike
>
> _______________________________________________
> o-xml mailing list
> o-xml-zRfLyl9bSvv/[email protected]
> http://lists.pingdynasty.com/mailman/listinfo/o-xml