mysql problem

Hi all,

I am retrieving some data from a syslog mysql database:

#!/usr/bin/perl
use warnings;
# use strict;
use DBI;

my $dbh = DBI->connect('dbi:mysql:syslog', 'user', 'passwd')
or die DBI->errstr;

my $sth = $dbh->prepare("SELECT * from logs where host = ?"
or die $dbh->errstr;

print "Enter host: ";
while ($host = <>) {
my @data;
chomp $host;
$sth->execute($host) or die $sth->errstr;

This works as expected, I am prompted to enter a hostname and get the
results in the rest of the script which is not necessary to show
because the error comes here:

if i substituet in my $sth

my $sth = $dbh->prepare("SELECT * FROM logs where datetime like '?'")
or die $dbh->errstr;
print "Enter datetime: ";
while ($datetime = <>) {
my @data;
chomp $datetime;
$sth->execute($datetime) or die $sth->errstr;

then I am prompted to enter a date, which I can, but immediately after
that:

DBD::mysql::st execute failed: called with 1 bind variables when 0 are
needed at datetime.pl line 19, <> line 1.

The difference is in the SELECT like '?', I have tried like '%?%', but
it does not work either. In fact I need '%?%' which is what really works
for the mysql statement.

If I define $datetime beforehand and interpolate it in the query, it
works, but I read here that it is not reccommended:

http://www.perl.com/pub/a/1999/10/DBI.html

Any help greatly appreciated, I continue reading perldoc DBD::mysql,
perldoc DBI etc.

--
Groeten,
J.I.Asenjo

RE: [dbi] mysql problem

I think that wants to be
prepare(q/SELECT * FROM logs where datetime like ?/)

i.e. no quotes around the ?. DBD::mysql is telling you there are no parameters
in the query -> "called with 1 bind variables when 0 are needed"

However, you might have problems doing a like on datetime field.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com


On 09-Feb-2006 J.I. Asenjo wrote:
> Hi all,
>
> I am retrieving some data from a syslog mysql database:
>
>#!/usr/bin/perl
> use warnings;
># use strict;
> use DBI;
>
> my $dbh = DBI->connect('dbi:mysql:syslog', 'user', 'passwd')
> or die DBI->errstr;
>
> my $sth = $dbh->prepare("SELECT * from logs where host = ?"
> or die $dbh->errstr;
>
> print "Enter host: ";
> while ($host = <>) {
> my @data;
> chomp $host;
> $sth->execute($host) or die $sth->errstr;
>
> This works as expected, I am prompted to enter a hostname and get the
> results in the rest of the script which is not necessary to show
> because the error comes here:
>
> if i substituet in my $sth
>
> my $sth = $dbh->prepare("SELECT * FROM logs where datetime like '?'")
> or die $dbh->errstr;
> print "Enter datetime: ";
> while ($datetime = <>) {
> my @data;
> chomp $datetime;
> $sth->execute($datetime) or die $sth->errstr;
>
> then I am prompted to enter a date, which I can, but immediately after
> that:
>
> DBD::mysql::st execute failed: called with 1 bind variables when 0 are
> needed at datetime.pl line 19, <> line 1.
>
> The difference is in the SELECT like '?', I have tried like '%?%', but
> it does not work either. In fact I need '%?%' which is what really works
> for the mysql statement.
>
> If I define $datetime beforehand and interpolate it in the query, it
> works, but I read here that it is not reccommended:
>
> http://www.perl.com/pub/a/1999/10/DBI.html
>
> Any help greatly appreciated, I continue reading perldoc DBD::mysql,
> perldoc DBI etc.
>
> --
> Groeten,
> J.I.Asenjo

Re: [dbi] mysql problem

On Thu, 09 Feb 2006, 11:33:32AM -0000¨, Martin J. Evans said:
> I think that wants to be
> prepare(q/SELECT * FROM logs where datetime like ?/)

I had already tried that, it does not work either, but thanks anyway.

> i.e. no quotes around the ?. DBD::mysql is telling you there are no parameters
> in the query -> "called with 1 bind variables when 0 are needed"
>
> However, you might have problems doing a like on datetime field.

yes, I can see that :). Oh, well, I'll just have to interpolate it,
never mind.
--
Groeten,
J.I.Asenjo

RE: mysql problem

------_=_NextPart_001_01C62D95.37724D17
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

my $sth =3D $dbh->prepare("SELECT * FROM logs where datetime like '?'")
or die $dbh->errstr;
print "Enter datetime: ";
while ($datetime =3D <>) {
my @data;
chomp $datetime;
$sth->execute($datetime) or die $sth->errstr;

then I am prompted to enter a date, which I can, but immediately after
that:

DBD::mysql::st execute failed: called with 1 bind variables when 0 are
needed at datetime.pl line 19, <> line 1.

>> I've seen this, too. You might try using a naked ? without the =
single quotes.

If I define $datetime beforehand and interpolate it in the query, it
works, but I read here that it is not reccommended:

>> True that it's not recommended, because for each iteration the query =
will need to be re-parsed, but if you are not executing this query a =
million times, it probably won't matter much.

------_=_NextPart_001_01C62D95.37724D17--

Re: mysql problem

--------------enigB066934A5258D16185C7DEC2
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

The statement has to look like this:

SELECT * FROM logs where datetime like ?

Tom

Jason Friedman wrote:
> my $sth = $dbh->prepare("SELECT * FROM logs where datetime like '?'")
> or die $dbh->errstr;
> print "Enter datetime: ";
> while ($datetime = <>) {
> my @data;
> chomp $datetime;
> $sth->execute($datetime) or die $sth->errstr;
>
> then I am prompted to enter a date, which I can, but immediately after
> that:
>
> DBD::mysql::st execute failed: called with 1 bind variables when 0 are
> needed at datetime.pl line 19, <> line 1.
>
>
>>>I've seen this, too. You might try using a naked ? without the single quotes.
>
>
> If I define $datetime beforehand and interpolate it in the query, it
> works, but I read here that it is not reccommended:
>
>
>>>True that it's not recommended, because for each iteration the query will need to be re-parsed, but if you are not executing this query a million times, it probably won't matter much.
>
>


--------------enigB066934A5258D16185C7DEC2
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with Mandriva - http://enigmail.mozdev.org

iD8DBQFD63LqkVPeOFLgZFIRAlBZAKCaqzxkRdu9YZ1ZgO9C/lsPBWMQBgCg iFun
oE6JO/mx8Vr8VjPiwMGBuTQ=
=OKf1
-----END PGP SIGNATURE-----

--------------enigB066934A5258D16185C7DEC2--

Re: mysql problem

On Thu, 09 Feb 2006, 05:50:46PM +0100¨, Tom Schindl said:
> The statement has to look like this:
>
> SELECT * FROM logs where datetime like ?

It does not work, because the sql query needs the % joker unless you
give something like "2006-01-29 14:13:01" when prompted (somehow I have
the feeling normal people do no do that :) ). The datetime column keeps
records from a syslog server, if you ask why.

Anyway, I am *nearly* where I want, so thanks for all your input.
--
Groeten,
J.I.Asenjo

RE: [dbi] mysql problem

J.I. Asenjo [mailto:nasenjo@asenjo.nl] wrote:
>=20
> On Thu, 09 Feb 2006, 11:33:32AM -0000=A8, Martin J. Evans said:
> > I think that wants to be
> > prepare(q/SELECT * FROM logs where datetime like ?/)
>=20
> I had already tried that, it does not work either, but thanks anyway.

Why would you use LIKE with a datetime field anyway? LIKE is for =
comparing
strings, not dates.

In any case, it does work, as long as you're binding the right value:

my $datetime =3D <>;
chomp($datetime);
my $sth =3D $dbh->prepare(q/SELECT * FROM logs where datetime like ?/);
$sth->execute("%$datetime%");


> > i.e. no quotes around the ?. DBD::mysql is telling you there are no
> parameters
> > in the query -> "called with 1 bind variables when 0 are needed"
> >
> > However, you might have problems doing a like on datetime field.
>=20
> yes, I can see that :). Oh, well, I'll just have to interpolate it,
> never mind.

You should try to avoid interpolating user-supplied values into your =
SQL.
It is a significant security risk, besides being less efficient.


Ronald