MySQL Basics

[suityou01]

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.

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

CALL `myDB`.`getCurrentPeriodId`(@currentPeriodId);
SELECT @currentPeriodId;

And it returns NULL

Why?

[chicane]

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

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:

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

[suityou01]

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

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:

#####@###########:~$ 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?

[suityou01]

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.

[chicane]

The proper command util is called 'mysql':

######@########:~$ 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.

[suityou01]

The proper command util is called 'mysql':

######@########:~$ 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.

[Cliphead]

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.

[chicane]

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!

[suityou01]

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.

[suityou01]

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.