Re: Why does app fail? - Mailing list pgsql-general

I don't know about that. I'm assuming that it is something the ODBC driver is supplying. The supplier simply states they don't support PostgreSQL, so there isn't much I can do in that arena.

 

I accept the back-end converts them. What I'm saying is could I not re-compile the back-end to make it convert to upper case instead? There must be a call somewhere in all that C code that does the deed and change it from a call to "tolower()" (or equivalent) to a call to "toupper()" (or equivalent).

 

I'm not bothered about having to remember the table name case. The application uses all upper-case anyway (hence the problem!) and other methods of access don't use ODBC.

 

Shame about the triggers on the system tables.....

 

Paul

>>> Martijn van Oosterhout <kleptog@svana.org> 25/02/2004 10:25:50 >>>
On Wed, Feb 25, 2004 at 10:08:02AM +0000, Paul Simpson wrote:
> Thank you for the advice, unfortunately, that isn't an option, you
> see I didn't write the application and so cannot control the calls it
> makes. What I need is for PG to do as it's told!

Now hang a moment. This application has code is specifically written to
handle postgresql (no other database has pg_catalog) and yet they can't
tweak it to make the queries case insensetive.

> I can only think of three solutions which I'd appreciate comments / advice on....
> 
> 1) Hack PG Source and re-compile to make upper-case the default table name.

The backend converts them to lower case anyway.

> 2) Hack the ODBC driver source to get it to always add quotes to table names.

Bad idea, then you have to remember the case. Best to always leave the
quotes off.

> 3) Given that the statement seems to be looking for the table name in
> one of the system tables, put a trigger on that table to
> automatically convert any new additions to upper case.

Triggers on system tables don't work.

> As an aside, it would seem that from searching the web, this is a
> common problem. The "quote the names" solution just doesn't work for
> a lot of people. I would suggest that if PG is to be seen as a
> realistic migration path from other DB's, this really needs to be
> fixed by the developers.

If you write code specific for postgres to check for tables, add a
lower() to the query so it matches. Like so:

select relname, nspname, relkind from pg_catalog.pg_class,
pg_catalog.pg_namespace where relkind in ('r', 'v')and nspname like
'public' and relname like lower('TTATTASSOC') and relname !~ '^pg_|^dd_' and
pg_namespace.oid = relnamespace order by nspname, relname

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> If the Catholic church can survive the printing press, science fiction
> will certainly weather the advent of bookwarez.
>    http://craphound.com/ebooksneitherenorbooks.txt - Cory Doctorow