Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7852

CONNECT does not work with if(exists( statement in procedures

    Details

      Description

      CONNECT returns error "Unsupported command" when encountering "if exists(select * from table) then" statement in stored procedure.

      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 10
      Server version: 10.0.17-MariaDB mariadb.org binary distribution
       
      Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [(none)]> use test
      Database changed
      MariaDB [test]> show create table test\G
      *************************** 1. row ***************************
             Table: test
      Create Table: CREATE TABLE `test` (
        `col1` int(10) DEFAULT NULL,
        `col2` varchar(255) DEFAULT NULL,
        `col3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAM
      P
      ) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=CCRDB01-WH_FDB_1;uid=username;pwd=password' `TABLE_TYPE`='ODBC' `BLOCK_SIZE`=10
      1 row in set (0.00 sec)
       
      MariaDB [test]> delimiter ;;
      MariaDB [test]> CREATE PROCEDURE `connect_if_exists_test`()
          ->  LANGUAGE SQL
          ->  NOT DETERMINISTIC
          ->  CONTAINS SQL
          ->  SQL SECURITY DEFINER
          ->  COMMENT ''
          -> BEGIN
          ->
          -> if (exists(select * from test)) then
          ->  select 1;
          -> else
          ->  select 0;
          -> end if;
          -> END;;
      Query OK, 0 rows affected (0.01 sec)
       
      MariaDB [test]>
      MariaDB [test]> delimiter ;
      MariaDB [test]> call connect_if_exists_test()\G
      ERROR 1148 (42000): CONNECT Unsupported command
      MariaDB [test]>

      The problem is most likely caused by the IF statement and not the "EXISTS" statement as the IF function can be used as a workaround. Like "if(exists(select * from test), true, false)". If this workaround is used, one should take note of issue MDEV-7616.

        Attachments

          Activity

            People

            • Assignee:
              bertrandop Olivier Bertrand
              Reporter:
              Tuco Tuco
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: