Thread

1. ODBC driver and non case-insensitivity?

   Chris Storah <cstorah@emis-support.demon.co.uk> — 2001-10-19T15:42:03Z

   Just searched the archives but no match for this problem that I can see:
   
   Exporting an MS SQL 2000 table to PostgreSQL 7.1.3 fails due to uppercase 
   table names.
   
   MS SQL is set to quote all identifiers so the queries are sent in the format:
   'create table TEST(ABC int)'
   However, when MS SQL sends a query 'select * from test', the ODBC fails with 
   "ERROR: Relation 'test' does not exist" as it is looking for the lowercase 
   version of the table.
   
   Through psql, the uppercase table names are converted into lowercase so 
   PostgreSQL seems to be okay. Is this a problem with the ODBC driver?
   
   If so, are there any patches available?
   
   
   Thanks,
   Chris
   
   

2. Re: ODBC driver and non case-insensitivity?

   Joel Burton <joel@joelburton.com> — 2001-10-19T20:33:25Z

   On Fri, 19 Oct 2001, Chris Storah wrote:
   
   > Just searched the archives but no match for this problem that I can see:
   >
   > Exporting an MS SQL 2000 table to PostgreSQL 7.1.3 fails due to uppercase
   > table names.
   >
   > MS SQL is set to quote all identifiers so the queries are sent in the format:
   > 'create table TEST(ABC int)'
   > However, when MS SQL sends a query 'select * from test', the ODBC fails with
   > "ERROR: Relation 'test' does not exist" as it is looking for the lowercase
   > version of the table.
   >
   > Through psql, the uppercase table names are converted into lowercase so
   > PostgreSQL seems to be okay. Is this a problem with the ODBC driver?
   >
   > If so, are there any patches available?
   
   How are you "exporting" a MS SQL table to PG? 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.
   
   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.
   
   Unquoted identifiers, eg test (no quotes) is sent as uppercase regardless
   of how you write it: test Test teST; if written w/quotes, case is
   preserved.
   
   If the MSSQL dump routine can't ditch the quoted identifiers, you could
   either (a) search/replace/sed/perl/whatever the quotes out of the SQL dump
   before importing to PG, or (b) once in PG, ALTER TABLE "FOO" RENAME TO
   "foo".
   
   (b) is tricky, though, as index names and ref integrity constraints and
   such aren't corrected for this. (a) is a better solution.
   
   HTH,
   -- 
   
   Joel BURTON  |  joel@joelburton.com  |  joelburton.com  |  aim: wjoelburton
   Independent Knowledge Management Consultant
   
   
   

3. Re: ODBC driver and non case-insensitivity?

   Chris Storah <cstorah@emis-support.demon.co.uk> — 2001-10-22T08:52:16Z

   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