{
    "mode": "perldoc",
    "parameter": "SQL::Statement::Embed",
    "section": "",
    "url": "https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement%3A%3AEmbed/json",
    "generated": "2026-06-09T12:40:16Z",
    "synopsis": "",
    "sections": {
        "NAME": {
            "content": "SQL::Statement::Embed - embed a SQL engine in a DBD or module\n",
            "subsections": []
        },
        "SYNOPSIS": {
            "content": "",
            "subsections": []
        },
        "DESCRIPTION": {
            "content": "SQL::Statement is designed to be easy to embed in other modules and to be especially easy to\nembed in DBI drivers. It provides a SQL Engine and the other module needs to then provide a data\nsource and a storage mechanism. For example, the DBD::CSV module uses SQL::Statement as an\nembedded SQL engine by implementing a file-based data source and by using DBI as the user\ninterface. Similarly DBD::Amazon uses SQL::Statement as its SQL engine, provides its own\nextensions to the supported SQL syntax, and uses on-the-fly searches of Amazon.com as its data\nsource.\n\nSQL::Statement is the basis for at least eight existing DBDs (DBI database drivers). If you have\na new data source, you too can create a DBD without having to reinvent the SQL wheel. It is fun\nand easy so become a DBD author today!\n\nSQL::Statement can be also be embedded without DBI. We will explore that first since developing\na DBD uses most of the same methods and techniques.\n",
            "subsections": []
        },
        "The role of SQL::Statement subclasses": {
            "content": "SQL::Statement provides a SQL parsing and execution engine. It neither provides a data source\nnor storage mechanism other than in-memory tables. The DBI::DBD::SqlEngine contains a subclass\nof SQL::Statement to abstract from embedding SQL::Statement into a DBD and lets you concentrate\non the extensions you need to make. DBD::File extends DBI::DBD::SqlEngine by providing access to\nfile-based storage mechanisms. It is quite possible to use things other than files as data\nsources, in which case you would not use DBD::File, instead you would replace DBD::File's\nmethods with your own. In the examples below, we use DBD::File, replacing only a few methods.\n\nSQL::Statement provides SQL parsing and evaluation and DBI::DBD::SqlEngine provides DBI\nintegration. The only thing missing is a data source - what we actually want to store and query.\nAs an example suppose we are going to create a subclass called 'Foo' that will provide as a data\nsource the in-memory storage which is used in SQL::RAM to provide the \"TEMP\" tables in\nSQL::Statement, but the rows are stored as a string using a serializer (Storable).\n\nConsider what needs to happen to perform a SELECT query on our 'Foo' data:\n\n* receive a SQL string\n* parse the SQL string into a request structure\n* open the table(s) specified in the request\n* define column names and positions for the table\n* read rows from the table\n* convert the rows from colon-separated format into perl arrays\n* match the columns and rows against the requested selection criteria\n* return requested rows and columns to the user\n\nTo perform operations like INSERT and DELETE, we also need to:\n\n* convert rows from perl arrays into colon-separated format\n* write rows\n* delete rows\n\nSQL::Statement takes care of all of the SQL parsing and evaluation. DBD::File takes care of file\nopening, reading, writing, and deleting. So the only things 'Foo' is really responsible for are:\n\n* define column names and positions for the table\n* convert rows from colon-separated format into perl arrays\n* convert rows from perl arrays into colon-separated format\n\nIn SQL::Statement subclasses these responsibilities are assigned to two objects. A ::Statement\nobject is responsible for opening the table by creating new ::Table objects. A ::Table object is\nresponsible for defining the column names and positions, opening data sources, reading,\nconverting, writing and deleting data.\n\nThe real work is therefore done in the ::Table object, the ::Statement subclass is required to\ndeliver the right ::Table object.\n",
            "subsections": []
        },
        "Creating a ::Statement object": {
            "content": "A subclass of SQL::Statement must provide at least one method called opentable(). The method\nshould open a new Table object and define the table's columns. For our 'Foo' module, here is the\ncomplete object definition:\n\npackage Foo;\n\npackage Foo::Statement;\nuse DBD::File;\nuse base qw(DBI::DBD::SqlEngine::Statement);\n\nsub opentable {\nmy ($self, $sth, $table, $createMode, $lockMode) = @;\n\nmy $class = ref $self;\n$class =~ s/::Statement/::Table/;\n\nreturn $class->new ($sth, $table, $createMode, $lockMode);\n}\n\nSince 'Foo' is an in-memory data source, we subclass SQL::Statement indirectly through\nDBD::File::Statement. The opentable() method lets DBD::File do the actual table opening. All we\ndo is define the files directory (fdir), the names of the columns (colnames) and the positions\nof the columns (colnums). DBD::File creates and returns a $tbl object. It names that object\naccording to the module that calls it, so in our case the object will be a Foo::Table object.\n",
            "subsections": []
        },
        "Creating a ::Table object": {
            "content": "Table objects are responsible for reading, converting, writing, and deleting data. Since\nDBD::File provides most of those services, our 'Foo' subclass only needs to define three methods\n- fetchrow() to read data, pushrow() to write data, and pushnames() to store column names. We\nwill leave deleting to DBD::File, since deleting a record in the 'Foo' format is the same\nprocess as deleting a record in any other simple file-based format. Here is the complete object\ndefinition:\n\npackage Foo::Table;\nuse base qw(DBD::File::Table);\n\nsub fetchrow {\nmy($self, $data) = @;\nmy $fieldstr = $self->{fh}->getline;\nreturn undef unless $fieldstr;\nchomp $fieldstr;\nmy @fields   = split /:/,$fieldstr;\n$self->{row} = (@fields ? \\@fields : undef);\n}\nsub pushrow {\nmy($self, $data, $fields) = @;\nmy $str = join ':', map { defined $ ? $ : '' } @$fields;\n$self->{fh}->print( $str.\"\\n\");\n1;\n}\nsub pushnames {}\n1;\n\nThe fetchrow() method uses DBD::File's getline() method to physically read a row of data, then\nwe convert it from native colon-separated format into a perl arrayref.\n\nThe pushrow() method converts from a perl arrayref back to colon-separated format then uses\nDBD::File's print() method to print it to file.\n\nThe pushnames method does nothing because it's purpose is to store column names in a file and\nin our 'Foo' subclass, we are defining the column names ourselves, not storing them in a file.\n",
            "subsections": []
        },
        "Trying out our new subclass": {
            "content": "Here is a script which should create and query a file in our 'Foo' format. It assumes you have\nsaved the Foo, Foo::Statement, and Foo::Table classes shown above into a file called Foo.pm.\n\n#!perl -w\nuse strict;\nuse Foo;\nmy $parser = SQL::Parser->new();\n$parser->{RaiseError}=1;\n$parser->{PrintError}=0;\nfor my $sql(split /\\n/,\n\"  DROP TABLE IF EXISTS groupid\nCREATE TABLE groupid (username CHAR,uid INT, gid INT)\nINSERT INTO groupid VALUES('joe',1,1)\nINSERT INTO groupid VALUES('sue',2,1)\nINSERT INTO groupid VALUES('bob',3,2)\nSELECT * FROM groupid             \"\n){\nmy $stmt = Foo::Statement->new($sql,$parser);\n$stmt->execute;\nnext unless $stmt->command eq 'SELECT';\nwhile (my $row=$stmt->fetch) {\nprint \"@$row\\n\";\n}\n}\n\nThis is the same script as shown in the section on executing and fetching in\nSQL::Statement::Structure except that instead of SQL::Statement->new(), we are using\nFoo::Statement->new(). The other difference is that the execute/fetch example was using\nin-memory storage while this script is using file-based storage and the 'Foo' format we defined.\nWhen you run this script, you will be creating a file called \"groupid\" and it will contain the\nspecified data in colon-separated format.\n",
            "subsections": []
        },
        "Developing a new DBD": {
            "content": "",
            "subsections": [
                {
                    "name": "Moving from a subclass to a DBD",
                    "content": "A DBD based on SQL::Statement uses the same two subclasses that are shown above. They should be\ncalled DBD::Foo::Statement and DBD::Foo::Table, but would otherwise be identical to the non-DBD\nsubclass illustrated above. To turn it into a full DBD, you have to subclass DBD::File,\nDBD::File::dr, DBD::File::db, and DBD::File::st. In many cases a simple subclass with few or no\nmethods overridden is sufficient.\n\nHere is a working DBD::Foo:\n\npackage DBD::Foo;\nuse base qw(DBD::File);\n\npackage DBD::Foo::dr;\n$DBD::Foo::dr::impdatasize = 0;\nuse base qw(DBD::File::dr);\n\npackage DBD::Foo::db;\n$DBD::Foo::db::impdatasize = 0;\nuse base qw(DBD::File::db);\n\npackage DBD::Foo::st;\n$DBD::Foo::st::impdatasize = 0;\nuse base qw(DBD::File::st);\n\npackage DBD::Foo::Statement;\nuse base qw(DBD::File::Statement);\n\nsub opentable {\nmy $self = shift @;\nmy $data = shift @;\n$data->{Database}->{fdir} = './';\nmy $tbl  = $self->SUPER::opentable($data,@);\n$tbl->{colnames} = [qw(username uid gid)];\n$tbl->{colnums}  = {username=>0,uid=>1,gid=>2};\nreturn $tbl;\n}\n\npackage DBD::Foo::Table;\nuse base qw(DBD::File::Table);\n\nsub fetchrow {\nmy($self, $data) = @;\nmy $fieldstr = $self->{fh}->getline;\nreturn undef unless $fieldstr;\nchomp $fieldstr;\nmy @fields   = split /:/,$fieldstr;\n$self->{row} = (@fields ? \\@fields : undef);\n}\nsub pushrow {\nmy($self, $data, $fields) = @;\nmy $str = join ':', map { defined $ ? $ : '' } @$fields;\n$self->{fh}->print( $str.\"\\n\");\n1;\n}\nsub pushnames {}\n1;\n\nA sample script to test our new DBD\nAssuming you saved the DBD::Foo shown above as a file called \"Foo.pm\" in a directory called\n\"DBD\", this script will work, so will most other DBI methods such as selectallarrayref,\nfetchrowhashref, etc.\n\n#!perl -w\nuse strict;\nuse lib qw(/home/jeff/data/module/lib); # or wherever you stored DBD::Foo\nuse DBI;\nmy $dbh=DBI->connect('dbi:Foo:');\n$dbh->{RaiseError}=1;\n$dbh->{PrintError}=0;\nfor my $sql(split /\\n/,\n\"  DROP TABLE IF EXISTS groupid\nCREATE TABLE groupid (username CHAR,uid INT, gid INT)\nINSERT INTO groupid VALUES('joe',1,1)\nINSERT INTO groupid VALUES('sue',2,1)\nINSERT INTO groupid VALUES('bob',3,2)\nSELECT * FROM groupid             \"\n){\nmy $stmt = $dbh->prepare($sql);\n$stmt->execute;\nnext unless $stmt->{NUMOFFIELDS};\nwhile (my $row=$stmt->fetch) {\nprint \"@$row\\n\";\n}\n}\n"
                }
            ]
        },
        "Expanding the DBD": {
            "content": "Now that we have a basic DBD operational, there are several directions for expansion. In the\nfirst place, we might want to override some or all of DBD::File::Table to provide alternate\nmeans of reading, writing, and deleting from our data source. We might want to override the",
            "subsections": [
                {
                    "name": "open_table",
                    "content": "from the file itself) or to provide other kinds of metadata. See SQL::Eval for documentation of\nthe API for ::Table objects and see DBD::File for an example subclass.\n\nWe might want to create extensions to the SQL syntax specific to our DBD. See the section on\nextending SQL syntax in SQL::Statement::Syntax.\n\nWe might want to provide a completely different kind of data source. See DBD::DBM (whose source\ncode includes documentation on subclassing SQL::Statement and DBD::File), and other DBD::File\nsubclasses such as DBD::CSV.\n\nWe might also want to provide a completely different storage mechanism, something not based on\nfiles at all. See DBD::Amazon and DBD::AnyData.\n\nAnd we will almost certainly want to fine-tune the DBI interface, see DBI::DBD.\n"
                }
            ]
        },
        "Getting help with a new DBD": {
            "content": "The dbi-devATperl.org mailing list should be your first stop in creating a new DBD. Tim Bunce,\nthe author of DBI and many DBD authors hang out there. Tell us what you are planning and we will\noffer suggestions about similar modules or other people working on similar issues, or on how to\nproceed.\n\nAUTHOR & COPYRIGHT\nCopyright (c) 2005, Jeff Zucker <jzuckerATcpan.org>, all rights reserved.\nCopyright (c) 2010-2020, Jens Rehsack <rehsackATcpan.org>, all rights reserved.\n\nThis document may be freely modified and distributed under the same terms as Perl itself.\n",
            "subsections": []
        }
    },
    "summary": "SQL::Statement::Embed - embed a SQL engine in a DBD or module",
    "flags": [],
    "examples": [],
    "see_also": []
}