Calling MySql Stored Procedure return no resultset

From: Michael K (m_lesta..ahoo.com)
Date: Mon Apr 02 2007 - 06:45:56 EDT

  • Next message: Demetrios Kyriakis: "DataViews - How to get started?"

    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

    This archive was generated by hypermail 2.0.0 : Mon Apr 02 2007 - 06:47:00 EDT