Thread: ADO Case Sensitivity in Win2000/XP
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. ***************************************************************
Greg Campbell wrote: [snip] > 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? IIRC, the setting isn't in the ODBC driver settings, but rather in the setup of PG. Strangely enough, with my application I converted a bunch of Access tables to PG, and noticed that on the backend, all of them seemed to be lowercased (from the POV of psql). On the frontend (a VB6 app), the SQL selects and such were mixed case (as that is how the original Access tables were defined), but the driver handles it all fine (I am using a DSN-less connection, so no special flags are being sent in the connection string, other than setting ksqo to false). One thing to learn from this for future applications is to instead of explicitly performing the SQL selects everywhere, have an include with a function or something you can call, passing the SQL statement, and returning something (the recordset or something, maybe). Everytime you do a select, call this function with your SQL statement. Then, if in the future you need to do something that will change every statement, you can re-parse and change the statement in that one function, instead of contemplating changing multiple areas throughout your code. If you find that despite what you do, you still need to make the change you outlined, perhaps now is the time to do this centralized function. Andrew Ayers Phoenix, Arizona > > > *************************************************************** > 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. > *************************************************************** > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- CONFIDENTIALITY NOTICE -- This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the intendedaddressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy,disclose or distribute to anyone the message or any information contained in the message. If you have received thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.
Thanks for the feedback, but perhaps I missed something. What setting in PG addresses this? and by the way, Yeah, I learned to bunch all the SQL interfacing into locales I refer to as DB lib files. I usually do about one file per table that has the database interfacing code for most SELECTs, INSERTs, UPDATEs, and DELETEs. I call these functions from all the user interface code pages. Andrew Ayers wrote: > > Greg Campbell wrote: > [snip] > > 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? > > IIRC, the setting isn't in the ODBC driver settings, but rather in the > setup of PG. > > Strangely enough, with my application I converted a bunch of Access > tables to PG, and noticed that on the backend, all of them seemed to be > lowercased (from the POV of psql). On the frontend (a VB6 app), the SQL > selects and such were mixed case (as that is how the original Access > tables were defined), but the driver handles it all fine (I am using a > DSN-less connection, so no special flags are being sent in the > connection string, other than setting ksqo to false). > > One thing to learn from this for future applications is to instead of > explicitly performing the SQL selects everywhere, have an include with a > function or something you can call, passing the SQL statement, and > returning something (the recordset or something, maybe). Everytime you > do a select, call this function with your SQL statement. Then, if in the > future you need to do something that will change every statement, you > can re-parse and change the statement in that one function, instead of > contemplating changing multiple areas throughout your code. If you find > that despite what you do, you still need to make the change you > outlined, perhaps now is the time to do this centralized function. > > Andrew Ayers > Phoenix, Arizona > > > > > > *************************************************************** > > 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. > > *************************************************************** > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > > > -- CONFIDENTIALITY NOTICE -- > > This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the intendedaddressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy,disclose or distribute to anyone the message or any information contained in the message. If you have received thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you. *************************************************************** 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. ***************************************************************
On Wed, 2003-09-10 at 17:54, Andrew Ayers wrote: > Greg Campbell wrote: > [snip] > > 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? > > IIRC, the setting isn't in the ODBC driver settings, but rather in the > setup of PG. > > Strangely enough, with my application I converted a bunch of Access > tables to PG, and noticed that on the backend, all of them seemed to be > lowercased (from the POV of psql). On the frontend (a VB6 app), the SQL > selects and such were mixed case (as that is how the original Access > tables were defined), but the driver handles it all fine (I am using a > DSN-less connection, so no special flags are being sent in the > connection string, other than setting ksqo to false). The reason is that, in accordance (almost) with the SQL standard, all identifiers are case-folded unless they are protected by double quotes. (The standard actually says to fold to upper case.) If you consistently leave out the quotes, the mixed case names will be translated automatically to lower case. You only get problems if one part of your application quotes them and another does not. As a matter of design, it is better not to use letter case to identify words in SQL identifiers. It's a Microsoftism, and Microsoftisms only lead to trouble. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "Draw near to God and he will draw near to you. Cleanse your hands, you sinners; and purify your hearts, you double minded." James 4:8