CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
Problem with update: Inserting new line after the altered value

 



zak100
User

Aug 16, 2014, 11:15 AM

Post #1 of 10 (1073 views)
Problem with update: Inserting new line after the altered value Can't Post

Hi,
I am trying to use update. I have written a code. It is altering the value but it is inserting a newline in the output. I am trying to alter the 'name varchar(128)' field. I have tried chop but its not working. My code is:


Code
 
#!C:/Perl64/bin/perl.exe
use DBI;
use strict;
use warnings;
my $dbh;
my $sql;
my $sth;
my @row;
my $Id;
my $name;
#out of range error

$dbh=DBI->connect('dbi:mysql:testdb','root','zulfi') or die "connection error: $DBI::errstr\n";
$sql = "Create table if not exists subscribers(Id int(10) not null auto_increment , Name varchar(128) , email varchar(128) , Primary key(id))";
$sth = $dbh->prepare($sql);
$sth->execute () or die("Cannot create table: " . $dbh->errstr);

$sql = "insert into subscribers ( Name, email) values ( ?, ?) " ;

# prepare your statement for connecting to the database
$sth = $dbh->prepare($sql);

# execute your SQL statement
$sth->execute( 'zak1', "zak1\@foo.com") or die("Cannot create table: " . $dbh->errstr);


#inserting 2nd record
$sth->execute( 'zak2', "zak2\@foo.com") or die("Cannot create table: " . $dbh->errstr);

#inserting 2nd record
$sth->execute( 'zak3', "zak3\@foo.com") or die("Cannot create table: " . $dbh->errstr);

$sql="select * from subscribers";
$sth=$dbh->prepare($sql);
$sth->execute or die "SQL error $DBI::errstr \n";

while (@row=$sth->fetchrow_array)
{
print "@row\n";
}


#//Update table
print "input ID whose data is to be updated";
$Id = <STDIN>;
print "input new value of name";
$name = <stdin>;
#$name = chop($name);
$sql = "UPDATE subscribers SET Name='$name' WHERE Id=$Id";
$sth = $dbh->prepare($sql) or die "Cannot prepare: " . $dbh->errstr();
$sth->execute() or die "Cannot execute: " . $sth->errstr();

$sql="select * from subscribers";
$sth=$dbh->prepare($sql);
$sth->execute or die "SQL error $DBI::errstr \n";

while (@row=$sth->fetchrow_array)
{
print "@row\n";
}


The output is:

Code
D:\Zulfi\PERL MAIN FOLDER\mysql LabProg\Lab10>perl update_selectdb.pl 
4 zak1 zak1@foo.com
5 zak2 zak2@foo.com
6 zak3 zak3@foo.com
7 zak1 zak1@foo.com
8 zak2 zak2@foo.com
9 zak3 zak3@foo.com
10 zak1 zak1@foo.com
11 zak2 zak2@foo.com
12 zak3 zak3@foo.com
13 zak1 zak1@foo.com
14 zak2 zak2@foo.com
15 zak3 zak3@foo.com
16 zak1 zak1@foo.com
17 zak2 zak2@foo.com
18 zak3 zak3@foo.com
19 zak1 zak1@foo.com
20 zak2 zak2@foo.com
21 zak3 zak3@foo.com
input ID whose data is to be updated4
input new value of nameZulfiqar
4 Zulfiqar
zak1@foo.com
5 zak2 zak2@foo.com
6 zak3 zak3@foo.com
7 zak1 zak1@foo.com
8 zak2 zak2@foo.com
9 zak3 zak3@foo.com
10 zak1 zak1@foo.com
11 zak2 zak2@foo.com
12 zak3 zak3@foo.com
13 zak1 zak1@foo.com
14 zak2 zak2@foo.com
15 zak3 zak3@foo.com
16 zak1 zak1@foo.com
17 zak2 zak2@foo.com
18 zak3 zak3@foo.com
19 zak1 zak1@foo.com
20 zak2 zak2@foo.com
21 zak3 zak3@foo.com

