Professional Web Applications Themes

MySQL procedures and order of result sets - MySQL

Hi, I'm a beginner with procedures, and there is something i really can't understand. I have a stored procedure which goes like : /************************************************** *******/ CREATE PROCEDURE `get_user`( IN _login VARCHAR(50), IN _passwd VARCHAR(32)) BEGIN DECLARE _userid MEDIUMINT UNSIGNED DEFAULT 0; SELECT UserID INTO _userid FROM `Users` WHERE `UserLogin` LIKE _login AND `UserPassword` LIKE _passwd LIMIT 1; SELECT * FROM `Users` WHERE `UserID`=_userid; SELECT * FROM `Addresses` WHERE `Addresses`.`UserID`=_userid; SELECT `PackID` FROM `Packs`, WHERE `Packs`.`UserID`=_userid; SELECT * FROM `Categories` WHERE `UserID`=_userid; END $$ /************************************************** *******/ I work with Java, and although my user could be created, it seemed he had ...

  1. #1

    Default MySQL procedures and order of result sets

    Hi,

    I'm a beginner with procedures, and there is something i really can't
    understand.

    I have a stored procedure which goes like :
    /************************************************** *******/
    CREATE PROCEDURE `get_user`(
    IN _login VARCHAR(50),
    IN _passwd VARCHAR(32))
    BEGIN

    DECLARE _userid MEDIUMINT UNSIGNED DEFAULT 0;

    SELECT UserID INTO _userid FROM `Users`
    WHERE `UserLogin` LIKE _login
    AND `UserPassword` LIKE _passwd
    LIMIT 1;

    SELECT * FROM `Users`
    WHERE `UserID`=_userid;

    SELECT * FROM `Addresses`
    WHERE `Addresses`.`UserID`=_userid;

    SELECT `PackID`
    FROM `Packs`,
    WHERE `Packs`.`UserID`=_userid;

    SELECT * FROM `Categories`
    WHERE `UserID`=_userid;

    END $$
    /************************************************** *******/

    I work with Java, and although my user could be created, it seemed he
    had no Addresses. So i tried to launch the procedure in MySQL Query
    Browser, who told me that the user had 2 Addresses, but the result set
    was not following directly the one from the Users table as i tried to
    do in the procedure (it is a bit of trouble for i use
    CallableStatements and they can only get ResultSets the one after the
    other, and it's more work to pick the right one if they come
    shuffled). It came completely at the end, after the two others (which
    happen to be both empty).

    So my question is : does anybody know why i do not get my result sets
    in the specified order ? Is there a special trick done by an optimizer
    somewhere that changes the order of the queries ?

    Thanks

    subtenante@gmail.com Guest

  2. #2

    Default Re: MySQL procedures and order of result sets

    On Apr 10, 8:44 am, com wrote:
     

    Did you try to call same procedure from MYSQL utility ? Are result
    sets in right order ?

    MasterZiv Guest

  3. #3

    Default Re: MySQL procedures and order of result sets

    > Did you try to call same procedure from MYSQL utility ? Are result 

    Thanks. Actually i used only MySQL Query Browser, which strangely
    outputs them in the wrong order. Toad and command line mysql client
    are both giving the right sequences, so i guess it comes from my java,
    not from the MySQL output.

    Sorry.

    subtenante Guest

Similar Threads

  1. How do you page large DB result sets?
    By kedward in forum Macromedia Flex General Discussion
    Replies: 3
    Last Post: February 13th, 05:17 PM
  2. DataAdapter and multiple result sets
    By Michael C# in forum ASP.NET Web Services
    Replies: 2
    Last Post: May 10th, 06:05 PM
  3. selecting multiple result sets from mysql_query
    By - in forum PHP Development
    Replies: 7
    Last Post: January 25th, 12:40 AM
  4. Replies: 0
    Last Post: September 11th, 04:35 PM
  5. Replies: 3
    Last Post: August 26th, 07:14 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139