Can't delete postgresql table record using Perl

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Can't delete postgresql table record using Perl

barry kimelman
Hello,

my laptop is windows 10 64-bit.
my Perl is activestate 5.28.1
my postgresql is 12.1

everything I do under psql works just fine. Now I have been writing some Perl scripts. The Perl scripts to display data work very well. However the Perl script I wrote to delete a record completes with no error yet when I look at the database table the record is still in there.

     1 #!C:\Perl64\bin\perl.exe -w
     2
     3 use strict;
     4 use warnings;
     5 use DBI;
     6 use FindBin;
     7 use lib $FindBin::Bin;
     8
     9 my ( $status , $dbh  , $database , $username , $password , $host );
    10 my ( $sql , $port , $num_rows );
    11
    12 $database = 'mydatabase';
    13 $username = 'myusername';
    14 $password = 'mypassword';
    15 $host = "127.0.0.1";
    16 $port = 5432;
    17
    18 $dbh = DBI->connect("dbi:Pg:dbname=$database;host=$host;port=$port",
    19                          $username,
    20                          $password,
    21                          {AutoCommit => 0, RaiseError => 1, PrintError => 0}
    22                         );
    23 unless ( defined $dbh ) {
    24 die("Error connecting to '${database}' on '${host}'  :\n$DBI::errstr\n ");
    25 } # UNLESS
    26
    27 $sql = "delete from laptop where name = 'Lenovo ThinkPad P71'";
    28 print "SQL for delete operation is\n$sql\n";
    29 $num_rows = $dbh->do($sql);
    30 unless ( defined $num_rows ) {
    31 print "error executing : ${sql}\n",$DBI::errstr,"\n";
    32 } # UNLESS
    33 else {
    34 print "${num_rows} rows were successfully deleted\n";
    35 } # ELSE
    36
    37 $dbh->disconnect; # disconnect from databse
    38
    39 exit 0;

Any idea as to why the record is still in the table ?

Thanks.
--

Barrry Kimelman
Winnipeg, Manitoba, Canada
Reply | Threaded
Open this post in threaded view
|

Re: Can't delete postgresql table record using Perl

David G Johnston
On Monday, February 17, 2020, barry kimelman <[hidden email]> wrote:

    21                          {AutoCommit => 0, RaiseError => 1, PrintError => 0}
   

Any idea as to why the record is still in the table 

AutoCommit is disabled and you never issued a commit?

David J. 
Reply | Threaded
Open this post in threaded view
|

RE: Can't delete postgresql table record using Perl

Aleksey M Boltenkov
In reply to this post by barry kimelman
You must do $dbh->commit() before $dbh->disconnect or use AutoCommit => 1.


Aleksey M Boltenkov.

17.02.2020, 19:11, barry kimelman <[hidden email]>
Hello,

my laptop is windows 10 64-bit.
my Perl is activestate 5.28.1
my postgresql is 12.1

everything I do under psql works just fine. Now I have been writing some Perl scripts. The Perl scripts to display data work very well. However the Perl script I wrote to delete a record completes with no error yet when I look at the database table the record is still in there.

     1 #!C:\Perl64\bin\perl.exe -w
     2
     3 use strict;
     4 use warnings;
     5 use DBI;
     6 use FindBin;
     7 use lib $FindBin::Bin;
     8
     9 my ( $status , $dbh  , $database , $username , $password , $host );
    10 my ( $sql , $port , $num_rows );
    11
    12 $database = 'mydatabase';
    13 $username = 'myusername';
    14 $password = 'mypassword';
    15 $host = "127.0.0.1";
    16 $port = 5432;
    17
    18 $dbh = DBI->connect("dbi:Pg:dbname=$database;host=$host;port=$port",
    19                          $username,
    20                          $password,
    21                          {AutoCommit => 0, RaiseError => 1, PrintError => 0}
    22                         );
    23 unless ( defined $dbh ) {
    24 die("Error connecting to '${database}' on '${host}'  :\n$DBI::errstr\n ");
    25 } # UNLESS
    26
    27 $sql = "delete from laptop where name = 'Lenovo ThinkPad P71'";
    28 print "SQL for delete operation is\n$sql\n";
    29 $num_rows = $dbh->do($sql);
    30 unless ( defined $num_rows ) {
    31 print "error executing : ${sql}\n",$DBI::errstr,"\n";
    32 } # UNLESS
    33 else {
    34 print "${num_rows} rows were successfully deleted\n";
    35 } # ELSE
    36
    37 $dbh->disconnect; # disconnect from databse
    38
    39 exit 0;

Any idea as to why the record is still in the table ?

Thanks.
--

Barrry Kimelman
Winnipeg, Manitoba, Canada
Reply | Threaded
Open this post in threaded view
|

Re: Can't delete postgresql table record using Perl

barry kimelman
Must be a lack of caffeine to make such an error  😐

The delete seems to be working ok now. I just need to be more careful when I cut and paste example code.

On Mon, Feb 17, 2020 at 10:36 AM Aleksey M Boltenkov <[hidden email]> wrote:
You must do $dbh->commit() before $dbh->disconnect or use AutoCommit => 1.


Aleksey M Boltenkov.

17.02.2020, 19:11, barry kimelman <[hidden email]>
Hello,

my laptop is windows 10 64-bit.
my Perl is activestate 5.28.1
my postgresql is 12.1

everything I do under psql works just fine. Now I have been writing some Perl scripts. The Perl scripts to display data work very well. However the Perl script I wrote to delete a record completes with no error yet when I look at the database table the record is still in there.

     1 #!C:\Perl64\bin\perl.exe -w
     2
     3 use strict;
     4 use warnings;
     5 use DBI;
     6 use FindBin;
     7 use lib $FindBin::Bin;
     8
     9 my ( $status , $dbh  , $database , $username , $password , $host );
    10 my ( $sql , $port , $num_rows );
    11
    12 $database = 'mydatabase';
    13 $username = 'myusername';
    14 $password = 'mypassword';
    15 $host = "127.0.0.1";
    16 $port = 5432;
    17
    18 $dbh = DBI->connect("dbi:Pg:dbname=$database;host=$host;port=$port",
    19                          $username,
    20                          $password,
    21                          {AutoCommit => 0, RaiseError => 1, PrintError => 0}
    22                         );
    23 unless ( defined $dbh ) {
    24 die("Error connecting to '${database}' on '${host}'  :\n$DBI::errstr\n ");
    25 } # UNLESS
    26
    27 $sql = "delete from laptop where name = 'Lenovo ThinkPad P71'";
    28 print "SQL for delete operation is\n$sql\n";
    29 $num_rows = $dbh->do($sql);
    30 unless ( defined $num_rows ) {
    31 print "error executing : ${sql}\n",$DBI::errstr,"\n";
    32 } # UNLESS
    33 else {
    34 print "${num_rows} rows were successfully deleted\n";
    35 } # ELSE
    36
    37 $dbh->disconnect; # disconnect from databse
    38
    39 exit 0;

Any idea as to why the record is still in the table ?

Thanks.
--

Barrry Kimelman
Winnipeg, Manitoba, Canada


--

Barrry Kimelman
Winnipeg, Manitoba, Canada