Fetching one single row from database using Perl

Question

I am trying to run a simple database connection with Perl using postgreSQL

use DBI;

$database = "postgres";
$user     = "postgres";
$password = "admin";

my $dbh = DBI->connect(  "dbi:Pg:dbname=$database"
                       , $user
                       , $password
                      )
or die "Can't Connect to database: $DBI::errstr\n";

# get data from the URL string
my $firstname = "haroon";
my $lastname ="ash";
my $age = 24;

# insert the data into the database
my $query = "INSERT INTO people (firstname, lastname, age) 
             VALUES ('$firstname','$lastname', '$age')";
$dbh->do($query);

# get the ID of the inserted person
$query = "SELECT MAX(id) FROM people";
my $sth = $dbh->prepare($query);
my $rv =$sth->execute;
if($rv < 0){
   print $DBI::errstr;
}
else {
   my $row = $sth->fetchrow_hashref; 
   my $person_id = $row->{'max'};
   print $firstname, $lastname 
       . "was successfully inserted at position " 
       . $person_id;
}

I am trying to print the person id which i had entered latest. But my $person_id = $row->{'max'} seems to give me the correct answer instead of my $person_id = $row->{'id'};. I am not understanding why is that.

Take a look at [`mysql_insert_id alternative for postgresql`](http://stackoverflow.com/q/55956/1733163) — Miller, Apr 04 '14 at 21:59

mpapec · Accepted Answer · 2014-04-04T16:51:11.273

2

You might want to set column alias for query,

$query = "SELECT MAX(id) AS id FROM people";

as postgres is putting his own alias for you, and that is max.

If all you want is last inserted id, you can

my $query = "INSERT INTO people (firstname, lastname, age) 
         VALUES (?,?,?)
         returning id
";

and fetch query as you would do with select. (check pg docs)

edited Apr 04 '14 at 16:51

answered Apr 04 '14 at 10:45

mpapec

50,217
8
67
127

@ThisSuitIsBlackNot note that this is merely a cp from OP, but here you go, updated. – mpapec Apr 04 '14 at 16:53
1

You should at least tell them that what they're doing is a bad idea and why they should use placeholders instead. I could do that in a comment but since you've already given an answer showing a query, you might as well slap a little education on the OP. – ThisSuitIsBlackNot Apr 04 '14 at 16:58
I didn't downvote, it must have been Borodin ;) But seriously, I'd be happy to remove it if you add some *explanation* about placeholders. Nothing wrong with mentioning strict and warnings as well. – ThisSuitIsBlackNot Apr 04 '14 at 17:32
@ThisSuitIsBlackNot perhaps some other time, feel free to write your own answer. – mpapec Apr 04 '14 at 18:32

score 1 · Answer 2 · answered Apr 04 '14 at 20:28

1

You can use the RETURNING keyword to return the id associated with the row you just inserted:

my $query = '
  INSERT INTO people (firstname, lastname, age)
  VALUES ($1, $2, $3)
  RETURNING id';
my $sth = $dbh->prepare($query);
$sth->execute($firstname, $lastname, $age);
my $rv = $sth->fetchrow_hashref();
printf "%s, %s was successfully inserted at position %d\n",
  $firstname, $lastname, $rv->{id};

answered Apr 04 '14 at 20:28

Dave Gray

715
5
11

Hey, what is this type of string (`$query`) called that works like a function? Can you provide some resources to learn more about it? – let me down slowly May 19 '21 at 10:40
1

That type of string contains [query placeholders](https://metacpan.org/pod/DBD::Pg#Placeholders) so you don't have to worry about escaping the contents of `$lastname` properly, since (for example) `O'Malley` can contain single quotes. – Dave Gray Jun 02 '21 at 23:04

score 0 · Answer 3 · answered Apr 04 '14 at 10:45

You did not enter a PERSON_ID in your insert statement.

Instead it seems the ID field was populated by the DB, an auto increment value during insert, it seems.

Without knowing the table definition (and potential insert trigger on the table) it is hard/impossible to give you a better answer.

Fetching one single row from database using Perl

3 Answers3