cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: Calling MySql Stored Procedure return no resultset
Date Tue, 03 Apr 2007 01:52:13 GMT
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
>
>


Mime
View raw message