{
    "mode": "perldoc",
    "parameter": "SQL::Statement::Structure",
    "section": "",
    "url": "https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement%3A%3AStructure/json",
    "generated": "2026-06-12T21:11:11Z",
    "synopsis": "use SQL::Statement;\nmy $sql    = \"SELECT a FROM b JOIN c WHERE c=? AND e=7 ORDER BY f DESC LIMIT 5,2\";\nmy $parser = SQL::Parser->new();\n$parser->{RaiseError}=1;\n$parser->{PrintError}=0;\n$parser->parse(\"LOAD 'MyLib::MySyntax' \");\nmy $stmt = SQL::Statement->new($sql,$parser);\nprintf \"Command             %s\\n\",$stmt->command;\nprintf \"Num of Placeholders %s\\n\",scalar $stmt->params;\nprintf \"Columns             %s\\n\",join( ',', map {$->name} $stmt->columndefs() );\nprintf \"Tables              %s\\n\",join( ',', map {$->name} $stmt->tables() );\nprintf \"Where operator      %s\\n\",join( ',', $stmt->where->op() );\nprintf \"Limit               %s\\n\",$stmt->limit();\nprintf \"Offset              %s\\n\",$stmt->offset();\n# these will work not before $stmt->execute()\nprintf \"Order Columns       %s\\n\",join(',', map {$->column} $stmt->order() );",
    "sections": {
        "NAME": {
            "content": "SQL::Statement::Structure - parse and examine structure of SQL queries\n",
            "subsections": []
        },
        "SYNOPSIS": {
            "content": "use SQL::Statement;\nmy $sql    = \"SELECT a FROM b JOIN c WHERE c=? AND e=7 ORDER BY f DESC LIMIT 5,2\";\nmy $parser = SQL::Parser->new();\n$parser->{RaiseError}=1;\n$parser->{PrintError}=0;\n$parser->parse(\"LOAD 'MyLib::MySyntax' \");\nmy $stmt = SQL::Statement->new($sql,$parser);\nprintf \"Command             %s\\n\",$stmt->command;\nprintf \"Num of Placeholders %s\\n\",scalar $stmt->params;\nprintf \"Columns             %s\\n\",join( ',', map {$->name} $stmt->columndefs() );\nprintf \"Tables              %s\\n\",join( ',', map {$->name} $stmt->tables() );\nprintf \"Where operator      %s\\n\",join( ',', $stmt->where->op() );\nprintf \"Limit               %s\\n\",$stmt->limit();\nprintf \"Offset              %s\\n\",$stmt->offset();\n\n# these will work not before $stmt->execute()\nprintf \"Order Columns       %s\\n\",join(',', map {$->column} $stmt->order() );\n",
            "subsections": []
        },
        "DESCRIPTION": {
            "content": "The SQL::Statement module can be used by itself, without DBI and without a subclass to parse SQL\nstatements and to allow you to examine the structure of the statement (table names, column\nnames, where clause predicates, etc.). It will also execute statements using in-memory tables.\nThat means that you can create and populate some tables, then query them and fetch the results\nof the queries as well as examine the differences between statement metadata during different\nphases of prepare, execute, fetch. See the remainder of this document for a description of how\nto create and modify a parser object and how to use it to parse and examine SQL statements. See\nSQL::Statement for other uses of the module.\n",
            "subsections": []
        },
        "Creating a parser object": {
            "content": "The parser object only needs to be created once per script. It can then be reused to parse any\nnumber of SQL statements. The basic creation of a parser is this:\n\nmy $parser = SQL::Parser->new();\n\nYou can set the error-reporting for the parser the same way you do in DBI:\n\n$parser->{RaiseError}=1;   # turn on die-on-error behaviour\n$parser->{PrinteError}=1;  # turn on warnings-on-error behaviour\n\nAs with DBI, RaiseError defaults to 0 (off) and PrintError defaults to 1 (on).\n\nFor many purposes, the built-in SQL syntax should be sufficient. However, if you need to, you\ncan change the behaviour of the parser by extending the supported SQL syntax either by loading a\nfile containing definitions; or by issuing SQL commands that modify the way the parser treats\ntypes, keywords, functions, and operators.\n\n$parser->parse(\"LOAD MyLib::MySyntax\");\n$parser->parse(\"CREATE TYPE myDataType\");\n\nSee SQL::Statement::Syntax for details of the supported SQL syntax and for methods of extending\nthe syntax.\n",
            "subsections": []
        },
        "Parsing SQL statements": {
            "content": "While you only need to define a new SQL::Parser object once per script, you need to define a new\nSQL::Statment object once for each statement you want to parse.\n\nmy $stmt = SQL::Statement->new($sql, $parser);\n\nThe call to new() takes two arguments - the SQL string you want to parse, and the SQL::Parser\nobject you previously created. The call to new is the equivalent of a DBI call to prepare() - it\nparses the SQL into a structure but does not attempt to execute the SQL unless you explicitly\ncall execute().\n",
            "subsections": []
        },
        "Examining the structure of SQL statements": {
            "content": "The following methods can be used to obtain information about a query:\n\ncommand\nReturns the SQL command. See SQL::Statement::Syntax for supported command. Example:\n\nmy $command = $stmt->command();\n\ncolumn definitions\nmy $numColumns = $stmt->columndefs();  # Scalar context\nmy @columnList = $stmt->columndefs();  # Array context\nmy($col1, $col2) = ($stmt->columndefs(0), $stmt->columndefs(1));\n\nThis method is used to retrieve column lists. The meaning depends on the query command:\n\nSELECT $col1, $col2, ... $colN FROM $table WHERE ...\nUPDATE $table SET $col1 = $val1, $col2 = $val2, ...\n$colN = $valN WHERE ...\nINSERT INTO $table ($col1, $col2, ..., $colN) VALUES (...)\n\nWhen used without arguments, the method returns a list of the columns $col1, $col2, ..., $colN,\nyou may alternatively use a column number as argument. Note that the column list may be empty as\nin\n\nINSERT INTO $table VALUES (...)\n\nand in *CREATE* or *DROP* statements.\n\nBut what does \"returning a column\" mean? It is returning an \"SQL::Statement::Util::Column\"\ninstance, a class that implements the methods \"table\" and \"name\", both returning the respective\nscalar. For example, consider the following statements:\n\nINSERT INTO foo (bar) VALUES (1)\nSELECT bar FROM foo WHERE ...\nSELECT foo.bar FROM foo WHERE ...\n\nIn all these cases exactly one column instance would be returned with\n\n$col->name() eq 'bar'\n$col->table() eq 'foo'\n\ntables\nmy $tableNum = $stmt->tables();  # Scalar context\nmy @tables = $stmt->tables();    # Array context\nmy($table1, $table2) = ($stmt->tables(0), $stmt->tables(1));\n\nSimilar to \"columns\", this method returns instances of \"SQL::Statement::Table\". For *UPDATE*,\n*DELETE*, *INSERT*, *CREATE* and *DROP*, a single table will always be returned. *SELECT*\nstatements can return more than one table, in case of joins. Table objects offer a single\nmethod, \"name\" which returns the table name.\n\nparams\nmy $paramNum = $stmt->params();  # Scalar context\nmy @params = $stmt->params();    # Array context\nmy($p1, $p2) = ($stmt->params(0), $stmt->params(1));\n\nThe \"params\" method returns information about the input parameters used in a statement. For\nexample, consider the following:\n\nINSERT INTO foo VALUES (?, ?)\n\nThis would return two instances of \"SQL::Statement::Param\". Param objects implement a single\nmethod, \"$param-\"num()>, which retrieves the parameter number. (0 and 1, in the above example).\nAs of now, not very useful ... :-)\n\nrowvalues\nmy $rowValueNum = $stmt->rowvalues(); # Scalar context\nmy @rowValues = $stmt->rowvalues(0);  # Array context\nmy($rval1, $rval2) = ($stmt->rowvalues(0,0),\n$stmt->rowvalues(0,1));\n\nThis method is used for statements like\n\nUPDATE $table SET $col1 = $val1, $col2 = $val2, ...\n$colN = $valN WHERE ...\nINSERT INTO $table (...) VALUES ($val1, $val2, ..., $valN),\n($val1, $val2, ..., $valN)\n\nto read the values $val1, $val2, ... $valN. It returns (lists of) scalar values or\n\"SQL::Statement::Param\" instances.\n\norder\nmy $orderNum = $stmt->order();   # Scalar context\nmy @order = $stmt->order();      # Array context\nmy($o1, $o2) = ($stmt->order(0), $stmt->order(1));\n\nIn *SELECT* statements you can use this for looking at the ORDER clause. Example:\n\nSELECT * FROM FOO ORDER BY id DESC, name\n\nIn this case, \"order\" could return 2 instances of \"SQL::Statement::Order\". You can use the\nmethods \"$o->table()\", \"$o->column()\", \"$o->direction()\" and \"$o->desc()\" to examine the order\nobject.\n\nlimit\nmy $limit = $stmt->limit();\n\nIn a SELECT statement you can use a \"LIMIT\" clause to implement cursoring:\n\nSELECT * FROM FOO LIMIT 5\nSELECT * FROM FOO LIMIT 5, 5\nSELECT * FROM FOO LIMIT 10, 5\n\nThese three statements would retrieve the rows 0..4, 5..9, 10..14 of the table FOO,\nrespectively. If no \"LIMIT\" clause is used, then the method \"$stmt->limit\" returns undef.\nOtherwise it returns the limit number (the maximum number of rows) from the statement (5 or 10\nfor the statements above).\n\noffset\nmy $offset = $stmt->offset();\n\nIf no \"LIMIT\" clause is used, then the method \"$stmt->limit\" returns *undef*. Otherwise it\nreturns the offset number (the index of the first row to be included in the limit clause).\n\nwherehash\nmy $wherehash = $stmt->wherehash();\n\nTo manually evaluate the *WHERE* clause, fetch the topmost where clause node with the\n\"wherehash\" method. Then evaluate the left-hand and right-hand side of the operation, perhaps\nrecursively. Once that is done, apply the operator and finally negate the result, if required.\n\nThe where clause nodes have (up to) 4 attributes:\n\nop          contains the operator, one of \"AND\", \"OR\", \"=\", \"<>\", \">=\", \">\", \"<=\", \"<\", \"LIKE\",\n\"CLIKE\", \"IS\", \"IN\", \"BETWEEN\" or a user defined operator, if any.\n\narg1        contains the left-hand side of the operator. This can be a scalar value, a hash\ncontaining column or function definition, a parameter definition (hash has attribute\n\"type\" defined) or another operation (hash has attribute \"op\" defined).\n\narg2        contains the right-hand side of the operator. This can be a scalar value, a hash\ncontaining column or function definition, a parameter definition (hash has attribute\n\"type\" defined) or another operation (hash has attribute \"op\" defined).\n\nneg         contains a TRUE value, if the operation result must be negated after evaluation.\n\nTo illustrate the above, consider the following WHERE clause:\n\nWHERE NOT (id > 2 AND name = 'joe') OR name IS NULL\n\nWe can represent this clause by the following tree:\n\n(id > 2)   (name = 'joe')\n\\   /\nNOT         AND\n\\      (name IS NULL)\n\\    /\nOR\n\nThus the WHERE clause would return an SQL::Statement::Op instance with the op() field set to\n'OR'. The arg2() field would return another SQL::Statement::Op instance with arg1() being the\nSQL::Statement::Column instance representing id, the arg2() field containing the value undef\n(NULL) and the op() field being 'IS'.\n\nThe arg1() field of the topmost Op instance would return an Op instance with op() eq 'AND' and",
            "subsections": [
                {
                    "name": "neg",
                    "content": "= 'joe'\".\n\nOf course there's a ready-for-use method for WHERE clause evaluation:\n\nThe WHERE clause evaluation depends on an object being used for fetching parameter and column\nvalues. Usually this can be an SQL::Statement::RAM::Table object or SQL::Eval object, but in\nfact it can be any object that supplies the methods\n\n$val = $eval->param($paramNum);\n$val = $eval->column($table, $column);\n\nOnce you have such an object, you can call evalwhere;\n\n$match = $stmt->evalwhere($eval);\n\nwhere\nmy $where = $stmt->where();\n\nThis method is used to examine the syntax tree of the \"WHERE\" clause. It returns *undef* (if no\n\"WHERE\" clause was used) or an instance of SQL::Statement::Term.\n\nThe where clause is evaluated automatically on the current selected row of the table currently\nworked on when it's \"value()\" method is invoked.\n\n\"SQL::Statement\" creates the object tree for where clause evaluation directly after successfully\nparsing a statement from the given \"whereclause\", if any.\n"
                }
            ]
        },
        "Executing and fetching data from SQL statements": {
            "content": "execute\nWhen called from a DBD or other subclass of SQL::Statement, the execute() method will be\nexecuted against whatever data-source (persistent storage) is supplied by the DBD or the\nsubclass (e.g. CSV files for DBD::CSV, or BerkeleyDB for DBD::DBM). If you are using\nSQL::Statement directly rather than as a subclass, you can call the execute() method and the\nstatements will be executed() using temporary in-memory tables. When used directly, like that,\nyou need to create a cache hashref and pass it as the first argument to execute:\n\nmy $cache  = {};\nmy $parser = SQL::Parser->new();\nmy $stmt   = SQL::Statement->new('CREATE TABLE x (id INT)',$parser);\n$stmt->execute( $cache );\n\nIf you are using a statement with placeholders, those can be passed to execute after the $cache:\n\n$stmt      = SQL::Statement->new('INSERT INTO y VALUES(?,?)',$parser);\n$stmt->execute( $cache, 7, 'foo' );\n\nfetch\nOnly a single \"fetch()\" method is provided - it returns a single row of data as an arrayref. Use\na loop to fetch all rows:\n\nwhile (my $row = $stmt->fetch()) {\n# ...\n}\n\nan example of executing and fetching\n#!/usr/bin/perl -w\nuse strict;\nuse SQL::Statement;\n\nmy $cache={};\nmy $parser = SQL::Parser->new();\nfor my $sql(split /\\n/,\n\"  CREATE TABLE a (b INT)\nINSERT INTO a VALUES(1)\nINSERT INTO a VALUES(2)\nSELECT MAX(b) FROM a  \"\n)\n{\n$stmt = SQL::Statement->new($sql,$parser);\n$stmt->execute($cache);\nnext unless $stmt->command eq 'SELECT';\nwhile (my $row=$stmt->fetch)\n{\nprint \"@$row\\n\";\n}\n}\nEND\n\nAUTHOR & COPYRIGHT\nCopyright (c) 2005, Jeff Zucker <jzuckerATcpan.org>, all rights reserved. Copyright (c)\n2009-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::Structure - parse and examine structure of SQL queries",
    "flags": [],
    "examples": [],
    "see_also": []
}