• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

MySQL Basics

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    MySQL Basics

    I need to get this MySQL stored proc working. I have omitted the guts and glory and gone right back to basics and even that won't frakin work

    I am not by any stretch a MySQL developer so please be patient.

    Code:
    DELIMITER $$
    
    DROP PROCEDURE IF EXISTS `myDB`.`getCurrentPeriodId` $$
    CREATE PROCEDURE `myDB`.`getCurrentPeriodId` (OUT currentPeriodId INT)
    BEGIN
        SET currentPeriodId = 500;
    END $$
    
    DELIMITER ;
    And I call it thusly

    Code:
    CALL `myDB`.`getCurrentPeriodId`(@currentPeriodId);
    SELECT @currentPeriodId;
    And it returns NULL

    Why?
    Knock first as I might be balancing my chakras.

    #2
    This probably won't help you much - but it works fine from here:

    Code:
    mysql> create database myDB;
    Query OK, 1 row affected (0.13 sec)
    
    mysql> DELIMITER $$
    mysql> 
    mysql> DROP PROCEDURE IF EXISTS `myDB`.`getCurrentPeriodId` $$
    Query OK, 0 rows affected, 1 warning (0.40 sec)
    
    mysql> CREATE PROCEDURE `myDB`.`getCurrentPeriodId` (OUT currentPeriodId INT)
        -> BEGIN
        ->     SET currentPeriodId = 500;
        -> END $$
    Query OK, 0 rows affected (0.37 sec)
    
    mysql> 
    mysql> DELIMITER ;
    mysql> CALL `myDB`.`getCurrentPeriodId`(@currentPeriodId);
    Query OK, 0 rows affected (0.08 sec)
    
    mysql> SELECT @currentPeriodId;
    +------------------+
    | @currentPeriodId |
    +------------------+
    | 500              | 
    +------------------+
    1 row in set (0.06 sec)
    mysql version details:

    Code:
    #####@###########:~$ mysql --version
    mysql  Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2

    Comment


      #3
      Originally posted by chicane View Post
      This probably won't help you much - but it works fine from here:

      Code:
      mysql> create database myDB;
      Query OK, 1 row affected (0.13 sec)
      
      mysql> DELIMITER $$
      mysql> 
      mysql> DROP PROCEDURE IF EXISTS `myDB`.`getCurrentPeriodId` $$
      Query OK, 0 rows affected, 1 warning (0.40 sec)
      
      mysql> CREATE PROCEDURE `myDB`.`getCurrentPeriodId` (OUT currentPeriodId INT)
          -> BEGIN
          ->     SET currentPeriodId = 500;
          -> END $$
      Query OK, 0 rows affected (0.37 sec)
      
      mysql> 
      mysql> DELIMITER ;
      mysql> CALL `myDB`.`getCurrentPeriodId`(@currentPeriodId);
      Query OK, 0 rows affected (0.08 sec)
      
      mysql> SELECT @currentPeriodId;
      +------------------+
      | @currentPeriodId |
      +------------------+
      | 500              | 
      +------------------+
      1 row in set (0.06 sec)
      mysql version details:

      Code:
      #####@###########:~$ mysql --version
      mysql  Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2
      Does help Chicane, it means I'm not going nuts.

      MySQL Query browser under XP is a bad idea. Buggy heap of crap.

      Do you know what the 'proper' command based util is called?
      Knock first as I might be balancing my chakras.

      Comment


        #4
        Originally posted by suityou01 View Post
        Does help Chicane, it means I'm not going nuts.

        MySQL Query browser under XP is a bad idea. Buggy heap of crap.

        Do you know what the 'proper' command based util is called?
        This behaviour is by design apparently.

        Linky

        Thank you for the bug report. This is an expected behavior because by
        design QB uses a new connection for each query, there is already a
        feature request to change this behavior.
        Knock first as I might be balancing my chakras.

        Comment


          #5
          The proper command util is called 'mysql':

          Code:
          ######@########:~$ mysql -u(username) -p(password)
          On a normal mysql installation, it should live in the mysql binary directory - something along the lines of "c:\program files\mysql\bin\".

          If you provide the "-p" parameter without specifying a password, it should prompt you for one.

          Comment


            #6
            Originally posted by chicane View Post
            The proper command util is called 'mysql':

            Code:
            ######@########:~$ mysql -u(username) -p(password)
            On a normal mysql installation, it should live in the mysql binary directory - something along the lines of "c:\program files\mysql\bin\".

            If you provide the "-p" parameter without specifying a password, it should prompt you for one.
            And what is one of those? Man alive since oracle got their mits on MySQL they've buggered around with the site. All I can find is MySQL workbench or QB. I don't want a full MySQL install just the client tools.
            Knock first as I might be balancing my chakras.

            Comment


              #7
              Originally posted by suityou01 View Post
              And what is one of those? Man alive since oracle got their mits on MySQL they've buggered around with the site. All I can find is MySQL workbench or QB. I don't want a full MySQL install just the client tools.
              Got a spare box? Any Linux distro will give you the option to install MySQL or will install it by default along with Apache and PHP.
              Me, me, me...

              Comment


                #8
                Originally posted by suityou01 View Post
                And what is one of those? Man alive since oracle got their mits on MySQL they've buggered around with the site. All I can find is MySQL workbench or QB. I don't want a full MySQL install just the client tools.
                Hmm... MySQL themselves seem to think mysql.exe should be in: "C:\Program Files\MySQL\MySQL Server 5.0\bin\":

                http://dev.mysql.com/doc/refman/5.0/...n-layouts.html

                If not, you might be best off doing a search for "mysql.exe" on the local drive and letting Windows chug away for 15 minutes while you make a soothing cup of tea. You sound a bit stressed like!

                Comment


                  #9
                  Originally posted by Cliphead View Post
                  Got a spare box? Any Linux distro will give you the option to install MySQL or will install it by default along with Apache and PHP.
                  Trouble is getting the Linux box onto ClientCos VPN. More hassle that it's worth. I have an XP lappy on their VPN so the easiest thing is to try and get the windows command line client installed.

                  Cannot see it anywhere on their site though. Girly "GUI" tools yes, manly "command line tools" have been removed.
                  Knock first as I might be balancing my chakras.

                  Comment


                    #10
                    Originally posted by chicane View Post
                    Hmm... MySQL themselves seem to think mysql.exe should be in: "C:\Program Files\MySQL\MySQL Server 5.0\bin\":

                    http://dev.mysql.com/doc/refman/5.0/...n-layouts.html

                    If not, you might be best off doing a search for "mysql.exe" on the local drive and letting Windows chug away for 15 minutes while you make a soothing cup of tea. You sound a bit stressed like!
                    Just a wee bit hacked off that a "I'll just write this simple stored procedure" has turned into a marathon tools search, google fest, CUK thread.

                    I don't want to install MySQL. Only the client tools.
                    Knock first as I might be balancing my chakras.

                    Comment

                    Working...
                    X