Thread: Lowercase folding - simple patch?
Hello, We have a large database with camel case names for schemas, tables, columns, etc. I'm able to connect via ODBC with MS Access,but not with Excel (MSQuery). The names are not quoted and lowercase folding kicks in - so all tables are 'not found'. If I manually edit the MSQuery sql and double-quote all names, i can get the queries to work - but each and everyquery must be manually edited. Obviously unworkable. Is it possible for the odbc driver to return schema, table and column names to MSQuery in double-quoted form so queries arebuilt correctly? Alternatively, could the odbc driver insert double-quotes on-the-fly once it sees uppercase charactersin the names? This would help us immensely!!! Thanks in advance, Tom Agnew __________________________________________________________________ Switch to Netscape Internet Service. As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register Netscape. Just the Net You Need. New! Netscape Toolbar for Internet Explorer Search from anywhere on the Web and block those annoying pop-ups. Download now at http://channels.netscape.com/ns/search/install.jsp
> We have a large database with camel case names for schemas, tables, > columns, etc. I'm able to connect via ODBC with MS Access, but not with > Excel (MSQuery). The names are not quoted and lowercase folding kicks in > - so all tables are 'not found'. If I manually edit the MSQuery sql and > double-quote all names, i can get the queries to work - but each and every > query must be manually edited. Obviously unworkable. Well, you pretty much have to get used to it. Sometimes, MS Query 'helps' you out by parsing and rewriting your query, especially if you have parameter variables in your query. In this case you are limited to the reduced set of functions and operators that MS query understands. I have no clue why MS has not fixed this to allow excel to send SQL directly to the server (one workaround is to manually insert the query into the save file). > Is it possible for the odbc driver to return schema, table and column > names to MSQuery in double-quoted form so queries are built correctly? > Alternatively, could the odbc driver insert double-quotes on-the-fly once > it sees uppercase characters in the names? update pg_class set relname = lower(relname) :-). <-- note, I am not suggesting you actually do this. Merlin
Hi Tom,
I encountered this problem as well, and I agree that it's rather a
nuisance. You've probably thought of this, but if you are just using a
limited set of tables in Excel (like for pivot tables), you might just
try wrapping them into a view with lowercase names - this is what I
did. It might also be worth dropping a line to Microsoft about this
problem, as they have evidently made this work in Access, and I believe
quoting for mixed case is part of SQL92.
All that said, I definitely second your suggestion.
Cheers,
Eric
agnewtj@netscape.net wrote:
>Hello,
>
>We have a large database with camel case names for schemas, tables, columns, etc. I'm able to connect via ODBC with
MSAccess, but not with Excel (MSQuery). The names are not quoted and lowercase folding kicks in - so all tables are
'notfound'. If I manually edit the MSQuery sql and double-quote all names, i can get the queries to work - but each
andevery query must be manually edited. Obviously unworkable.
>
>Is it possible for the odbc driver to return schema, table and column names to MSQuery in double-quoted form so
queriesare built correctly? Alternatively, could the odbc driver insert double-quotes on-the-fly once it sees
uppercasecharacters in the names?
>
>This would help us immensely!!!
>
>Thanks in advance,
>Tom Agnew
>
>
>__________________________________________________________________
>Switch to Netscape Internet Service.
>As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register
>
>Netscape. Just the Net You Need.
>
>New! Netscape Toolbar for Internet Explorer
>Search from anywhere on the Web and block those annoying pop-ups.
>Download now at http://channels.netscape.com/ns/search/install.jsp
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
>
>
>
>
> did. It might also be worth dropping a line to Microsoft about this > problem, as they have evidently made this work in Access, and I believe > quoting for mixed case is part of SQL92. MS Query basically hasn't changed at all since 1997. Their preferred way to do it is to embed vb code into the spreadsheet. Merlin
Does somebody know, wether the following code is correct:
odbcapi.c:
RETCODE SQL_API
SQLColumns(HSTMT StatementHandle, ...)
{
...
if (SC_is_lower_case(stmt, conn)) /* case-insensitive identifier */
ifallupper = FALSE;
In above there is one hidden problem:
SC_is_lower_case() is implemented as follows:
statement.h:#define SC_is_lower_case(a, b) (a->options.metadata_id,
b->connInfo.lower_case_identifier)
GCC compiler complaints (-Wall enabled) about the definition:
odbcapi.c:140: warning: left-hand operand of comma expression has no effect
So this means, that a->options.metadata_id is not used at all. Maybe the
comma
should be replaced with && or || ?
Marko Ristola
> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Marko Ristola
> Sent: 01 August 2005 17:28
> To: Merlin Moncure
> Cc: Eric E; pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] Lowercase folding - simple patch?
>
>
> Does somebody know, wether the following code is correct:
>
> odbcapi.c:
>
>
> RETCODE SQL_API
> SQLColumns(HSTMT StatementHandle, ...)
> {
> ...
>
> if (SC_is_lower_case(stmt, conn)) /* case-insensitive
> identifier */
> ifallupper = FALSE;
>
> In above there is one hidden problem:
> SC_is_lower_case() is implemented as follows:
>
> statement.h:#define SC_is_lower_case(a, b)
> (a->options.metadata_id,
> b->connInfo.lower_case_identifier)
>
>
> GCC compiler complaints (-Wall enabled) about the definition:
>
> odbcapi.c:140: warning: left-hand operand of comma expression
> has no effect
>
> So this means, that a->options.metadata_id is not used at
> all. Maybe the
> comma
> should be replaced with && or || ?
I *think* && is appropriate, however I'm too busy too look into this in
depth right now. Any chance you can confirm this?
(a->options.metadata_id is attribute SQL_ATTR_METADATA_ID btw).
Regards, Dave.
According to Microsoft: Attribute SQL_ATTR_METADATA_ID: SQL_TRUE means, that table names and table columns are case insensitive. SQL_FALSE means, that table names and table columns are case sensitive. So, with this attribute, Microsoft Excel might give a hint wether the table names are supposed to be handled as case sensitive (SQL_FALSE) or case insensitive (SQL_TRUE). It seems, that the lower case setting can be forced via ODBC config via "lower_case_identifier" variable. Alternatively the program (Excel) can activate it when needed via SQL_ATTR_METADATA_ID attribute. One Untested Solution into the Problem: SC_is_lower_case(): (metadata_id || lower_case_identifier) This way the case insensitivity can be either activated/deactivated with ODBC API function calls, or forced via Windows ODBC Driver setting regardless what Excel wants. The bug is now, that only the ODBC Driver setting is used under the CVS HEAD psqlodbc. I don't know, wether the newest stable psqlodbc works with the ODBC Driver setting either, because that feature has been fixed with the looong sprintf() with about 70 arguments. Marko Ristola Dave Page wrote: > > > > >> >> > >I *think* && is appropriate, however I'm too busy too look into this in >depth right now. Any chance you can confirm this? >(a->options.metadata_id is attribute SQL_ATTR_METADATA_ID btw). > >Regards, Dave. > >---------------------------(end of broadcast)--------------------------- >TIP 6: explain analyze is your friend > >