D:\Zulfi\PERL MAIN FOLDER\mysql LabProg\Lab10>select * from subscriber;

The database output is:

Code
mysql> select * from subscribers; 
+----+-----------+--------------+
| Id | Name | email |
+----+-----------+--------------+
| 4 | Zulfiqar
| zak1@foo.com |
| 5 | zak2 | zak2@foo.com |
| 6 | zak3 | zak3@foo.com |
| 7 | zak1 | zak1@foo.com |
| 8 | zak2 | zak2@foo.com |
| 9 | zak3 | zak3@foo.com |
| 10 | zak1 | zak1@foo.com |
| 11 | zak2 | zak2@foo.com |
| 12 | zak3 | zak3@foo.com |
| 13 | zak1 | zak1@foo.com |
| 14 | zak2 | zak2@foo.com |
| 15 | zak3 | zak3@foo.com |
| 16 | zak1 | zak1@foo.com |
| 17 | zak2 | zak2@foo.com |
| 18 | zak3 | zak3@foo.com |
| 19 | zak1 | zak1@foo.com |
| 20 | zak2 | zak2@foo.com |
| 21 | zak3 | zak3@foo.com |
+----+-----------+--------------+
18 rows in set (0.00 sec)

mysql>


Somebody please guide me with this problem.

Zulfi.


FishMonger
Veteran / Moderator

Aug 16, 2014, 11:40 AM

Post #2 of 10 (1069 views)
Re: [zak100] Problem with update: Inserting new line after the altered value [In reply to] Can't Post

You need to use chomp NOT chop to remove the line terminator. On Windows the default line terminator is "\r\n". The chop function removes the last character i.e., "\n". The chomp function will remove both characters.


zak100
User

Aug 16, 2014, 8:52 PM

Post #3 of 10 (1058 views)
Re: [FishMonger] Problem with update: Inserting new line after the altered value [In reply to] Can't Post

Hi,
Thanks for your help. Its working now. The code is after changes:

Code
#!C:/Perl64/bin/perl.exe 
use DBI;
use strict;
use warnings;
my $dbh;
my $sql;
my $sth;
my @row;
my $Id;
my $name;
#out of range error

$dbh=DBI->connect('dbi:mysql:testdb','root','zulfi') or die "connection error: $DBI::errstr\n";
$sql = "Create table if not exists subscribers(Id int(10) not null auto_increment , Name varchar(128) , email varchar(128) , Primary key(id))";
$sth = $dbh->prepare($sql);
$sth->execute () or die("Cannot create table: " . $dbh->errstr);

$sql = "insert into subscribers ( Name, email) values ( ?, ?) " ;

# prepare your statement for connecting to the database
$sth = $dbh->prepare($sql);

# execute your SQL statement
$sth->execute( 'zak1', "zak1\@foo.com") or die("Cannot create table: " . $dbh->errstr);


#inserting 2nd record
$sth->execute( 'zak2', "zak2\@foo.com") or die("Cannot create table: " . $dbh->errstr);

#inserting 2nd record
$sth->execute( 'zak3', "zak3\@foo.com") or die("Cannot create table: " . $dbh->errstr);

$sql="select * from subscribers";
$sth=$dbh->prepare($sql);
$sth->execute or die "SQL error $DBI::errstr \n";

while (@row=$sth->fetchrow_array)
{
print "@row\n";
}


#//Update table
print "input ID whose data is to be updated";
$Id = <STDIN>;
print "input new value of name";
$name = <stdin>;
chomp($name);
$sql = "UPDATE subscribers SET Name='$name' WHERE Id=$Id";
$sth = $dbh->prepare($sql) or die "Cannot prepare: " . $dbh->errstr();
$sth->execute() or die "Cannot execute: " . $sth->errstr();

$sql="select * from subscribers";
$sth=$dbh->prepare($sql);
$sth->execute or die "SQL error $DBI::errstr \n";

