Description:
Unable to insert properly formatted string literal. Server or driver appears to be incorrectly parsing the string literal as a control statement.
Same two insert statements work perfectly in other DBMSs such as MS SQL Server, FirebirdSQL, PostgreSQL, DB2, Oracle, etc.
How to repeat:
create table in_sync_dbms_object
(
dbms_cd char(8) not null,
object_type_cd char(8) not null,
sql_type_cd char(8) not null,
ref_dbms_cd char(8),
ref_object_type_cd char(8),
ref_sql_type_cd char(8),
object_sql text,
object_script text,
object_sql_cat_col varchar(128),
object_sql_schem_col varchar(128),
object_sql_parent_name_col varchar(128),
object_sql_name_col varchar(128),
object_sql_sig_col varchar(128),
object_sql_subtype_col varchar(128),
object_sql_ord_position_col varchar(128),
object_sql_text_col varchar(128),
side_effect_desc varchar(80),
ctrl_ins_dtm timestamp not null default current_timestamp,
ctrl_upd_dtm timestamp not null,
ctrl_usr_id varchar(128) not null,
primary key (dbms_cd, object_type_cd, sql_type_cd)
);
insert into in_sync_dbms_object
(dbms_cd, object_type_cd, sql_type_cd, object_sql,
object_sql_text_col, object_sql_subtype_col,
ctrl_ins_dtm, ctrl_upd_dtm, ctrl_usr_id) values
('FB', 'CKT', 'RS',
'select
rc.rdb$constraint_name as constraint_name,
d.rdb$field_name as column_name,
CASE t.rdb$trigger_inactive
when 0 then 1
when 1 then 0
else t.rdb$trigger_inactive
END as status,
case when rc.rdb$constraint_name like ''INTEG\_%'' ESCAPE ''\'' then 1
else 0 end as system_named,
rc.rdb$relation_name as table_name,
t.rdb$trigger_source as trigger_source
from
rdb$relation_constraints rc,
rdb$check_constraints cc,
rdb$triggers t left outer join rdb$dependencies d on
t.rdb$trigger_name = d.rdb$dependent_name and
(select count(*) from rdb$dependencies d2 where
d2.rdb$dependent_name = t.rdb$trigger_name) = 1
where
rc.rdb$relation_name = :table_name and
rc.rdb$constraint_type = ''CHECK'' and
rc.rdb$constraint_name = cc.rdb$constraint_name and
cc.rdb$trigger_name = t.rdb$trigger_name and
t.rdb$relation_name = rc.rdb$relation_name and
t.rdb$trigger_type = 1 and
t.rdb$system_flag != 0',
'trigger_source', NULL,
'2012-10-24 00:00:00', '2012-11-09 00:00:00', 'sys');
insert into in_sync_dbms_object
(dbms_cd, object_type_cd, sql_type_cd, object_sql,
object_sql_text_col, object_sql_subtype_col,
ctrl_ins_dtm, ctrl_upd_dtm, ctrl_usr_id ) values
('PG', 'CKT', 'RS',
'select
tc.constraint_name,
ccu.column_name,
case
when tc.constraint_name like tc.table_name || ''\_check%'' escape ''\'' then 1
when tc.constraint_name like tc.table_name || ''\_'' || ccu.column_name || ''\_check%'' escape ''\'' then 1
else 0 end as system_named,
tc.table_catalog as table_cat,
tc.table_schema as table_schem,
tc.table_name,
cc.check_clause
from
information_schema.table_constraints as tc left outer join
information_schema.constraint_column_usage ccu on
tc.table_schema = ccu.table_schema and
tc.table_name = ccu.table_name and
tc.constraint_name = ccu.constraint_name and
(select count(*) from information_schema.constraint_column_usage ccu2
where
tc.table_schema = ccu2.table_schema and
tc.table_name = ccu2.table_name and
tc.constraint_name = ccu2.constraint_name) = 1,
information_schema.check_constraints as cc
where
tc.table_schema = :table_schem and
tc.table_name = :table_name and
tc.constraint_type = ''CHECK'' and
tc.constraint_name not like ''%_not_null'' and
tc.table_schema = cc.constraint_schema and
tc.constraint_name = cc.constraint_name',
'check_clause', NULL,
'2012-10-30 00:00:00', '2012-11-12 00:00:00', 'sys');
Suggested fix:
I get the following errors executing the insert statements from SQL workbench, or ODBC client:
SQL Error. Native Code: 1064, SQLState: 42000, Return Code: -1
[MySQL][ODBC 5.2(a) Driver][mysqld-5.5.15]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'then 1
else 0 end as system_named,
rc.rdb$relation_name as table_name,
t.rdb$' at line 6
SQL Error. Native Code: 1064, SQLState: 42000, Return Code: -1
[MySQL][ODBC 5.2(a) Driver][mysqld-5.5.15]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'then 1
when tc.constraint_name like tc.table_name || ''\_'' || ccu.column_name' at line 6
Description: Unable to insert properly formatted string literal. Server or driver appears to be incorrectly parsing the string literal as a control statement. Same two insert statements work perfectly in other DBMSs such as MS SQL Server, FirebirdSQL, PostgreSQL, DB2, Oracle, etc. How to repeat: create table in_sync_dbms_object ( dbms_cd char(8) not null, object_type_cd char(8) not null, sql_type_cd char(8) not null, ref_dbms_cd char(8), ref_object_type_cd char(8), ref_sql_type_cd char(8), object_sql text, object_script text, object_sql_cat_col varchar(128), object_sql_schem_col varchar(128), object_sql_parent_name_col varchar(128), object_sql_name_col varchar(128), object_sql_sig_col varchar(128), object_sql_subtype_col varchar(128), object_sql_ord_position_col varchar(128), object_sql_text_col varchar(128), side_effect_desc varchar(80), ctrl_ins_dtm timestamp not null default current_timestamp, ctrl_upd_dtm timestamp not null, ctrl_usr_id varchar(128) not null, primary key (dbms_cd, object_type_cd, sql_type_cd) ); insert into in_sync_dbms_object (dbms_cd, object_type_cd, sql_type_cd, object_sql, object_sql_text_col, object_sql_subtype_col, ctrl_ins_dtm, ctrl_upd_dtm, ctrl_usr_id) values ('FB', 'CKT', 'RS', 'select rc.rdb$constraint_name as constraint_name, d.rdb$field_name as column_name, CASE t.rdb$trigger_inactive when 0 then 1 when 1 then 0 else t.rdb$trigger_inactive END as status, case when rc.rdb$constraint_name like ''INTEG\_%'' ESCAPE ''\'' then 1 else 0 end as system_named, rc.rdb$relation_name as table_name, t.rdb$trigger_source as trigger_source from rdb$relation_constraints rc, rdb$check_constraints cc, rdb$triggers t left outer join rdb$dependencies d on t.rdb$trigger_name = d.rdb$dependent_name and (select count(*) from rdb$dependencies d2 where d2.rdb$dependent_name = t.rdb$trigger_name) = 1 where rc.rdb$relation_name = :table_name and rc.rdb$constraint_type = ''CHECK'' and rc.rdb$constraint_name = cc.rdb$constraint_name and cc.rdb$trigger_name = t.rdb$trigger_name and t.rdb$relation_name = rc.rdb$relation_name and t.rdb$trigger_type = 1 and t.rdb$system_flag != 0', 'trigger_source', NULL, '2012-10-24 00:00:00', '2012-11-09 00:00:00', 'sys'); insert into in_sync_dbms_object (dbms_cd, object_type_cd, sql_type_cd, object_sql, object_sql_text_col, object_sql_subtype_col, ctrl_ins_dtm, ctrl_upd_dtm, ctrl_usr_id ) values ('PG', 'CKT', 'RS', 'select tc.constraint_name, ccu.column_name, case when tc.constraint_name like tc.table_name || ''\_check%'' escape ''\'' then 1 when tc.constraint_name like tc.table_name || ''\_'' || ccu.column_name || ''\_check%'' escape ''\'' then 1 else 0 end as system_named, tc.table_catalog as table_cat, tc.table_schema as table_schem, tc.table_name, cc.check_clause from information_schema.table_constraints as tc left outer join information_schema.constraint_column_usage ccu on tc.table_schema = ccu.table_schema and tc.table_name = ccu.table_name and tc.constraint_name = ccu.constraint_name and (select count(*) from information_schema.constraint_column_usage ccu2 where tc.table_schema = ccu2.table_schema and tc.table_name = ccu2.table_name and tc.constraint_name = ccu2.constraint_name) = 1, information_schema.check_constraints as cc where tc.table_schema = :table_schem and tc.table_name = :table_name and tc.constraint_type = ''CHECK'' and tc.constraint_name not like ''%_not_null'' and tc.table_schema = cc.constraint_schema and tc.constraint_name = cc.constraint_name', 'check_clause', NULL, '2012-10-30 00:00:00', '2012-11-12 00:00:00', 'sys'); Suggested fix: I get the following errors executing the insert statements from SQL workbench, or ODBC client: SQL Error. Native Code: 1064, SQLState: 42000, Return Code: -1 [MySQL][ODBC 5.2(a) Driver][mysqld-5.5.15]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'then 1 else 0 end as system_named, rc.rdb$relation_name as table_name, t.rdb$' at line 6 SQL Error. Native Code: 1064, SQLState: 42000, Return Code: -1 [MySQL][ODBC 5.2(a) Driver][mysqld-5.5.15]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'then 1 when tc.constraint_name like tc.table_name || ''\_'' || ccu.column_name' at line 6