Created attachment 149957 [details] sampledb tried to execute the following script: INSERT INTO "t2" (accountchart, accountcode) SELECT accountchart, accountcode FROM "t1"
Confirming with : Version: 6.1.5.2 Build ID: 90f8dcf33c87b3705e78202e3df5142b201bd805 Threads CPU : 8; OS : Mac OS X 10.14.3; UI Render : par défaut; Locale : fr-FR (fr_FR.UTF-8); Calc: group threaded Steps to reproduce : 1) Open the test ODB file. 2) In Tools > SQL, enter the select insert statement provided 3) error message : 1: firebird_sdbc error: *Dynamic SQL Error *SQL error code = -206 *Column unknown *ACCOUNTCHART *At line 4, column 1 caused by 'isc_dsql_prepare' According to the Firebird documentation, this is suppposed to work.
You need to quote the identifiers in this case. INSERT INTO "t2" ("accountchart", "accountcode") SELECT "accountchart", "accountcode" FROM "t1" With that done it will run as expected in the SQL window. However, it still fails in a query definition (w/Run SQL Direct = true) but with the quotes the error is different: Error code: 1 firebird_sdbc error: *Cursor is not open caused by 'isc_dsql_fetch'
Also the query works as expected when used in a macro: Sub Main stmt = thisdatabasedocument.currentcontroller.activeconnection.createStatement stmt.execute( "INSERT INTO " + """t2""" + "(" + """accountchart""" +", "+ """accountcode"""+") "+" SELECT "+ """accountchart""" +", "+"""accountcode""" +" FROM "+ """t1""" ) End Sub This runs as expected. So, the question really is; Should it run in the query definition window with SQL Direct = True? I'm not sure but doesn't the query definition require a result set?
@Drew : No, it shouldn't run in the query window, or at least, the GUI query interface isn't currently designed to accept INSERT statements (and never has been). This part of the GUI is only designed to take select statements and returne result sets, but not allow for updating or insertion. We should close this as WFM, because the solution is to use DML statements with quoting via the Tools > SQL window as you have indicated, and not the Query GUI window (irrespective of whether it is executed directly or not).
The error, which appears, says: Database is looking for uppercased ACCOUNTCHART, but "accountchart" seems to be the name of the field in the table. Queries without double-quotes will work well, but INSERTS and UPDATES won't work without double-quotes with the internal Firebird Database. This has nothing to do with with the GUI for queries. Query-GUI doesn't allow INSERT and UPDATE - only queries without changing the content of the table. The test for this bug could only be executed in Tools > SQL (for persons, who won't start macros ...).
(In reply to Alex Thurgood from comment #4) > > We should close this as WFM, because the solution is to use DML statements > with quoting via the Tools > SQL window as you have indicated, and not the > Query GUI window (irrespective of whether it is executed directly or not). Don't agree. When queries in Tools > SQL will work without double-quotes INSERTS and UPDATES should do it the same way. SELECT * FROM t2 will work. t2 will be accepted in a query. INSERT INTO t2 ... won't work. t2 won't be accepted in INSERTS, also won't be accepted in UPDATES.
(In reply to Robert Großkopf from comment #6) > (In reply to Alex Thurgood from comment #4) > > > > We should close this as WFM, because the solution is to use DML statements > > with quoting via the Tools > SQL window as you have indicated, and not the > > Query GUI window (irrespective of whether it is executed directly or not). > > Don't agree. When queries in Tools > SQL will work without double-quotes > INSERTS and UPDATES should do it the same way. > > SELECT * FROM t2 > > will work. t2 will be accepted in a query. > > INSERT INTO t2 ... > > won't work. t2 won't be accepted in INSERTS, also won't be accepted in > UPDATES. Ok, so agreeing that it is not a valid statement in query definer, then what we have is identical behavior in the SQL Window and a Basic macro (API) currently. Saying after just now trying your examples in that macro with same results. Your point that the rules for quoting are different between select statements and other DML (update,insert) could be an error however, or maybe a RFE?
(In reply to Drew Jensen from comment #7) > (In reply to Robert Großkopf from comment #6) > > (In reply to Alex Thurgood from comment #4) > > > > > > We should close this as WFM, because the solution is to use DML statements > > > with quoting via the Tools > SQL window as you have indicated, and not the > > > Query GUI window (irrespective of whether it is executed directly or not). > > > > Don't agree. When queries in Tools > SQL will work without double-quotes > > INSERTS and UPDATES should do it the same way. > > > > SELECT * FROM t2 > > > > will work. t2 will be accepted in a query. > > > > INSERT INTO t2 ... > > > > won't work. t2 won't be accepted in INSERTS, also won't be accepted in > > UPDATES. > > Ok, so agreeing that it is not a valid statement in query definer, then what > we have is identical behavior in the SQL Window and a Basic macro (API) > currently. Saying after just now trying your examples in that macro with > same results. > > Your point that the rules for quoting are different between select > statements and other DML (update,insert) could be an error however, or maybe > a RFE? sorry - or maybe not an error, maybe it is FB eng12 (embedded engine) behavior? IDK, only thing I can think to do is ask on the FB mailing list.
After reading a couple threads on the Firebird ML it seems this needs a little different explanation. The issue with the quoting being different between SELECT and INSERT is LibreOffice added it seems. To see this in action start with a create statement in the SQL window which does not quote any identifier ie: create table b1 (id integer, dat varchar(15)) Now the rule is that all unquoted identifiers are cast to uppercase. The table name is B1 and fields are ID and DAT. Back in the SQL window however libreoffice will quote identifiers used in select statements. So entering select id from b1 will throw an error (table b1 not found) precisely because the LO parser added the quotes. While using the API via Basic; execute command does not quote identifiers in the string executequery does so; stmt.execute( "select id from b1") passes because the FB engine cast identifiers to uppercase. stmt.executequery fails with error 'table b1 not found' because the identifiers where quoted. All the GUI tools in Base default to mixed case identifiers so when any DML needing execute vs executequery all the identifiers would need to be quoted explicitly. Long way to say it would look like a feature request to update the LO parser for UPDATE, INSERT, etc rather than a bug. A platform nuance worth space in the documentation me thinks also.
So we can either open a new report with RFE for the requirement for LO to quote field names, failing which they get converted by the firebird engine to ALLCAPS, or else change the title here (which won't really reflect the initial reporters observations). My preference would be for a new bug report to be opened as RFE and close this one as WFM, but I'm not forcing the issue as it is highly unlikely that I will be fixing this.
(In reply to Alex Thurgood from comment #10) > So we can either open a new report with RFE for the requirement for LO to > quote field names, failing which they get converted by the firebird engine > to ALLCAPS, or else change the title here (which won't really reflect the > initial reporters observations). > > My preference would be for a new bug report to be opened as RFE and close > this one as WFM, but I'm not forcing the issue as it is highly unlikely that > I will be fixing this. Right. Also worth noting, Firebird (along with DB2 and Oracle and HSQLdb) actually conform to the SQL:2008 and SQL-99 standards which define databases to be case insensitive for identifiers unless they are quoted and all of those RDBMS do so by casting non-quoted identifiers to uppercase. @perie_gut I'm setting this issue as WORKS FOR ME.