while (@row=$sth->fetchrow_array)
{
print "@row\n";
}


The output is:

Code
mysql> select * from subscribers; 
+----+----------+--------------+
| Id | Name | email |
+----+----------+--------------+
| 4 | Zulfiqar | zak1@foo.com |
| 5 | zak2 | zak2@foo.com |
| 6 | zak3 | zak3@foo.com |
| 7 | zak1 | zak1@foo.com |
| 8 | zak2 | zak2@foo.com |
| 9 | zak3 | zak3@foo.com |
| 10 | zak1 | zak1@foo.com |
| 11 | zak2 | zak2@foo.com |
| 12 | zak3 | zak3@foo.com |
| 13 | zak1 | zak1@foo.com |
| 14 | zak2 | zak2@foo.com |
| 15 | zak3 | zak3@foo.com |
| 16 | zak1 | zak1@foo.com |
| 17 | zak2 | zak2@foo.com |
| 18 | zak3 | zak3@foo.com |
| 19 | zak1 | zak1@foo.com |
| 20 | zak2 | zak2@foo.com |
| 21 | zak3 | zak3@foo.com |
| 22 | zak1 | zak1@foo.com |
| 23 | zak2 | zak2@foo.com |
| 24 | zak3 | zak3@foo.com |
| 25 | zak1 | zak1@foo.com |
| 26 | zak2 | zak2@foo.com |
| 27 | zak3 | zak3@foo.com |
| 28 | zak1 | zak1@foo.com |
| 29 | zak2 | zak2@foo.com |
| 30 | zak3 | zak3@foo.com |
+----+----------+--------------+
27 rows in set (0.00 sec)


Zulfi.


FishMonger
Veteran / Moderator

Aug 17, 2014, 8:47 AM

Post #4 of 10 (1043 views)
Re: [zak100] Problem with update: Inserting new line after the altered value [In reply to] Can't Post


Quote
Thanks for your help. Its working now.


That's great.

Now, you just need to do some code clean-up to fix the formatting, sql error messages, and some of the var declarations.


zak100
User

Aug 19, 2014, 8:43 AM

Post #5 of 10 (882 views)
Re: [FishMonger] Problem with update: Inserting new line after the altered value [In reply to] Can't Post

Hi,
Thanks. I am interested in formatting. Please provide me an example link.

Zulfi.


FishMonger
Veteran / Moderator

Aug 19, 2014, 9:19 AM

Post #6 of 10 (879 views)
Re: [zak100] Problem with update: Inserting new line after the altered value [In reply to] Can't Post

Perl Best Practices
Standards and Styles for Developing Maintainable Code
http://shop.oreilly.com/product/9780596001735.do
This book is a little old but still very relevant with good advise and examples.


Modern Perl Programming
Perl programming for the modern Perl programmer.
http://modernperlbooks.com/mt/index.html

Those books cover more than just proper formatting. They cover a wide range of coding practices.

As to your formatting, here is a rework of the db connection and first sql statement where I added RaiseError to handle the error handling and added vertical & horizontal white space for better readability and maintainability.


Code
my $dbh = DBI->connect('dbi:mysql:testdb', 'root', 'zulfi', 
{RaiseError => 1})
or die "connection error: $DBI::errstr\n";

my $sql = <<'CREATE';
Create table if not exists subscribers
(
Id int(10) not null auto_increment,
Name varchar(128),
email varchar(128),
Primary key(id)
)
CREATE

my $sth = $dbh->prepare($sql);
$sth->execute;



(This post was edited by FishMonger on Aug 19, 2014, 9:21 AM)


zak100
User

Aug 27, 2014, 11:03 AM

Post #7 of 10 (371 views)
Re: [FishMonger] Problem with update: Inserting new line after the altered value [In reply to] Can't Post

Hi,
I have done some formatting but its only related to coding. Its not related to indentation. Please guide me if i am right or not. However now update is also not working:

