Incorrect SQL code generated for retrieving data from a specific table in the Content pane

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Incorrect SQL code generated for retrieving data from a specific table in the Content pane

martin van dinther
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/
     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) 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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Incorrect SQL code generated for retrieving data from a specific table in the Content pane

Neville Rowe
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 <[hidden email]>
Sent: 23 June 2016 16:44
To: [hidden email]
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop
------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Incorrect SQL code generated for retrieving data from a specific table in the Content pane

martin van dinther
Hi Neville,

Did you check the log file contents? It will show you the warnings.
Also did you try the test case (test_column_names) which returns the
wrong contents when viewed via the Contents tab?

Recently (2016-03-06) some changes have been made, see
https://sourceforge.net/p/squirrel-sql/git/ci/4864dc6f625ac1d0fb3cd25f0d689d9d4070f945/
It introduces a fallback to use "select * from
"schema_name"."table_name" tbl " as a last resort.
This is why you see the contents now, but only after sending two invalid
queries to the RDBMS, which are written to the log.

I analysed
https://sourceforge.net/p/squirrel-sql/git/ci/4864dc6f625ac1d0fb3cd25f0d689d9d4070f945/tree/sql12/core/src/net/sourceforge/squirrel_sql/client/session/mainpanel/objecttree/tabs/table/ContentsTab.java
and found that it is trying to pull some pseudo column(s) which uniquely
identify a row (via md.getBestRowIdentifier(ti)).
However most RDBMS do not have such columns.
If it does not have this (so pseudoColumn.equals("")), then you no
longer need to construct the select list as done via:

String coded = gatherColumnsForContentSelect(md, ti).toString();

You could skip that code and call

rs = createResultSet(ti, stmt, "*");

directly. This would resolve the problem for MonetDB (and possibly other
RDBMS which do not support pseudo column(s) which uniquely identify a row).
In code (replacing lines 248 .. 268):
            if (pseudoColumn.length() > 0)
            {
               final String coded = gatherColumnsForContentSelect(md,
ti).toString();
               rs = createResultSet(ti, stmt, coded + pseudoColumn);
               if(null == rs)
               {
                  pseudoColumn = "";
                  rs = createResultSet(ti, stmt, "*");
               }
            } else {
               rs = createResultSet(ti, stmt, "*");
            }

            if(null == rs)
            {
                throw new IllegalStateException("Failed any way to
execute content SQL. See former warning log entries for details.");
            }



I also analysed where the composition of the select list is done, as
that is where the double quote's are not added;
   String coded = gatherColumnsForContentSelect(md, ti).toString();
This method incorrectly appends all column names (separated by ,) to a
string without adding double quote's around the columnnames.
It is calling
  CellComponentFactory.getColumnForContentSelect(table, columnInfo[i],
dialectType, "tbl.")
which is calling
  BaseDataTypeComponent.getColumnForContentSelect(dialectType, prefix)
which is calling
  DialectUtils2.checkColumnDoubleQuotes(dialectType,
_colDef.getColumnName());
which is calling
  shouldQuoteColumnName(dialectType, columnName)
to determine if the column name must be double quoted.
I think this logic (hardcoded over-ruling for specific RDBMS)
  if(dialectType == DialectType.HSQLDB || dialectType ==
DialectType.POSTGRES || dialectType == DialectType.H2) { return true; }
is NOT working/correct for all RDBMS.
You should call DatabaseMetaData.getIdentifierQuoteString() to find out
which string is used to quote an identifier and use it for all RDBMS.
Most RDBMS these days support Quoted identifiers. If not the value of
DatabaseMetaData.getIdentifierQuoteString() should be empty or null.
Note: Similar problem and logic is implemented in
DatabaseObjectInfo.generateQualifiedName() which constructs the fully
qualified table name including using IdentifierQuotes.

It is best to NOT hardcode RDBMS specific exceptions in generic code.
So please check if JDBC drivers for HSQLDB, POSTGRES and H2 all return a
non-empty DatabaseMetaData.getIdentifierQuoteString(). Probably they all
return " (the double quote character).
If so, remove the line   if(dialectType == DialectType.HSQLDB ||
dialectType == DialectType.POSTGRES || dialectType == DialectType.H2) {
return true; }
and always use the value of DatabaseMetaData.getIdentifierQuoteString()
to determine whether identifiers can and should be quoted or not.


Hope this helps.

Regards,
Martin van Dinther


On 26-06-16 10:27, Neville Rowe wrote:

>
> 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 <[hidden email]>
> *Sent:* 23 June 2016 16:44
> *To:* [hidden email]
> *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/
> <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
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop


------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-develop
Loading...