# perldoc > DBD::SQLite::VirtualTable::PerlData

---
type: CommandReference
command: DBD::SQLite::VirtualTable::PerlData
mode: perldoc
section: ""
source: perldoc
---

## Quick Reference

- `$dbh->sqlite_create_module(perl => "DBD::SQLite::VirtualTable::PerlData")` -- register the module
- `CREATE VIRTUAL TABLE atbl USING perl(col1, col2, arrayrefs="package::var")` -- bind to array-of-arrays
- `CREATE VIRTUAL TABLE htbl USING perl(col1, col2, hashrefs="package::var")` -- bind to array-of-hashes
- `CREATE VIRTUAL TABLE ctbl USING perl(col, colref="package::var")` -- bind to flat array
- `SELECT * FROM atbl WHERE col1 = ?` -- query virtual table
- `INSERT INTO atbl VALUES (...)` -- modify underlying Perl data
- Use `temp.` prefix to auto-cleanup on disconnect

## Name

`DBD::SQLite::VirtualTable::PerlData` -- virtual table hooked to Perl data

## Synopsis

perl
$dbh->sqlite_create_module(perl => "DBD::SQLite::VirtualTable::PerlData");
sql
CREATE VIRTUAL TABLE atbl USING perl(foo, bar, etc,
                                     arrayrefs="some::global::var::aref");
CREATE VIRTUAL TABLE htbl USING perl(foo, bar, etc,
                                     hashrefs="some::global::var::href");
CREATE VIRTUAL TABLE ctbl USING perl(single_col,
                                     colref="some::global::var::ref");
SELECT foo, bar FROM atbl WHERE ...;
## Options

- `arrayrefs="package::var"` -- bind to a global arrayref where each element is an arrayref of row values. Number of columns must match.
- `hashrefs="package::var"` -- bind to a global arrayref where each element is a hashref. Keys should correspond to column names.
- `colref="package::var"` -- bind to a global arrayref of scalars for a single-column virtual table.

## Examples

### Module registration (common to all)

perl
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile", '', '',
                        {RaiseError => 1, AutoCommit => 1});
$dbh->sqlite_create_module(perl => "DBD::SQLite::VirtualTable::PerlData");
Use `our` instead of `my` for global variables.

### Arrayref: file statistics

perl
our $file_stats = [ map { [ $_, stat $_ ] } @files ];
$dbh->do(<<'');
CREATE VIRTUAL TABLE temp.file_stats
  USING perl(path, dev, ino, mode, nlink, uid, gid, rdev, size,
             atime, mtime, ctime, blksize, blocks,
             arrayrefs="main::file_stats")
my $sth = $dbh->prepare(<<'');
SELECT * FROM file_stats
  WHERE mtime BETWEEN ? AND ?
    AND uid IN (...)
### Hashref: Unicode character information

perl
use Unicode::UCD 'charinfo';
our $chars = [map {charinfo($_)} 0x300..0x400];
$dbh->do(<<'');
CREATE VIRTUAL TABLE charinfo USING perl(
  code, name, block, script, category,
  hashrefs="main::chars"
)
my $sth = $dbh->prepare(<<'');
SELECT * FROM charinfo
 WHERE script='Greek'
   AND name LIKE '%SIGMA%'
### Colref: `SELECT ... WHERE ... IN` array

perl
our $values = \@ARGV;
$dbh->do('CREATE VIRTUAL TABLE temp.intarray'
        .'  USING perl(i INT, colref="main::values")');
my $sql = "SELECT * FROM some_table WHERE some_col IN intarray";
my $result = $dbh->selectall_arrayref($sql);
The virtual table is read-write: `INSERT INTO intarray VALUES (99)` will push `99` into `@ARGV`.