You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Michael K <m_...@yahoo.com> on 2007/04/02 12:45:56 UTC

Calling MySql Stored Procedure return no resultset

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

Re: Calling MySql Stored Procedure return no resultset

Posted by Andrus Adamchik <an...@objectstyle.org>.
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
>
>

Re: Calling MySql Stored Procedure return no resultset

Posted by Andrus Adamchik <an...@objectstyle.org>.
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