Thanks Andrus. Its working fine now.
Michael
----- Original Message ----
From: Andrus Adamchik <andru..bjectstyle.org>
To: use..ayenne.apache.org
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
This archive was generated by hypermail 2.0.0 : Tue Apr 03 2007 - 07:52:37 EDT