command¶

Returns the SQL command. See SQL::Statement::Syntax for supported command. Example:

    my $command = $stmt->command();

column definitions¶

    my $numColumns = $stmt->column_defs();  # Scalar context
    my @columnList = $stmt->column_defs();  # Array context
    my($col1, $col2) = ($stmt->column_defs(0), $stmt->column_defs(1));

This method is used to retrieve column lists. The meaning depends on the query command:

    SELECT $col1, $col2, ... $colN FROM $table WHERE ...
    UPDATE $table SET $col1 = $val1, $col2 = $val2, ...
        $colN = $valN WHERE ...
    INSERT INTO $table ($col1, $col2, ..., $colN) VALUES (...)

When used without arguments, the method returns a list of the columns $col1, $col2, ..., $colN, you may alternatively use a column number as argument. Note that the column list may be empty as in

    INSERT INTO $table VALUES (...)

and in CREATE or DROP statements.

But what does "returning a column" mean? It is returning an "SQL::Statement::Util::Column" instance, a class that implements the methods "table" and "name", both returning the respective scalar. For example, consider the following statements:

    INSERT INTO foo (bar) VALUES (1)
    SELECT bar FROM foo WHERE ...
    SELECT foo.bar FROM foo WHERE ...

In all these cases exactly one column instance would be returned with

    $col->name() eq 'bar'
    $col->table() eq 'foo'

tables¶

    my $tableNum = $stmt->tables();  # Scalar context
    my @tables = $stmt->tables();    # Array context
    my($table1, $table2) = ($stmt->tables(0), $stmt->tables(1));

Similar to "columns", this method returns instances of "SQL::Statement::Table". For UPDATE, DELETE, INSERT, CREATE and DROP, a single table will always be returned. SELECT statements can return more than one table, in case of joins. Table objects offer a single method, "name" which returns the table name.

params¶

    my $paramNum = $stmt->params();  # Scalar context
    my @params = $stmt->params();    # Array context
    my($p1, $p2) = ($stmt->params(0), $stmt->params(1));

The "params" method returns information about the input parameters used in a statement. For example, consider the following:

    INSERT INTO foo VALUES (?, ?)

This would return two instances of "SQL::Statement::Param". Param objects implement a single method, "$param-"num()>, which retrieves the parameter number. (0 and 1, in the above example). As of now, not very useful ... :-)

row_values¶

    my $rowValueNum = $stmt->row_values(); # Scalar context
    my @rowValues = $stmt->row_values(0);  # Array context
    my($rval1, $rval2) = ($stmt->row_values(0,0),
                          $stmt->row_values(0,1));

This method is used for statements like

    UPDATE $table SET $col1 = $val1, $col2 = $val2, ...
        $colN = $valN WHERE ...
    INSERT INTO $table (...) VALUES ($val1, $val2, ..., $valN),
                                    ($val1, $val2, ..., $valN)

to read the values $val1, $val2, ... $valN. It returns (lists of) scalar values or "SQL::Statement::Param" instances.

order¶

    my $orderNum = $stmt->order();   # Scalar context
    my @order = $stmt->order();      # Array context
    my($o1, $o2) = ($stmt->order(0), $stmt->order(1));

In SELECT statements you can use this for looking at the ORDER clause. Example:

    SELECT * FROM FOO ORDER BY id DESC, name

In this case, "order" could return 2 instances of "SQL::Statement::Order". You can use the methods "$o->table()", "$o->column()", "$o->direction()" and "$o->desc()" to examine the order object.

limit¶

    my $limit = $stmt->limit();

In a SELECT statement you can use a "LIMIT" clause to implement cursoring:

    SELECT * FROM FOO LIMIT 5
    SELECT * FROM FOO LIMIT 5, 5
    SELECT * FROM FOO LIMIT 10, 5

These three statements would retrieve the rows 0..4, 5..9, 10..14 of the table FOO, respectively. If no "LIMIT" clause is used, then the method "$stmt->limit" returns undef. Otherwise it returns the limit number (the maximum number of rows) from the statement (5 or 10 for the statements above).

offset¶

    my $offset = $stmt->offset();

If no "LIMIT" clause is used, then the method "$stmt->limit" returns undef. Otherwise it returns the offset number (the index of the first row to be included in the limit clause).

