Re: Calling MySql Stored Procedure return no resultset

From: Michael K (m_lesta..ahoo.com)
Date: Tue Apr 03 2007 - 07:51:46 EDT

  • Next message: Marc Gabriel-Willem: "RE: Cayenne -- Apache automatic deploy problem"

    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