jimb has asked for the wisdom of the Perl Monks concerning the following question:

Using DBD::AnyData on a tab delimited file, I am trying to do a select where the where clause includes a field that has spaces in its name:

select * from dbms where "System Status" = 'active'

I tried using double quotes around the column names, but without success.

Also, I did read about using {col_names => 'a, b,c'}; however, since the file comes from an external source, column placement may vary (yet the column names remain the same).

One solution is to pre-process the file by replacing the spaces with underscores, etc. But I am looking a better solution. Any suggestions are welcomed. Thanks.

Replies are listed 'Best First'.
Re: AnyData and Column Names with Spaces
by linuxer (Curate) on Mar 21, 2009 at 20:36 UTC

    I use SQL not frequently enough, so I'm not really sure about this.

    From what I remember, in SQL string delimiter is the single quote, not the double quote, so I think it should be something like this:

    SELECT * FROM dbms WHERE 'System Status' = 'active';

    If you use placeholders in the statement, you could write it like this:

    # ...
my $sth = $dbh->prepare( "SELECT * FROM dbms WHERE '?' = '?';" );
$sth->execute('System Status', 'active');
# now get the results...

    [download]
    Also see What are placeholders in DBI, and why would I want to use them? for this.

    It would also be helpful to see your piece of code which is currently in use.

[reply]
[d/l]
[select]
Re: AnyData and Column Names with Spaces
by hangon (Deacon) on Mar 22, 2009 at 18:03 UTC

    You might want to try backquotes. Many SQL implementations use them for quoting table & column names. 

    select * from dbms where `System Status` = 'active'

    [download]
[reply]
[d/l]
Re: AnyData and Column Names with Spaces
by jimb (Novice) on Mar 23, 2009 at 21:16 UTC

    Thanks. I tried both of your suggestions, but no joy...

    Here's the code sniplet: 
    my $dbh = DBI->connect( 'dbi:AnyData(RaiseError => 1):' );
$dbh->func( 'sms', 'Tab', $file, 'ad_catalog');

my $query = qq/select * from sms where "System Status" = '?'/;

my $sth = $dbh->prepare( $query );
$sth->execute( 'ACTIVE' );

return $sth->fetchall_hashref( 'System Number' );

    [download]
    Here's the data sniplet: 
    System Number\tSystem Status\tSystem Duration
10001        \tACTIVE       \t600
10002        \tINACTIVE     \t0
10003        \tACTIVE       \t900
...

    [download]

    When I use just select * from rms, naturally, I get all the file's data (of which I am currently filtering later in my process).

    However, it's interesting that with select * from rms, I'm able to use 'System Number' as a parameter in fetchall_hashref to retrieve all the data keyed on it, especially when it contains a space in its name.

[reply]
[d/l]
[select]

      Sorry for late answer. General syntax for quoting identifiers in SQL is the double quote ("), so your statement should look like:

      $sql = q(select * from sms where "System Status" = 'foo');

      If you want use placeholders, ensure that they are not quoted:

      $sql = q(select * from sms where "System Status" = ?);

      Best source for help on DBD::AnyData, DBD::CSV and DBD::DBM would be the dbi-users@perl.org mailing list.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom