RE: Strange behaviour while using DBI with binding
You asked why I am concatenating the 2 scalars, comma and the space below?
my $limit = "$offset" . ', ' . "$number_rows";
I agree your suggestion below should work as well, but I was desperate to find
the error, so I tried several option to see whether DBI will parse the string
correctly.
Below I tried quoting the string as you mentioned below, but it still didn't
work as expected. MySQL has no problem if the value for "limit" has space
between the offset and number of rows as long as there is a comma after the
offset.
LIMIT 5, 10 and LIMIT 5,10 are the same in MySQL.
# limit should be like “10, 20”.
my $limit = “$offset, $number_rows”; # $limit = “$offset,$number_rows” did not
work as well.
my $sth = $dbh->prepare(qq{
Select fname, lname, dob, substr(desc, 1, 200) from user left
join personal_data on
user.id = personal_data.id where gender = ? and position = ?
order by lname limit ?
});
$sth->execute($gender, $role, $limit);
As can be seen in the MySQL query log below, the DBI parser ignores the comma,
space and second value and only passes the offset to the database. In the first
query the $limit variable contained 0,10, but DBI passes only the 0 (offset
value) to the db server. The only way to get around this is for me to pass
$limit directly in the query like:
my $sth = $dbh->prepare(qq{
Select fname, lname, dob, substr(desc, 1, 200) from user left
join personal_data on
user.id = personal_data.id where gender = ? and position = ?
order by lname limit $limit
});
$sth->execute($gender, $role);
==
100818 12:12:2416 Connect dbuser20...@localhost ON employees
16 Query set autocommit=1
16 Query SELECT a_session FROM sessions WHERE
id='e8c13b42f381eb683214d55af6c93ce4'
16 Quit
17 Connect dbuser20...@localhost ON employees
17 Query set autocommit=1
17 Query SELECT fname, lname, dob, substr(desc, 1, 200)
FROM user LEFT JOIN personal_data ON user.id = personal_data.id WHERE gender =
'male' and position = 'HR Admin' order by lname LIMIT 0
\n\r192.168.0.88\0\0\0_SESSION_REMOTE_ADDR\n15 Quit
100818 12:12:2416 Connect dbuser20...@localhost ON employees
16 Query set autocommit=1
16 Query SELECT a_session FROM sessions WHERE
id='e8c13b42f381eb683214d55af6c93ce4'
16 Quit
17 Connect dbuser20...@localhost ON employees
17 Query set autocommit=1
17 Query SELECT fname, lname, dob, substr(desc, 1, 200)
FROM user LEFT JOIN personal_data ON user.id = personal_data.id WHERE gender =
'male' and position = 'HR Admin' order by lname LIMIT 10
K�kL\0\0\0\0\0\0\0_SESSION_CTIME\nfemale\0\0\0query_seek_genderz�kL\0\0\0\0\0\0\0_SESSION_ATIME\n15
Quit
100818 12:12:2416 Connect dbuser20...@localhost ON employees
16 Query set autocommit=1
16 Query SELECT a_session FROM sessions WHERE
id='e8c13b42f381eb683214d55af6c93ce4'
16 Quit
17 Connect dbuser20...@localhost ON employees
17 Query set autocommit=1
17 Query SELECT fname, lname, dob, substr(desc, 1, 200)
FROM user LEFT JOIN personal_data ON user.id = personal_data.id WHERE gender =
'male' and position = 'HR Admin' order by lname LIMIT 20
Quit
=> -Original Message-
=> From: Uri Guttman [mailto:u...@stemsystems.com]
=> Sent: 18 August 2010 04:15
=> To: Mimi Cafe
=> Cc: beginners@perl.org
=> Subject: Re: Strange behaviour while using DBI with binding
=>
=> > "MC" == Mimi Cafe writes:
=>
=> MC> I experienced a strange behaviour while using DBI binding for
=> MySQL query
=> MC> with LIMIT clause. My CGI program behaved so strange and the
=> result was
=> MC> always unpredictable and it took me several hours before I
=> finally detected
=> MC> the problem.
=>
=> MC> # limit should be like "10, 20".
=>
=> MC> my $limit = "$offset" . ', ' . "$number_rows";
=>
=>
=> why are you quoting scalar variables? that should be one simpler
=> string:
=>
=> my $limit = "$offset, $number_rows";
=>
=> MC> my Sth = $dbh->prepare(qq{Select fname, lname, dob, substr(desc,
=> 1, 200)
=>
=> what is Sth? that isn't legal perl. please copy/paste real
=> code. obviously it should be $sth.
=>
=> MC> from user left join personal_data on user.id = personal_data.id
=> where gender
=> MC> = ? and position = ? order by lname limit ?});
=>
=> also you can format sql strings to be readable. do that. i like here
=> docs for long multiline strings:
=>
=> my $sth = $dbh->prepare( < Select
Re: Newbie: Perl how evaluate files newer than an hour within ftp
Hi Atropo,
On Tuesday 17 August 2010 15:37:51 Atropo wrote:
> Hi all, i have this simple script to check if a file has arrive since
> the last hour
>
> find2perl tmp -type f -eval '-M $_ < 1/24' -print |perl
>
> but now i would like to check in a remote server, maybe on ftp
> session. i have this ftp.pl
>
> perl -w >> ftp.log -MNet::FTP -le'
Please:
1. Don't write your program as a gigantic -e '...' block. Instead - put it in
a separate file. You can't honestly expect us to debug it.
2. See https://www.socialtext.net/perl5/index.cgi?ancient_perl
3. "use strict;" and "use warnings;".
4. Quoting perlbot:
{{{
use strict; use warnings; use lexical filehandles instead of globals
(open my $tmp ..vs.. open TMP), use three argument form of open, don't use
prototypes unless you really want to. don't call subs with & unless it's
required (&sub() ..vs.. sub()). check for errors (open (); ..vs.. open() or
die $!)
}}}
> ($second, $minute, $hour, $dayOfMonth, $month, $yearOffset, $dayOfWeek
> +, $dayOfYear, $daylightSavings) = localtime();
You should use a good datetime module instead. I like
http://search.cpan.org/dist/DateTime/ but there are others.
> $year = 1900 + $yearOffset;
> $theGMTime = "$hour:$minute:$second, $months[$month] $dayOfMonth,
> $year";
> ( $host, $user, $pass, $dir ) = @ARGV;
> $ftp = Net::FTP->new($host) or die "$...@\n"; #send mail if cannot
> connect do not know how to do it
> $ftp->login( $user, $pass ) or die $ftp->message;
> $ftp->cwd($dir) or die $ftp->message;
> $ftp->binary;
> for $file ($ftp->ls){
> push @files, $file;
> }
>
> for $file (@files) {
>$quesesto=localtime($ftp->mdtm($file));
>$localtimenoformat=localtime(time());
>$mdtmnoformat=$ftp->mdtm($file);
>print "MDTM with FORMAT= $quesesto\n";
>print "Localtime No Format = $localtimenoformat\n";
>print "MDTM No Format = $mdtmnoformat\n";
> #envia_mail ();
> }
> $ftp->quit or die $ftp->message;
>
> sub envia_mail {
> print "Content-type: text/html\n\n";
>
> #$title='';
> $to="alexis_vasqu...@codetel\.com\.do";
> $from= `hostname`;
> #$subject='';
>
> open(MAIL, "|/usr/sbin/sendmail -t");
>
> ## Mail Header
> print MAIL "To: $to\n";
> print MAIL "From: $from\n";
> print MAIL "Subject: $subject\n\n";
> ## Mail Body
> print MAIL "This is a test message \n";
>
> close(MAIL);
>
> print "$title\n\n\n";
>
> ## HTML content sent, let use know we sent an email
> print "
> $title
>
> A message has been sent from $from to $to
>
> ";
You should use a here-document here.
>
> }
>
> 'host user pass dir
Don't append the «host» parameter to the end of hte '' - it will be part
of the program.
>
> Still cant't figure out how to check if file is newer that an hour. I
> can get this:
>
> MDTM with FORMAT = Fri Aug 21 12:23:15 2009
> Localtime No Format = Fri Aug 13 10:17:10 2010
> MDTM No Format = 1250871795
>
> But don't know how to get the 'localtime with NO Format' to compare
> with the 'MDTM with NO Format'
Just use timestamps, or use the DateTime module. You can find if a timestamp
is in the last hour by "time() - $timestamp < 60*60".
Regards,
Shlomi Fish
--
-
Shlomi Fish http://www.shlomifish.org/
"The Human Hacking Field Guide" - http://shlom.in/hhfg
God considered inflicting XSLT as the tenth plague of Egypt, but then
decided against it because he thought it would be too evil.
Please reply to list if it's a mailing list post - http://shlom.in/reply .
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Re: Strange behaviour while using DBI with binding
> "MC" == Mimi Cafe writes:
MC> You asked why I am concatenating the 2 scalars, comma and the space
below?
MC> my $limit = "$offset" . ', ' . "$number_rows";
MC> I agree your suggestion below should work as well, but I was desperate to
find the error, so I tried several option to see whether DBI will parse the
string correctly.
i don't see any code where there is no space after the comma.
MC> Below I tried quoting the string as you mentioned below, but it
MC> still didn't work as expected. MySQL has no problem if the value
MC> for "limit" has space between the offset and number of rows as
MC> long as there is a comma after the offset.
i don't see that at all.
MC> LIMIT 5, 10 and LIMIT 5,10 are the same in MySQL.
MC> # limit should be like “10, 20”.
MC> my $limit = “$offset, $number_rows”; # $limit = “$offset,$number_rows”
did not work as well.
there is a space there. you claim it makes no difference but that isn't
what i see here. show the dbi log of the code without the space.
MC> my $sth = $dbh->prepare(qq{
MC> Select fname, lname, dob, substr(desc, 1, 200) from user left
join personal_data on
MC> user.id = personal_data.id where gender = ? and position = ?
order by lname limit ?
MC> });
why not try to hard code the limit in that string? don't use a bind
value. there is no need for a bind here as your numbers are hard coded.
MC> $sth->execute($gender, $role, $limit);
MC> As can be seen in the MySQL query log below, the DBI parser
MC> ignores the comma, space and second value and only passes the
MC> offset to the database. In the first query the $limit variable
MC> contained 0,10, but DBI passes only the 0 (offset value) to the db
MC> server. The only way to get around this is for me to pass $limit
MC> directly in the query like:
again, this is with a space. not what i asked to see.
MC> my $sth = $dbh->prepare(qq{
MC> Select fname, lname, dob, substr(desc, 1, 200) from user left
join personal_data on
MC> user.id = personal_data.id where gender = ? and position = ?
order by lname limit $limit
MC> });
same thing here. i don't see the limit without the space.
uri
--
Uri Guttman -- u...@stemsystems.com http://www.sysarch.com --
- Perl Code Review , Architecture, Development, Training, Support --
- Gourmet Hot Cocoa Mix http://bestfriendscocoa.com -
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
RE: Strange behaviour while using DBI with binding
>From your last comments, I am not sure where this is leading to, but here is
all I have to say.
Below are 2 pieces of code. Both have been tested with space in the string
and again without space.
$limit = "$offset,$number_rows" or $limit = "$offset, $number_rows";
1) This does not work (with or without space in the string).
my $sth = $dbh->prepare(qq{
Select fname, lname, dob, substr(desc, 1, 200) from user
left join personal_data on
user.id = personal_data.id where gender = ? and position = ?
order by lname limit ?
});
$sth->execute($gender, $role, $limit);
2) This works fine (with or without space in the string).
my $sth = $dbh->prepare(qq{
Select fname, lname, dob, substr(desc, 1, 200) from user
left join personal_data on
user.id = personal_data.id where gender = ? and position = ?
order by lname limit $limit
});
$sth->execute($gender, $role);
In the first example, DBI always passed the value for offset and ignored the
second value after the comma.
I'm now using the second code as it works without problem even if string
contains space. I just need to understand why DBI behaves that way.
I cannot hard code limit in the string because the value varies. This is
used in a pager and the values of limit varies depending on which page is to
be displayed.
Re: print string in file
Thanks all
it worked.
--Irfan
From: Dr.Ruud
To: beginners@perl.org
Sent: Wed, August 18, 2010 12:35:57 AM
Subject: Re: print string in file
Irfan Sayed wrote:
> print MYFILE "\n" where MYFILE is a
> file
>handler.
s/handler/handle/
print $MYFILE qq{\n};
-- Ruud
-- To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
