RE: oXml DB Question

It turns out I was testing an oxml query that I hadn't changed and I am
having problems getting this to return a value using Oracle as my
back-end.  I'm an Oracle DBA first (AJAX/Java dabbler second ;) so I
would like to get oxml to return via a http call from my browser the id
of the newly inserted record.  To be proper the code to do this is a
stored function (not procedure) that returns a values.  Because of this
whatever JDBC is doing under the cover it doesn't seem to like the
example you graciously cooked up for me. =20

I think the basic problem is that since Oracle is returning a variable
then the code must be a function with an explicit return value (REF
CURSOR in this case).  JDBC in turn because of the functional
definition, seems to want some thing like "call var :=3D
main.insert_rec()".  As far as I know there isn't a way to do what you
seem to be doing in mySql which is declaring a procedure that is
actually returning values in Oracle unless I return the REF CURSOR via
an OUT parameter. =20

Just for the list here is my Oracle code and the resultant the oxml code
in case someone can spot a problem with my code or a way around this
dilemma.  To be clear I would really like to return: <foo>12</foo> where
12 is the pk of the record I just inserted.

CREATE OR REPLACE=20
PACKAGE main is

  -- utility functions
  function get_pk_show(v_str_show_id in varchar2) return varchar2; =20
 =20
  -- search tree functions
  function add_search_folder(v_show_id in varchar2, v_name in varchar2,
v_pk_parent in integer) return main.ref_cursor;
  -- types
  type ref_cursor is ref cursor;
 =20
end main;
/


CREATE OR REPLACE=20
PACKAGE BODY main is

  function get_pk_show(v_str_show_id in varchar2) return varchar2 as
    v_pk_show varchar2(32);
  begin
    -- get pk_show from str_show_id
    select pk_show into v_pk_show=20
	  from pts.show
	  where str_show_id =3D v_str_show_id;
    return v_pk_show;
  end;
  =09
  function add_search_folder(v_show_id in varchar2, v_name in varchar2,
v_pk_parent in integer)=20
    return main.ref_cursor
  as
    v_pk_search integer;
    c main.ref_cursor;
  begin
 =20
    -- insert
    insert into search (pk_parent, pk_show, b_folder, str_name)=20
       values (v_pk_parent, pk_show,  -1, v_name)
       returning pk_search=20
       into v_pk_search;
    commit;

    -- cursor (a hack)
    open c for
      select v_pk_search pk_search from dual;
   =20
    return c;
  end;
end main;
/

<oxml_examples>
<!--  this works because it effectively "eats" the return value=20
  <db:query name=3D"createSearchFolder" connection=3D"dev1">
    <db:param name=3D"in_show" type=3D"String"/>
    <db:param name=3D"in_folder" type=3D"String"/>
    <db:sql>
      DECLARE=20
        val INTEGER;=20
      BEGIN
        val :=3D
codex.main.add_search_folder({$in_show},{$in_folder},NULL);=20
      END;
    </db:sql>
  </db:query>
-->

<!-- new version per Martin's comments, jdbc throws:
SEVERE: Servlet.service() for servlet ObjectBox threw exception
org.oXML.extras.db.DatabaseException: failed to execute query <call
codex.main.add_search_folder('beo','foo',NULL)>
I've also tried call var :=3D codex... but in this case jdbc complains
that var isn't declared -->

  <db:query name=3D"createSearchFolder" connection=3D"dev1">
    <db:param name=3D"in_show" type=3D"String"/>
    <db:param name=3D"in_folder" type=3D"String"/>
    <db:sql>
      call codex.main.add_search_folder({$in_show},{$in_folder},NULL)
    </db:sql>
    <db:result>
      <add_folder in_show=3D"{$in_show}"
in_folder=3D"{$in_folder}">{$pk_search}</add_folder>
    </db:result>
  </db:query>

  <!--
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
-->
  <o:param name=3D"show" select=3D"''"/>
  <o:param name=3D"folder" select=3D"''"/>
  <resultset>
    <db:call query=3D"createSearchFolder" in_show=3D"$show"
in_folder=3D"$folder"/>
  </resultset>

</oxml_examples>

Anyhow not sure how to cheat this.  JDBC seems to demand I do something
(and declare) the variable into which the return value would be
returned.  Oracle won't return something unless it is a function (or a
IN OUT parameter which I don't think oXml SQL extensions handles). =20

BTW, I have tested this from Oracle and the function works as expected
so I'm relatively sure there may be some sort of mismatch somewhere
else.  Anyway thanks for any comments you may have.

Cheers.

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

Michael,

I've installed MySQL 5.0 to try out some stored procedures, and have
found that they work pretty well with the usual db:query and db:execute
constructs.

To illustrate, here's an example - given this table:
     create table users(
	id int primary key auto_increment,
         name varchar(80) not null);

and this procedure:
create procedure getusers(IN in_name varchar(20))
  select * from users where name like concat('%', in_name, '%');

you can use this db:query to retrieve the data:

   <db:query name=3D"searchusers">
     <db:param name=3D"search" type=3D"String"/>
     <db:sql>
       call getusers({$search})
     </db:sql>
     <db:result>
       <user userID=3D"{$id}">{$name}</user>
     </db:result>
   </db:query>

so for example
   <db:call query=3D"searchusers" search=3D"'foo'"/> will retrieve users =
in
the form:
<user userID=3D"1">foobar</user>
<user userID=3D"2">foo</user>
etc.

or you could opt for a db:execute oneliner:
<db:execute sql=3D"call getusers('foo')"><user =
userID=3D"{$id}">{$name}</=20
user></db:execute>

not too sure about OUT and INOUT parameters though, I suspect they'd
have to be combined with a select in the SQL query that calls the
procedure.

One thing I found with MySQL 5.0 was that returning resultsets from
stored procedures was only supported with recent versions of the
Connector/J JDBC drivers, eg v3.0.15 fails but v3.1.10 works.

hth,

/m

ps - why are you using stored procedures?!