SqlDbx Forum

cthomson

Member

Formatting Issue

I searched to see if someone else mentioned this so I hope I didn't miss something...

It appears that the IntelliSense does not recognize field names surrounded by square brackets [].  This causes it to format words that are part of field names as though they were text in a string constant.  For example, I have a database upgraded to SQL Server from Access that has many tables containing a field named "Report Date" (yes, I know spaces in the field name are not a good idea but they are permissible and I have to maintain backward compatibility).  When using square brackets surrounding the field name, the word "date" in the field name gets capitalized and colored as a keyword rather than being treated as a field name.

Similarly, when using double quote marks surrounding a field name, the name is formatted as a string rather than as a field name.

sqldbxhelp

Administrator

Re: Formatting Issue

I need more information. In what situation square brackets not recognized.
Not sure understand the issue with double quotes either.

cthomson

Member

Re: Formatting Issue

For example, one of the tables in a database has fields Store, Report Date, and Bank Deposit.  If I want to use the Report Date field in a SELECT statement or a WHERE clause, I can either type [Report Date] or "Report Date" to allow using a field name with an embedded space.

If I type [Report Date], I get [Report DATE] with the word "date" capitalized and colored as a keyword (blue in my case because I use that color and capitalize keywords) rather than being left as entered, because it's really part of a field name.

Similarly, if I type "Report Date", the formatter doesn't change the capitalization but it does color it as though it were a string literal rather than a field name.

Both ways function properly - they don't cause a syntax error and they produce correct query results - it's only the way they are formatted while typing the query.

Last edited by cthomson (2013-03-27 11:36:46)

sqldbxhelp

Administrator

Re: Formatting Issue

Sorry, but I do not see how you can expect editor to recognize Report Date to be a column name when you just type it in Editor. As a matter of fact it does not matter if it has space or not it's just a word. Capitalization of date when you type ] probably should be fixed.

cthomson

Member

Re: Formatting Issue

I'm not really concerned if it is recognized as a field name (I don't have field names getting any special formatting) but it would be nice if it was NOT recognized as something else such as a keyword or string constant.

The editor can recognize it as a field name when I type it based on the delimiters.  The ANSI SQL standard has double quotes as field name delimiters (single quotes delimit string literals).  SQL Server and Sybase also recognize square brackets as field name delimiters... the other major databases don't use square brackets for anything so square brackets can safely be assumed to be identifier delimiters.  MySQL uses back-quotes for identifier delimiters.  Again, the others don't use back-quotes for any purpose so identifier delimiter is a safe assumption.

So, if I type a double quote, or an open square bracket in MS-SQL or Sybase, or a back-quote in MySQL, whatever follows up to the next quote or closing square bracket would be a field name (the editor knows from the current connection information what database syntax is in use).  And even if there is no distinction in the editor for different syntax versions, the assumptions about delimiter usage would be valid in almost every case.

Specifically, when I type [Report Date the editor should know that I am typing a delimited identifier because of the opening square bracket so it should not capitalize DATE and color it as a keyword.  The same applies if I started typing "Report Date - the double quote indicates an identifier rather than a string literal.

Last edited by cthomson (2013-03-28 13:58:30)

sqldbxhelp

Administrator

Re: Formatting Issue

Agree. You raise a valid point here.

yuyuki

Member

Re: Formatting Issue

Hi,

I meet also some formatting problem in Oracle.

When I format this:
SELECT
    *
FROM
    T_DMV_PARAM_CONF p
    INNER JOIN T_DMV_SPEC_PARAM_CONF s ON s.PAC_ID = p.PAC_ID;

I have this:
SELECT *
FROM
    T_DMV_PARAM_CONF p INNER
    JOIN T_DMV_SPEC_PARAM_CONF s ON s.PAC_ID = p.PAC_ID;

and for Package it's horrible :s

When I have this:
CREATE OR REPLACE PACKAGE DMV_PRC_CONF
AS
    PROCEDURE lst_env (p_cur_out OUT SYS_REFCURSOR);
   
    PROCEDURE lst_platform (p_cur_out OUT SYS_REFCURSOR);
END;

The result is this:
CREATE OR REPLACE PACKAGE DMV_PRC_CONF AS PROCEDURE lst_env (p_cur_out OUT SYS_REFCURSOR)
;
PROCEDURE lst_platform (p_cur_out OUT SYS_REFCURSOR)
;
END;

Not really beautifull.

Could you fix it,

Thank you