Skip Menu |
 

This queue is for tickets about the SQL-Statement CPAN distribution.

Report information
The Basics
Id: 55973
Status: rejected
Priority: 0/
Queue: SQL-Statement

People
Owner: Nobody in particular
Requestors: ledda [...] ukr.net
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in: 1.25
Fixed in: (no value)



Subject: Case sensitive column names
Download (untitled) / with headers
text/plain 1.2k
For SQL queries like: "select * from mytable" column names in result are all lowercase, no matter that they were specified in different case. To fix this issue I've changed getColumnObject() in SQL/Statement.pm. When we create $expcol instead of $colName for display name I'm using $self->{ORG_NAME}->{$colName}. Here is complete part of the code: my $expcol = [ $colName, # column name $table, # table name SQL::Statement::ColumnValue->new( $self, $table . '.' . $colName ), # term $self->{ORG_NAME}->{$colName}, # display name $colName, $newcol, ]; More details about this issue: I'm loading data from CSV DB with DBI & DBD::CSV module. Code for loading data is written in such way that it specifies case sensitive column names. E.g. $dbh->{'csv_tables'}->{$tab} = { 'eol' => "\n", 'col_names' => [ qw( MyCol1 MyCol2 )], 'file' => $fname }; ... # SQL command is: select * from mytable ... $sth->execute(@ex); $dbRec = $sth->fetchrow_hashref() In current version $dbRec keys are mycol1 mycol2, with SQL::Satement version 1.20 and below they are MyCol1 MyCol2. If SQL command is written like: select MyCol1, MyCol2 from mytable then $dbRec keys are MyCol1 MyCol2 in both versions: 1.20 and the latest.
RT-Send-CC: h.m.brand [...] xs4all.nl
Download (untitled) / with headers
text/plain 584b
This isn't a bug - this works as intended. Other DBMS will change the default case for column namen, too (Oracle to upper cased names, PostgreSQL to lower cased names). To fix some internal issues with keeping mixed cases in table/column names, all cases were converted to lower cased names since 1.22 (See changelog). SQL itself doesn't say anything about keeping cases in indentifiers. I intend to reject this bug, but I will give you a chance to argue why it's an important issue and why it must be fixed immediately (important and critical suggest immediately fix is required).
From: ledda [...] ukr.net
On Mon Mar 29 05:54:50 2010, REHSACK wrote: Show quoted text
> This isn't a bug - this works as intended. Other DBMS will change
the Show quoted text
> default case for column namen, too (Oracle to upper cased names, > PostgreSQL to lower cased names). >
Note, that when you connect to mentioned DBMS you don't specify table fields explicitly as I do on my connect. However when you do, e.g. using quoted Oracle field names, you'll receive requested case in field names. Thats why it seems reasonable to expect that program will respect specified field names. Show quoted text
> To fix some internal issues with keeping mixed cases in table/column > names, all cases were converted to lower cased names since 1.22 (See > changelog). >
Thats fine for internal stuff but not for outside interface. When user explicitly specify what he wants to receive, program should follow his orders and shouldn't make programmers life harder. E.g. request like this allready follows this rule: select MyCol1, MyCol2 from mytable In resulting hash we have keys: MyCol1 MyCol2.
RT-Send-CC: h.m.brand [...] xs4all.nl
Download (untitled) / with headers
text/plain 1.8k
Show quoted text
> > This isn't a bug - this works as intended. Other DBMS will change
the Show quoted text
> > default case for column namen, too (Oracle to upper cased names, > > PostgreSQL to lower cased names).
> > Note, that when you connect to mentioned DBMS you don't specify > tablefields explicitly as I do on my connect.
You seem to miss some details in processing. Even if you do in an Oracle DBMS: : CREATE TABLE foo (Id INT, baR VARCHAR(64)); : INSERT INTO foo (1, 'Car'); : INSERT INTO bar (2, 'Truck'); : SELECT * from bar; in one SQL script or SBI session, the resulting column names are upper cased. You cannot expect - even if most DBMS supporting multiple commands in one session, that the share information between several statements. Show quoted text
> However when you do, e.g. using quoted Oracle field names, you'll > receive requested case in field names.
When you use quoted field names in SQL::Statement / DBD::CSV, you'll receive requested case for those field names in that statement, too. Show quoted text
> Thats why it seems reasonable to expect that program will respect > specified field names.
So please be so kind and quote them! Show quoted text
> > To fix some internal issues with keeping mixed cases in table/column > > names, all cases were converted to lower cased names since 1.22 (See > > changelog).
> > Thats fine for internal stuff but not for outside interface. When > user explicitly specify what he wants to receive, program should > follow his orders and shouldn't make programmers life harder.
You didn't specify exactly what you need. Please see t/17quoting.t for some examples how to use quoting. Show quoted text
> E.g. request like this allready follows this rule: > select MyCol1, MyCol2 from mytable > In resulting hash we have keys: MyCol1 MyCol2.
Please quote in those cases: select "MyCol1", "MyCol2" from "mytable" This will work as expected. I would wonder if Oracle or PostgreSQL will return the cases as written in your example.
From: ledda [...] ukr.net
Download (untitled) / with headers
text/plain 2.9k
On Mon Mar 29 07:12:15 2010, REHSACK wrote: Show quoted text
> > > This isn't a bug - this works as intended. Other DBMS will
change Show quoted text
> the
> > > default case for column namen, too (Oracle to upper cased names, > > > PostgreSQL to lower cased names).
> > > > Note, that when you connect to mentioned DBMS you don't specify > > tablefields explicitly as I do on my connect.
> > You seem to miss some details in processing. Even if you do in an
Oracle Show quoted text
> DBMS: > : CREATE TABLE foo (Id INT, baR VARCHAR(64)); > : INSERT INTO foo (1, 'Car'); > : INSERT INTO bar (2, 'Truck'); > : SELECT * from bar; > > in one SQL script or SBI session, the resulting column names are
upper Show quoted text
> cased. You cannot expect - even if most DBMS supporting multiple > commands in one session, that the share information between several > statements. >
I'm not trying to share info between multiple SQL statements. $dbh->{'csv_tables'}->{$tab} = { 'eol' => "\n", 'col_names' => [ qw( MyCol1 MyCol2 )], 'file' => $fname }; With this command I'm describing table structure in DBI object. As I know there only two ways to do it: like I did with 'col_names' parameter or as a first row in CSV file. In the cases when "*" expansion is required DB driver should get this info from current table description. Remember that user may run "CREATE" and then change table with ALTER table command. Show quoted text
> > However when you do, e.g. using quoted Oracle field names, you'll > > receive requested case in field names.
> > When you use quoted field names in SQL::Statement / DBD::CSV, you'll > receive requested case for those field names in that statement, too. >
> > Thats why it seems reasonable to expect that program will respect > > specified field names.
> > So please be so kind and quote them! >
I'll be fine for me to quote fields on the stage when I'm describing table structure: 'col_names' => [ ""Col1"", ""Col2"",... ] But as I understand, it won't work on "*" expansion too. Show quoted text
> > > To fix some internal issues with keeping mixed cases in
table/column Show quoted text
> > > names, all cases were converted to lower cased names since 1.22
(See Show quoted text
> > > changelog).
> > > > Thats fine for internal stuff but not for outside interface. When > > user explicitly specify what he wants to receive, program should > > follow his orders and shouldn't make programmers life harder.
> > You didn't specify exactly what you need. Please see t/17quoting.t
for Show quoted text
> some examples how to use quoting. >
> > E.g. request like this allready follows this rule: > > select MyCol1, MyCol2 from mytable > > In resulting hash we have keys: MyCol1 MyCol2.
> > Please quote in those cases: > select "MyCol1", "MyCol2" from "mytable" > This will work as expected. >
This command already working as I expect, without any quoting required. If I do: ... select MyCol1, MyCol2 from mytable ... $dbRec = $sth->fetchrow_hashref() .. In %$dbRec I'll have keys: MyCol1, MyCol2. In current version only "*" expansion works according "more suitable for machine" way.
From: ledda [...] ukr.net
Here is a reply of H.Merijn Brand on this bug report: I don't think that is a bug. Oracle will also change the casing unless the field name is quoted. I *do* however see a problem with quoted fields: $ cat xx.pl #!/pro/bin/perl use strict; use warnings; use Data::Peek; use DBI; my $dbh = DBI->connect ("dbi:CSV:", undef, undef, { f_ext => ".csv/r", f_dir => ".", f_schema => "undef", RaiseError => 1, PrintError => 1, }); $dbh->do (qq; create table foo ( c_Foo integer, S_foo char (20), F_FoO text, "x_Foo" integer); ); my $sth = $dbh->prepare ("select * from foo"); $sth->execute; DDumper [ $_, $sth->{$_} ] for "NAME", "NAME_lc", "NAME_uc"; $dbh->do ("drop table foo"); $ perl xx.pl [ 'NAME', [ 'c_foo', 's_foo', 'f_foo', '_x_foo_' ] ] [ 'NAME_lc', [ 'c_foo', 's_foo', 'f_foo', '_x_foo_' ] ] [ 'NAME_uc', [ 'C_FOO', 'S_FOO', 'F_FOO', '_X_FOO_' ] ]


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.