cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael K <>
Subject Calling MySql Stored Procedure return no resultset
Date Mon, 02 Apr 2007 10:45:56 GMT

I've written a stored procedure in mysql db that execute some join queries.

Here is my sample stored procedure look like:


DROP PROCEDURE IF EXISTS `schooler`.`SearchDD` $$
CREATE PROCEDURE `SearchDD`(IN userInput varchar(200))
  SELECT 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 = association.search_topic_id inner join mk_search_topics as targetTable
on = 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 $$


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");
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.


8:00? 8:25? 8:40? Find a flick in no time 
with the Yahoo! Search movie showtime shortcut.
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message