0

To be honest, I'm feeling pretty stupid right now. But this simply isn't working.

Scenario
I have a stored procedure that includes an output parameter. I'm trying to SELECT a value INTO that parameter. This seems simple, but it continues giving me faulty results. I've checked many online sources, and I'm certain that I'm trying to do it properly.


Code

DELIMITER //
CREATE PROCEDURE `spGetId`(
    IN ParamA VARCHAR(32),
    OUT OutputId INT
)
BEGIN
    SELECT `id` INTO OutputId
    FROM `Table`
    WHERE `column_a` = ParamA;
END//

CALL spGetId('foobar', @Bloop)//
SELECT @Bloop//


Results
I have two rows in this table, their IDs being '1' and '2'. The result I get back is '31', whether the SELECT statement matches anything or not. I have tried many variations, including removing the WHERE clause entirely and having the SELECT return a COUNT(1) into the parameter (which gives me a result of '32', despite there being only 2 rows), and I have tried "declaring" the @Bloop variable before using it in the sproc call by using SET @Bloop = 0.

If you have any insight on why this is happening, and what I can do to make it return the proper value, I would be much obliged. Also, if you can show me how to achieve the same desired result using a Stored Function instead, with a return value, I'd appreciate that even more! My desired approach is using a stored function, but I had similar problems with that, then gave up and tried using a stored proc, only to find I was getting similar results.

Anything you can offer would be helpful!

Edit:

CREATE TABLE `Table` (
 `id` int(11) NOT NULL auto_increment,
 `column_a` varchar(32) character set utf8 NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

mysql> SELECT * FROM Table;
+------+----------+
| id   | column_a |
+------+----------+
|    1 | asdf     |
|    2 | foobar   |
+------+----------+

When I call spGetId() with any argument, it returns the value '31' (even if the argument is 'foobar', which should return an integer value of '2' (or ascii 0x32)). If I modify spGetId() to return the total rowcount of Table, instead of returning '2', it returns '32'.

JMTyler
  • 1,604
  • 2
  • 21
  • 24
  • Can you show the output of "show create table `Table`" and "select * from `Table`" please ? – Martin Nov 27 '10 at 13:16
  • Try call `show warnings;` right after you `CALL spGetId('bleep', @Bloop)`, to me, is seems like type conversion issue – ajreal Nov 27 '10 at 19:15
  • Tested whit the `create table` from your latest edit and it works without problems. Are you sure you are testing this on the right database? – Alex Jasmin Nov 27 '10 at 21:24
  • @Alexandre Sorry, I'm not quite sure what you mean by "the right database". – JMTyler Nov 28 '10 at 01:24

2 Answers2

1

Your stored proc is working. I think it is returning the ascii value of the character '1' instead of the integer value 1.

Martin
  • 9,674
  • 5
  • 36
  • 36
  • That sounds pretty viable. But, the ID it *should* be returning is '2', so I'm not sure why it would be returning '1'. And if the column is an int(11) and the sproc return value is int(11), why would it return an ascii value? Do you know if there would be anything else at play there? – JMTyler Nov 27 '10 at 17:36
0

I need to learn to vary my testing environments.

I'm still not sure exactly what the problem was, but it looks like phpMyAdmin was performing some kind of type conversion of its own, and I had been running all my tests through that particular client.

Throwing together a quick PHP script of my own and manually calling the sproc (and in further testing, calling a stored function as well) provided the desired results.

So, lesson learned: don't ever trust the client. Got to remember to switch it up a bit.

JMTyler
  • 1,604
  • 2
  • 21
  • 24