UPDATE DB

[JimJx]

Hi again all,

I am still working on the DB and ran intpo anpther problem that I hope
you all can assist with...

I have a table that consists of 13 columns, the last column being an
autoincrement field 'ID'

Now I have a form on my site that passes data to the script. The
script is supposed to insert the new data into "ID" but that doesn't
happen for some reason. I know that the data being passed is accurate
because I put a print statement in and it worked fine. It just isn't
making it into the DB.

No errors, nada.....

Any suggestions?

Thanks!
Jim

sub Update() {
my $dbh = DBI->connect('DBI:mysql:mydb',$DBuser, $DBpass)
or die "Couldn't connect to database: " . DBI->errstr;

my $sth = $dbh->prepare( 'UPDATE valley SET Category="$Category",
Name="$Name", Description="$Description", Contact="$Contact",
Phone="$Phone", Fax="$Fax", Address="$Address", City="$City",
State="$State", ZipCode="$ZipCode", Email="$Email", URL="$URL",
Keywords="$Keywords" where ID = "$ID"' )or die "Couldn't prepare: " .
DBI->errstr;

$sth->execute()or die "Couldn't execute: " . DBI->errstr;;

$sth->finish ( );
$dbh->disconnect;

print "Category=$Category, Name=$Name, Description=$Description,
Contact=$Contact, Phone=$Phone, Fax=$Fax, Address=$Address, City=
$City, State=$State, ZipCode=$ZipCode, Email=$Email, URL=$URL,
Keywords=$Keywords, ID=$ID";
&Bottom;
exit;
}

 

[JimJx]

Jim,

If this script is cgi, then add below to the top of your code:
use CGI::Carp qw/fatalsToBrowser/;

then your errors will print to the browser.

Where did $ID come from? Is it the result of a SELECT statement?
You need to force it as integer, not as string.

Your Perl vars should type the same as they are declared in the
MySQL Table create.

hth,
Mark

CentOS 5 RPMhttp://www.tlviewer.org/rt3/

Thanks for replying Mark.

At the top of my script I have

use strict;
use DBI;
use CGI::Carp qw/fatalsToBrowser warningsToBrowser/;

Still, no errors anywhere. None show in the browser or the logs. As
I said, nada.....

$ID does come from a SELECT. And I hate to show my ignorance here,
but how do I force $ID to integer?

Thanks!
Jim

Jim

 

[Paul Lalli]

Now I have a form on my site that passes data to the script. The
script is supposed to insert the new data into "ID" but that doesn't
happen for some reason. I know that the data being passed is accurate
because I put a print statement in and it worked fine.

Your print statement has an important difference from your prepare
statement.

It just isn't making it into the DB.

Yeah it is. It just isn't inserting what you think it is.

my $sth = $dbh->prepare( 'UPDATE valley SET Category="$Category",

You're using a single quoted string. Single quotes do not
interpolate. This statement says that you want to set Category to the
literal string '$Category', not whatever value the variable $Category
has. It also says to update where ID is equal to the literal string
'$ID'. I'm betting you have no such row where that's true.

Either change your single quotes to double quotes and backslash all
the internal double quotes, or preferably, use placeholders and let
Perl/DBI take care of this mess for you.

Name="$Name", Description="$Description", Contact="$Contact",
Phone="$Phone", Fax="$Fax", Address="$Address", City="$City",
State="$State", ZipCode="$ZipCode", Email="$Email", URL="$URL",
Keywords="$Keywords" where ID = "$ID"' )or die "Couldn't prepare: " .
DBI->errstr;

my $sth = $dbh->prepare('UPDATE valley SET Category = ?, Name = ?,
Description = ?, Contact = ?, Phone = ?, Fax = ?, Address = ?, City
= ?, State = ?, ZipCode = ?, Email = ?, URL = ?, Keywords = ? WHERE ID
= ?') or die "Couldn't prepare: " . DBI->errstr;

$sth->execute()or die "Couldn't execute: " . DBI->errstr;;

$sth->execute($Category, $Name, $Description, $Contact, $Phone, $Fax,
$Address, $City, $State, $ZipCode, $Email, $URL, $Keywords, $ID) or
die "Couldn't execute: " . DBI->errstr;

$sth->finish ( );
$dbh->disconnect;

print "Category=$Category, Name=$Name, Description=$Description,
Contact=$Contact, Phone=$Phone, Fax=$Fax, Address=$Address, City=
$City, State=$State, ZipCode=$ZipCode, Email=$Email, URL=$URL,
Keywords=$Keywords, ID=$ID";

In your print statement, you used Double quotes, where you used Single
quotes in your prepare statement. Change the double quotes here to
single quotes to see what you were actually trying to execute.

Paul Lalli

 

[JimJx]

Your print statement has an important difference from your prepare
statement.


Yeah it is. It just isn't inserting what you think it is.


You're using a single quoted string. Single quotes do not
interpolate. This statement says that you want to set Category to the
literal string '$Category', not whatever value the variable $Category
has. It also says to update where ID is equal to the literal string
'$ID'. I'm betting you have no such row where that's true.

Either change your single quotes to double quotes and backslash all
the internal double quotes, or preferably, use placeholders and let
Perl/DBI take care of this mess for you.


my $sth = $dbh->prepare('UPDATE valley SET Category = ?, Name = ?,
Description = ?, Contact = ?, Phone = ?, Fax = ?, Address = ?, City
= ?, State = ?, ZipCode = ?, Email = ?, URL = ?, Keywords = ? WHERE ID
= ?') or die "Couldn't prepare: " . DBI->errstr;


$sth->execute($Category, $Name, $Description, $Contact, $Phone, $Fax,
$Address, $City, $State, $ZipCode, $Email, $URL, $Keywords, $ID) or
die "Couldn't execute: " . DBI->errstr;





In your print statement, you used Double quotes, where you used Single
quotes in your prepare statement. Change the double quotes here to
single quotes to see what you were actually trying to execute.

Paul Lalli

Excellent everyone!!! Thanks a million, I was starting to bang my
head on the wall and giving myself Excedrin headache #42....

I just never thought about the placeholders, I guess I need to brush
up a little more.

Once again,

Thanks everyone!
Jim

 

[JimJx]

To convert "numeric string" to number I'm used to use

$ID = "123"; # this is a string but contain digits only
$ID *= 1; # now it is a integer number

$ID = "123.45"; # this is a string but contain digits only and the decimal
dot
$ID *= 1; # now it is a float number

--

Petr Vileta, Czech republic
(My server rejects all messages from Yahoo and Hotmail. Send me your mail
from another non-spammer site please.)

Excellent Petr, thank you for that!
Jim

 

[Ron Bergin]

To convert "numeric string" to number I'm used to use

$ID = "123"; # this is a string but contain digits only
$ID *= 1; # now it is a integer number

$ID = "123.45"; # this is a string but contain digits only and the decimal
dot
$ID *= 1; # now it is a float number

--

Petr Vileta, Czech republic
(My server rejects all messages from Yahoo and Hotmail. Send me your mail
from another non-spammer site please.)

It really depends on the context in which it's being used/evaluated.

use strict;
use warnings;

my $str = "123";

my $ID = "123"; # this is a string but contain digits only
$ID *= 1; # now it is a integer number

if($ID eq $str) {
print "$ID eq $str evaluated in string context\n";
}

if($ID == $str) {
print "$ID == $str evaluated in numerical context\n";
}

 

[Uri Guttman]

PV> To convert "numeric string" to number I'm used to use

PV> $ID = "123"; # this is a string but contain digits only
PV> $ID *= 1; # now it is a integer number

PV> $ID = "123.45"; # this is a string but contain digits only and the
PV> decimal dot
PV> $ID *= 1; # now it is a float number

neither of those is ever really needed. perl will convert strings
to/from numbers on demand. you are burning cpu cycles for no reason if
you add those conversion lines.

uri

 

[Uri Guttman]

J> Excellent Petr, thank you for that!

it is not excellent. see my other post. his conversion code is generally
not needed.

uri

 

[Uri Guttman]

PV> Hmm, interesting ;-) I tested it now and you are right, but in some
PV> case, I can't to remember which, the == comparation fail. Maybe in old
PV> version of Perl, I really don' know now. For this I began to use "brute
PV> force" type conversion. Maybe I vaste CPU time but I'm sure that I have
PV> variable type what I need for other operations.

but you never need it. never. perl converts according the operation
being done. if you use a numeric op like == or + or * it will convert
any strings to numbers. and perl converts any numbers to strings when
they are used with ops like . "" and print. the only issue is if you
have a string which isn't a proper number. perl will convert it until it
runs into a bad character and will issue a warning if enabled. your
manual conversions are just doing it a step before perl will do it
itself.

uri