Thanks Andrus. Its working fine now.
Michael ----- Original Message ---- From: Andrus Adamchik <[EMAIL PROTECTED]> To: [email protected] Sent: Tuesday, April 3, 2007 8:52:13 AM Subject: Re: Calling MySql Stored Procedure return no resultset Actually I just checked in the code for MySQL stored procedure support. As we can't add new features to the 2.0.* branch, you have two choices - either use 3.0 build [1] or rebuild 2.0 branch yourself, adding these two files to the org.apache.cayenne.dba.mysql package - [2]. Let me know if you have any problems. Andrus [1] http://people.apache.org/~aadamchik/nightly/04022007/cayenne-3.0- SNAPSHOT.tar.gz [2] http://svn.apache.org/viewvc?view=rev&revision=524994 On Apr 2, 2007, at 6:55 PM, Andrus Adamchik wrote: > 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_tabl >> e_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 > > ____________________________________________________________________________________ Bored stiff? Loosen up... Download and play hundreds of games for free on Yahoo! Games. http://games.yahoo.com/games/front
