Michael,
Cayenne didn't officially support MySQL stored procedures as of 1.2
and 2.0. But in fact it is fairly easy to turn it on, based on the
code used in other adapters. Let me poke around in the next few days
- I may be able to enable it in 3.0 pretty quickly.
Andrus
On Apr 2, 2007, at 6:45 AM, Michael K wrote:
Hi,
I've written a stored procedure in mysql db that execute some join
queries.
Here is my sample stored procedure look like:
DELIMITER $$
DROP PROCEDURE IF EXISTS `schooler`.`SearchDD` $$
CREATE PROCEDURE `SearchDD`(IN userInput varchar(200))
BEGIN
CREATE TEMPORARY TABLE sp_mysrctoc12
SELECT sourceTable.id as
source_id,sourceTable.topic_name,sourceTable.tid as
source_tid,targetTable.topic_name as parent_name,
sourceTable.scope as
source_scope,sourceTable.st_timestamp,sourceTable.tid_table_name as
source_tid_table_name,sourceTable.storage_id as
source_storage_id,sourceTable.storage_label as source_storage_label
FROM mk_search_topics as sourceTable inner join
mk_search_topic_association as association on
sourceTable.id = association.search_topic_id inner join
mk_search_topics as targetTable on
targetTable.id = association.target_topic_id
where sourceTable.is_root_topic=0
and targetTable.is_root_topic=1
and sourceTable.storage_label = userInput;
select
source_id,topic_name,parent_name,relevance,source_tid,source_tid_table
_name,source_storage_id,source_storage_label
from sp_mysrctoc12 group by parent_name order by topic_name desc;
DROP TABLE sp_mysrctoc12;
END $$
DELIMITER ;
Then I mapped the above stored procedure to Cayenne using modeller
with one input parameter defined as userInput varchar(200). I also
created DBEntity and ObjEntity with readOnly set to true.
In my java code, I wrote something like this:
DataContext ctxt = sSession.getDataContext();
ProcedureQuery query = new ProcedureQuery("SearchDD");
query.addParameter("userInput","abcdef");
List rows = ctxt.performQuery(query);
// Display the row size
System.out.println("row size: "+row.size());
When I run the code in web application, it prints out 'row size: 0'.
I executed the same stored procedure with the same parameter in
mysql command line, it returned 4 rows.
I wonder if this has something to do with accessing temporary table
in multithreaded environment.
I'm currently using Cayenne version 2.0.2 running jdk 1.5, mysql
5.0.18 with jconnector 5.0.5.
Please help.
Thanks,
Michael
______________________________________________________________________
______________
8:00? 8:25? 8:40? Find a flick in no time
with the Yahoo! Search movie showtime shortcut.
http://tools.search.yahoo.com/shortcuts/#news
