#873 Whitespace in column name breaks table content editor

open
Colin Bell
Core (461)
5
2012-12-29
2010-05-17
Anonymous
No

In SQuirrel 3.1.1 on Ubuntu amd64:

When a record is inserted or updated in the table content editor ("Content" tab while viewing a table object), SQuirrel does not place quotes around the column names in the SQL it generates. As a result, SQuirrel will fail to edit tables where column names contain spaces.

Example:

Suppose I have the following table:
CREATE TABLE "fails" (
"product" TEXT NOT NULL,
"serial number" INTEGER NOT NULL
);

When editing the table, SQuirrel will give the following error:
Exception seen during check on DB. Exception was:
[SQLITE_ERROR] SQL error or missing database (near "number": syntax error)
Insert was probably not completed correctly . DB may be corrupted!

The log file shows:
2010-05-17 17:32:23,277 [AWT-EventQueue-1] INFO net.sourceforge.squirrel_sql.client.session.DataSetUpdateableTableModelImpl - insertRow: pstmt sql = INSERT INTO fails ( product,serial number) VALUES ( ?, ?)

Obviously, the SQL statement should have been:
INSERT INTO "fails" ( "product", "serial number" ) VALUES ( ?, ?)

If I enter the SQL statement (with quotes) by hand, it works just fine.

UPDATE behaves similarly.

This example is from SQLite3 via the xerial JDBC driver, but I have seen
the same behaviour with MySQL. (Requiring backticks instead of double-quotes,
of course.)

Of course, everyone disapproves of whitespace in column names, but they do
exist in the wild, and often we inherit databases we didn't design ourselves. So
it would be very useful if SQuirrel could edit tables with whitespace in identifiers.

Alex

Discussion