# phpman > perldoc > SQL::Statement::Embed

## NAME
    [SQL::Statement::Embed](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement%3A%3AEmbed/markdown) - embed a SQL engine in a DBD or module

## SYNOPSIS
## DESCRIPTION
    [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) is designed to be easy to embed in other modules and to be especially easy to
    embed in DBI drivers. It provides a SQL Engine and the other module needs to then provide a data
    source and a storage mechanism. For example, the [DBD::CSV](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3ACSV/markdown) module uses [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) as an
    embedded SQL engine by implementing a file-based data source and by using DBI as the user
    interface. Similarly [DBD::Amazon](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AAmazon/markdown) uses [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) as its SQL engine, provides its own
    extensions to the supported SQL syntax, and uses on-the-fly searches of Amazon.com as its data
    source.

    [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) is the basis for at least eight existing DBDs (DBI database drivers). If you have
    a new data source, you too can create a DBD without having to reinvent the SQL wheel. It is fun
    and easy so become a DBD author today!

    [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) can be also be embedded without DBI. We will explore that first since developing
    a DBD uses most of the same methods and techniques.

## The role of [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) subclasses
    [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) provides a SQL parsing and execution engine. It neither provides a data source
    nor storage mechanism other than in-memory tables. The [DBI::DBD::SqlEngine](https://www.chedong.com/phpMan.php/perldoc/DBI%3A%3ADBD%3A%3ASqlEngine/markdown) contains a subclass
    of [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) to abstract from embedding [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) into a DBD and lets you concentrate
    on the extensions you need to make. [DBD::File](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile/markdown) extends [DBI::DBD::SqlEngine](https://www.chedong.com/phpMan.php/perldoc/DBI%3A%3ADBD%3A%3ASqlEngine/markdown) by providing access to
    file-based storage mechanisms. It is quite possible to use things other than files as data
    sources, in which case you would not use [DBD::File](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile/markdown), instead you would replace [DBD::File](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile/markdown)'s
    methods with your own. In the examples below, we use [DBD::File](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile/markdown), replacing only a few methods.

    [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) provides SQL parsing and evaluation and [DBI::DBD::SqlEngine](https://www.chedong.com/phpMan.php/perldoc/DBI%3A%3ADBD%3A%3ASqlEngine/markdown) provides DBI
    integration. The only thing missing is a data source - what we actually want to store and query.
    As an example suppose we are going to create a subclass called 'Foo' that will provide as a data
    source the in-memory storage which is used in [SQL::RAM](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3ARAM/markdown) to provide the "TEMP" tables in
    [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown), but the rows are stored as a string using a serializer (Storable).

    Consider what needs to happen to perform a SELECT query on our 'Foo' data:

     * receive a SQL string
     * parse the SQL string into a request structure
     * open the table(s) specified in the request
     * define column names and positions for the table
     * read rows from the table
     * convert the rows from colon-separated format into perl arrays
     * match the columns and rows against the requested selection criteria
     * return requested rows and columns to the user

    To perform operations like INSERT and DELETE, we also need to:

     * convert rows from perl arrays into colon-separated format
     * write rows
     * delete rows

    [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) takes care of all of the SQL parsing and evaluation. [DBD::File](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile/markdown) takes care of file
    opening, reading, writing, and deleting. So the only things 'Foo' is really responsible for are:

     * define column names and positions for the table
     * convert rows from colon-separated format into perl arrays
     * convert rows from perl arrays into colon-separated format

    In [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) subclasses these responsibilities are assigned to two objects. A ::Statement
    object is responsible for opening the table by creating new ::Table objects. A ::Table object is
    responsible for defining the column names and positions, opening data sources, reading,
    converting, writing and deleting data.

    The real work is therefore done in the ::Table object, the ::Statement subclass is required to
    deliver the right ::Table object.

## Creating a ::Statement object
    A subclass of [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) must provide at least one method called open_table(). The method
    should open a new Table object and define the table's columns. For our 'Foo' module, here is the
    complete object definition:

        package Foo;

        package [Foo::Statement](https://www.chedong.com/phpMan.php/perldoc/Foo%3A%3AStatement/markdown);
        use [DBD::File](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile/markdown);
        use base qw([DBI::DBD::SqlEngine::Statement](https://www.chedong.com/phpMan.php/perldoc/DBI%3A%3ADBD%3A%3ASqlEngine%3A%3AStatement/markdown));

        sub open_table {
            my ($self, $sth, $table, $createMode, $lockMode) = @_;

            my $class = ref $self;
            $class =~ s/::Statement/::Table/;

            return $class->new ($sth, $table, $createMode, $lockMode);
        }

    Since 'Foo' is an in-memory data source, we subclass [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) indirectly through
    [DBD::File::Statement](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile%3A%3AStatement/markdown). The open_table() method lets [DBD::File](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile/markdown) do the actual table opening. All we
    do is define the files directory (f_dir), the names of the columns (col_names) and the positions
    of the columns (col_nums). [DBD::File](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile/markdown) creates and returns a $tbl object. It names that object
    according to the module that calls it, so in our case the object will be a [Foo::Table](https://www.chedong.com/phpMan.php/perldoc/Foo%3A%3ATable/markdown) object.

## Creating a ::Table object
    Table objects are responsible for reading, converting, writing, and deleting data. Since
    [DBD::File](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile/markdown) provides most of those services, our 'Foo' subclass only needs to define three methods
    - fetch_row() to read data, push_row() to write data, and push_names() to store column names. We
    will leave deleting to [DBD::File](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile/markdown), since deleting a record in the 'Foo' format is the same
    process as deleting a record in any other simple file-based format. Here is the complete object
    definition:

     package [Foo::Table](https://www.chedong.com/phpMan.php/perldoc/Foo%3A%3ATable/markdown);
     use base qw([DBD::File::Table](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile%3A%3ATable/markdown));

     sub fetch_row {
        my($self, $data) = @_;
        my $fieldstr = $self->{fh}->getline;
        return undef unless $fieldstr;
        chomp $fieldstr;
        my @fields   = split /:/,$fieldstr;
        $self->{row} = (@fields ? \@fields : undef);
     }
     sub push_row {
        my($self, $data, $fields) = @_;
        my $str = join ':', map { defined $_ ? $_ : '' } @$fields;
        $self->{fh}->print( $str."\n");
        1;
     }
     sub push_names {}
     1;

    The fetch_row() method uses [DBD::File](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile/markdown)'s getline() method to physically read a row of data, then
    we convert it from native colon-separated format into a perl arrayref.

    The push_row() method converts from a perl arrayref back to colon-separated format then uses
    [DBD::File](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile/markdown)'s print() method to print it to file.

    The push_names method does nothing because it's purpose is to store column names in a file and
    in our 'Foo' subclass, we are defining the column names ourselves, not storing them in a file.

## Trying out our new subclass
    Here is a script which should create and query a file in our 'Foo' format. It assumes you have
    saved the Foo, [Foo::Statement](https://www.chedong.com/phpMan.php/perldoc/Foo%3A%3AStatement/markdown), and [Foo::Table](https://www.chedong.com/phpMan.php/perldoc/Foo%3A%3ATable/markdown) classes shown above into a file called Foo.pm.

     #!perl -w
     use strict;
     use Foo;
     my $parser = [SQL::Parser](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AParser/markdown)->new();
     $parser->{RaiseError}=1;
     $parser->{PrintError}=0;
     for my $sql(split /\n/,
     "  DROP TABLE IF EXISTS group_id
        CREATE TABLE group_id (username CHAR,uid INT, gid INT)
        INSERT INTO group_id VALUES('joe',1,1)
        INSERT INTO group_id VALUES('sue',2,1)
        INSERT INTO group_id VALUES('bob',3,2)
        SELECT * FROM group_id             "
     ){
        my $stmt = [Foo::Statement](https://www.chedong.com/phpMan.php/perldoc/Foo%3A%3AStatement/markdown)->new($sql,$parser);
        $stmt->execute;
        next unless $stmt->command eq 'SELECT';
        while (my $row=$stmt->fetch) {
            print "@$row\n";
        }
     }

    This is the same script as shown in the section on executing and fetching in
    [SQL::Statement::Structure](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement%3A%3AStructure/markdown) except that instead of [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown)->new(), we are using
    [Foo::Statement](https://www.chedong.com/phpMan.php/perldoc/Foo%3A%3AStatement/markdown)->new(). The other difference is that the execute/fetch example was using
    in-memory storage while this script is using file-based storage and the 'Foo' format we defined.
    When you run this script, you will be creating a file called "group_id" and it will contain the
    specified data in colon-separated format.

## Developing a new DBD
### Moving from a subclass to a DBD
    A DBD based on [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) uses the same two subclasses that are shown above. They should be
    called [DBD::Foo::Statement](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFoo%3A%3AStatement/markdown) and [DBD::Foo::Table](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFoo%3A%3ATable/markdown), but would otherwise be identical to the non-DBD
    subclass illustrated above. To turn it into a full DBD, you have to subclass [DBD::File](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile/markdown),
    [DBD::File::dr](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile%3A%3Adr/markdown), [DBD::File::db](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile%3A%3Adb/markdown), and [DBD::File::st](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile%3A%3Ast/markdown). In many cases a simple subclass with few or no
    methods overridden is sufficient.

    Here is a working [DBD::Foo](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFoo/markdown):

     package [DBD::Foo](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFoo/markdown);
     use base qw([DBD::File](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile/markdown));

     package [DBD::Foo::dr](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFoo%3A%3Adr/markdown);
     $[DBD::Foo::dr::imp_data_size](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFoo%3A%3Adr%3A%3Aimpdatasize/markdown) = 0;
     use base qw([DBD::File::dr](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile%3A%3Adr/markdown));

     package [DBD::Foo::db](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFoo%3A%3Adb/markdown);
     $[DBD::Foo::db::imp_data_size](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFoo%3A%3Adb%3A%3Aimpdatasize/markdown) = 0;
     use base qw([DBD::File::db](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile%3A%3Adb/markdown));

     package [DBD::Foo::st](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFoo%3A%3Ast/markdown);
     $[DBD::Foo::st::imp_data_size](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFoo%3A%3Ast%3A%3Aimpdatasize/markdown) = 0;
     use base qw([DBD::File::st](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile%3A%3Ast/markdown));

     package [DBD::Foo::Statement](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFoo%3A%3AStatement/markdown);
     use base qw([DBD::File::Statement](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile%3A%3AStatement/markdown));

     sub open_table {
         my $self = shift @_;
         my $data = shift @_;
         $data->{Database}->{f_dir} = './';
         my $tbl  = $self->[SUPER::open_table](https://www.chedong.com/phpMan.php/perldoc/SUPER%3A%3Aopentable/markdown)($data,@_);
         $tbl->{col_names} = [qw(username uid gid)];
         $tbl->{col_nums}  = {username=>0,uid=>1,gid=>2};
         return $tbl;
     }

     package [DBD::Foo::Table](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFoo%3A%3ATable/markdown);
     use base qw([DBD::File::Table](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile%3A%3ATable/markdown));

     sub fetch_row {
        my($self, $data) = @_;
        my $fieldstr = $self->{fh}->getline;
        return undef unless $fieldstr;
        chomp $fieldstr;
        my @fields   = split /:/,$fieldstr;
        $self->{row} = (@fields ? \@fields : undef);
     }
     sub push_row {
         my($self, $data, $fields) = @_;
         my $str = join ':', map { defined $_ ? $_ : '' } @$fields;
         $self->{fh}->print( $str."\n");
         1;
     }
     sub push_names {}
     1;

  A sample script to test our new DBD
    Assuming you saved the [DBD::Foo](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFoo/markdown) shown above as a file called "Foo.pm" in a directory called
    "DBD", this script will work, so will most other DBI methods such as selectall_arrayref,
    fetchrow_hashref, etc.

     #!perl -w
     use strict;
     use lib qw(/home/jeff/data/module/lib); # or wherever you stored [DBD::Foo](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFoo/markdown)
     use DBI;
     my $dbh=DBI->connect('dbi:Foo:');
     $dbh->{RaiseError}=1;
     $dbh->{PrintError}=0;
     for my $sql(split /\n/,
     "  DROP TABLE IF EXISTS group_id
        CREATE TABLE group_id (username CHAR,uid INT, gid INT)
        INSERT INTO group_id VALUES('joe',1,1)
        INSERT INTO group_id VALUES('sue',2,1)
        INSERT INTO group_id VALUES('bob',3,2)
        SELECT * FROM group_id             "
     ){
        my $stmt = $dbh->prepare($sql);
        $stmt->execute;
        next unless $stmt->{NUM_OF_FIELDS};
        while (my $row=$stmt->fetch) {
            print "@$row\n";
        }
     }

## Expanding the DBD
    Now that we have a basic DBD operational, there are several directions for expansion. In the
    first place, we might want to override some or all of [DBD::File::Table](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile%3A%3ATable/markdown) to provide alternate
    means of reading, writing, and deleting from our data source. We might want to override the
### open_table
    from the file itself) or to provide other kinds of metadata. See [SQL::Eval](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AEval/markdown) for documentation of
    the API for ::Table objects and see [DBD::File](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile/markdown) for an example subclass.

    We might want to create extensions to the SQL syntax specific to our DBD. See the section on
    extending SQL syntax in [SQL::Statement::Syntax](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement%3A%3ASyntax/markdown).

    We might want to provide a completely different kind of data source. See [DBD::DBM](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3ADBM/markdown) (whose source
    code includes documentation on subclassing [SQL::Statement](https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement/markdown) and [DBD::File](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile/markdown)), and other [DBD::File](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AFile/markdown)
    subclasses such as [DBD::CSV](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3ACSV/markdown).

    We might also want to provide a completely different storage mechanism, something not based on
    files at all. See [DBD::Amazon](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AAmazon/markdown) and [DBD::AnyData](https://www.chedong.com/phpMan.php/perldoc/DBD%3A%3AAnyData/markdown).

    And we will almost certainly want to fine-tune the DBI interface, see [DBI::DBD](https://www.chedong.com/phpMan.php/perldoc/DBI%3A%3ADBD/markdown).

## Getting help with a new DBD
    The dbi-devATperl.org mailing list should be your first stop in creating a new DBD. Tim Bunce,
    the author of DBI and many DBD authors hang out there. Tell us what you are planning and we will
    offer suggestions about similar modules or other people working on similar issues, or on how to
    proceed.

AUTHOR & COPYRIGHT
      Copyright (c) 2005, Jeff Zucker <jzuckerATcpan.org>, all rights reserved.
      Copyright (c) 2010-2020, Jens Rehsack <rehsackATcpan.org>, all rights reserved.

    This document may be freely modified and distributed under the same terms as Perl itself.