RE: oXml DB Question

Thanks for the example.  I just tested it and the CALL structure works
perfectly.  I also updated the objectbox.jar to 1.1.4 and it also seems
to work.  Any idea on how you might return the value of a function?  My
call is creating a record in a table with a primary key (PK) based on a
sequence.  It  would be perfect if the stored procedure (actually a
fuction) could insert the record and return the primary key of the
record back to my app.  Do you know if that is possible?

I can work around the issue currently but it's a hack and won't scale.


-----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?!