I am converting ASP web apps backend from MySQL to PostGreSQL.
I want the SQL to be as generic as possible.
The tables (relations) and columns (fields) are mixed case
as seen from the psql command line on my PostGreSQL server (RH Linux)
and when viewed in Access as link tables.
When I use ADO for a web site to execute a simple query like
"SELECT EmployeeID FROM Employees"
the query fails with Relation "employees" does not exist.
I find that using double-quote forced lower case in the sql statement
works e.g.
"SELECT ""employeeid"" FROM ""employees"" "
The backend is mixed case. The client string was mixed cased.
I could take the client string and execute it in psql on the backend
server.
The ODBC driver is seeing lower case and I have to double-quote the
lower case in the string to make it work. Lower-casing and
double-quoting all tables names and field name is the kind of backend
specific sql changing I wanted to avoid. Is there a setting on the ODBC
Driver for Windows that will recognize the mixed case properly?
***************************************************************
This email and all files transmitted with it are
confidential and intended solely for the use of the
individual or entity to whom they are addressed. If you
are receiving this email in error please notify the
system manager.
This footnote also confirms that this message
has been swept for the presence of computer viruses.
***************************************************************