|
From: Neville R. <row...@ho...> - 2016-06-26 08:27:44
|
I'm not able to replicate this - any chance of more information? I suspect specifically MonetDB version and Monet JDBC version may be helpful. I'm using the latest Squirrel from GIT which is the same as the latest snapshot, along with a latest download of MonetDB and the MonetDB jdbc driver, and it seems to work fine. I'm looking at the content pane for the columns object under MonetDB/sys/SYSTEM VIEW, but I've also checked the _columns system table. I've also tested with hsqldb creating a table with columns called default and null and it works fine there too. The only oddity I can see is in the Objects tab I have to refresh the tree in order to see it. So at the point where we cache the DDL (I think as soon as we open the connection) it isn't yet available on the jdbc. Thoughts? N. ________________________________ From: martin van dinther <mar...@mo...> Sent: 23 June 2016 16:44 To: squ...@li... Subject: [Squirrel-sql-develop] Incorrect SQL code generated for retrieving data from a specific table in the Content pane Hi, I noticed SQuirreL is generating an incorrect SQL SELECT statement when selecting the Content panel for a specific table in the Objects tab. This has been reported many times before, but it is not fixed correctly/permanently for all RDBMS in 3.7 or latest snapshot 20160613_2107. See: https://sourceforge.net/p/squirrel-sql/bugs/1183/ SQuirreL SQL Client / Bugs / #1183 Empty Contents Tab in ...<https://sourceforge.net/p/squirrel-sql/bugs/1183/> sourceforge.net #1183 Empty Contents Tab in SQuirreL when Column-Names contain dashes https://sourceforge.net/p/squirrel-sql/bugs/1210/ SQuirreL is generating an SQL in the format of: select tbl.id,tbl.name,tbl.type,tbl.type_digits,tbl.type_scale,tbl.table_id,tbl.default,tbl.null,tbl.number,tbl.storage from "sys"."columns" tbl Which produces errors/warnings (in any RDBMS as default and null are reserved keywords): 2016-06-23 16:10:30,352 [Thread-5] WARN net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.table.ContentsTab - Failed to execute content SQL: select tbl.id,tbl.name,tbl.type,tbl.type_digits,tbl.type_scale,tbl.table_id,tbl.default,tbl.null,tbl.number,tbl.storage from "sys"."columns" tbl java.sql.SQLException: syntax error, unexpected DEFAULT in: "select tbl.id,tbl.name,tbl.type,tbl.type_digits,tbl.type_scale,tbl.table_id,tbl." at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2602) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:2350) at nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:507) at nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:345) at nl.cwi.monetdb.jdbc.MonetStatement.executeQuery(MonetStatement.java:525) at net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.table.ContentsTab.createResultSet(ContentsTab.java:357) at net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.table.ContentsTab.createDataSet(ContentsTab.java:252) at net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.BaseDataSetTab$1.run(BaseDataSetTab.java:131) at net.sourceforge.squirrel_sql.fw.util.TaskExecuter.run(TaskExecuter.java:82) at java.lang.Thread.run(Thread.java:745) 2016-06-23 16:10:30,353 [Thread-5] WARN net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.table.ContentsTab - Failed to execute content SQL: select tbl.id,tbl.name,tbl.type,tbl.type_digits,tbl.type_scale,tbl.table_id,tbl.default,tbl.null,tbl.number,tbl.storage from "sys"."columns" tbl at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2602) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:2350) at nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:507) at nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:345) at nl.cwi.monetdb.jdbc.MonetStatement.executeQuery(MonetStatement.java:525) at net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.table.ContentsTab.createResultSet(ContentsTab.java:357) at net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.table.ContentsTab.createDataSet(ContentsTab.java:257) at net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.tabs.BaseDataSetTab$1.run(BaseDataSetTab.java:131) at net.sourceforge.squirrel_sql.fw.util.TaskExecuter.run(TaskExecuter.java:82) at java.lang.Thread.run(Thread.java:745) The used SQL select query generates wrong SQL for columns which are: - reserved keywords (such as default, null, user), - contain a character which has a special meaning in expressions (such as -, +, /, *, _, %) or - columns which exist multiple times but with different case such as: create table test_column_names ("abc" int, "ABC" int, "aBc" int); insert into test_column_names values (1, 11, 111); insert into test_column_names values (2, 12, 112); select * from test_column_names; abc ABC aBc 1 11 111 2 12 112 For this table SQuirreL would generate the query: select tbl.abc, tbl.ABC, tbl.aBc from "sys"."test_column_names" tbl which results in an incorrect output: abc abc abc 1 1 1 2 2 2 Note: this output is for MonetDB (www.monetdb.org<http://www.monetdb.org>) which uses lowercase names as default case. Other RDBMS usually use UPPERcase as default case. For those the output would be: ABC ABC ABC 11 11 11 12 12 12 I suggest to correct the select query code generation (when viewing data in Content pane) by: - add double quote's to all column names (as is done for the schema name and the table/view name) - eliminate the alias: tbl as it is not needed in this single table query. It only makes the query text longer. So for the above test_column_names example the generated select query would become: select "abc", "ABC", "aBc" from "sys"."test_column_names" which will give the correct output i.e. same as select * from "sys"."test_column_names" output. This is standard SQL and should work for all SQL-compliant RDBMS. Also it would not need to fallback to the select * from "sys"."test_column_names" (so sending two queries to the RDBMS as is added in 3.7) and not write any errors/warnings to the log file. Hope this helps in resolving the Content problem and improving SQuirreL. Thanks for the great SQuirreL tool. Ciao, Martin van Dinther ------------------------------------------------------------------------------ Attend Shape: An AT&T Tech Expo July 15-16. Meet us at AT&T Park in San Francisco, CA to explore cutting-edge tech and listen to tech luminaries present their vision of the future. This family event has something for everyone, including kids. Get more information and register today. http://sdm.link/attshape _______________________________________________ Squirrel-sql-develop mailing list Squ...@li... https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop |
