MySQL Forums
Forum List  »  Stored Procedures

Re: Error " #1241 - Operand should contain 1 column(s)" when calling a function
Posted by: Jay Alverson
Date: August 24, 2009 11:27AM

That's one complex function.

You might try copying it into a PROCEDURE and then put SELECT statements
every so often so that you can debug it from top to bottom.

Then call the procedure and see if you can isolate the problem. Once you
get a chunk of code secure, just comment out the SELECT and move to the
next block.

That's usually what I do with complex functions; that way I can see how far
a variable or piece of code gets. I also often create temp tables so that
I can monitor the progress of the executing code:

mysql> 
mysql> use test;
Database changed
mysql> 
mysql> DELIMITER $$
mysql> 
mysql> DROP PROCEDURE IF EXISTS `test`.`GS` $$
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE PROCEDURE `test`.`GS` (str VARCHAR(500), delim VARCHAR(10))
    -> BEGIN
    ->   DECLARE pos SMALLINT;
    ->   DECLARE dastr VARCHAR(100);
    ->   DECLARE daCount SMALLINT;
    -> 
    ->   SET daCount = 0;
    -> 
    -> 
    -> 
    ->   drop table if exists test_getstring;
    ->   create table test_getstring (pos smallint, dastr varchar(500), str varchar(500), dapos smallint);
    -> 
    ->   label1: LOOP
    ->     SET pos = instr(str, delim);
    ->     SET dastr = SUBSTRING_INDEX(str, delim, 1);
    ->     IF pos > 0 THEN
    ->       SET daCount = daCount + 1;
    ->       SET str = mid(str, pos+1, CHAR_LENGTH(str));
    ->       ELSE LEAVE label1;
    ->     END IF;
    -> 
    ->     insert into test_getstring values (pos, dastr, str, dapos);
    -> 
    -> 
    ->     if daCount > 10000 then LEAVE label1; end if;
    ->   END LOOP label1;
    ->   SET daCount = daCount + 1;
    -> 
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> DELIMITER ;
mysql> 
mysql> call GS("One;Two;Three;Four;Five;Six;Seven", ";");
Query OK, 1 row affected (0.08 sec)

mysql> 
mysql> select * from test_getstring;
+------+-------+-------------------------------+-------+
| pos  | dastr | str                           | dapos |
+------+-------+-------------------------------+-------+
|    4 | One   | Two;Three;Four;Five;Six;Seven |  NULL | 
|    4 | Two   | Three;Four;Five;Six;Seven     |  NULL | 
|    6 | Three | Four;Five;Six;Seven           |  NULL | 
|    5 | Four  | Five;Six;Seven                |  NULL | 
|    5 | Five  | Six;Seven                     |  NULL | 
|    4 | Six   | Seven                         |  NULL | 
+------+-------+-------------------------------+-------+
6 rows in set (0.00 sec)

mysql> 
mysql> notee

>

Thanks, Jay

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Error " #1241 - Operand should contain 1 column(s)" when calling a function
3419
August 24, 2009 11:27AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.