Problems in a query performed within a Stored Function in Mysql

Question

Problems in a query performed within a Stored Function in Mysql

Navigation

#1 by (0 votes)

1

I have a problem inside a query in Mysql performed within Funcion .

The question is, among the various activities that the function is performing, is testing whether a given data has already been entered into a table.

The table is simple, it has the ID (primary key) id_fb (foreign key) and datetime .

When I run the query in phpMyAdmin , it returns the correct values, but when I run the same query within the function, it returns the value of the first row of the table.

The function is as follows (only with the part that matters)

DELIMITER $$

 CREATE FUNCTION teste1 (id_fb INT)
   RETURNS VARCHAR(512)

  BEGIN

    DECLARE ID_final varchar(20);

    SELECT 'id' INTO ID_final FROM 'brindes_teste' WHERE DATE('data_hora') = CURDATE() AND 'id_fb' = id_fb LIMIT 1;

    RETURN CONCAT('JÁ PARTICIPOU HOJE = ', ID_final, ' ID_FB=' ,id_fb );        

 END $$

The table is as follows:

Structure for table brindes_teste

CREATE TABLE 'brindes_teste' (
  'id' int(11) NOT NULL,
  'id_fb' bigint(17) DEFAULT NULL,
  'data_hora' datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Tabela brindes - DEV';

Data dump for table brindes_teste

INSERT INTO 'brindes_teste' ('id', 'id_fb', 'data_hora') VALUES
(2, 1235900, '2016-09-18 00:00:00'),
(3, 1441809, '2016-09-18 00:00:00'),
(4, 1453793, '2016-09-18 00:00:00'),
(7, 1249127, '2016-09-18 00:00:00'),
(9, 1737770, '2016-09-18 00:00:00');

When I make the following query:

SELECT 'id' FROM 'brindes_teste' WHERE DATE('data_hora') = CURDATE() AND 'id_fb' = 1249127 LIMIT 1;

The result is 7, which is expected

Now, when the same query is performed inside the function:

SELECT teste1(1249127)

The result is 2 (id of the first record in the table)

Can anyone identify the source of the problem?

mysql stored-procedures

asked by anonymous 18.09.2016 / 19:38

1 answer

Ordering method Bubblesort with dynamic allocation + pointer Search content within file

score 0 · Answer 1

Change the name of your input variable. Always good to put a prefix in the name of the variables to not confuse with the name of the column. ex: p_id_fb. p = parameter. Ex:

DELIMITER $$

 CREATE FUNCTION teste1 (p_id_fb INT) -- Nome da váriavel diferente do nome da coluna da tabela
   RETURNS VARCHAR(512)

  BEGIN

DECLARE ID_final varchar(20);

SELECT 'id' INTO ID_final FROM 'brindes_teste' WHERE DATE('data_hora') = CURDATE() AND 'id_fb' = p_id_fb LIMIT 1;

RETURN CONCAT('JÁ PARTICIPOU HOJE = ', ID_final, ' ID_FB=' ,p_id_fb );        

 END $$