UPDATE DB

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;
}

Re: UPDATE DB

On Wed, 05 Sep 2007 13:26:02 -0700, JimJx wrote:

> 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;
> }

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 RPM
http://www.tlviewer.org/rt3/

Re: UPDATE DB

On Sep 5, 5:55 pm, Mark Pryor wrote:
> On Wed, 05 Sep 2007 13:26:02 -0700, JimJx wrote:
> > 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;
> > }
>
> 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

Re: UPDATE DB

On Wed, 05 Sep 2007 15:01:08 -0700, JimJx wrote:

> On Sep 5, 5:55 pm, Mark Pryor wrote:
>> On Wed, 05 Sep 2007 13:26:02 -0700, JimJx wrote:
>> > 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.
>>
> 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?
>

before you run the prepare/SELECT do
$ID = 1; #init to some integer value

--
Mark

Re: UPDATE DB

On 2007-09-05, JimJx wrote:
>
> 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;

If $ID is an integer (which you've mentioned in other posts), it should
not be quoted. Really, you should be using placeholders, so that DBI
can determine the correct datatypes e.g.

my $sth=$dbh->prepare('UPDATE valley SET Category=? WHERE ID=?');
$sth->execute($Category,$ID);


--keith

--
kkeller-usenet@wombat.san-francisco.ca.us
(try just my userid to email me)
AOLSFAQ=http://www.therockgarden.ca/aolsfaq.txt
see X- headers for PGP signature information

Re: UPDATE DB

On Sep 5, 4:26 pm, JimJx wrote:
> 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

Re: UPDATE DB

On Sep 5, 9:28 pm, Paul Lalli wrote:
> On Sep 5, 4:26 pm, JimJx wrote:
>
> > 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

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

Re: UPDATE DB

JimJx wrote:
> $ID does come from a SELECT. And I hate to show my ignorance here,
> but how do I force $ID to integer?
>
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.)

Re: UPDATE DB

On Sep 5, 10:23 pm, "Petr Vileta" wrote:
> JimJx wrote:
> > $ID does come from a SELECT. And I hate to show my ignorance here,
> > but how do I force $ID to integer?
>
> 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

Re: UPDATE DB

On Sep 5, 7:23 pm, "Petr Vileta" wrote:
> JimJx wrote:
> > $ID does come from a SELECT. And I hate to show my ignorance here,
> > but how do I force $ID to integer?
>
> 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";
}

Re: UPDATE DB

>>>>> "PV" == Petr Vileta writes:

PV> JimJx wrote:
>> $ID does come from a SELECT. And I hate to show my ignorance here,
>> but how do I force $ID to integer?
>>
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 ------ uri@stemsystems.com -------- http://www.stemsystems.com
--Perl Consulting, Stem Development, Systems Architecture, Design and Coding-
Search or Offer Perl Jobs ---------------------------- http://jobs.perl.org

Re: UPDATE DB

>>>>> "J" == JimJx writes:

J> On Sep 5, 10:23 pm, "Petr Vileta" wrote:
>> JimJx wrote:
>> > $ID does come from a SELECT. And I hate to show my ignorance here,
>> > but how do I force $ID to integer?
>>
>> 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

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 ------ uri@stemsystems.com -------- http://www.stemsystems.com
--Perl Consulting, Stem Development, Systems Architecture, Design and Coding-
Search or Offer Perl Jobs ---------------------------- http://jobs.perl.org

Re: UPDATE DB

Uri Guttman wrote:
>>>>>> "PV" == Petr Vileta writes:
>
>> JimJx wrote:
> >> $ID does come from a SELECT. And I hate to show my ignorance
> here, >> but how do I force $ID to integer?
> >>
>> 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
>
> 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.
>
Hmm, interesting ;-) I tested it now and you are right, but in some case, I
can't to remember which, the == comparation fail. Maybe in old version of
Perl, I really don' know now. For this I began to use "brute force" type
conversion. Maybe I vaste CPU time but I'm sure that I have variable type
what I need for other operations.
--

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

Re: UPDATE DB

>>>>> "PV" == Petr Vileta writes:

PV> Uri Guttman wrote:
>>
>>> $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
>>
>> 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.
>>
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

--
Uri Guttman ------ uri@stemsystems.com -------- http://www.stemsystems.com
--Perl Consulting, Stem Development, Systems Architecture, Design and Coding-
Search or Offer Perl Jobs ---------------------------- http://jobs.perl.org