Hi!
As far as I understand generic SQL, all identifiers are case insensitive
by default. A simple-minded database could simply force lower or upper
case on all identifiers while processing queries, and it would not
violate SQL. It could switch from upper to lower case or vice versa with
every minor patch and still would not violate SQL.
That's why I would never rely on any database to preserve case in
identifiers.
Yes, there are ways to force a database to preserve case on identifiers
(quoting them everywhere should work most of the times), but as I said:
Don't rely on that, it may be an implementation artefact that might
change at any time.
FetchHashKeyName is the way to go, force either lower (NAME_lc) or upper
(NAME_uc) case on any identifier returned from the database, and compare
accordingly. If you want to keep your camelcased names, wrap them in
lc() or uc() before comparing them with DBI result hash keys. Perl
should be smart enough to optimize ($key eq
uc("myPrettyCamelCasedName")) to ($key eq 'MYPRETTYCAMELCASEDNAME') at
compile time.
As an alternative to using lc/uc, consider tieing the DBI result hash to
Tie::CPHash. It adds some overhead, your keys won't be camelcased, but
at least you can access the values in the hash using camelcased keys.
Alexander
On 28.02.2017 19:37, Jonas B. Nielsen wrote:
Hello All,
I have been using DBI for some years with MySQL, PostgreSQL, SQLite and Oracle.
When using DBI with Oracle I always set FetchHashKeyName to NAME_lc.
Over the weekend I was prototyping a new system and I wanted to use SQLite
since I was somewhat offline. All of the hash keys were lower-case by default,
but the protocol I was trying to adhere to was using camelCase.
So my question is (and is has possibly been responded to before):
- Would it be possible to have FetchHashKeyName preserve case? so if a database
was using camelCase this would be preserved.
Our basis datamodel is encapsulated in views for separate
service/application/protocol implementations, so if I would get the views to
adhere to the protocol, I would not have to translated the hash keys (and
maintain the translation dictionary afterwards.
I checked some FAQ without luck
(https://www.fnal.gov/docs/products/perl/pod/site_perl/DBI/FAQ.html), which
however seem a bit obsolete (last update 1997) and I cannot find any trace of
the FAQ on MetaCPAN.
jonasbn
—
pauseid: JONASBN
email: jona...@cpan.org
twitter: @jonasbn
blog: https://lastmover.wordpress.com/
--
Alexander Foken
mailto:alexan...@foken.de http://www.foken.de/alexander/
