Simple Select Query

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

Hello Wise Monks,

I am stuck. I've been searching all over the web for a way to query table names and fields with spaces in the name. I'm using DBI to access a microsoft access database using odbc. I've tried several ways to do it and no luck. Here's some of the code that I have tried so far.

my $sqlstatement="SELECT * FROM \'MPCCA ALL INVENTORY\' WHERE Campus =
+ 'VT'";

my $sqlstatement="SELECT * FROM 'MPCCA ALL INVENTORY' WHERE Campus = '
+VT'";

my $sqlstatement="SELECT * FROM MPCCA ALL INVENTORY WHERE Campus = 'VT
+'";

my $sqlstatement="SELECT * FROM 'MPCCA ALL INVENTORY' WHERE \'Type Equ
+ipment\' = 'Computer'";

my $sqlstatement="SELECT * FROM 'MPCCA ALL INVENTORY' WHERE Type Equip
+ment = 'Computer'";

my $sqlstatement="SELECT * FROM 'MPCCA ALL INVENTORY' WHERE 'Type Equi
+pment' = 'Computer'";
[download]

It's an old database and changing the names of the fields isn't an option. Any help would be very appreciated.

Comment on Simple Select Query Download Code

Replies are listed 'Best First'.
Re: Simple Select Query by Transient (Hermit) on Jul 06, 2005 at 21:51 UTC
try `my $sqlstatement='SELECT * FROM "MPCCA ALL INVENTORY" WHERE Type Equip +ment = '.$dbh->quote('Computer');` [download] (people will say to use bind params... listen to them)	[reply] [d/l]
Re^2: Simple Select Query by bkiahg (Pilgrim) on Jul 06, 2005 at 22:00 UTC
Thank you! `my $sqlstatement='SELECT * FROM "MPCCA ALL INVENTORY" WHERE "Type Equipment" = '.$dbh->quote('Computer');` Worked!	[reply] [d/l]
Re: Simple Select Query by ikegami (Patriarch) on Jul 06, 2005 at 21:59 UTC
Table and field names are delimited using square brackets and/or backticks (depends on DB, I guess), not single or double quotes. Otherwise, it wouldn't know the different between a field name and a string literal in some places. For example, `'Type Equipment'='Computer'` compares two constant strings, whereas `[Type Equipment]='Computer'` compares a field to a constant string. Your queries are: `SELECT * FROM [MPCCA ALL INVENTORY] WHERE Campus='VT' SELECT * FROM [MPCCA ALL INVENTORY] WHERE [Type Equipment]='Computer'` [download]	[reply] [d/l] [select]
Re^2: Simple Select Query by bkiahg (Pilgrim) on Jul 06, 2005 at 22:02 UTC
TMTOWTDI!	[reply]
Re: Simple Select Query by davidrw (Prior) on Jul 06, 2005 at 21:56 UTC
Also look at SQL::Abstract to write the sql for you (i haven't tried personally, but it should alsmost certainly handle quoting the table names). Also look at Class::DBI -- another way to avoid writing sql and also making easy-to-write powerful code. random info -- in other databases you can quote the table names with backticks (no clue if that works w/access)	[reply]