Incorrect integer value: 'H' for column 'variable' at row 2 MySQL

Question

I have a stored procedure in MySQL

CREATE PROCEDURE insertFromJunction ()
BEGIN
DECLARE iteration INT DEFAULT NULL;
DECLARE adjacentID INT DEFAULT 0;
DECLARE junction VARCHAR(20) DEFAULT 0;
SET junction = 'H';
SET iteration = 1;

loop2: LOOP
    SET adjacentID = (SELECT junction FROM Hybrid WHERE Hybrid_ID = iteration);

    IF iteration = 2 THEN LEAVE loop2;            --it can even be 1,3 etc
    END IF; 

    SET iteration = iteration + 1;

END LOOP loop2;

END;
$$

Now here is the hybrid table

Hybrid_ID  N    K    J    H
1          15   10   11   9
2          13   15   5    7
3          20   16   4    10
4          NULL NULL 3    NULL
5          NULL NULL 2    NULL
6          NULL NULL NULL NULL

So, basically what should happen is that the loop runs first time adjacenID = 9, then iteration = 1+1=2.

Then loop runs second time adjacentID = 7 and loop should terminate (from IF). But there is an error and it is when the SET staement is executing.

Error Code: 1366. Incorrect integer value: 'H' for column 'adjacentID' at row 2

What is being done wrong. I checked all the data types too.

The error persists even when termination condition is set to iteration = 1 which make me suspicious because there is no point of row 2 in that case.

In `SELECT junction FROM Hybrid WHERE Hybrid_ID = iteration` both `junction` and `iteration` are variables. `junction` is set to `'H'` above. You try to put this value to `adjacentID` which is INTEGER, so implicit convertion applied. `'H'` cannot be converted to numeric, so error occures. Why you're surprized? PS. Your procedure is not recursive. — Akina, Apr 29 '21 at 12:18
So i presume in the original somehting else happens with junction, trying to switch the culumn that is selected over the iterations. But this isn't dynamic sql, so you always get the value of junction, 'H' — Turo, Apr 29 '21 at 12:21
Provide the task itself - what operation the procedure must perform? Show desired output for "Now here is the hybrid table" values with explanations. Specify **precise** MySQL version. — Akina, Apr 29 '21 at 12:24
See https://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure for dynamic sql in stored procedures in mysql — Turo, Apr 29 '21 at 12:25
PS. Does `SET junction = 'H';` means that you want to select from column `H`? — Akina, Apr 29 '21 at 12:25
If so then you need in dynamic Sql (prepared statement, https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html) — Akina, Apr 29 '21 at 12:28
@Akina @Turo I used `SET @adjacentID = 'SELECT ? FROM Hybrid WHERE Hybrid_ID = ?' `, `PREPARE stmt FROM @adjacentID`, `EXECUTE stmt USING @junction,@iteration` but it treats H as a string rather than column. Can you post the solution as an asnwer. Also I don't intend to display the result but only set it. — mr.loop, Apr 29 '21 at 14:33

Akina · Accepted Answer · 2021-04-30T18:50:54.240

1

CREATE PROCEDURE insertFromJunction ()
BEGIN
-- Initialize needed user-defined variables
SET @junction = 'H';
SET @iteration = 1;

loop2: LOOP
    -- Build query text
    SET @adjacentID = CONCAT( 'SELECT ',
                              @junction,
                              ' FROM Hybrid WHERE Hybrid_ID = ',
                              @iteration);
    -- Prepare and execute the query
    PREPARE stmt FROM @adjacentID;
    EXECUTE stmt;
    DROP PREPARE stmt;
    -- Debug exit
    IF @iteration = 2 THEN 
        LEAVE loop2;
    END IF; 
    -- increase iterator
    SET @iteration = @iteration + 1;
END LOOP loop2;

END

edited Apr 30 '21 at 18:50

answered Apr 29 '21 at 15:53

Akina

39,301
5
14
25

it works, but when I used `SET @adjacentID = 'SELECT ? FROM Hybrid WHERE Hybrid_ID = ?';` and then `PREPARE stmt FROM @adjacentID;`, the output was again string 'H' rahter than `7` which occurs under column H. I wonder why is that considering I only changed the format – mr.loop Apr 30 '21 at 17:53
1

@mr.loop Column name **CANNOT BE SUBSTITUTED FROM A VALUE**. Including parametrized value. It is a literal which is accepted as-is, literally. So you select not column named `H` but string literal with value `'H'`. – Akina Apr 30 '21 at 18:50
thanks, that's why dynamic sql was needed. – mr.loop Apr 30 '21 at 23:48

Incorrect integer value: 'H' for column 'variable' at row 2 MySQL

1 Answers1