Database INSERT Operation in Perl


Perl INSERT operation is required when you want to create some records into a table. Here we are using table TEST_TABLE to create our records. So once our database connection is established, we are ready to create records into TEST_TABLE. Following is the procedure to create single record into TEST_TABLE. You can create as many as records you like using the same concept.

Record creation takes the following steps −

  • Preparing SQL statement with INSERT statement. This will be done using prepare() API.
  • Executing SQL query to select all the results from the database. This will be done using execute() API.
  • Releasing Stattement handle. This will be done using finish() API.
  • If everything goes fine then commit this operation otherwise you can rollback complete transaction. Commit and Rollback are explained in next sections.
my $sth = $dbh->prepare("INSERT INTO TEST_TABLE
   (FIRST_NAME, LAST_NAME, SEX, AGE, INCOME )
   values
   ('john', 'poul', 'M', 30, 13000)");
$sth->execute() or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Using Bind Values

There may be a case when values to be entered is not given in advance. So you can use bind variables which will take the required values at run time. Perl DBI modules make use of a question mark in place of actual value and then actual values are passed through execute() API at the run time. Following is the example −

my $first_name = "john";
my $last_name = "poul";
my $sex = "M";
my $income = 13000;
my $age = 30;
my $sth = $dbh->prepare("INSERT INTO TEST_TABLE
   (FIRST_NAME, LAST_NAME, SEX, AGE, INCOME )
   values
   (?,?,?,?)");
$sth->execute($first_name,$last_name,$sex, $age, $income)
or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;
raja
Published on 02-Dec-2019 07:48:46
Advertisements