execute method Execute SQL

DBIx::Custom
here

Use the execute method to execute the SQL.

# Execute SQL
my $result = $dbi->execute("select * from book");

The return value is a DBIx::Custom::Result object. Use the all method to get all the rows.

# Fetch all rows
my $rows = $result->all;

Named placeholder

DBIx::Custom supports named placeholders on all RDBMSs, such as those supported by Oracle.

#Named placeholder
my $result = $dbi->execute("select * from book where title =: title",
  {title =>'Perl'});

Named placeholders start with a colon, such as ": title". The value to be embedded can be specified by the hash reference. This has the same meaning as the following executable statement.

my $title ='Perl';
my $sql = "select * from book where title =?";
my $dbh = DBI->connect(...);
my $sth = $dbh->prepare($sql);
$sth->execute($title);

Named placeholders are very useful because you don't have to worry about the order of the parameter values.

If you want to use a named placeholder with the same name

You may have used the same name in a named placeholder. For example, if you want to specify a start date and an end date. In such a case, write as follows.

#Use named placeholder more than once
my $result = $dbi->execute(
  "select * from book where date>: date and date <: date",
  {date => [3, 5]});

If you want to pass multiple values, specify the parameter value as an array reference, such as "date => [3, 5]".

Abbreviated notation for named placeholders

Named placeholders often repeat the same name twice, such as "title =: title", so a shorthand notation is provided.

# Placeholder abbreviation
: Placeholder name {operator}

For example, ": title {=}" has the same meaning as "title =: title". It can be used with other operators as well.

#Named placeholder suger syntax
: title {like}->titlelike: title
: title {<}->title<: title
: title {>}->title>: title

By using this, the execute sample introduced at the beginning can be rewritten as follows.

#Named placeholder suger syntax example
my $result = $dbi->execute("select * from book where: title {=}",
  {title =>'Perl'});

Use column name including table name

You can also use the column name including the table name as the placeholder name.

my $result = $dbi->execute("select * from book where: book.title {=}",
  {'book.title'=>'Perl'});

execute method options

You can specify various options for DBIx::Custom. It can be described as follows using the third and subsequent arguments.

$dbi->execute($sql, $param, option name => option value, ...)

execute method options

A list of options for the execute method.

SQL processing after_build_sql

After_build_sql can be used to process SQL after it has been built. The value specifies a code reference for the conversion.

after_build_sql => $code_ref => $code_ref

The following is an example. This is a sample to get the number of executed SQL columns.

$dbi->select(
  table =>'book',
  column =>'distinct (name)',
  after_build_sql => sub {
    "select count (*) from ($_ [0]) as t1"
  }
);

The following SQL is executed:

<pre>
select count (*) from (select distinct (name) from book) as t1;

Add to the end of SQL append

You can use the append option to add a statement to the end of the SQL.

append =>'order by name'

Specify bind type bind_type

If the data you are inserting is binary data, you may need to explicitly specify that. For example, in SQLite, if you want to insert BLOB type data, you need to write as follows when using DBI.

$sth->bind_param($pos, $value, DBI::SQL_BLOB);

Use the bind_type option to do the same with the execute method.

#Bind type option
bind_type => {image => DBI::SQL_BLOB}
bind_type => [image => DBI::SQL_BLOB]
bind_type => [[qw / image audio /] => DBI::SQL_BLOB]

The value of bind_type is specified by the hash reference. You can also use an array reference if you want to specify the same value for multiple columns (third method).

Specifying a filter filter

You can use the filter option to filter the value of the parameter before embedding it in the placeholder.

#Filter option
filter => {title => sub {uc $_ [0]}}
filter => {title =>'upper_case'}
filter => [[qw / title author /] => sub {uc $_ [0]}]

The filter is described in the format "{column name => filter (subroutine or filter name)}". If you use it with a filter name, you need to register the filter in advance using the register_filter method.

If you want to use the same filter for multiple column names, you can also specify it using the array reference (third method).

ID specification id

If you specify the id option, you can specify the parameter without specifying the column name when primary_key is set.

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

For example, if [id1, id2] is set in primary_key, id => [4, 5] specifies {id1 => 4, id2 => 5} in the parameter that is the second argument of execute. Will be the same as.

$dbi->execute(
  "select * from book where id1 =: id1 and id2 =: id2",
  {},
  primary_key => ['id1','id2'],
  id => [4, 5],
);

The above execute is the same as below.

$dbi->execute(
  "select * from book where id1 =: id1 and id2 =: id2",
  {id1 => 4, id2 => 5}
);

Get query object query

If you set the query option to 1, you can get the DBIx::Custom::Query object as the return value instead of executing the SQL.

query => 1

The DBIx::Custom::Query object is useful for finding out what kind of query is actually being executed.

my $sql = $query->sql;
my $columns = $query->columns;

Passing the query object to the execute method is a deprecated feature and should not be used. This was a potentially very buggy implementation. Use the reuse option instead to increase execution speed.

Primary key settings primary_key

Set the primary_key used in the id option.

primary_key =>'id'
primary_key => ['id1','id2']

Query reuse reuse

When the reuse option is used, the same SQL is executed consecutively.It has been confirmed that the execution speed can be increased by 2 to 3 times. Specify a hash reference as an argument to save the query.

reuse => $hash_ref

Below is a sample.

my $queries = {};
for my $param (@params) {
  $dbi->execute($sql, $row, reuse => $queries);
}

Of course you can also use it with the insert, update, delete and select methods.

my $queries = {};
for my $row (@rows) {
  $dbi->insert($row, table =>'book', reuse => $queries);
}

table name table

You can specify the table name with the table option.

table =>'author'

Generally, the execute method executes the specified SQL, so there is no need to specify the table option.

The table option has the following effects: If the column name in the named placeholder is not given as a fully qualified name, the column name will be fully qualified with the specified table name. The column names used in the parameters are also fully qualified.

$dbi->execute("select * from book where title =: title and author =: author",
  {title =>'Perl', author =>'Ken'}, table =>'book');

The above execute has the same meaning as the following.

$dbi->execute(
  "select * from book where title =: book.title and author =: book.author",
  {'book.title' =>'Perl','book.author' =>'Ken'});

The table option is to be used when into1 or into2 of type_rule is set. This is because it is not possible to determine which table column name it is unless it is a fully qualified name, so filtering by type_rule does not work.

Therefore, you need to be aware of it only when you are setting into1 or into2 in type_rule.

Table aliases table_alias

You can set table aliases with the table_alias option.

table_alias => {worker =>'user'} # {alias name => table name}

The key is the alias name and the value is the table name. If you set an alias for the table, the filtering effect of into1 and into2 specified by the C method will also work with the alias name. ..

In this example, the C and C filters set on the columns of the user table will also be valid for the columns of the worker table.

Disable type rules type_rule_off

You can use the type_rule_off option to disable the into1 and into2 filters set in type_rule.

type_rule_off => 1

It has no effect on the from1 and from2 filters.

Disable type rule 1 type_rule1_off

You can use the type_rule1_off option to disable the into1 filter set in type_rule.

type_rule1_off => 1

It has no effect on the from1 and from2 filters.

Disable type rule 2 type_rule2_off

You can use the type_rule2_off option to disable the into2 filter set in type_rule.

type_rule2_off => 1

It has no effect on the from1 and from2 filters.

DBIx::Custom