Comments interspersed.
On Fri, 3 Jan 2003 17:48:34 +1100 Ron Savage <[EMAIL PROTECTED]> wrote:
> On Thu, 2 Jan 2003 21:13:27 -0800 (PST), Michael A Chase wrote:
> >On Fri, 3 Jan 2003 15:36:11 +1100 Ron Savage <[EMAIL PROTECTED]>
> >wrote:
> >
> >>In this, admittedly unusual, situation the above occurs:
> >>OS: Win2K
> >>Perl: V 5.8.0
> >>DBI: V 1.32
> >>ODBC: 1.01
> >>DSN: Points to a Microsoft Access database
> >>Table name: Nathan Bailey (note the single space)
> >>
> >>If I do:
> >>select * from `Nathan Bailey`
> >>it works. Yes, they are backticks.
> >>If I do
> >>select col_a, col_b from `Nathan Bailey`
> >>I get:
> >>Describe failed during DBI::st=HASH(0x1c9f0b8)->FETCH(ParamValues)
> >
> >I presume this is the error message in $DBI::errstr (aka $sth-
> >>errstr).
>
> Possibly. See code below.
>
> >Adding $dbh->trace(4); before you prepare the statement may give you
> >more details of what is happening. The contents of
>
> See output below.
>
> $sth->{Statement}
> >would also be interesting.
>
> Wouldn't we all like to see this. If only ShowErrorStatement => 1
> added the SQL to the error message in this context.
Fortunately it is part of the trace() output.
> >It would help us understand your problem if you post a snippet of the
> >code that demonstrates the problem.
>
> See output below.
>
>>>This problem does not arise with other 'funny' table names, eg:
>>>`Display_ISDT_210_&_Digital` (note the &).
>
> -----><8-----
> #!/usr/bin/perl
>
> use strict;
> use warnings;
>
> use DBI;
> use Error qw/:try/;
>
> # -----------------------------------------------
>
> try
> {
> my($dbh) = DBI -> connect
> (
> 'dbi:ODBC:mnt', '', '',
> {
> AutoCommit => 1,
> HandleError => sub {Error::Simple ->
>record($_[0]); 0},
> PrintError => 0,
> RaiseError => 1,
> ShowErrorStatement => 1,
> }
> );
> my($sql) = 'select dpt_cde, cmt from `Nathan Bailey`';
> #$sql = 'select * from `Nathan Bailey`';
I haven't seen backticks used as identifier quotes before.
They may be confusing either ODBC or Access.
> $dbh -> trace(4);
> my($sth) = $dbh -> prepare($sql);
> print "SQL: $$sth{'Statement'}. \n";
> $sth -> execute();
> my($count) = 0;
>
> my($data, %h);
> while ($data = $sth -> fetch() )
> {
> $h{$$data[0]} = $$data[1] if (defined $$data[0]);
> $count++;
>
> next if ($count > 10);
>
> print "$_ => $h{$_}. \n";
> }
> }
> catch Error::Simple with
> {
> my($error) = shift -> text();
The parenthesis are not required by my. They provide a list
context which may result in a different result from some
subroutines than what you expect.
> chomp($error);
>
> print $error;
> };
> -----><8-----
> DBI::db=HASH(0x1beb048) trace level set to 4 in DBI 1.32-ithread
> -> prepare for DBD::ODBC::db (DBI::db=HASH(0x1bfe3bc)~0x1beb048
> 'select dpt_cde, cmt from `Nathan Bailey`') thr#15d43cc
>
> dbih_setup_handle(DBI::st=HASH(0x1bfe488)=>DBI::st=HASH(0x1bc3d0c),
> DBD::ODBC::st, 1bfe494, Null!)
> dbih_make_com(DBI::db=HASH(0x1beb048), DBD::ODBC::st, 208)
> thr#15d43cc
> SQLPrepare returned 0
>
> dbd_st_prepare'd sql f30808368, ExecDirect=0
> select dpt_cde, cmt from `Nathan Bailey`
> <- prepare= ( DBI::st=HASH(0x1bfe488) ) [1 items] at x.pl line 27
> via c:/Perl/site/lib/Error.pm line 379
> <- FETCH= 'select dpt_cde, cmt from `Nathan Bailey`' ('Statement'
> from cache) at x.pl line 29 via c:/Perl/site/lib/Error.pm line 379
> -> execute for DBD::ODBC::st (DBI::st=HASH(0x1bfe488)~0x1bc3d0c)
> thr#15d43cc
> dbd_st_execute (outparams = 0)...
> dbd_st_execute (for hstmt 30808368 before)...
> dbd_error: err_rc=-1 rc=0 s/d/e: 30808368/30806776/30806608
> dbd_error: SQL-HY000 (native -3007): [Microsoft][ODBC Microsoft
> Access Driver] The specified field 'dpt_cde' could refer to more than
> one table listed in the FROM clause of your SQL statement.
> (SQL-HY000)
It looks like Access via ODBC thinks you are selecting from more than
one table. Try quoting the table name with brackets ([]) or normal
quotes (") and see if that avoids this error.
Something like this (untested) should do it for you:
my $quoted_table = $dbh -> quote_identifier( "Nathan Bailey" );
my $sql = 'select dpt_cde, cmt from $quoted_table';
> SQL: select dpt_cde, cmt from `Nathan Bailey`.
> Describe failed during DBI::st=HASH(0x1bc3d0c)->FETCH(ParamValues)
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
