Обсуждение: ODBC driver and non case-insensitivity?
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
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
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