0

I just reinstalled XAMPP using this installer: xampp-win32-1.8.3-2-VC11-installer. The issue below still exists. I have saved the routine, and have called it from the phpmyadmin sql console.

Environemnt: 5.6.14 - MySQL Community Server (GPL), Apache/2.4.7 (Win32) OpenSSL/1.0.1e PHP/5.5.6

Breakdown is as follows: Trying to insert a record only if one does not previously exist. Once the record is inserted I request for that record to be returned, else return false. In the following stored procedure below I always get a false returning, even if a new record is inserted. It's like the ELSE statement is being ignored. Why is this occurring and how can it be corrected?

DROP PROCEDURE IF EXISTS `session_initiation`//

CREATE PROCEDURE `session_initiation`(
    IN _user_id     bigint(20),
    IN _device_id   bigint(20),
    IN _token       varchar(255)
)
BEGIN
    IF NOT EXISTS 
    (
        SELECT (1) from active_session where token = _token 
    )  
    THEN
        INSERT INTO active_session (user_id, device_id, token, datetime_create, datetime_expiry ) VALUES ( _user_id, _device_id, _token, NOW(), NOW() + INTERVAL 1 DAY );
        SELECT * from active_session where token = _token;
    ELSE
         SELECT FALSE;
    END IF;
END//

I would expect if the record does not exist, that the record would be inserted and then that same record would be returned. Instead I get the record inserted, and the following being returned:

FALSE
_____
  0

Appreciate anyone that can help correct my issue. I am running this directly from the SQL tab within phpmyadmin sql console.

Here are some calls to the procedure:

call session_initiation(1,1,'abc123')   //inserts and returns false = 0
call session_initiation(1,1,'123abc')   //inserts and returns false = 0
call session_initiation(1,1,'abc123')   //returns false = 0, does not reinsert the record.

As demo-ed by @wchiquito

The solution I provided is correct in SQL Fiddle. I made slight modifications to include an primary field, which is auto_incremented.

Here are the table details:

DROP TABLE IF EXISTS `active_session`;
CREATE TABLE IF NOT EXISTS `active_session` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `device_id` bigint(20) NOT NULL,
  `token` varchar(255) DEFAULT NULL,
  `datetime_create` datetime DEFAULT NULL,
  `datetime_expiry` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`session_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
mcv
  • 1,380
  • 3
  • 16
  • 41
  • Can you run the stored procedure from the MySQL command line? The purpose is to rule out a problem with phpMyAdmin. – wchiquito Mar 13 '14 at 15:17
  • I ran it using shell as supplied in the xampp controller and yes the query produces the correct result - that makes me smile. On the phpmyadmin side it doesn't. – mcv Mar 13 '14 at 15:45
  • 1
    The problem seems to be in phpAdmin and not in the stored procedure. – wchiquito Mar 13 '14 at 16:02
  • Yes. I went to the my support page of phpadmin and it said go here. So I reopened this issue again with tags of phpmyadmin and routines. Having the procedure return false was a poor move on my behalf, cause if a pdo statement fails in any form it returns false. I changed it to a string and got the results I wanted, but I sure do like checking my routines on the phpmyadmin console to verify that the action being performed is what I want. I am exhausted... hope that the phpmyadmin people see this and fix it for a future release. – mcv Mar 13 '14 at 16:09

1 Answers1

1

Change:

...
IF NOT EXISTS (SELECT (1) from active_session where token = _token) THEN
...

by:

...
IF (SELECT (1) from active_session where token = _token) IS NOT NULL THEN
...

UPDATE

After creating a test SQL Fiddle the problem you describe I can not reproduce. Your code works as expected. The stored procedure does not seem to be the problem.

wchiquito
  • 16,177
  • 2
  • 34
  • 45
  • I have tried so many different ways, none seem to work correctly. I upgraded the phpMyAdmin and now when I run any stored procedure the output is not being displayed. I think I might try to not rely on the phpMyAdmin sql console for a solution. Even the most simplest procedure of get_country_list does not display correctly. – mcv Mar 12 '14 at 18:42
  • thank you. I will have to remember to use sql fiddle next time. – mcv Mar 13 '14 at 11:06
  • I reinstalled XAMPP and the issue has not disappaeared. So I reopened this and included more info. Thanks for showing me sqlfiddle, but there is a bug in the environment which is preventing me to move forward :( – mcv Mar 13 '14 at 14:01