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/