Code
D:\Zulfi\PERL MAIN FOLDER\mysql LabProg\Lab10>perl updateformatted.pl 
1 zak1 zak1@foo.com
2 zak2 zak2@foo.com
3 zak3 zak3@foo.com
4 zak1 zak1@foo.com
5 zak2 zak2@foo.com
6 zak3 zak3@foo.com
input ID whose data is to be updated3
input new value of namezak333
1 zak1 zak1@foo.com
2 zak2 zak2@foo.com
3 zak3 zak3@foo.com
4 zak1 zak1@foo.com
5 zak2 zak2@foo.com
6 zak3 zak3@foo.com

D:\Zulfi\PERL MAIN FOLDER\mysql LabProg\Lab10>


Please guide me with problem in my code which is given below:

Code
 
#!C:/Perl64/bin/perl.exe
use DBI;
use strict;
use warnings;

my $sql;
my $sth;
my @row;
my $Id;
my $name;
#out of range error

my $dbh = DBI->connect('dbi:mysql:testdb', 'root', 'zulfi12345',
{RaiseError => 1})
or die "connection error: $DBI::errstr\n";

$sql = <<'CREATE';
Create table if not exists subscribers
(
Id int(10) not null auto_increment,
Name varchar(128),
email varchar(128),
Primary key(id)
)
CREATE

$sth = $dbh->prepare($sql);
$sth->execute () or die("Cannot create table: " . $dbh->errstr);

$sql = <<'INSERT';

insert into subscribers ( Name, email) values ( ?, ?)
INSERT

$sth = $dbh->prepare($sql);
$sth->execute( 'zak1', "zak1\@foo.com") or die("Cannot create table: " . $dbh->errstr);
$sth->execute( 'zak2', "zak2\@foo.com") or die("Cannot create table: " . $dbh->errstr);
$sth->execute( 'zak3', "zak3\@foo.com") or die("Cannot create table: " . $dbh->errstr);

$sql = <<'SELECT';
select * from subscribers
SELECT
$sth=$dbh->prepare($sql);
$sth->execute or die "SQL error $DBI::errstr \n";

while (@row=$sth->fetchrow_array)
{
print "@row\n";
}


#//Update table
print "input ID whose data is to be updated";
$Id = <STDIN>;
print "input new value of name";
$name = <stdin>;
chomp($name);

$sql = <<'UPDATE';
UPDATE subscribers SET Name='$name' WHERE Id='$Id';
UPDATE

$sth = $dbh->prepare($sql) or die "Cannot prepare: " . $dbh->errstr();
$sth->execute() or die "Cannot execute: " . $sth->errstr();

$sql = <<'SELECT';
select * from subscribers
SELECT
$sth=$dbh->prepare($sql);
$sth->execute or die "SQL error $DBI::errstr \n";

while (@row=$sth->fetchrow_array)
{
print "@row\n";
}


Zulfi.


FishMonger
Veteran / Moderator

Aug 27, 2014, 11:40 AM

Post #8 of 10 (365 views)
Re: [zak100] Problem with update: Inserting new line after the altered value [In reply to] Can't Post


Quote
$sql = <<'UPDATE';


The single quotes will prevent variable interpolation which means that your $name and $id in that here doc will be seen as verbatim rather than being interpolated and having their values put there. Either change them to double quotes or simply remove them since the default will enable var interpolation.

Using a here doc for a single short line like that doesn't make any sense. Just do a normal 1 line var assignment. The here doc comes in handy when you have a multi-line string.


zak100
User

Aug 29, 2014, 11:16 AM

Post #9 of 10 (309 views)
Re: [FishMonger] Problem with update: Inserting new line after the altered value [In reply to] Can't Post

Hi,
Thanks for giving time to me. I have tried with out quotes and also with double quotes (in here doc with Update )but its not working. The code is:


Code
#!C:/Perl64/bin/perl.exe 
use DBI;
use strict;
use warnings;

