NAME

foo.db

  use DBI;
  my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","");
  my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","");
  my $dbh = DBI->connect("dbi:SQLite:uri=file:$path_to_dbfile?mode=rwc");
  use DBD::SQLite::Constants qw/:file_open/;
  my $dbh = DBI->connect("dbi:SQLite:$dbfile", undef, undef, {
    sqlite_open_flags => SQLITE_OPEN_READONLY,
  });
  ($fh, $filename) = tempfile($template, EXLOCK => 0);
  dbish dbi:SQLite:foo.db
  use DBI qw(:sql_types);
  my $dbh = DBI->connect("dbi:SQLite:dbfile","","");

  my $blob = `cat foo.jpg`;
  my $sth = $dbh->prepare("INSERT INTO mytable VALUES (1, ?)");
  $sth->bind_param(1, $blob, SQL_BLOB);
  $sth->execute();
  $sth = $dbh->prepare("SELECT * FROM mytable WHERE id = 1");
  $sth->execute();
  my $row = $sth->fetch;
  my $blobo = $row->[1];

  # now $blobo == $blob
  my $sth = $dbh->prepare(q{
    SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?;
  });
  $sth->execute(5);
  SELECT bar FROM foo GROUP BY bar HAVING count(*) > "5";
  use DBI qw(:sql_types);  # Don't forget this

  my $sth = $dbh->prepare(q{
    SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?;
  });
  $sth->bind_param(1, 5, SQL_INTEGER);
  $sth->execute();
  my $sth = $dbh->prepare(q{
    SELECT bar FROM foo GROUP BY bar HAVING count(*) > (? + 0);
  });
  $sth->execute(5);
  my $sth = $dbh->prepare(q{
    SELECT bar FROM foo GROUP BY bar HAVING count(*) > cast(? as integer);
  });
  $sth->execute(5);
  $dbh->{sqlite_see_if_its_a_number} = 1;
  my $sth = $dbh->prepare(q{
    SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?;
  });
  $sth->execute(5);
  my $dbh = DBI->connect('dbi:SQLite:foo', undef, undef, {
    AutoCommit => 1,
    RaiseError => 1,
    sqlite_see_if_its_a_number => 1,
  });
  my $dbh = DBI->connect('dbi:SQLite:foo', undef, undef, {
    AutoCommit => 1,
    RaiseError => 1,
    sqlite_see_if_its_a_number => 1,
  });
  my $sth = $dbh->prepare('INSERT INTO foo VALUES(?)');
  # '1.230' will be inserted as a text, instead of 1.23 as a number,
  # even though sqlite_see_if_its_a_number is set.
  $sth->bind_param(1, '1.230', SQL_VARCHAR);
  $sth->execute;
  my $sth = $dbh->prepare(
    'update TABLE set a=?1 where b=?2 and a IS NOT ?1'
  );
  $sth->execute(1, 2);
  $dbh->do("PRAGMA foreign_keys = ON");
  $dbh->do("PRAGMA foreign_keys = OFF");
  $dbh->{AutoCommit} = 1;

  $dbh->begin_work; # or $dbh->do('BEGIN TRANSACTION');

  # $dbh->{AutoCommit} is turned off temporarily during a transaction;

  $dbh->commit; # or $dbh->do('COMMIT');

  # $dbh->{AutoCommit} is turned on again;
  $dbh->{AutoCommit} = 0;

  # $dbh->do('BEGIN TRANSACTION') is not necessary, but possible

  ...

  $dbh->commit; # or $dbh->do('COMMIT');

  # $dbh->{AutoCommit} stays intact;

  $dbh->{AutoCommit} = 1;  # ends the transactional mode
  my $dbh = DBI->connect("dbi:SQLite::memory:", "", "", {
    sqlite_use_immediate_transaction => 0,
  });
  $sth = $dbh->prepare("SELECT * FROM t");
  $dbh->begin_work;
  eval {
      $sth->execute;
      $row = $sth->fetch;
      ...
      die "For some reason";
      ...
  };
  if($@) {
     $sth->finish;  # You need this for SQLite
     $dbh->rollback;
  } else {
     $dbh->commit;
  }
  SELECT url, count(*) as count
  FROM access_log
  GROUP BY url
  ORDER BY count desc
  LIMIT 20
  PRAGMA synchronous = OFF
  $dbh->do("PRAGMA cache_size = 800000");
  use DBI qw(:sql_types);
  $dbh->{sqlite_unicode} = 1;
  my $sth = $dbh->prepare("INSERT INTO mytable (blobcolumn) VALUES (?)");

  # Binary_data will be stored as is.
  $sth->bind_param(1, $binary_data, SQL_BLOB);
  $sth = $dbh->table_info(undef, $schema, $table, $type, \%attr);
  @names = $dbh->primary_key(undef, $schema, $table);
  $sth   = $dbh->primary_key_info(undef, $schema, $table, \%attr);
  $sth = $dbh->foreign_key_info(undef, $pk_schema, $pk_table,
                                undef, $fk_schema, $fk_table);
  CASCADE              0
  RESTRICT             1
  SET NULL             2
  NO ACTION            3
  SET DEFAULT          4
  INITIALLY DEFERRED   5
  INITIALLY IMMEDIATE  6
  NOT DEFERRABLE       7
  $sth = $dbh->statistics_info(undef, $schema, $table,
                                $unique_only, $quick);
  my $bool = $dbh->ping;
  $dbh->func( ..., "(method name without sqlite_ prefix)" );
  $dbh->func( ..., "sqlite_trace");
  $h->last_insert_id($catalog, $schema, $table_name, $field_name [, \%attr ])
  $dbh->sqlite_create_function( 'now', 0, sub { return time } );
  INSERT INTO mytable ( now() );
  SELECT * from table WHERE column REGEXP '\bA\w+'
  SELECT * from table WHERE column REGEXP '(?i:\bA\w+)'
  $code_ref->($dbh, $collation_name)
  package variance;

  sub new { bless [], shift; }

  sub step {
      my ( $self, $value ) = @_;

      push @$self, $value;
  }

  sub finalize {
      my $self = $_[0];

      my $n = @$self;

      # Variance is NULL unless there is more than one row
      return undef unless $n || $n == 1;

      my $mu = 0;
      foreach my $v ( @$self ) {
          $mu += $v;
      }
      $mu /= $n;

      my $sigma = 0;
      foreach my $v ( @$self ) {
          $sigma += ($v - $mu)**2;
      }
      $sigma = $sigma / ($n - 1);

      return $sigma;
  }

  $dbh->sqlite_create_aggregate( "variance", 1, 'variance' );
  SELECT group_name, variance(score)
  FROM results
  GROUP BY group_name;
  $code_ref->($action_code, $database, $table, $rowid)
  $code_ref->($action_code, $string1, $string2, $database, $trigger_or_view)
  $dbh->sqlite_enable_load_extension(1);
  $sth = $dbh->prepare("select load_extension('libsqlitefunctions.so')")
  or die "Cannot prepare: " . $dbh->errstr();
  $code_ref->($statement)
  $code_ref->($statement, $elapsed_time)
  my $status = $sth->sqlite_st_status();
  my $cur = $status->{fullscan_step};
  use DBD::SQLite::Constants qw/:database_connection_configuration_options/;

  my $dbh = DBI->connect('dbi:SQLite::memory:');
  # This disables language features that allow ordinary SQL
  # to deliberately corrupt the database file
  $dbh->sqlite_db_config( SQLITE_DBCONFIG_DEFENSIVE, 1 );

  # This disables two-arg version of fts3_tokenizer.
  $dbh->sqlite_db_config( SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER, 0 );
  my $current_value = $dbh->sqlite_db_config( SQLITE_DBCONFIG_DEFENSIVE, -1 );
  my $status = DBD::SQLite::sqlite_status();
  my $cur  = $status->{memory_used}{current};
  my $high = $status->{memory_used}{highwater};
  use DBD::SQLite;
  $dbh->sqlite_set_authorizer(sub {
    my $action_code = shift;
    return $action_code == DBD::SQLite::DELETE ? DBD::SQLite::DENY
                                               : DBD::SQLite::OK;
  });
  OK
  DENY
  IGNORE
  # constant              string1         string2
  # ========              =======         =======
  CREATE_INDEX            Index Name      Table Name
  CREATE_TABLE            Table Name      undef
  CREATE_TEMP_INDEX       Index Name      Table Name
  CREATE_TEMP_TABLE       Table Name      undef
  CREATE_TEMP_TRIGGER     Trigger Name    Table Name
  CREATE_TEMP_VIEW        View Name       undef
  CREATE_TRIGGER          Trigger Name    Table Name
  CREATE_VIEW             View Name       undef
  DELETE                  Table Name      undef
  DROP_INDEX              Index Name      Table Name
  DROP_TABLE              Table Name      undef
  DROP_TEMP_INDEX         Index Name      Table Name
  DROP_TEMP_TABLE         Table Name      undef
  DROP_TEMP_TRIGGER       Trigger Name    Table Name
  DROP_TEMP_VIEW          View Name       undef
  DROP_TRIGGER            Trigger Name    Table Name
  DROP_VIEW               View Name       undef
  INSERT                  Table Name      undef
  PRAGMA                  Pragma Name     1st arg or undef
  READ                    Table Name      Column Name
  SELECT                  undef           undef
  TRANSACTION             Operation       undef
  UPDATE                  Table Name      Column Name
  ATTACH                  Filename        undef
  DETACH                  Database Name   undef
  ALTER_TABLE             Database Name   Table Name
  REINDEX                 Index Name      undef
  ANALYZE                 Table Name      undef
  CREATE_VTABLE           Table Name      Module Name
  DROP_VTABLE             Table Name      Module Name
  FUNCTION                undef           Function Name
  SAVEPOINT               Operation       Savepoint Name
  CREATE TABLE foo(
      txt1 COLLATE perl,
      txt2 COLLATE perllocale,
      txt3 COLLATE nocase
  )
  SELECT * FROM foo ORDER BY name COLLATE perllocale
  my $dbh = DBI->connect(
      "dbi:SQLite:dbname=foo", "", "",
      {
          RaiseError     => 1,
          sqlite_unicode => 1,
      }
  );
  use DBD::SQLite;
  $DBD::SQLite::COLLATION{no_accents} = sub {
    my ( $a, $b ) = map lc, @_;
    tr[����������������������������]
      [aaaaaacdeeeeiiiinoooooouuuuy] for $a, $b;
    $a cmp $b;
  };
  my $dbh  = DBI->connect("dbi:SQLite:dbname=dbfile");
  my $sql  = "SELECT ... FROM ... ORDER BY ... COLLATE no_accents");
  my $rows = $dbh->selectall_arrayref($sql);
  CREATE VIRTUAL TABLE city_buildings USING rtree(
     id,               -- Integer primary key
     minLong, maxLong, -- Minimum and maximum longitude
     minLat, maxLat    -- Minimum and maximum latitude
  );
  # IDs that are contained within query coordinates
  my $contained_sql = <<"";
  SELECT id FROM city_buildings
     WHERE  minLong >= ? AND maxLong <= ?
     AND    minLat  >= ? AND maxLat  <= ?

  # ... and those that overlap query coordinates
  my $overlap_sql = <<"";
  SELECT id FROM city_buildings
     WHERE    maxLong >= ? AND minLong <= ?
     AND      maxLat  >= ? AND minLat  <= ?

  my $contained = $dbh->selectcol_arrayref($contained_sql,undef,
                        $minLong, $maxLong, $minLat, $maxLat);

  my $overlapping = $dbh->selectcol_arrayref($overlap_sql,undef,
                        $minLong, $maxLong, $minLat, $maxLat);
  use File::ShareDir 'dist_dir';
  use File::Spec::Functions 'catfile';

  # the whole sqlite3.h header
  my $sqlite3_h = catfile(dist_dir('DBD-SQLite'), 'sqlite3.h');

  # or only a particular header, amalgamated in sqlite3.c
  my $what_i_want = 'parse.h';
  my $sqlite3_c = catfile(dist_dir('DBD-SQLite'), 'sqlite3.c');
  open my $fh, '<', $sqlite3_c or die $!;
  my $code = do { local $/; <$fh> };
  my ($parse_h) = $code =~ m{(
    /\*+[ ]Begin[ ]file[ ]$what_i_want[ ]\*+
    .+?
    /\*+[ ]End[ ]of[ ]$what_i_want[ ]\*+/
  )}sx;
  open my $out, '>', $what_i_want or die $!;
  print $out $parse_h;
  close $out;