Description:
prepared statement with update on a view doesn't work (but plain query works).
Output from mysql-test-run ps_view_0001.test:
At line 89: query 'EXECUTE stmt USING @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11, @v12, @v13, @v14, @v15, @v16, @v17, @v18, @v19 ' failed: 1347: Column 'SQLX' is not updatable
How to repeat:
###################### ps_view_001.test ######################
# #
# test for updatable view #
# #
##############################################################
USE test;
--disable_warnings
drop table if exists REPOSRC;
drop view if exists PROGDIR;
--enable_warnings
let $type= 'InnoDB' ;
# create table REPOSRC
eval create table REPOSRC (
PROGNAME varchar(40) character set latin1 collate latin1_bin NOT NULL default '',
R3STATE char(1) character set latin1 collate latin1_bin NOT NULL default '',
SQLX char(1) character set latin1 collate latin1_bin NOT NULL default '',
EDTX char(1) character set latin1 collate latin1_bin NOT NULL default '',
DBNA char(2) character set latin1 collate latin1_bin NOT NULL default '',
CLAS varchar(4) character set latin1 collate latin1_bin NOT NULL default '',
TYPE char(3) character set latin1 collate latin1_bin NOT NULL default '',
OCCURS char(1) character set latin1 collate latin1_bin NOT NULL default '',
SUBC char(1) character set latin1 collate latin1_bin NOT NULL default '',
APPL char(1) character set latin1 collate latin1_bin NOT NULL default '',
SECU varchar(8) character set latin1 collate latin1_bin NOT NULL default '',
CNAM varchar(12) character set latin1 collate latin1_bin NOT NULL default '',
CDAT varchar(8) character set latin1 collate latin1_bin NOT NULL default '00000000',
VERN varchar(6) character set latin1 collate latin1_bin NOT NULL default '',
LEVL varchar(4) character set latin1 collate latin1_bin NOT NULL default '',
RSTAT char(1) character set latin1 collate latin1_bin NOT NULL default '',
RMAND char(3) character set latin1 collate latin1_bin NOT NULL default '',
RLOAD char(1) character set latin1 collate latin1_bin NOT NULL default '',
UNAM varchar(12) character set latin1 collate latin1_bin NOT NULL default '',
UDAT varchar(8) character set latin1 collate latin1_bin NOT NULL default '00000000',
UTIME varchar(6) character set latin1 collate latin1_bin NOT NULL default '000000',
DATALG int(11) NOT NULL default '0',
VARCL char(1) character set latin1 collate latin1_bin NOT NULL default '',
DBAPL char(1) character set latin1 collate latin1_bin NOT NULL default '',
FIXPT char(1) character set latin1 collate latin1_bin NOT NULL default '',
SSET char(1) character set latin1 collate latin1_bin NOT NULL default '',
SDATE varchar(8) character set latin1 collate latin1_bin NOT NULL default '00000000',
STIME varchar(6) character set latin1 collate latin1_bin NOT NULL default '',
IDATE varchar(8) character set latin1 collate latin1_bin NOT NULL default '00000000',
ITIME varchar(6) character set latin1 collate latin1_bin NOT NULL default '',
LDBNAME varchar(20) character set latin1 collate latin1_bin NOT NULL default '',
UCCHECK char(1) character set latin1 collate latin1_bin NOT NULL default '',
MAXLINELN smallint(6) NOT NULL default '0',
DATA longblob,
PRIMARY KEY (PROGNAME,R3STATE)
) engine = $type ;
#create view PROGDIR
CREATE VIEW .PROGDIR AS select sql_no_cache .T0001.PROGNAME AS `NAME`,.T0001.R3STATE AS `STATE`,.T0001.SQLX AS `SQLX`,.T0001.EDTX AS `EDTX`,.T0001.VARCL AS `VARCL`,.T0001.DBAPL AS `DBAPL`,.T0001.DBNA AS `DBNA`,.T0001.CLAS AS `CLAS`,.T0001.TYPE AS `TYPE`,.T0001.OCCURS AS `OCCURS`,.T0001.SUBC AS `SUBC`,.T0001.APPL AS `APPL`,.T0001.SECU AS `SECU`,.T0001.CNAM AS `CNAM`,.T0001.CDAT AS `CDAT`,.T0001.UNAM AS `UNAM`,.T0001.UDAT AS `UDAT`,.T0001.VERN AS `VERN`,.T0001.LEVL AS `LEVL`,.T0001.RSTAT AS `RSTAT`,.T0001.RMAND AS `RMAND`,.T0001.RLOAD AS `RLOAD`,.T0001.FIXPT AS `FIXPT`,.T0001.SSET AS `SSET`,.T0001.SDATE AS `SDATE`,.T0001.STIME AS `STIME`,.T0001.IDATE AS `IDATE`,.T0001.ITIME AS `ITIME`,.T0001.LDBNAME AS `LDBNAME`,.T0001.UCCHECK AS `UCCHECK` from .REPOSRC T0001 where ((.T0001.R3STATE = _latin1'A') or (.T0001.R3STATE = _latin1'I'));
# SET VARIABLES
SET @v0 = '';
SET @v1 = '';
SET @v2 = 'X';
SET @v3 = 'S';
SET @v4 = 'D$';
SET @v5 = '';
SET @v6 = '';
SET @v7 = '';
SET @v8 = '1';
SET @v9 = 'S';
SET @v10 = '';
SET @v11 = '';
SET @v12 = '000';
SET @v13 = '';
SET @v14 = 'X';
SET @v15 = '';
SET @v16 = 'D$S';
SET @v17 = 'X';
SET @v18 = 'SDB1FMYS';
SET @v19 = 'A';
# please note, that the query works fine
UPDATE `PROGDIR` SET `SQLX` = @v0 ,`EDTX` = @v1 ,`VARCL` = @v2 ,`DBAPL` = @v3 ,`DBNA` = @v4 ,`CLAS` = @v5 , `TYPE` = @v6 ,`OCCURS` = @v7 ,`SUBC` = @v8 ,`APPL` = @v9 ,`SECU` = @v10 ,`RSTAT` = @v11 , `RMAND` = @v12 ,`RLOAD` = @v13 ,`FIXPT` = @v14 ,`SSET` = @v15 ,`LDBNAME` = @v16 ,`UCCHECK` = @v17 WHERE `NAME` = @v18 AND `STATE` = @v19 ;
# PREPARE stmt_name
PREPARE stmt FROM 'UPDATE `PROGDIR` SET `SQLX` = ? ,`EDTX` = ? ,`VARCL` = ? ,`DBAPL` = ? ,`DBNA` = ? ,`CLAS` = ? , `TYPE` = ? ,`OCCURS` = ? ,`SUBC` = ? ,`APPL` = ? ,`SECU` = ? ,`RSTAT` = ? , `RMAND` = ? ,`RLOAD` = ? ,`FIXPT` = ? ,`SSET` = ? ,`LDBNAME` = ? ,`UCCHECK` = ? WHERE `NAME` = ? AND `STATE` = ? ' ;
# EXECUTE stmt_name
EXECUTE stmt USING @v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7, @v8, @v9, @v10, @v11, @v12, @v13, @v14, @v15, @v16, @v17, @v18, @v19 ;
# 3. DEALLOCATE PREPARE stmt_name;
DEALLOCATE PREPARE stmt ;
