How to quote column names on the commandline of fb

问题:

As the title says, how am I going to deal with a column name in FBExport that look like a keyword?

this is how my statement looks like:

-Q "SELECT a.ID, a.USERID, a.`WHEN`, a.INOUT FROM ATTENDANT a"

then I get this error:

Engine Code    : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 26
WHEN

When I use

"WHEN"


Error: Switches must begin with -

tried 'When'

-Q "SELECT a.ID, a.USERID, a.'WHEN', a.INOUT FROM ATTENDANT a;"
SQL Message : -104
Invalid token

Engine Code    : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 26
'WHEN'

Error: Switches must begin with -

What are the correct escape characters?

回答1:

For dialect 3 database, Firebird allows quoting object names using double quotes ("<objectname>"). Be aware that quoting object names makes them case sensitive, so "WHEN" is not the same as "when". If your database is dialect 1 then this is not possible, and you should first convert your database to dialect 3.

However the problem this is a command line option, meaning that

-Q "SELECT a.ID, a.USERID, a."WHEN", a.INOUT FROM ATTENDANT a"

Is split by your shell to the arguments:

1. -Q
2. SELECT a.ID, a.USERID, a.
3. WHEN
4. , a.INOUT FROM ATTENDANT a

While you want:

1. -Q
2. SELECT a.ID, a.USERID, a."WHEN", a.INOUT FROM ATTENDANT a

To achieve that, you need to escape the double quote inside the second argument, so:

-Q "SELECT a.ID, a.USERID, a.\"WHEN\", a.INOUT FROM ATTENDANT a"

or - as indicated by a_horse_with_no_name in the comments - wrap the argument in single quotes:

-Q 'SELECT a.ID, a.USERID, a."WHEN", a.INOUT FROM ATTENDANT a'

This doesn't really have to do with Firebird or FBExport, but is a result of how your shell (eg bash) parses commandline arguments.

回答2:

It looks like someone else is dealing with the external access to the Firebird database from Safescan TimeAttenedant

It was a little bit stupid from Safescan to name a column WHEN because this is a keyword in Firebird.

In the context of an insert statement, I have no success with a column list like:

insert into attendant (ID, USERID, DEVICEID, WHEN, INOUT, VERIFYMODE, WORKCODE) values (1092, 1, 1, '28.08.2017 08:00', 0, 4, 3);

"WHEN", \"WHEN\", \'WHEN\', ... no success

Remedy - Insert all data without column list, ex:

insert into attendant values (1034, 2, 1, '28.08.2017 08:00', 0, 4, null, null, null, null, null, 3);

Query is much easier: select * from attendant;