Re: 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="searchusers">
     <db:param name="search" type="String"/>
     <db:sql>
       call getusers({$search})
     </db:sql>
     <db:result>
       <user userID="{$id}">{$name}</user>
     </db:result>
   </db:query>

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

or you could opt for a db:execute oneliner:
<db:execute sql="call getusers('foo')"><user userID="{$id}">{$name}</ 
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?!