where_hash¶

    my $where_hash = $stmt->where_hash();

To manually evaluate the WHERE clause, fetch the topmost where clause node with the "where_hash" method. Then evaluate the left-hand and right-hand side of the operation, perhaps recursively. Once that is done, apply the operator and finally negate the result, if required.

The where clause nodes have (up to) 4 attributes:

op

contains the operator, one of "AND", "OR", "=", "<>", ">=", ">", "<=", "<", "LIKE", "CLIKE", "IS", "IN", "BETWEEN" or a user defined operator, if any.

arg1

contains the left-hand side of the operator. This can be a scalar value, a hash containing column or function definition, a parameter definition (hash has attribute "type" defined) or another operation (hash has attribute "op" defined).

arg2

contains the right-hand side of the operator. This can be a scalar value, a hash containing column or function definition, a parameter definition (hash has attribute "type" defined) or another operation (hash has attribute "op" defined).

neg

contains a TRUE value, if the operation result must be negated after evaluation.

To illustrate the above, consider the following WHERE clause:

    WHERE NOT (id > 2 AND name = 'joe') OR name IS NULL

We can represent this clause by the following tree:

              (id > 2)   (name = 'joe')
                     \   /
          NOT         AND
                         \      (name IS NULL)
                          \    /
                            OR

Thus the WHERE clause would return an SQL::Statement::Op instance with the op() field set to 'OR'. The arg2() field would return another SQL::Statement::Op instance with arg1() being the SQL::Statement::Column instance representing id, the arg2() field containing the value undef (NULL) and the op() field being 'IS'.

The arg1() field of the topmost Op instance would return an Op instance with op() eq 'AND' and neg() returning TRUE. The arg1() and arg2() fields would be Op's representing "id > 2" and "name = 'joe'".

Of course there's a ready-for-use method for WHERE clause evaluation:

The WHERE clause evaluation depends on an object being used for fetching parameter and column values. Usually this can be an SQL::Statement::RAM::Table object or SQL::Eval object, but in fact it can be any object that supplies the methods

    $val = $eval->param($paramNum);
    $val = $eval->column($table, $column);

Once you have such an object, you can call eval_where;

    $match = $stmt->eval_where($eval);

where¶

    my $where = $stmt->where();

This method is used to examine the syntax tree of the "WHERE" clause. It returns undef (if no "WHERE" clause was used) or an instance of SQL::Statement::Term.

The where clause is evaluated automatically on the current selected row of the table currently worked on when it's value() method is invoked.

"SQL::Statement" creates the object tree for where clause evaluation directly after successfully parsing a statement from the given "where_clause", if any.

execute¶

When called from a DBD or other subclass of SQL::Statement, the execute() method will be executed against whatever data-source (persistent storage) is supplied by the DBD or the subclass (e.g. CSV files for DBD::CSV, or BerkeleyDB for DBD::DBM). If you are using SQL::Statement directly rather than as a subclass, you can call the execute() method and the statements will be executed() using temporary in-memory tables. When used directly, like that, you need to create a cache hashref and pass it as the first argument to execute:

  my $cache  = {};
  my $parser = SQL::Parser->new();
  my $stmt   = SQL::Statement->new('CREATE TABLE x (id INT)',$parser);
  $stmt->execute( $cache );

If you are using a statement with placeholders, those can be passed to execute after the $cache:

  $stmt      = SQL::Statement->new('INSERT INTO y VALUES(?,?)',$parser);
  $stmt->execute( $cache, 7, 'foo' );

fetch¶

Only a single fetch() method is provided - it returns a single row of data as an arrayref. Use a loop to fetch all rows:

 while (my $row = $stmt->fetch()) {
     # ...
 }

an example of executing and fetching¶

 #!/usr/bin/perl -w
 use strict;
 use SQL::Statement;
 my $cache={};
 my $parser = SQL::Parser->new();
 for my $sql(split /\n/,
 "  CREATE TABLE a (b INT)
    INSERT INTO a VALUES(1)
    INSERT INTO a VALUES(2)
    SELECT MAX(b) FROM a  "
 )
 {
    $stmt = SQL::Statement->new($sql,$parser);
    $stmt->execute($cache);
    next unless $stmt->command eq 'SELECT';
    while (my $row=$stmt->fetch)
    {
        print "@$row\n";
    }
 }
 __END__