Re: query only partially done
Dan Muey wrote:
>>I think you will need to show us more of your code. Where are the ids
>>coming from?
>>
>>
>
>First the html generated by the script :
>
>
>
>I get this html by doing :
>
> while(@row = $sth->fetchrow_array) {
>
>if($tmp_bgcolor eq "$bgcolor_a") { $tmp_bgcolor
>= $bgcolor_b; }
>else { $tmp_bgcolor = $bgcolor_a; }
>
>$code = '';
>($tmpa = $row[0]) =~ s/([^\x20-\x7E])/'\x' . sprintf "%x", ord $1/ge;
>$code = " -$tmpa- name=\"IDS\" value=\"$row[0],\"> ";
>$code ="$code href=\"$mysql_man_script_name?req_lib=$req_lib&req_id=$row[0]&action=see
>_rec\"> View Rec
>ord Deleted By : $row[1] On : $row[2] ::
>$row[$menu_option_index_num]";
>$code = "$code \n";
>print $code;
>$code = '';
>}
>
>With the altered code it outputs :
>
> -28-
> -18-
>
>If you check them both and submit, it does this script :
>$ids = $in{'IDS'};
>$ids =~ s/\,$//;
>print "-$ids-";# which out put -> -27,18-
>($tmpa = $ids) =~ s/([^\x20-\x7E])/'\x' . sprintf "%x", ord $1/ge;
>print $tmpa; # which out put -> 27,\x018
>print "";
>@recs = split(/,/, $ids);
>
>
>
>>Perhaps the later ids include a non-printable character, which trace()
>>
>>
>is
>
>
>>outputting as a period. Try printing your query like this:
>>($tmp = $query) =~ s/([^\x20-\x7E])/'\x' . sprintf "%x", ord $1/ge;
>>print $tmp;
>>
>>
>
>$tmp prints out as : DELETE FROM customer WHERE ID IN ('25','\x018')
>sure enough odd char
>It seems to get there somewhere in between pressing submit and after
>parse
>( I use the &ReadParse subroutine in cgi-lib )
>
>I just did "$query =~ s/([^\x20-\x7E])//ge;"
> to remove any nonprintable chars right before do() and all is well.
>Thanks for the idea! I still wonder where it's coming from.
>
It comes from old cgi-lib leaving the values of multivalued fields
separated with \0, which you did not remove. You obiously appended the
commas to your IDS checkbox values in order to have something to split
in case of multiple selections. This is not necessary. Eliminate the
commas from your checkbox values and retrieve the checked IDS saying
my @recs = split /\0/, $in{'IDS'};
Or better: use CGI.pm instead of cgi-lib, there will be not too many
changes necessary to your code. Your checkboxes then should look like
and to fetch the cecked ids using CGI.pm just say
my @recs = param('IDS'); # function-oriented style
>Any ideas how a non printable character would get ther ewould be good to
>know.
>
see above
>
>Thanks for your help everyone!
>Dan
>
>
>>Ronald
>>
>>
Bodo
Re: query only partially done
On Wed, Aug 28, 2002 at 11:11:23AM -0500, Dan Muey wrote:
>
> >I think you will need to show us more of your code. Where are the ids
> >coming from?
>
> First the html generated by the script :
>
>
> @recs = split(/,/, $ids);
>
> >Perhaps the later ids include a non-printable character, which trace() is
> >outputting as a period. Try printing your query like this:
> >($tmp = $query) =~ s/([^\x20-\x7E])/'\x' . sprintf "%x", ord $1/ge;
> >print $tmp;
>
> $tmp prints out as : DELETE FROM customer WHERE ID IN ('25','\x018')
(Woops, I should have made that sprintf "%02x", ord $1)
> sure enough odd char
> It seems to get there somewhere in between pressing submit and after
> parse
> ( I use the &ReadParse subroutine in cgi-lib )
I thought that might be it. cgi-lib uses the null character to join
multiple values for a single parameter. Instead of including a comma in
each value to split on later, you can just split on /\0/.
However, I would really recommend switching to the CGI module, which will
do all the parameter parsing and unencoding for you.
Ronald
RE: query only partially done
>I think you will need to show us more of your code. Where are the ids
>coming from?
First the html generated by the script :
I get this html by doing :
while(@row = $sth->fetchrow_array) {
if($tmp_bgcolor eq "$bgcolor_a") { $tmp_bgcolor
= $bgcolor_b; }
else { $tmp_bgcolor = $bgcolor_a; }
$code = '';
($tmpa = $row[0]) =~ s/([^\x20-\x7E])/'\x' . sprintf "%x", ord $1/ge;
$code = " -$tmpa- ";
$code ="$code View Rec
ord Deleted By : $row[1] On : $row[2] ::
$row[$menu_option_index_num]";
$code = "$code \n";
print $code;
$code = '';
}
With the altered code it outputs :
-28-
-18-
If you check them both and submit, it does this script :
$ids = $in{'IDS'};
$ids =~ s/\,$//;
print "-$ids-";# which out put -> -27,18-
($tmpa = $ids) =~ s/([^\x20-\x7E])/'\x' . sprintf "%x", ord $1/ge;
print $tmpa; # which out put -> 27,\x018
print "";
@recs = split(/,/, $ids);
>Perhaps the later ids include a non-printable character, which trace()
is
>outputting as a period. Try printing your query like this:
>($tmp = $query) =~ s/([^\x20-\x7E])/'\x' . sprintf "%x", ord $1/ge;
>print $tmp;
$tmp prints out as : DELETE FROM customer WHERE ID IN ('25','\x018')
sure enough odd char
It seems to get there somewhere in between pressing submit and after
parse
( I use the &ReadParse subroutine in cgi-lib )
I just did "$query =~ s/([^\x20-\x7E])//ge;"
to remove any nonprintable chars right before do() and all is well.
Thanks for the idea! I still wonder where it's coming from.
Any ideas how a non printable character would get ther ewould be good to
know.
Thanks for your help everyone!
Dan
>Ronald
Re: query only partially done
On Wed, Aug 28, 2002 at 09:33:19AM -0500, Dan Muey wrote:
> print $query;
> $dbh->trace(2,"trace.txt");
> $dbh->do($query) or die "Can not execute $query :" . $dbh->errstr .
> "\n";
> print $query;
>
> Here is pasted in the output from the above two print $query bits :
> DELETE FROM customer WHERE ID IN ('23','18')
> DELETE FROM customer WHERE ID IN ('23','18')
>
> Here is the content of the trace.txt
>
> DBI::db=HASH(0x81bd0b4) trace level set to 2 in DBI 1.21-nothread
> Note: perl is running without the recommended perl -w option
> -> do for DBD::mysql::db (DBI::db=HASH(0x81bd03c)~0x81bd0b4
> 'DELETE FROM customer WHERE ID IN ('23','.18')')
> <- do= 1 at mysql_man.cgi line 404
> -> disconnect for DBD::mysql::db (DBI::db=HASH(0x81bd03c)~0x81bd0b4)
> imp_dbh->svsock: 8141a44
> <- disconnect= 1 at mysql_man.cgi line 47
> -> DESTROY for DBD::mysql::db (DBI::db=HASH(0x81bd0b4)~INNER)
> <- DESTROY= undef during global destruction
>
> So for some reason the do() seems to be adding a '.' in front of all
> ids except for the first one. No wonder it doesn't match the ids!
I think you will need to show us more of your code. Where are the ids
coming from?
Perhaps the later ids include a non-printable character, which trace() is
outputting as a period. Try printing your query like this:
($tmp = $query) =~ s/([^\x20-\x7E])/'\x' . sprintf "%x", ord $1/ge;
print $tmp;
Ronald
RE: query only partially done
>[EMAIL PROTECTED]
>Add $dbh -> trace( 4, "file" ); before the do() to see what DBI thinks
is
>happening.
>Mac :})
Thanks good idea!
Ok here's my modified perl : ( I did 2 instead of 4 becaus e4 didn't do
anything )
print $query;
$dbh->trace(2,"trace.txt");
$dbh->do($query) or die "Can not execute $query :" . $dbh->errstr .
"\n";
print $query;
Here is pasted in the output from the above two print $query bits :
DELETE FROM customer WHERE ID IN ('23','18')
DELETE FROM customer WHERE ID IN ('23','18')
Here is the content of the trace.txt
DBI::db=HASH(0x81bd0b4) trace level set to 2 in DBI 1.21-nothread
Note: perl is running without the recommended perl -w option
-> do for DBD::mysql::db (DBI::db=HASH(0x81bd03c)~0x81bd0b4
'DELETE FROM customer WHERE ID IN ('23','.18')')
<- do= 1 at mysql_man.cgi line 404
-> disconnect for DBD::mysql::db (DBI::db=HASH(0x81bd03c)~0x81bd0b4)
imp_dbh->svsock: 8141a44
<- disconnect= 1 at mysql_man.cgi line 47
-> DESTROY for DBD::mysql::db (DBI::db=HASH(0x81bd0b4)~INNER)
<- DESTROY= undef during global destruction
So for some reason the do() seems to be adding a '.' in front of all
ids except for the first one. No wonder it doesn't match the ids!
I tried a regex to remove periods -> =~ s/\.//g; before the
print statements and $query doesn't seem to have it when
printed out with or without the regex.
A few more answers anyway. But why the period added in the do()?
Re: query only partially done
On Tue, 27 Aug 2002 13:54:25 -0500 Dan Muey <[EMAIL PROTECTED]> wrote:
>
>
> >1) is the id field a character? in other words, do you need the
> >quotes? id='10' ??
> It is -> ID INT(11) NOT NULL AUTO_INCREMENT and -> PRIMARY KEY(ID)
>
> >2) why not just use
>
> >DELETE FROM customer WHERE ID in ('7','8','9','10')
>
> >??
> >...I'll try that...
>
> I tried the above method and same thing. It would only delete record '7'
> I tried the above and the old with out the single quotes and then it
> wouldn't do anything except die.
Add $dbh -> trace( 4, "file" ); before the do() to see what DBI thinks is
happening.
--
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.
Re: query only partially done
>1) is the id field a character? in other words, do you need the
>quotes? id='10' ??
It is -> ID INT(11) NOT NULL AUTO_INCREMENT and -> PRIMARY KEY(ID)
>2) why not just use
>DELETE FROM customer WHERE ID in ('7','8','9','10')
>??
>...I'll try that...
I tried the above method and same thing. It would only delete record '7'
I tried the above and the old with out the single quotes and then it
wouldn't do anything except die.
Dan
>-Joe
--- Dan Muey <[EMAIL PROTECTED]> wrote:
> Here is the deal : I have a script that does quite a bit mysql
> manipulation everything works super except one simple delete
> statement
>
> print $query;
> This is copied and pasted from the above print statement :
> DELETE FROM customer WHERE ID='10' OR ID='9' OR ID='8' OR ID='7'
>
> $dbh->do($query) or die "Can not execute $query :" . $dbh->errstr .
> "\n";
>
> It does delete the first record specified ( in this case ID='10' )
> it acts as if the query where just : DELETE FROM customer WHERE
> ID='10'
>
> I get no error messages, it goes on and finishes displaying the
> page.
> When I've had bad queries before it would just stop at the 'do'
> statement and not finish printing the html out for the rest of the
> page.
>
> If I paste that query into the mysql>prompt it works like a charm
> and
> deletes all. ( with an added semi colon of course )
>
> i've usd the prepare/execue method, I've had it do execute a query
> for
> each id :
> foreach $ID(@ids) {
> $query = "DELETE FROM customer WHERE ID=\'$ID\'";
> print "$query \n";
> $dbh->do($query) or die "Can not execute $query :" .
> $dbh->errstr ."\n";
> $query = '';
> }
> results in this output :
>
> DELETE FROM customer WHERE ID='8'
> DELETE FROM customer WHERE ID='9'
> DELETE FROM customer WHERE ID='10'
> It acts as if only 'DELETE FROM customer WHERE ID='8'' is being
> executed
> or executed each time but $query is different each time in the
> print
> statement.
>
> all have the exact same result - only the first record specified
> gets
> deleted,( ID='8' above )the script behaves as if the query went ok,
> but
> they are still
> there and if I copy and paste the query into the command line it
> works
> fine.
>
> Here's what I got :
>
> perl, version 5.005_03 built for i386-freebsd
> mysql 323
> DBI 1.21
>
> Why isn't it doing the entire query from my script?
>
> Thanks
>
> Dan
RE: query only partially done
>1) is the id field a character? in other words, do you need the
>quotes? id='10' ??
It is -> ID INT(11) NOT NULL AUTO_INCREMENT and -> PRIMARY KEY(ID)
>2) why not just use
>DELETE FROM customer WHERE ID in ('7','8','9','10')
>??
Good question I'll try that way. I also have the same prob for and
update
statement. I'll try it on both. Thanks
Dan
>-Joe
--- Dan Muey <[EMAIL PROTECTED]> wrote:
> Here is the deal : I have a script that does quite a bit mysql
> manipulation everything works super except one simple delete
> statement
>
> print $query;
> This is copied and pasted from the above print statement :
> DELETE FROM customer WHERE ID='10' OR ID='9' OR ID='8' OR ID='7'
>
> $dbh->do($query) or die "Can not execute $query :" . $dbh->errstr .
> "\n";
>
> It does delete the first record specified ( in this case ID='10' )
> it acts as if the query where just : DELETE FROM customer WHERE
> ID='10'
>
> I get no error messages, it goes on and finishes displaying the
> page.
> When I've had bad queries before it would just stop at the 'do'
> statement and not finish printing the html out for the rest of the
> page.
>
> If I paste that query into the mysql>prompt it works like a charm
> and
> deletes all. ( with an added semi colon of course )
>
> i've usd the prepare/execue method, I've had it do execute a query
> for
> each id :
> foreach $ID(@ids) {
> $query = "DELETE FROM customer WHERE ID=\'$ID\'";
> print "$query \n";
> $dbh->do($query) or die "Can not execute $query :" .
> $dbh->errstr ."\n";
> $query = '';
> }
> results in this output :
>
> DELETE FROM customer WHERE ID='8'
> DELETE FROM customer WHERE ID='9'
> DELETE FROM customer WHERE ID='10'
> It acts as if only 'DELETE FROM customer WHERE ID='8'' is being
> executed
> or executed each time but $query is different each time in the
> print
> statement.
>
> all have the exact same result - only the first record specified
> gets
> deleted,( ID='8' above )the script behaves as if the query went ok,
> but
> they are still
> there and if I copy and paste the query into the command line it
> works
> fine.
>
> Here's what I got :
>
> perl, version 5.005_03 built for i386-freebsd
> mysql 323
> DBI 1.21
>
> Why isn't it doing the entire query from my script?
>
> Thanks
>
> Dan
query only partially done
Here is the deal : I have a script that does quite a bit mysql
manipulation everything works super except one simple delete statement
print $query;
This is copied and pasted from the above print statement:
DELETE FROM customer WHERE ID='10' OR ID='9' OR ID='8' OR ID='7'
$dbh->do($query) or die "Can not execute $query :" . $dbh->errstr .
"\n";
It does delete the first record specified ( in this case ID='10' )
it acts as if the query where just : DELETE FROM customer WHERE ID='10'
I get no error messages, it goes on and finishes displaying the page.
When I've had bad queries before it would just stop at the 'do'
statement and not finish printing the html out for the rest of the page.
If I paste that query into the mysql>prompt it works like a charm and
deletes all. ( with an added semi colon of course )
i've usd the prepare/execue method, I've had it do execute a query for
each id :
foreach $ID(@ids) {
$query = "DELETE FROM customer WHERE ID=\'$ID\'";
print "$query \n";
$dbh->do($query) or die "Can not execute $query :" .
$dbh->errstr ."\n";
$query = '';
}
results in this output :
DELETE FROM customer WHERE ID='8'
DELETE FROM customer WHERE ID='9'
DELETE FROM customer WHERE ID='10'
It acts as if only 'DELETE FROM customer WHERE ID='8'' is being executed
or executed each time but $query is different each time in the print
statement.
all have the exact same result - only the first record specified gets
deleted,( ID='8' above )the script behaves as if the query went ok, but
they are still
there and if I copy and paste the query into the command line it works
fine.
Here's what I got :
perl, version 5.005_03 built for i386-freebsd
mysql 323
DBI 1.21
Why isn't it doing the entire query from my script?
Thanks
Dan
