Skip Menu |
 

This queue is for tickets about the DBD-ODBC CPAN distribution.

Report information
The Basics
Id: 20398
Status: rejected
Priority: 0/
Queue: DBD-ODBC

People
Owner: Nobody in particular
Requestors: ken.wilson [...] dsl.pipex.com
Cc:
AdminCc:

Bug Information
Severity: Normal
Broken in: (no value)
Fixed in: (no value)



Subject: Cannot INSERT with a column name of 'value'.
Download (untitled) / with headers
text/plain 1.5k
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
On Mon Jul 10 13:04:39 2006, guest wrote: Show quoted text
> 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;
Try $sql = q{insert into table ("value") values (?)}; $sth->execute(1.25); You need to quote reserved words which "value" is. You are also better using parameterized inserts. I am rejecting this as a bug - sorry for the length of time it took to reply.


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.