my $sql;
my $sth;
my @row;
my $Id;
my $name;
#out of range error

my $dbh = DBI->connect('dbi:mysql:testdb', 'root', 'zulfi12345',
{RaiseError => 1})
or die "connection error: $DBI::errstr\n";

$sql = <<'CREATE';
Create table if not exists subscribers
(
Id int(10) not null auto_increment,
Name varchar(128),
email varchar(128),
Primary key(id)
)
CREATE

$sth = $dbh->prepare($sql);
$sth->execute () or die("Cannot create table: " . $dbh->errstr);

$sql = <<'INSERT';

insert into subscribers
( Name, email)
values ( ?, ?)
INSERT

$sth = $dbh->prepare($sql);
$sth->execute( 'zak1', "zak1\@foo.com") or die("Cannot create table: " . $dbh->errstr);
$sth->execute( 'zak2', "zak2\@foo.com") or die("Cannot create table: " . $dbh->errstr);
$sth->execute( 'zak3', "zak3\@foo.com") or die("Cannot create table: " . $dbh->errstr);

$sql = <<'SELECT';
select * from
subscribers
SELECT

$sth=$dbh->prepare($sql);
$sth->execute or die "SQL error $DBI::errstr \n";

while (@row=$sth->fetchrow_array)
{
print "@row\n";
}


#//Update table
print "input ID whose data is to be updated";
$Id = <STDIN>;
print "input new value of name";
$name = <stdin>;
chomp($name);

$sql = <<"UPDATE";
UPDATE subscribers SET Name=$name
WHERE Id=$Id;
UPDATE

$sth = $dbh->prepare($sql) or die "Cannot prepare: " . $dbh->errstr();
$sth->execute() or die "Cannot execute: " . $sth->errstr();

$sql = <<'SELECT';
select * from subscribers
SELECT
$sth=$dbh->prepare($sql);
$sth->execute or die "SQL error $DBI::errstr \n";

while (@row=$sth->fetchrow_array)
{
print "@row\n";
}


I am getting following errors:

Code
D:\Zulfi\PERL MAIN FOLDER\mysql LabProg\Lab10>perl updateformatted.pl 
1 zak1 zak1@foo.com
2 zak2 zak2@foo.com
3 zak3 zak3@foo.com
4 zak1 zak1@foo.com
5 zak2 zak2@foo.com
6 zak3 zak3@foo.com
7 zak1 zak1@foo.com
8 zak2 zak2@foo.com
9 zak3 zak3@foo.com
10 zak1 zak1@foo.com
11 zak2 zak2@foo.com
12 zak3 zak3@foo.com
13 zak1 zak1@foo.com
14 zak2 zak2@foo.com
15 zak3 zak3@foo.com
input ID whose data is to be updated3
input new value of namevvvvvvvvv
DBD::mysql::st execute failed: Unknown column 'vvvvvvvvv' in 'field list' at upd
ateformatted.pl line 68, <stdin> line 2.
DBD::mysql::st execute failed: Unknown column 'vvvvvvvvv' in 'field list' at upd
ateformatted.pl line 68, <stdin> line 2.

D:\Zulfi\PERL MAIN FOLDER\mysql LabProg\Lab10>

Plzz guide me.


(This post was edited by zak100 on Aug 29, 2014, 11:19 AM)


FishMonger
Veteran / Moderator

Aug 29, 2014, 12:31 PM

Post #10 of 10 (303 views)
Re: [zak100] Problem with update: Inserting new line after the altered value [In reply to] Can't Post

You forgot to chomp $Id

You also forgot to put quotes around $name in the update statement which will cause an error.

You should be using placeholders in those insert and update statements. Especially when the data is coming from the user.

Since you're enabling RaiseError in the connect statement, you don't need any of the die statements on the prepare and execute statements; that's what RaiseError handles for you.


(This post was edited by FishMonger on Aug 29, 2014, 12:32 PM)

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives