bind_param oddity?

Hi guys,

I'm sure I'm missing something *very* obvious but this one has me
scratching my head. Using DBI 1.52, DBD::mysql 3.0007, Perl 5.8.8,
MySQL 5.0.18.

The following code executes fine:

my $table = 'CCHISTORY';
my $status = 'D';
my $limit = 86400;

my $SQL = < select count(*) from $table
where TRXSTATUS = '$status'
and (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(TRXTIMESTAMP)) < $limit
EOSQL

my $sth = $dbh->prepare($SQL) || die $DBI::errstr, "\n";
$sth->execute() || die $sth->errstr(), "\n";

Now, because I want use use placeholders instead, I attempt the following:

my $SQL = < select count(*) from ?
where TRXSTATUS = ?
and (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(TRXTIMESTAMP)) < ?
EOSQL

my $sth = $dbh->prepare($SQL) || die $DBI::errstr, "\n";

my $table = 'CCHISTORY';
my $status = 'D';
my $limit = 86400;

my @bindParams = ($table, $status, $limit);
$sth->execute(@bindParams) || die $sth->errstr(), "\n";

Which fails with:

You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server for the right syntax to use near
''CCHISTORY'
and TRXSTATUS = 'D'
and (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(TRXTIMEST' at line 1

Anyone see an obvious error in my code that I'm overlooking?

Thanks.

--
Kevin.

Re: bind_param oddity?

Kevin Spencer wrote:
> Hi guys,
>
> I'm sure I'm missing something *very* obvious but this one has me
> scratching my head. Using DBI 1.52, DBD::mysql 3.0007, Perl 5.8.8,
> MySQL 5.0.18.
>
> The following code executes fine:
>
> my $table = 'CCHISTORY';
> my $status = 'D';
> my $limit = 86400;
>
> my $SQL = < > select count(*) from $table
> where TRXSTATUS = '$status'
> and (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(TRXTIMESTAMP)) < $limit
> EOSQL
>
> my $sth = $dbh->prepare($SQL) || die $DBI::errstr, "\n";
> $sth->execute() || die $sth->errstr(), "\n";
>
> Now, because I want use use placeholders instead, I attempt the following:
>
> my $SQL = < > select count(*) from ?
> where TRXSTATUS = ?
> and (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(TRXTIMESTAMP)) < ?
> EOSQL

You can't use place holders for table names or column selectors.

> my $sth = $dbh->prepare($SQL) || die $DBI::errstr, "\n";
>
> my $table = 'CCHISTORY';
> my $status = 'D';
> my $limit = 86400;
>
> my @bindParams = ($table, $status, $limit);
> $sth->execute(@bindParams) || die $sth->errstr(), "\n";
>
> Which fails with:
>
> You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server for the right syntax to use near
> ''CCHISTORY'
> and TRXSTATUS = 'D'
> and (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(TRXTIMEST' at line 1
>
> Anyone see an obvious error in my code that I'm overlooking?
>
> Thanks.
>
> --
> Kevin.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

RE: bind_param oddity?

Kevin Spencer wrote:
> Hi guys,
>=20
> I'm sure I'm missing something *very* obvious but this one has me
> scratching my head. Using DBI 1.52, DBD::mysql 3.0007, Perl 5.8.8,
> MySQL 5.0.18.
>=20
[snip]
>=20
> my $SQL =3D < > select count(*) from ?
> where TRXSTATUS =3D ?
> and (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(TRXTIMESTAMP)) < ?
> EOSQL

You can't bind a table name -- binding is only for "value" types.

This is attempting to execute:
select count(*) from 'CCHISTORY' ... (which won't run in mysql either)

Regards,
Philip

Re: bind_param oddity?

On Nov 22, 2006, at 12:10 PM, Kevin Spencer wrote:

> Hi guys,


> Now, because I want use use placeholders instead, I attempt the
> following:
>
> my $SQL = < > select count(*) from ?
> where TRXSTATUS = ?
> and (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(TRXTIMESTAMP)) < ?
> EOSQL


> Anyone see an obvious error in my code that I'm overlooking?
>
> Thanks.
>
> --
> Kevin.

You can't replace table names with placeholders. The quick sort-of
intuitive
explanation is that the database can cache an internal version of the
query with a execution plan, and can put in placeholder values at
execution time for constants, but it can't do this for table or
column names,
or for a different number of variables in "IN" lists (because this
might parse
to a concatenation of 'OR' clauses, for example).

--
Christopher Sarnowski
csarnows@pcbi.upenn.edu
Penn Bioinformatics Core
1316 Blockley Hall MC 6021
(215) 746-7784

Re: bind_param oddity?

Kevin Spencer wrote:
> Hi guys,
>
> I'm sure I'm missing something *very* obvious but this one has me
> scratching my head. Using DBI 1.52, DBD::mysql 3.0007, Perl 5.8.8,
> MySQL 5.0.18.
>
> .....

> Now, because I want use use placeholders instead, I attempt the
> following:
>
> my $SQL = < > select count(*) from ?
> where TRXSTATUS = ?
> and (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(TRXTIMESTAMP)) < ?
> EOSQL
> Anyone see an obvious error in my code that I'm overlooking?
>
> Thanks.
>
> --
> Kevin.
I'm pretty sure that you can't use bind variables in place of table
names. Certainly true of Oracle stuff.

Martin

Re: bind_param oddity?

On 11/22/06, Garrett, Philip (MAN-Corporate) wrote:
> Kevin Spencer wrote:
> > Hi guys,
> >
> > I'm sure I'm missing something *very* obvious but this one has me
> > scratching my head. Using DBI 1.52, DBD::mysql 3.0007, Perl 5.8.8,
> > MySQL 5.0.18.
> >
> [snip]
> >
> > my $SQL = < > > select count(*) from ?
> > where TRXSTATUS = ?
> > and (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(TRXTIMESTAMP)) < ?
> > EOSQL
>
> You can't bind a table name -- binding is only for "value" types.
>
> This is attempting to execute:
> select count(*) from 'CCHISTORY' ... (which won't run in mysql either)

Philip, Christopher, Martin,

Thanks to all of you. That was indeed the problem. See, I knew it
was something obvious I was missing. Thanks again guys.

--
Kevin.

Re: bind_param oddity?

Also you would leave your DB wide open to SQL insertion attacks if one could
do this soit is good that they don't.

""Kevin Spencer"" wrote in message
news:7f5545290611220910k4312a12epa193793901663b36@mail.gmail .com...
> Hi guys,
>
> I'm sure I'm missing something *very* obvious but this one has me
> scratching my head. Using DBI 1.52, DBD::mysql 3.0007, Perl 5.8.8,
> MySQL 5.0.18.
>
> The following code executes fine:
>
> my $table = 'CCHISTORY';
> my $status = 'D';
> my $limit = 86400;
>
> my $SQL = < > select count(*) from $table
> where TRXSTATUS = '$status'
> and (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(TRXTIMESTAMP)) < $limit
> EOSQL
>
> my $sth = $dbh->prepare($SQL) || die $DBI::errstr, "\n";
> $sth->execute() || die $sth->errstr(), "\n";
>
> Now, because I want use use placeholders instead, I attempt the following:
>
> my $SQL = < > select count(*) from ?
> where TRXSTATUS = ?
> and (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(TRXTIMESTAMP)) < ?
> EOSQL
>
> my $sth = $dbh->prepare($SQL) || die $DBI::errstr, "\n";
>
> my $table = 'CCHISTORY';
> my $status = 'D';
> my $limit = 86400;
>
> my @bindParams = ($table, $status, $limit);
> $sth->execute(@bindParams) || die $sth->errstr(), "\n";
>
> Which fails with:
>
> You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server for the right syntax to use near
> ''CCHISTORY'
> and TRXSTATUS = 'D'
> and (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(TRXTIMEST' at line 1
>
> Anyone see an obvious error in my code that I'm overlooking?
>
> Thanks.
>
> --
> Kevin.