| Subject: | Cannot INSERT with a column name of 'value'. |
Running ActiveState perl V5.8.6.811 on Win XP with SP2.
Using DBI V1.5 and DBD-ODBC V1.13.
I am trying to interface to a Microsoft Access database via DBI ODBC and
after tearing my hair out (not much of it left anyway!) I have found the
cause of my problem and it may be a bug in DBD-ODBC (or DBI) or it may
be a user error as I am fairly new to SQL and perl.
I cannot INSERT a new record into a table which has a column name of
'Value'. Simple testcase follows:
use strict;
use DBI;
my $dbh = DBI->connect('dbi:ODBC:test db')
or die "cannot open database\n";
my $sql = "INSERT INTO table (Value) VALUES (1.25)";
# Value is a double in the database
my $sth = $dbh->prepare($sql) or die $dbh->errstr;
$sth->execute() or die $dbh->errstr;
...
This fails with Access reporting a syntax error on the INSERT stmt with
error code of (SQL-42000). If I change the column name it works fine.
Unfortunately in the real application the column name cannot be changed
as it is used by a proprietary application which I have no control over.
Is there any way of quoting the column name to bypass this problem?
Simply putting the column name in single quotes does not work as the DBI
puts another set of quotes around it and reports an unknown column name.
Is this a known problem or maybe an SQL "feature" that I am not aware of?
Will appreciate any help you can give me on this and apologize if this
is the wrong forum for such a request but I don't know if this is a
problem with DBD-ODBC or not.
Regards, Ken