[ psqlodbc-Bugs-1000435 ] Special characters in table names

Bugs item #1000435, was opened at 2005-11-21 06:30
You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1000435&group_id=1000125

Category: None
Group: None
>Status: Deleted
Resolution: None
Priority: 3
Submitted By: Chris Dunlop (chrisrd)
Assigned to: Nobody (None)
Summary: Special characters in table names

Initial Comment:
I'm not sure if this is actually a MSSQL 2K or a psqlODBC problem...

Exporting a table named "SEARCHMANAGER#CNLKP" (an auto-generated table name) from MSSQL 2K to PG, using psqlODBC v08.01.01.01, the create table succeeds with a quoted table name, but the data copy fails because the table name isn't quoted to protect the special characters.

Using "log_statement = true" in postgresql.conf, the output is:

2005-11-21 17:17:10 [16940] LOG: statement: CREATE TABLE "SEARCHMANAGER#CNLKP" (
"RECORD_ID" varchar (32) NOT NULL,
-- more fields deleted
)
2005-11-21 17:17:11 [16940] LOG: statement: SELECT 1
2005-11-21 17:17:11 [16940] LOG: statement: select * from SEARCHMANAGER#CNLKP
2005-11-21 17:17:11 [16940] ERROR: syntax error at or near "#" at character 28

----------------------------------------------------------------------

>Comment By: Dave Page (dpage)
Date: 2005-11-25 08:57

Message:
No, there is no easy way to do this I'm afraid. You would need to do some serious coding to write a suitable parser to rewrite query strings.

----------------------------------------------------------------------

Comment By: Chris Dunlop (chrisrd)
Date: 2005-11-24 22:34

Message:
Yes, it's definately the # that's causing the problem, there
were a lot of other tables without any # in their name that
were exported without this problem.

And yes, I can rename the problematical tables for the
purposes of the export so it's not a show stopper problem.

However these table names are used by an application outside
my control so I don't have the opportunity to fix the
problem once and for all, and I'll be needing to export the
data periodically so it will be an ongoing issue.

If psqlodbc is telling DTS to quote the identifiers properly
then it sounds like it's definately a DTS problem. And I
guess we're not going to be able to fix DTS!

How much parsing of the statement is actually done - to
address my immediate problem, would it be possible to
double-quote any unquoted tables name[s] in a select statement ?

----------------------------------------------------------------------

Comment By: Dave Page (dpage)
Date: 2005-11-24 09:24

Message:
psqlODBC essentially does pass queries unmodified to the backend. It only makes rudimentary attempts to parse queries, and even then, only in non-default configurations.

The driver does tell apps like DTS when to quote identifiers, however ODBC doesn't allow a great deal of flexibility in how it can describe that - I suspect in this case the # is throwing things. Does it work for tablenames without it? Can you rename the table?

----------------------------------------------------------------------

Comment By: Chris Dunlop (chrisrd)
Date: 2005-11-23 22:03

Message:
Yes, DTS.

So the quoting is supposed to be done in the app rather than
psqlODBC ? If so is there any way to make psqlODBC add the
quoting ?

Sorry, I don't know how psqlODBC works internally, or even
how ODBC works - I can imagine it transparently passes SQL
statements from the app through to the backend with no
interpretation, in which case it would be tricky for it to
add appropriate quoting. I can also imagine it parses each
statement to do transformations before passing the statement
to the backend, in which case adding quoting might not be so
difficult.

----------------------------------------------------------------------

Comment By: Dave Page (dpage)
Date: 2005-11-23 16:55

Message:
What app are you using to do the data transfer? (DTS I assume). Seems it is not quoting the table name as it should.

----------------------------------------------------------------------

You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1000435&group_id=1000125