Click here to get back home

Perl DBI Module: SQL query where there is space in field name
 Home | NewsGroups | Search | About
Login Password
Register Now!
 comp.lang.perl.misc    Post an article   get this group's latest topics as an RSS feed add this group's latest topics to your My MSN content add this group's latest topics to your My Yahoo content
Subject Author Date
Perl DBI Module: SQL query where there is space in field name ambarish.mitra 05-08-2008
Get Chitika Premium
Posted by ambarish.mitra on May 8, 2008, 10:00 am
Please log in for more thread options
 Hi all,

Using the DBI module, I have connected to a CSV file, and am trying to
execute SQL queries on the CSV file. I am stuck when there is a space
in the field name and I cannot proceed.

The CSV file col heading:
"Attribute","Display Name","Semantic Type","Display Type". (ie, space
in the heading)

I am trying to "prepare" only those lines for which 'Display Type' is
given.

The error line is given:

my $sth = $dbh->prepare("select * from report where [Display Type]
=MultiLineText");


SQL ERROR: Bad table or column name '[Display Type]' has chars not
alphanumeric or underscore!
SQL ERROR: Couldn't find predicate!


I have tried some googling and also tried multiple combination of
brackets/qoutes/escapes with a hope that one of them will work, but
without luck.

Question: What is the way to fire this command in PERL where the field
name (and value as well) can have spaces?

Regards,
Ambarish.

Posted by Keith Keller on May 8, 2008, 12:47 pm
Please log in for more thread options
>
> Using the DBI module, I have connected to a CSV file, and am trying to
> execute SQL queries on the CSV file. I am stuck when there is a space
> in the field name and I cannot proceed.

The obvious workaround would be to remove the spaces from the field name.

> Question: What is the way to fire this command in PERL where the field
> name (and value as well) can have spaces?

Spaces are fine in the values, it's just the column names that are
problematic. perldoc DBD::CSV talks about whitespace being problematic
in the table name, and I would guess that it's similar for column names.

--keith

--
kkeller-usenet@wombat.san-francisco.ca.us
(try just my userid to email me)
AOLSFAQ=http://www.therockgarden.ca/aolsfaq.txt
see X- headers for PGP signature information


Posted by Ben Morrow on May 8, 2008, 12:48 pm
Please log in for more thread options

Quoth ambarish.mitra@gmail.com:
> Hi all,
>
> Using the DBI module, I have connected to a CSV file, and am trying to
> execute SQL queries on the CSV file. I am stuck when there is a space
> in the field name and I cannot proceed.
>
> The CSV file col heading:
> "Attribute","Display Name","Semantic Type","Display Type". (ie, space
> in the heading)
>
> I am trying to "prepare" only those lines for which 'Display Type' is
> given.
>
> The error line is given:
>
> my $sth = $dbh->prepare("select * from report where [Display Type]
> =MultiLineText");
>
>
> SQL ERROR: Bad table or column name '[Display Type]' has chars not
> alphanumeric or underscore!
> SQL ERROR: Couldn't find predicate!

Assuming you are using DBD::CSV, the docs point you to SQL::Statement,
which says you should quote identifiers with double quotes. Quoting with
square brackets is (IIRC) an MS Access-specific syntax. So try

my $sth = $dbh->prepare(
'select * from report where "Display Type"=MultiLineText'
);

Ben

--
For the last month, a large number of PSNs in the Arpa[Inter-]net have been
reporting symptoms of congestion ... These reports have been accompanied by an
increasing number of user complaints ... As of June,... the Arpanet contained
47 nodes and 63 links. [ftp://rtfm.mit.edu/pub/arpaprob.txt] * ben@morrow.me.uk

Posted by RedGrittyBrick on May 8, 2008, 12:59 pm
Please log in for more thread options
ambarish.mitra@gmail.com wrote:

> [Apparently using DBD::CSV]
>
> my $sth = $dbh->prepare("select * from report where [Display Type]
> =MultiLineText");
> ...
> SQL ERROR: Bad table or column name '[Display Type]' has chars not
> alphanumeric or underscore!
> ...
> Question: What is the way to fire this command in PERL where the field
> name (and value as well) can have spaces?
>

See col_names and skip_first_row in
http://search.cpan.org/~jzucker/DBD-CSV-0.22/lib/DBD/CSV.pm

I think you can use these to assign legal column names without requiring
changes to the CSV.

--
RGB

Posted by ambarish.mitra on May 9, 2008, 3:26 am
Please log in for more thread options
wrote:
> ambarish.mi...@gmail.com wrote:
> > [Apparently using DBD::CSV]
>
> > my $sth = $dbh->prepare("select * from report where [Display Type]
> > =MultiLineText");
> > ...
> > SQL ERROR: Bad table or column name '[Display Type]' has chars not
> > alphanumeric or underscore!
> > ...
> > Question: What is the way to fire this command in PERL where the field
> > name (and value as well) can have spaces?
>
> See col_names and skip_first_row
inhttp://search.cpan.org/~jzucker/DBD-CSV-0.22/lib/DBD/CSV.pm
>
> I think you can use these to assign legal column names without requiring
> changes to the CSV.
>
> --
> RGB


Absolutely. Bang on target. This works. We have to set up our col
names without spaces using col_names like:
$dbh->-> = {'file' => 'report.csv',
'col_names' =>
["Attr","Name","S","Type"]
};

After this, trying to do "prepare" (with where Type=) works perfectly.

Thanks to Ben and others who spent some time on this question.

There are some people quarreling over non-issues in this thread.
Requesting them to take that fight offline so that the thread contents
remain focussed and helpful for generations to come.

Similar ThreadsPosted
Re: Perl DBI Module: SQL query where there is space in field name June 12, 2008, 4:21 am
query regarding perl module installation in local directory July 28, 2007, 4:08 pm
Simplest way (or module) to tweak query in CGI server-side validation June 12, 2008, 9:10 pm
matching a pattern with a space or no space?? November 9, 2005, 7:45 am
SNMP module to query net-snmp August 31, 2006, 7:08 am
CGI - How to use upload_hook and query the query string ? February 7, 2006, 1:00 pm
how to check whether the field is filled or empty in perl TK November 14, 2007, 9:40 am
How to check a form's field and exit Perl program if it has a value? July 7, 2007, 2:08 pm
Simple (!) HTML/PERL code fpr uploading files through web pages entyr field ?? November 18, 2004, 11:12 pm
Perl Regex Query November 9, 2006, 11:14 am

Our other projects:

Art Dolls, Fairies and Mermaids - Sunnyfaces.net

Roy's Linux, Programming and Search Engines messages

1-Script XML SitemapXML Sitemap