#1506 Copy as SQL INSERT-VALUES statement wont work with quoted Column Names

[Claus]

If a column was created via quotes, insert into columns has to be quoted too.

Example to retest (System i, should be the same in Oracle):

create table "test" (
"Dummy" char(1)
);

insert into "test" values ('a');
insert into "test" values ('b');

select * from "test";

Select result and use
Copy as SQL INSERT-VALUES
will result in

INSERT INTO "TEST" (Dummy) VALUES ('a');
INSERT INTO "TEST" (Dummy) VALUES ('b');

instead of:

INSERT INTO "TEST" ("Dummy") VALUES ('a');
INSERT INTO "TEST" ("Dummy") VALUES ('b');

[Claus]

Tested in SQuirreL snapshot-20220705_1007 and snapshot-20220614_2245

[Claus]

Oh sorry - additional error

INSERT INTO "test" ("Dummy") VALUES ('a');
INSERT INTO "test" ("Dummy") VALUES ('b');

Table has to be in lower case too...

If
create table "test"
would have been
create table "TeSt"
it should be
INSERT INTO "TeSt" ("Dummy") VALUES ('a');

[Gerd Wagner]

Quoting columns is fixed and will be available from snapshot-20220710_0250 on.

[Gerd Wagner]

On your "additional error": Is fixed, too. Excerpt from change log:
'#1506: Concerning SQL-insert-scripts generated from SQL queries (ctrl+T -> sql2ins) or copied from SQL result tables:
Scripted table names used to be all upper case. Now the original spelling is preserved.

[Nick Fenwick]

SquirrelSQL 4.5.1 (latest release):
Was this Bug raised when working against Oracle database? I think it's what has been causing me grief for the last 8 months :) When working against MySQL, quoted column names causes syntax errors, e.g. selecting a single field of results and choosing 'Copy as SQL INSERT-VALUES' creates the SQL:

INSERT INTO my_table ("style") VALUES ('whatever');

And running against MySQL (actually MariaDB 10.5.19):

Error: (conn=291) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"style") VALUES ('whatever')' at line 1
SQLState: 42000
ErrorCode: 1064

For MySQL we should use backticks:

INSERT INTO my_table (style) VALUES ('whatever');

I'm not familiar with SquirrelSQL development so cannot track this bug fix to a commit in a repo somewhere, otherwise I'd look a bit deeper and see if it was implemented with configurable behaviour, please correct me if there is a workaround to the above problem. Thanks!

[Gerd Wagner]

Nick, quoting is configurable (though not to use backticks, yet) and does not depend on the database you are connected to. See menu File --> Global Preferences --> tab SQL Scripts --> First and second check box.

[Nick Fenwick]

Wonderful, thank you Gerd. I can stop having to paste the script into an
editor and Replace All to get rid of the quotes :)  Apologies for the noise.