How to insert a string with a two-levels deep nesting of quotes in a H2 database?-CodePudding

I need to insert this string, from the H2 console, with a two-levels deep nesting of quotes. Since H2 DB uses single quotes, I cannot use double quotes. I saw this SO fix for a string with a nested quote.

I tried to insert, but got this error

INSERT INTO Encouragement VALUES(31,'WhoIAmInChrist','NoAnxietyInChrist','Uplifting','God is bigger than our fearful hearts: Isaiah 35:4 '\''Say to those with fearful hearts, '\''Be strong, do not fear; your God will come, he will come with vengeance; with divine retribution he will come to save you'\'' '\'' (NIV).')

In the browser console output, I see the error message:

INSERT INTO Encouragement VALUES(31,'WhoIAmInChrist','NoAnxietyInChrist','Uplifting','God is bigger than our fearful hearts: Isaiah 35:4 \"Say to those with fearful hearts, \"Be strong, do not fear; your God will come, he will come with vengeance; with divine retribution he will come to save you'\" '\" (NIV).');

Syntax error in SQL statement "INSERT INTO Encouragement VALUES(31,'WhoIAmInChrist','NoAnxietyInChrist','Uplifting','God is bigger than our fearful hearts: Isaiah 35:4 \\""Say to those with fearful hearts, \\""Be strong, do not fear; your God will come, he will come with vengeance; with divine retribution he will come to save you'[*]\\"" '\\"" (NIV).')"; SQL statement:
INSERT INTO Encouragement VALUES(31,'WhoIAmInChrist','NoAnxietyInChrist','Uplifting','God is bigger than our fearful hearts: Isaiah 35:4 \"Say to those with fearful hearts, \"Be strong, do not fear; your God will come, he will come with vengeance; with divine retribution he will come to save you'\" '\" (NIV).') [42000-214] 42000/42000 (Help)

Here is the schema:

CREATE TYPE Category AS ENUM('WhoIAmInChrist','Default')
CREATE TYPE Tone AS ENUM('Default', 'Uplifting', 'Urging', 'Warning', 'Soothing', 'Comforting', 'Inspiring', 'Centering', 'Balanced')
CREATE TYPE Topic AS ENUM('Default', 'AcceptedInChrist', 'SignificantInChrist', 'SecureInChrist', 'NoAnxietyInChrist');
CREATE TABLE Encouragement(ID INT PRIMARY KEY, CATEGORY Category, TOPIC Topic, TONE Tone, MESSAGE VARCHAR(255))

CodePudding user response：

You can escape single quotes by duplicating them. For example:

INSERT INTO enc VALUES(31,'God is 35:4 ''Say'' Be strong.');

will insert the value God is 35:4 'Say' Be strong..