Insert row

Insert row

DBIx::Custom Documents >

Insert a row by insert method.

$dbi->insert({id => 1, title => 'Perl'}, table => 'book');

First argument is inserted data which is hash reference. Set table option to table name. The following SQL is executed.

insert into book (id, title) values (?, ?);

Coresponding values is embdded into place holders.

insert method's options

insert method can use all of execute method options, and use the following ones.

ctime - Created time

Set the inserted time to the specified column which mean created time.

ctime => 'created_time'

Default time format is YYYY-mm-dd HH:MM:SS. This can be changed by now attribute.

Example:

$dbi->insert({title => 'Perl}, table => 'book', ctime => 'created_time');

This is same as the following example.

use Time::Piece;
my $now_tp = localtime;
my $now = $t->strftime('%Y-%m-%d %H:%M:%S');
$dbi->insert({title => 'Perl', created_time => $now}, table => 'book');

If both ctime and mtime is set, the two times is exactly same.

id - ID

Set parameter value without column name. primary_key must be set to column names.

id => 4
id => [4, 5]

Inserted data using primary_key's column names is created.

$dbi->insert(
  {title => 'Perl', author => 'Ken'}
  table => 'book',
  primary_key => ['id1', 'id2'],
  id => [4, 5],
);

This is same as the following one.

$dbi->insert(
  {id1 => 4, id2 => 5, title => 'Perl', author => 'Ken'},
  table => 'book'
);

prefix - prefix

Add some strings between insert and into TABLE_NAME.

prefix => 'or replace'

Example:

$dbi->insert(
  {title => 'Perl'},
  table => 'book',
  prefix => 'or replace'
);

The following SQL is executed.

insert or replace into book (title) values (?);

table - Table name

Table name.

table => 'book'

mtime - updated time

Set the inserted time to the specified column which mean updated time.

mtime => 'updated_time'

Default time format is YYYY-mm-dd HH:MM:SS. This can be changed by now attribute.

Example:

$dbi->insert({title => 'Perl}, mtime => 'updated_time');

This is same as the following example.

use Time::Piece;
my $now_tp = localtime;
my $now = $t->strftime('%Y-%m-%d %H:%M:%S');
$dbi->insert({title => 'Perl', updated_time => $now}, table => 'book');

If both ctime and mtime is set, the two times is exactly same.

wrap - wrapping place holder with some string

Wrapping place holder with some string. If you want to edit place holder, use this option. Palce holder is replaced by the specified code reference.

wrap => {price => sub { "$_[0] + 5" }}

Example:

$dbi->insert(
  {id => 1, price => 100},
  table => 'book',
  wrap => {price => sub { "$_[0] + 5" }}
);

The following SQL is executed.

insert into book (id, price) values (?, ? + 5 );

Insert row using constant value

If you insert row using constant value, not place holder's value, use scalar reference.

$dbi->insert({id => 1, ctime => \"now()"}, table => 'book');

The following SQL is executed.

insert into book (id, ctime) values (?, now());