Re: ODBC driver and non case-insensitivity?

On Friday 19 Oct 2001 9:33 pm, Joel Burton wrote:
> On Fri, 19 Oct 2001, Chris Storah wrote:
> > MS SQL is set to quote all identifiers so the queries are sent in the
> > ...
Sorry about that, I wasn't clear about the quoting.
MS SQL was set to quote all identifiers so that I could force the "TEST" type.
This worked for the create table but not for the select queries - I will
check the ODBC dumps to see what it is sending.

> How are you "exporting" a MS SQL table to PG?

Tried a few - Replication via ODBC, DTS to ODBC and about to try Access to
confirm whether SQL is at fault.

> If this is a dump utility in MS SQL, there may be an option in this dumping
> program to not quote identifiers (table names, etc.) to be uppercase.

Not that I can find - MSSQL and Oracle default to uppercase table and field
names which means MSProject (which is the data I am transferring by the way)
creates in uppercase but queries in lowercase...
If I manually change the tables in MS SQL to lowercase the ODBC transfer
works fine.

> However, if the dumping program is dumping with quoted uppercase
> identifers, it's not a bug that PG doesn't find the table: "TEST" is a
> different table than "test". This is not an ODBC problem at all, if I
> understand your problem.

The reason I tried quoted identifiers was due to the tables being created
uppercase via the ODBC. Originally they were unquoted - psql converts
unquoted tables into lowercase names so that "Test","TEST" etc. become
"test". The transfer then works okay as the 'select test' hits the correct
table.

> However, if the dumping program is dumping with quoted uppercase
> identifers, it's not a bug that PG doesn't find the table: "TEST" is a
> different table than "test". This is not an ODBC problem at all, if I
> understand your problem.

Yes, the quoted identifiers worked okay for table creation. Inserts all
failed though due to the table names being lowercase (not sure if this is MS
or ODBC yet).

Possible problems:
1) Confusion on my part.
2) MS SQL creating tables and fields uppercase but using lowercase names for
the inserts.
3) ODBC driver converting quoted table names to lowercase in inserts/selects.

Possible solution:
1) Force all unquoted tables to be lowercase (as psql)
2) Scrap MS Project for an Open Source project (none powerful enough just yet)
3) dump to text file, run it through tr, sed or a perl script to put it into
pgdump format or import it directly.

At the moment the last one is my solution, but it would be nice to run a
table export on MS SQL and have it going into PostgreSQL directly.

Even better would be PostgreSQL support for MS Project via ODBC - stored
procedures may be an issue though!

Chris