Driver Subclasses

After setting up the DBI handle that it will use, the SQLEngine is reblessed into a matching subclass, if one is available. Thus, if you connect a DBIx::SQLEngine through DBD::mysql, by passing a DSN such as "dbi:mysql:test", your object will automatically shift to being an instance of the DBIx::SQLEngine::Driver::Mysql class. This allows the driver-specific subclasses to compensate for differences in the SQL dialect or execution ideosyncracies of that platform.

This release includes the following driver subclasses, which support the listed database platforms:

Mysql

MySQL via DBD::mysql or DBD::ODBC (Free RDBMS)

Pg

PostgreSQL via DBD::Pg or DBD::ODBC (Free RDBMS)

Oracle

Oracle via DBD::Oracle or DBD::ODBC (Commercial RDBMS)

Sybase

Sybase via DBD::Sybase or DBD::ODBC (Commercial RDBMS)

Informix

Informix via DBD::Informix or DBD::ODBC (Commercial RDBMS)

MSSQL

Microsoft SQL Server via DBD::ODBC (Commercial RDBMS)

Sybase::MSSQL

Microsoft SQL Server via DBD::Sybase and FreeTDS libraries

SQLite

SQLite via DBD::SQLite (Free Package)

AnyData

AnyData via DBD::AnyData (Free Package)

CSV

CSV files via DBD::CSV (Free Package)

To understand which SQLEngine driver class will be used for a given database connection, see the discussion of driver and class names in DBIx::AnyDBD.

The public interface of described below is shared by all of the driver subclasses. The superclass methods aim to produce and perform generic queries in an database-independent fashion, using standard SQL syntax. Subclasses may override these methods to compensate for idiosyncrasies of their database server or mechanism. To facilitate cross-platform subclassing, many of these methods are implemented by calling combinations of other methods, which may individually be overridden by subclasses.

Driver Object Creation

Public Methods: Call the new() method to create a Driver object with associated DBI database handle.

new()

  DBIx::SQLEngine->new( $dsn ) : $sqldb
  DBIx::SQLEngine->new( $dsn, $user, $pass ) : $sqldb
  DBIx::SQLEngine->new( $dsn, $user, $pass, $args ) : $sqldb
  DBIx::SQLEngine->new( $dbh ) : $sqldb
  DBIx::SQLEngine->new( $cnxn_name ) : $sqldb
  DBIx::SQLEngine->new( $cnxn_name, @params ) : $sqldb
    

Based on the arguments supplied, invokes one of the below new_with_* methods and returns the resulting new object.

Internal Methods: These methods are called internally by new().

new_with_connect()

  DBIx::SQLEngine::Driver->new_with_connect( $dsn ) : $sqldb
  DBIx::SQLEngine::Driver->new_with_connect( $dsn, $user, $pass ) : $sqldb
  DBIx::SQLEngine::Driver->new_with_connect( $dsn, $user, $pass, $args ) : $sqldb
    

Accepts the same arguments as the standard DBI connect method.

new_with_dbh()

  DBIx::SQLEngine::Driver->new_with_dbh( $dbh ) : $sqldb
    

Accepts an existing DBI database handle and creates a new Driver object around it.

new_with_name()

  DBIx::SQLEngine::Driver->new_with_name( $cnxn_name ) : $sqldb
  DBIx::SQLEngine::Driver->new_with_name( $cnxn_name, @params ) : $sqldb
    

Passes the provided arguments to interpret_named_connection, defined below, and uses its results to make a new connection.

Named Connections

Public Methods: Call these methods to define connections.

define_named_connections()

  DBIx::SQLEngine->define_named_connections( $name, $cnxn_info )
  DBIx::SQLEngine->define_named_connections( %names_and_info )
    

Defines one or more named connections using the names and definitions provided.

The definition for each connection is expected to be in one of the following formats:

define_named_connections_from_text()

  DBIx::SQLEngine->define_named_connections_from_text($name, $cnxn_info_text)
  DBIx::SQLEngine->define_named_connections_from_text(%names_and_info_text)
    

Defines one or more connections, using some special processing to facilitate storing dynamic connection definitions in an external source such as a text file or database table.

The interpretation of each definition is determined by its first non-whitespace character:

Internal Methods: The following methods are called internally by new_with_name().

named_connections()

  DBIx::SQLEngine::Driver->named_connections() : %names_and_info
  DBIx::SQLEngine::Driver->named_connections( $name ) : $cnxn_info
  DBIx::SQLEngine::Driver->named_connections( \@names ) : @cnxn_info
  DBIx::SQLEngine::Driver->named_connections( $name, $cnxn_info, ... )
  DBIx::SQLEngine::Driver->named_connections( \%names_and_info )
    

Accessor and mutator for a class-wide hash mappping connection names to their definitions. Used internally by the other named_connection methods.

named_connection()

  DBIx::SQLEngine::Driver->named_connection( $name ) : $cnxn_info
    

Retrieves the connection definition matching the name provided. Croaks if no connection has been defined for that name. Used interally by the interpret_named_connection method.

interpret_named_connection()

  DBIx::SQLEngine::Driver->interpret_named_connection($name, @params) : $dbh
  DBIx::SQLEngine::Driver->interpret_named_connection($name, @params) : $dsn
  DBIx::SQLEngine::Driver->interpret_named_connection($name, @params) : @args
    

Combines the connection definition matching the name provided with the following arguments and returns the resulting connection arguments. Croaks if no connection has been defined for that name.

Depending on the definition associated with the name, it is combined with the provided parameters in one the following ways:

Examples: These samples demonstrate use of the named_connections feature.

• Here's a simple definition with a DSN string:

    DBIx::SQLEngine->define_named_connections('test'=>'dbi:mysql:test');
  
    $sqldb = DBIx::SQLEngine->new( 'test' );
      

• Here's an example that includes a user name and password:

    DBIx::SQLEngine->define_named_connections( 
      'reference' => [ 'dbi:mysql:livedata', 'myuser', 'mypasswd' ],
    );
  
    $sqldb = DBIx::SQLEngine->new( 'reference' );
      

• Here's a definition that requires a user name and password to be provided:

    DBIx::SQLEngine->define_named_connections( 
      'production' => [ 'dbi:mysql:livedata', \$1, \$2 ],
    );
  
    $sqldb = DBIx::SQLEngine->new( 'production', $user, $password );
      

• Here's a definition using Perl code to set up the connection arguments:

    DBIx::SQLEngine->define_named_connections( 
      'finance' => sub { "dbi:oracle:accounting", "bob", "123" },
    );
  
    $sqldb = DBIx::SQLEngine->new( 'finance' );
      

• Connection names are interpreted recursively, allowing them to be used as aliases:

    DBIx::SQLEngine->define_named_connections(
      'test'       => 'dbi:AnyData:test',
      'production' => 'dbi:Mysql:our_data:dbhost',
    );
  
    DBIx::SQLEngine->define_named_connections(
      '-active'    => 'production',
    );
  
    $sqldb = DBIx::SQLEngine->new( '-active' );
      

• You can also use named connecctions to hijack regular connections:

    DBIx::SQLEngine->define_named_connections(
      'dbi:Mysql:students:db_host' => 'dbi:AnyData:test',
    );
    
    $sqldb = DBIx::SQLEngine->new( 'dbi:Mysql:students:db_host' );
      

• Connection definitions can be stored in external text files or other sources and then evaluated into data structures or code references. The below code loads a simple text file of query definitions

    open( CNXNS, '/path/to/my/connections' );
    %cnxn_info = map { split /\:\s*/, $_, 2 } grep { /^[^#]/ } <CNXNS>;
    close CNXNS;
  
    $sqldb->define_named_connections_from_text( %cnxn_info );
      

  Placing the following text in the target file will define all of the connections used above:

    # Simple DSN that doesn't need any parameters
    test: dbi:mysql:test
    
    # Definition that includes a user name and password
    reference: [ 'dbi:mysql:livedata', 'myuser', 'mypasswd' ]
    
    # Definition that requires a user name and password 
    production: [ 'dbi:mysql:livedata', \$1, \$2 ]
  
    # Definition using Perl code to set up the connection arguments
    finance: "dbi:oracle:accounting", "bob", "123"
      

Select to Retrieve Data

Public Methods: There are several ways to retrieve information from a SELECT query.

The fetch_* methods select and return matching rows.

fetch_select()

  $sqldb->fetch_select( %sql_clauses ) : $row_hashes
  $sqldb->fetch_select( %sql_clauses ) : ($row_hashes, $column_hashes)
    

Retrieve rows from the datasource as an array of hashrefs. If called in a list context, also returns an array of hashrefs containing information about the columns included in the result set.

fetch_select_rows()

  $sqldb->fetch_select_rows( %sql_clauses ) : $row_arrays
  $sqldb->fetch_select_rows( %sql_clauses ) : ($row_arrays, $column_hashes)
    

Like fetch_select, but returns an array of arrayrefs, rather than hashrefs.

fetch_one_row()

  $sqldb->fetch_one_row( %sql_clauses ) : $row_hash
    

Calls fetch_select, then returns only the first row of results.

fetch_one_value()

  $sqldb->fetch_one_value( %sql_clauses ) : $scalar
    

Calls fetch_select, then returns the first value from the first row of results.

The visit_* and fetchsub_* methods allow you to loop through the returned records without necessarily loading them all into memory at once.

visit_select()

  $sqldb->visit_select( $code_ref, %sql_clauses ) : @results
  $sqldb->visit_select( %sql_clauses, $code_ref ) : @results
    

Retrieve rows from the datasource as a series of hashrefs, and call the user provided function for each one. For your convenience, will accept a coderef as either the first or the last argument. Returns the results returned by each of those function calls. Processing with visit_select rather than fetch_select can be more efficient if you are looping over a large number of rows and do not need to keep them all in memory.

Note that some DBI drivers do not support simultaneous use of more than one statement handle; if you are using such a driver, you will receive an error if you run another query from within your code reference.

visit_select_rows()

  $sqldb->visit_select_rows( $code_ref, %sql_clauses ) : @results
  $sqldb->visit_select_rows( %sql_clauses, $code_ref ) : @results
    

Like visit_select, but for each row the code ref is called with the current row retrieved as a list of values, rather than a hash ref.

fetchsub_select()

  $self->fetchsub_select( %clauses ) : $coderef
    

Execute a query and returns a code reference that can be called repeatedly to retrieve a row as a hashref. When all of the rows have been fetched it will return undef.

The code reference is blessed so that when it goes out of scope and is destroyed it can call the statement handle's finish() method.

Note that some DBI drivers do not support simultaneous use of more than one statement handle; if you are using such a driver, you will receive an error if you run another query while this code reference is still in scope.

fetchsub_select_rows()

  $self->fetchsub_select_rows( %clauses ) : $coderef
    

Like fetchsub_select, but for each row returns a list of values, rather than a hash ref. When all of the rows have been fetched it will return an empty list.

SQL Select Clauses: The above select methods accept a hash describing the clauses of the SQL statement they are to generate, using the values provided for the keys defined below.

'sql'

May contain a plain SQL statement to be executed, or a reference to an array of a SQL statement followed by parameters for embedded placeholders. Can not be used in combination with the table and columns arguments.

'named_query'

Uses the named_query catalog to build the query. May contain a defined query name, or a reference to an array of a query name followed by parameters to be handled by interpret_named_query. See "NAMED QUERY CATALOG" for details.

'union'

Calls sql_union() to produce a query that combines the results of multiple calls to sql_select(). Should contain a reference to an array of hash-refs, each of which contains key-value pairs to be used in one of the unified selects. Can not be used in combination with the table and columns arguments.

'table' or 'tables'

The name of the tables to select from. Required unless one of the above parameters is provided. May contain a string with one or more table names, or a reference to an array or hash of table names and join criteria. See the sql_join() method for details.

'columns'

Optional; defaults to '*'. May contain a comma-separated string of column names, or an reference to an array of column names, or a reference to a hash mapping column names to "as" aliases, or a reference to an object with a "column_names" method.

'distinct'

Optional. Boolean. Adds the "distinct" keyword to the query if value is true.

'where' or 'criteria'

Optional. May contain a literal SQL where clause, an array ref with a SQL clause and parameter list, a hash of field => value pairs, or an object that supports a sql_where() method. See the sql_where() method for details.

'group'

Optional. May contain a comma-separated string of column names or experessions, or an reference to an array of the same.

'order'

Optional. May contain a comma-separated string of column names or experessions, optionally followed by "DESC", or an reference to an array of the same.

'limit'

Optional. Maximum number of rows to be retrieved from the server. Relies on DBMS-specific behavior provided by sql_limit().

'offset'

Optional. Number of rows at the start of the result which should be skipped over. Relies on DBMS-specific behavior provided by sql_limit().

Examples: These samples demonstrate use of the select features.

• Each query can be written out explicitly or generated on demand using whichever syntax is most appropriate to your application; the following examples are functionally equivalent:

    $hashes = $sqldb->fetch_select( 
      sql => "select * from students where status = 'minor'"
    );
  
    $hashes = $sqldb->fetch_select( 
      sql => [ 'select * from students where status = ?', 'minor' ]
    );
  
    $hashes = $sqldb->fetch_select( 
      sql => 'select * from students', where => { 'status' => 'minor' }
    );
  
    $hashes = $sqldb->fetch_select( 
      table => 'students', where => [ 'status = ?', 'minor' ]
    );
  
    $hashes = $sqldb->fetch_select( 
      table => 'students', where => { 'status' => 'minor' } 
    );
  
    $hashes = $sqldb->fetch_select( 
      table => 'students', where => 
        DBIx::SQLEngine::Criteria->type_new('Equality','status'=>'minor')
    );
      

• Both generated and explicit SQL can be stored as named queries and then used again later; the following examples are equivalent to those above:

    $sqldb->define_named_query(
      'minor_students' => "select * from students where status = 'minor'" 
    );
    $hashes = $sqldb->fetch_select( 
      named_query => 'minor_students' 
    );
  
    $sqldb->define_named_query(
      'minor_students' => {
          table => 'students', where => { 'status' => 'minor' } 
      }
    );
    $hashes = $sqldb->fetch_select( 
      named_query => 'minor_students' 
    );
      

• Here's a use of some optional clauses listing the columns returned, and specifying a sort order:

    $hashes = $sqldb->fetch_select( 
      table => 'students', columns => 'name, age', order => 'name'
    );
      

• Here's a where clause that uses a function to find the youngest people; note the use of a backslash to indicate that "min(age)" is an expression to be evaluated by the database server, rather than a literal value:

    $hashes = $sqldb->fetch_select( 
      table => 'students', where => { 'age' => \"min(age)" } 
    );
      

• If you know that only one row will match, you can use fetch_one_row:

    $joe = $sqldb->fetch_one_row( 
      table => 'student', where => { 'id' => 201 }
    );
      

  All of the SQL select clauses are accepted, including explicit SQL statements with parameters:

    $joe = $sqldb->fetch_one_row( 
      sql => [ 'select * from students where id = ?', 201 ]
    );
      

• And when you know that there will only be one row and one column in your result set, you can use fetch_one_value:

    $count = $sqldb->fetch_one_value( 
      table => 'student', columns => 'count(*)'
    );
      

  All of the SQL select clauses are accepted, including explicit SQL statements with parameters:

    $maxid = $sqldb->fetch_one_value( 
      sql => [ 'select max(id) from students where status = ?', 'minor' ]
    );
      

• You can use visit_select to make a traversal of all rows that match a query without retrieving them all at once:

    $sqldb->visit_select( 
      table => 'student',
      sub {
        my $student = shift;
        print $student->{id}, $student->{name}, $student->{age};
      }
    );
      

  You can collect values along the way:

    my @firstnames = $sqldb->visit_select( 
      table => 'student',
      sub {
        my $student = shift;
        ( $student->{name} =~ /(\w+)\s/ ) ? $1 : $student->{name};
      }
    );
      

  You can visit with any combination of the other clauses supported by fetch_select:

     $sqldb->visit_select( 
      table => 'student', 
      columns => 'id, name', 
      order => 'name, id desc',
      where => 'age < 22',
      sub {
        my $student = shift;
        print $student->{id}, $student->{name};
      }
    );
      

• You can use fetchsub_select to make a traversal of some or all rows without retrieving them all at once:

    my $fetchsub = $sqldb->fetchsub_select( 
      table => 'student',
      where => 'age < 22',
    );
    while ( my $student = $fetchsub->() ) {
      print $student->{id}, $student->{name}, $student->{age};
    }
      

  You can use fetchsub_select_rows to treat each row as a list of values instead of a hashref:

    my $fetchsub = $sqldb->fetchsub_select_rows( 
      table => 'student',
      columns => 'id, name, age',
    );
    while ( my @student = $fetchsub->() ) {
      print $student[0], $student[1], $student[2];
    }
      

Internal Methods: The following methods are used to construct select queries. They are called automatically by the public select methods, and do not need to be invoked directly.

sql_select()

  $sqldb->sql_select ( %sql_clauses ) : $sql_stmt, @params
    

Generate a SQL select statement and returns it as a query string and a list of values to be bound as parameters. Internally, this sql_ method is used by the fetch_ and visit_ methods above, and calls any of the other sql_ methods necessary.

sql_where()

  $sqldb->sql_where( $criteria, $sql, @params ) : $sql, @params
    

Modifies the SQL statement and parameters list provided to append the specified criteria as a where clause. Triggered by use of a where or criteria clause in a call to sql_select(), sql_update(), or sql_delete().

The criteria may be a literal SQL where clause (everything after the word "where"), or a reference to an array of a SQL string with embedded placeholders followed by the values that should be bound to those placeholders.

If the criteria argument is a reference to hash, it is treated as a set of field-name => value pairs, and a SQL expression is created that requires each one of the named fields to exactly match the value provided for it, or if the value is an array reference to match any one of the array's contents; see DBIx::SQLEngine::Criteria::HashGroup for details.

Alternately, if the criteria argument is a reference to an object which supports a sql_where() method, the results of that method will be used; see DBIx::SQLEngine::Criteria for classes with this behavior.

If no SQL statement or parameters are provided, this just returns the where clause and associated parameters. If a SQL statement is provided, the where clauses is appended to it; if the SQL statement already includes a where clause, the additional criteria are inserted into the existing statement and AND'ed together with the existing criteria.

sql_escape_text_for_like()

  $sqldb->sql_escape_text_for_like ( $text ) : $escaped_expr
    

Fails with message "DBMS-Specific Function".

Subclasses should, based on the datasource's server_type, protect a literal value for use in a like expression.

sql_join()

  $sqldb->sql_join( $table1, $table2, ... ) : $sql, @params
  $sqldb->sql_join( \%table_names_and_criteria ) : $sql, @params
  $sqldb->sql_join( $table1, \%criteria, $table2 ) : $sql, @params
  $sqldb->sql_join( $table1, $join_type=>\%criteria, $table2 ) : $sql, @params
    

Processes one or more table names to create the "from" clause of a select statement. Table names may appear in succession for normal "cross joins", or you may specify a "complex join" by placing an inner or outer joining operation between them.

A joining operation consists of a string containing the word "join", followed by an array reference or hash reference that specifies the criteria. The string should be one of the types of joins supported by your database, typically the following: "cross join", "inner join", "outer join", "left outer join", "right outer join". Any underscores in the string are converted to spaces, making it easier to use as an unquoted string.

The joining criteria can be an array reference of a string containing a bit SQL followed by any necessary placeholder parameters, or a hash reference which will be converted to SQL with the DBIx::SQLEngine::Criteria package.

If an array reference is used as a table name, its contents are evaluated by being passed to another call to sql_join, and then the results are treated as a parenthesized expression.

If a hash reference is used as a table name, its contents are evaluated as criteria in "table1.column1" => "table2.column2" format. The table names and criteria are passed to another call to sql_join, and then the results are treated as a parenthesized expression.

Portability: While the cross and inner joins are widely supported, the various outer join capabilities are only present in some databases. Subclasses may provide a degree of emulation; for one implementation of this, see DBIx::SQLEngine::Driver::Trait::NoComplexJoins.

Examples: These samples demonstrate use of the join feature.

sql_limit()

  $sqldb->sql_limit( $limit, $offset, $sql, @params ) : $sql, @params
    

Modifies the SQL statement and parameters list provided to apply the specified limit and offset requirements. Triggered by use of a limit or offset clause in a call to sql_select().

Portability: Limit and offset clauses are handled differently by various DBMS platforms. For example, MySQL accepts "limit 20,10", Postgres "limit 10 offset 20", and Oracle requires a nested select with rowcount. The sql_limit method can be overridden by subclasses to adjust this behavior.

Examples: These samples demonstrate use of the limit feature.

sql_union()

  $sqldb->sql_union( \%clauses_1, \%clauses_2, ... ) : $sql, @params
    

Returns a combined select query using the "union" operator between the SQL statements produced by calling sql_select() with each of the provided arrays of arguments. Triggered by use of a union clause in a call to sql_select().

Portability: Union queries are only supported by some databases. Croaks if the dbms_union_unsupported() capability method is set. Subclasses may provide a degree of emulation; for one implementation of this, see DBIx::SQLEngine::Driver::Trait::NoUnions.

Examples: These samples demonstrate use of the union feature.

Insert to Add Data

do_insert()

  $sqldb->do_insert( %sql_clauses ) : $row_count
    

Insert a single row into a table in the datasource. Should return 1, unless there's an exception.

do_bulk_insert()

  $sqldb->do_bulk_insert( %sql_clauses, values => [ @array_or_hash_refs ] ) : $row_count
    

Inserts several rows into a table. Returns the number of rows inserted.

This is provided so that drivers which have alternate bulk-loader interfaces can hook into that support here, and to allow specialty options like "statements_per_transaction =" 100> in order to optimize performance on servers such as Oracle, where auto-committing one statement at a time is slow.

Internal Methods: The following method is called by do_insert() and does not need to be called directly.

sql_insert()

  $sqldb->sql_insert ( %sql_clauses ) : $sql_stmt, @params
    

Generate a SQL insert statement and returns it as a query string and a list of values to be bound as parameters. Internally, this sql_ method is used by the do_ method above.

SQL Insert Clauses: The above insert methods accept a hash describing the clauses of the SQL statement they are to generate, and require a value for one or more of the following keys:

'sql'

Optional; overrides all other arguments. May contain a plain SQL statement to be executed, or a reference to an array of a SQL statement followed by parameters for embedded placeholders.

'named_query'

Uses the named_query catalog to build the query. May contain a defined query name, or a reference to an array of a query name followed by parameters to be handled by interpret_named_query. See "NAMED QUERY CATALOG" for details.

'table'

Required. The name of the table to insert into.

'columns'

Optional; defaults to '*'. May contain a comma-separated string of column names, or an reference to an array of column names, or a reference to a hash whose keys contain the column names, or a reference to an object with a "column_names" method.

'values'

Required. May contain a string with one or more comma-separated quoted values or expressions in SQL format, or a reference to an array of values to insert in order, or a reference to a hash whose values are to be inserted. If an array or hash reference is used, each value may either be a scalar to be used as a literal value (passed via placeholder), or a reference to a scalar to be used directly (such as a sql function or other non-literal expression).

'sequence'

Optional. May contain a string with the name of a column in the target table which should receive an automatically incremented value. If present, triggers use of the DMBS-specific do_insert_with_sequence() method, described below.

Examples: These samples demonstrate use of the insert feature.

• Here's a simple insert using a hash of column-value pairs:

    $sqldb->do_insert( 
      table => 'students', 
      values => { 'name'=>'Dave', 'age'=>'19', 'status'=>'minor' } 
    );
      

• Here's the same insert using separate arrays of column names and values to be inserted:

    $sqldb->do_insert( 
      table => 'students', 
      columns => [ 'name', 'age', 'status' ], 
      values => [ 'Dave', '19', 'minor' ]
    );
      

• Here's a bulk insert of multiple rows:

    $sqldb->do_insert( 
      table => 'students', 
      columns => [ 'name', 'age', 'status' ], 
      values => [
        [ 'Dave', '19', 'minor' ],
        [ 'Alice', '20', 'minor' ],
        [ 'Sam', '22', 'adult' ],
      ]
    );
      

• Of course you can also use your own arbitrary SQL and placeholder parameters.

    $sqldb->do_insert( 
      sql=>['insert into students (id, name) values (?, ?)', 201, 'Dave']
    );
      

• And the named_query interface is supported as well:

    $sqldb->define_named_query(
      'insert_student' => 'insert into students (id, name) values (?, ?)'
    );
    $hashes = $sqldb->do_insert( 
      named_query => [ 'insert_student', 201, 'Dave' ]
    );
      

Internal Methods: The following methods are called by do_insert() and do not need to be called directly.

do_insert_with_sequence()

  $sqldb->do_insert_with_sequence( $seq_name, %sql_clauses ) : $row_count
    

Insert a single row into a table in the datasource, using a sequence to fill in the values of the column named in the first argument. Should return 1, unless there's an exception.

Fails with message "DBMS-Specific Function".

Portability: Auto-incrementing sequences are handled differently by various DBMS platforms. For example, the MySQL and MSSQL subclasses use auto-incrementing fields, Oracle and Pg use server-specific sequence objects, and AnyData and CSV lack this capability, which can be emulated with an ad-hoc table of incrementing values.

To standardize their use, this package defines an interface with several typical methods which may or may not be supported by individual subclasses. You may need to consult the documentation for the SQLEngine Driver subclass and DBMS platform you're using to confirm that the sequence functionality you need is available.

Drivers which don't support native sequences may provide a degree of emulation; for one implementation of this, see DBIx::SQLEngine::Driver::Trait::NoSequences.

Subclasses will probably want to call either the _seq_do_insert_preinc() method or the _seq_do_insert_postfetch() method, and define the appropriate other seq_* methods to support them. These two methods are not part of the public interface but instead provide a template for the two most common types of insert-with-sequence behavior. The _seq_do_insert_preinc() method first obtaines a new number from the sequence using seq_increment(), and then performs a normal do_insert(). The _seq_do_insert_postfetch() method performs a normal do_insert() and then fetches the resulting value that was automatically incremented using seq_fetch_current().

seq_fetch_current()

  $sqldb->seq_fetch_current( $table, $field ) : $current_value
    

Fetches the current sequence value.

Fails with message "DBMS-Specific Function".

seq_increment()

  $sqldb->seq_increment( $table, $field ) : $new_value
    

Increments the sequence, and returns the newly allocated value.

Fails with message "DBMS-Specific Function".

Update to Change Data

do_update()

  $sqldb->do_update( %sql_clauses ) : $row_count
    

Modify one or more rows in a table in the datasource.

Internal Methods: These methods are called by the public update method.

sql_update()

  $sqldb->sql_update ( %sql_clauses ) : $sql_stmt, @params
    

Generate a SQL update statement and returns it as a query string and a list of values to be bound as parameters. Internally, this sql_ method is used by the do_ method above.

SQL Update Clauses: The above update methods accept a hash describing the clauses of the SQL statement they are to generate, and require a value for one or more of the following keys:

'sql'

Optional; conflicts with table, columns and values arguments. May contain a plain SQL statement to be executed, or a reference to an array of a SQL statement followed by parameters for embedded placeholders.

'named_query'

Uses the named_query catalog to build the query. May contain a defined query name, or a reference to an array of a query name followed by parameters to be handled by interpret_named_query. See "NAMED QUERY CATALOG" for details.

'table'

Required unless sql argument is used. The name of the table to update.

'columns'

Optional unless sql argument is used. Defaults to '*'. May contain a comma-separated string of column names, or an reference to an array of column names, or a reference to a hash whose keys contain the column names, or a reference to an object with a "column_names" method.

'values'

Required unless sql argument is used. May contain a string with one or more comma-separated quoted values or expressions in SQL format, or a reference to an array of values to insert in order, or a reference to a hash whose values are to be inserted. If an array or hash reference is used, each value may either be a scalar to be used as a literal value (passed via placeholder), or a reference to a scalar to be used directly (such as a sql function or other non-literal expression).

'where' or 'criteria'

Optional, but remember that ommitting this will cause all of your rows to be updated! May contain a literal SQL where clause, an array ref with a SQL clause and parameter list, a hash of field => value pairs, or an object that supports a sql_where() method. See the sql_where() method for details.

Examples: These samples demonstrate use of the update feature.

• Here's a basic update statement with a hash of columns-value pairs to change:

    $sqldb->do_update( 
      table => 'students', 
      where => 'age > 20', 
      values => { 'status'=>'adult' } 
    );
      

• Here's an equivalent update statement using separate lists of columns and values:

    $sqldb->do_update( 
      table => 'students', 
      where => 'age > 20', 
      columns => [ 'status' ], 
      values => [ 'adult' ]
    );
      

• You can also use your own arbitrary SQL statements and placeholders:

    $sqldb->do_update( 
      sql=>['update students set status = ? where age > ?', 'adult', 20]
    );
      

• And the named_query interface is supported as well:

    $sqldb->define_named_query(
      'update_minors' => 
          [ 'update students set status = ? where age > ?', 'adult', 20 ]
    );
    $hashes = $sqldb->do_update( 
      named_query => 'update_minors'
    );
      

Delete to Remove Data

do_delete()

  $sqldb->do_delete( %sql_clauses ) : $row_count
    

Delete one or more rows in a table in the datasource.

Internal Methods: These methods are called by the public delete methods.

sql_delete()

  $sqldb->sql_delete ( %sql_clauses ) : $sql_stmt, @params
    

Generate a SQL delete statement and returns it as a query string and a list of values to be bound as parameters. Internally, this sql_ method is used by the do_ method above.

SQL Delete Clauses: The above delete methods accept a hash describing the clauses of the SQL statement they are to generate, and require a value for one or more of the following keys:

'sql'

Optional; conflicts with 'table' argument. May contain a plain SQL statement to be executed, or a reference to an array of a SQL statement followed by parameters for embedded placeholders.

'named_query'

Uses the named_query catalog to build the query. May contain a defined query name, or a reference to an array of a query name followed by parameters to be handled by interpret_named_query. See "NAMED QUERY CATALOG" for details.

'table'

Required unless explicit "sql => ..." is used. The name of the table to delete from.

'where' or 'criteria'

Optional, but remember that ommitting this will cause all of your rows to be deleted! May contain a literal SQL where clause, an array ref with a SQL clause and parameter list, a hash of field => value pairs, or an object that supports a sql_where() method. See the sql_where() method for details.

Examples: These samples demonstrate use of the delete feature.

• Here's a basic delete with a table name and criteria.

    $sqldb->do_delete( 
      table => 'students', where => { 'name'=>'Dave' } 
    );
      

• You can use your own arbitrary SQL and placeholders:

    $sqldb->do_delete( 
      sql => [ 'delete from students where name = ?', 'Dave' ]
    );
      

• You can combine an explicit delete statement with dynamic criteria:

    $sqldb->do_delete( 
      sql => 'delete from students', where => { 'name'=>'Dave' } 
    );
      

• And the named_query interface is supported as well:

    $sqldb->define_named_query(
      'delete_by_name' => 'delete from students where name = ?'
    );
    $hashes = $sqldb->do_delete( 
      named_query => [ 'delete_by_name', 'Dave' ]
    );
      

Defining Named Queries

define_named_queries()

  $sqldb->define_named_query( $query_name, $query_info )
  $sqldb->define_named_queries( $query_name, $query_info, ... )
  $sqldb->define_named_queries( %query_names_and_info )
    

Defines one or more named queries using the names and definitions provided.

The definition for each query is expected to be in one of the following formats:

define_named_queries_from_text()

  $sqldb->define_named_queries_from_text($query_name, $query_info_text)
  $sqldb->define_named_queries_from_text(%query_names_and_info_text)
    

Defines one or more queries, using some special processing to facilitate storing dynamic query definitions in an external source such as a text file or database table.

The interpretation of each definition is determined by its first non-whitespace character:

Interpreting Named Queries

named_queries()

  $sqldb->named_queries() : %query_names_and_info
  $sqldb->named_queries( $query_name ) : $query_info
  $sqldb->named_queries( \@query_names ) : @query_info
  $sqldb->named_queries( $query_name, $query_info, ... )
  $sqldb->named_queries( \%query_names_and_info )
    

Accessor and mutator for a hash mappping query names to their definitions. Used internally by the other named_query methods. Created with Class::MakeMethods::Standard::Inheritable, so if called as a class method, uses class-wide values, and if called on an instance defaults to its class' value but may be overridden.

named_query()

  $sqldb->named_query( $query_name ) : $query_info
    

Retrieves the query definition matching the name provided. Croaks if no query has been defined for that name.

interpret_named_query()

  $sqldb->interpret_named_query( $query_name, @params ) : %clauses
    

Combines the query definition matching the name provided with the following arguments and returns the resulting hash of query clauses. Croaks if no query has been defined for that name.

Depending on the definition associated with the name, it is combined with the provided parameters in one the following ways:

See the Examples section below for illustrations of these various options.

Executing Named Queries

Public Methods: These methods provide a simple way to use named queries.

fetch_named_query()

  $sqldb->fetch_named_query( $query_name, @params ) : $rows
  $sqldb->fetch_named_query( $query_name, @params ) : ( $rows, $columns )
    

Calls fetch_select using the named query and arguments provided.

visit_named_query()

  $sqldb->visit_named_query($query_name, @params, $code) : @results
  $sqldb->visit_named_query($code, $query_name, @params) : @results
    

Calls visit_select using the named query and arguments provided.

do_named_query()

  $sqldb->do_named_query( $query_name, @params ) : $row_count
    

Calls do_query using the named query and arguments provided.

Examples: These samples demonstrate use of the named_query feature.

• A simple named query can be defined in SQL or as generator clauses:

    $sqldb->define_named_query('all_students', 'select * from students');
  
    $sqldb->define_named_query('all_students', { table => 'students' });
      

  The results of a named select query can be retrieved in several equivalent ways:

    $rows = $sqldb->fetch_named_query( 'all_students' );
  
    $rows = $sqldb->fetch_select( named_query => 'all_students' );
  
    @rows = $sqldb->visit_select( named_query => 'all_students', sub { $_[0] } );
      

• There are numerous ways of defining a query which accepts parameters; any of the following are basically equivalent:

    $sqldb->define_named_query('student_by_id', 
                          'select * from students where id = ?' );
  
    $sqldb->define_named_query('student_by_id', 
                { sql=>['select * from students where id = ?', \$1 ] } );
  
    $sqldb->define_named_query('student_by_id', 
                { table=>'students', where=>[ 'id = ?', \$1 ] } );
  
    $sqldb->define_named_query('student_by_id', 
                { table=>'students', where=>{ 'id' => \$1 } } );
  
    $sqldb->define_named_query('student_by_id', 
      { action=>'select', table=>'students', where=>{ 'id'=>\$1 } } );
      

  Using a named query with parameters requires that the arguments be passed after the name:

    $rows = $sqldb->fetch_named_query( 'student_by_id', $my_id );
  
    $rows = $sqldb->fetch_select(named_query=>['student_by_id', $my_id]);
      

  If the query is defined using a plain string, as in the first line of the student_by_id example, no checking is done to ensure that the correct number of parameters have been passed; the result will depend on your database server, but will presumably be a fatal error. In contrast, the definitions that use the \$1 format will have their parameters counted and arranged before being executed.

• Queries which insert, update, or delete can be defined in much the same way as select queries are; again, all of the following are roughly equivalent:

    $sqldb->define_named_query('delete_student', 
                              'delete from students where id = ?');
  
    $sqldb->define_named_query('delete_student', 
                      [ 'delete from students where id = ?', \$1 ]);
  
    $sqldb->define_named_query('delete_student', 
      { action=>'delete', table=>'students', where=>{ id=>\$1 } });
      

  These modification queries can be invoked with one of the do_ methods:

    $sqldb->do_named_query( 'delete_student', 201 );
  
    $sqldb->do_query( named_query => [ 'delete_student', 201 ] );
  
    $sqldb->do_delete( named_query => [ 'delete_student', 201 ] );
      

• Queries can be defined using subroutines:

    $sqldb->define_named_query('name_search', sub {
      my $name = lc( shift );
      return "select * from students where name like '%$name%'"
    });
  
    $rows = $sqldb->fetch_named_query( 'name_search', 'DAV' );
      

• Query definitions can be stored in external text files or database tables and then evaluated into data structures or code references. The below code loads a simple text file of query definitions

    open( QUERIES, '/path/to/my/queries' );
    my %queries = map { split /\:\s*/, $_, 2 } grep { /^[^#]/ } <QUERIES>;
    close QUERIES;
  
    $sqldb->define_named_queries_from_text( %queries );
      

  Placing the following text in the target file will define all of the queries used above:

    # Simple query that doesn't take any parameters
    all_students: select * from students
    
    # Query with one required parameter
    student_by_id: [ 'select * from students where id = ?', \$1 ]
  
    # Generated query using hash format
    delete_student: { action=>'delete', table=>'students', where=>{ id=>\$1 } }
    
    # Perl expression to be turned into a query generating subroutine
    name_search: "select * from students where name like '%\L$_[0]\E%'"
      

Detect Tables and Columns

detect_table_names()

  $sqldb->detect_table_names () : @table_names
    

Attempts to collect a list of the available tables in the database we have connected to. Uses the DBI tables() method.

detect_table()

  $sqldb->detect_table ( $tablename ) : @columns_or_empty
  $sqldb->detect_table ( $tablename, 1 ) : @columns_or_empty
    

Attempts to query the given table without retrieving many (or any) rows. Uses a server-specific "trivial" or "guaranteed" query provided by sql_detect_any.

If succssful, the columns contained in this table are returned as an array of hash references, as described in the Column Information section below.

Catches any exceptions; if the query fails for any reason we return an empty list. The reason for the failure is logged via warn() unless an additional argument with a true value is passed to surpress those error messages.

Internal Methods: These methods are called by the public detect methods.

sql_detect_table()

  $sqldb->sql_detect_table ( $tablename )  : %sql_select_clauses
    

Subclass hook. Retrieve something from the given table that is guaranteed to exist but does not return many rows, without knowning its table structure.

Defaults to "select * from table where 1 = 0", which may not work on all platforms. Your subclass might prefer "select * from table limit 1" or a local equivalent.

Create and Drop Tables

create_table()

  $sqldb->create_table( $tablename, $column_hash_ary )
    

Create a table.

The columns to be created in this table are defined as an array of hash references, as described in the Column Information section below.

drop_table()

  $sqldb->drop_table( $tablename )
    

Delete the named table.

Column Information: The information about columns is presented as an array of hash references, each containing the following keys:

• "name => $column_name_string"

  Defines the name of the column.

  Portability: No case or length restrictions are imposed on column names, but for incresased compatibility, you may wish to stick with single-case strings of moderate length.

• "type => $column_type_constant_string"

  Specifies the type of column to create. Discussed further below.

• "required => $not_nullable_boolean"

  Indicates whether a value for this column is required; if not, unspecified or undefined values will be stored as NULL values. Defaults to false.

• "length => $max_chars_integer"

  Only applicable to column of "type => 'text'".

  Indicates the maximum number of ASCII characters that can be stored in this column.

Internal Methods: The above public methods use the following sql_ methods to generate SQL DDL statements.

sql_create_table()

  $sqldb->sql_create_table ($tablename, $columns) : $sql_stmt
    

Generate a SQL create-table statement based on the column information. Text columns are checked with sql_create_column_text_length() to provide server-appropriate types.

sql_create_columns()

  $sqldb->sql_create_columns( $column, $fragment_array_ref ) : $sql_fragment
    

Generates the SQL fragment to define a column in a create table statement.

sql_drop_table()

  $sqldb->sql_drop_table ($tablename) : $sql_stmt
    

Column Type Methods

Internal Methods: These methods are used to build create table statements.

sql_create_column_type()

  $sqldb->sql_create_column_type ( $table, $column, $columns ) : $col_type_str
    

Returns an appropriate

dbms_create_column_types()

  $sqldb->dbms_create_column_types () : %column_type_codes
    

Subclass hook. Defaults to empty. Should return a hash mapping column type codes to the specific strings used in a SQL create statement for such a column.

Subclasses should provide at least two entries, for the symbolic types referenced elsewhere in this interface, "sequential" and "binary".

sql_create_column_text_length()

  $sqldb->sql_create_column_text_length ( $length ) : $col_type_str
    

Returns "varchar(length)" for values under 256, otherwise calls dbms_create_column_text_long_type.

dbms_create_column_text_long_type()

  $sqldb->dbms_create_column_text_long_type () : $col_type_str
    

Fails with message "DBMS-Specific Function".

Subclasses should, based on the datasource's server_type, return the appropriate type of column for long text values, such as "BLOB", "TEXT", "LONGTEXT", or "MEMO".

Generating Schema and Record Objects

Those objects relies on a Driver, typically passed to their constructor or initializer. The following convenience methods let you start this process from your current SQLEngine Driver object.

Public Methods: The following methods provide access to objects which represent tables, columns and records in a given Driver. They each ensure the necessary classes are loaded using require().

tables()

  $sqldb->tables() : $tableset
    

Returns a new DBIx::SQLEngine::Schema::TableSet object containing table objects with the names discovered by detect_table_names(). See DBIx::SQLEngine::Schema::TableSet for more information on this object's interface.

table()

  $sqldb->table( $tablename ) : $table
    

Returns a new DBIx::SQLEngine::Schema::Table object with this SQLEngine Driver and the given table name. See DBIx::SQLEngine::Schema::Table for more information on this object's interface.

record_class()

  $sqldb->record_class( $tablename ) : $record_class
  $sqldb->record_class( $tablename, $classname ) : $record_class
  $sqldb->record_class( $tablename, $classname, @traits ) : $record_class
    

Generates a Record::Class which corresponds to the given table name. Note that the record class is a class name, not an object. If no class name is provided, one is generated based on the table name. See DBIx::SQLEngine::Record::Base for more information on this object's interface.

Database Capability Information

The following methods all default to returning undef, but may be overridden by subclasses to return a true or false value, indicating whether their connection has this limitation.

Public Methods: These methods return driver class capability information.

dbms_detect_tables_unsupported()

Can the database driver return a list of tables that currently exist? (True for some simple drivers like CSV.)

dbms_joins_unsupported()

Does the database driver support select statements with joins across multiple tables? (True for some simple drivers like CSV.)

dbms_union_unsupported()

Does the database driver support select queries with unions to join the results of multiple select statements? (True for many simple databases.)

dbms_drop_column_unsupported()

Does the database driver have a problem removing a column from an existing table? (True for Postgres.)

dbms_column_types_unsupported()

Does the database driver store column type information, or are all columns the same type? (True for some simple drivers like CSV.)

dbms_null_becomes_emptystring()

Does the database driver automatically convert null values in insert and update statements to empty strings? (True for some simple drivers like CSV.)

dbms_emptystring_becomes_null()

Does the database driver automatically convert empty strings in insert and update statements to null values? (True for Oracle.)

dbms_placeholders_unsupported()

Does the database driver support having ? placehoders or not? (This is a problem for Linux users of DBD::Sybase connecting to MS SQL Servers on Windows.)

dbms_transactions_unsupported()

Does the database driver support real transactions with rollback and commit or not?

dbms_multi_sth_unsupported()

Does the database driver support having multiple statement handles active at once or not? (This is a problem for several types of drivers.)

dbms_indexes_unsupported()

Does the database driver support server-side indexes or not?

dbms_storedprocs_unsupported()

Does the database driver support server-side stored procedures or not?

Begin, Commit and Rollback Transactions

DBIx::SQLEngine assumes auto-commit is on by default, so unless otherwise specified, each query is executed as a separate transaction. To execute multiple queries within a single transaction, use the as_one_transaction method.

Public Methods: These methods invoke transaction functionality.

are_transactions_supported()

  $boolean = $sqldb->are_transactions_supported( );
    

Checks to see if the database has transaction support.

as_one_transaction()

  @results = $sqldb->as_one_transaction( $sub_ref, @args );
    

Starts a transaction, calls the given subroutine with any arguments provided, and then commits the transaction; if an exception occurs, the transaction is rolled back instead. Will fail if we don't have transaction support.

For example:

  my $sqldb = DBIx::SQLEngine->new( ... );
  $sqldb->as_one_transaction( sub { 
    $sqldb->do_insert( ... );
    $sqldb->do_update( ... );
    $sqldb->do_delete( ... );
  } );
    

Or using a reference to a predefined subroutine:

  sub do_stuff {
    my $sqldb = shift;
    $sqldb->do_insert( ... );
    $sqldb->do_update( ... );
    $sqldb->do_delete( ... );
    1;
  }
  
  my $sqldb = DBIx::SQLEngine->new( ... );
  $sqldb->as_one_transaction( \&do_stuff, $sqldb )
    or warn "Unable to complete transaction";
    

as_one_transaction_if_supported()

  @results = $sqldb->as_one_transaction_if_supported($sub_ref, @args)
    

If transaction support is available, this is equivalent to as_one_transaction. If transactions are not supported, simply performs the code in $sub_ref with no transaction protection.

This is obviously not very reliable, but may be of use in some ad-hoc utilities or test scripts.

Create and Drop Indexes

Public Methods: These methods create and drop indexes.

create_index()

  $sqldb->create_index( %clauses )
    

drop_index()

  $sqldb->drop_index( %clauses )
    

Internal Methods: These methods are called by the public index methods.

sql_create_index()

  $sqldb->sql_create_index( %clauses ) : $sql, @params
    

sql_drop_index()

  $sqldb->sql_drop_index( %clauses ) : $sql, @params
    

Examples: These samples demonstrate use of the index feature.

•   $sqldb->create_index( 
      table => $table_name, columns => @columns
    );
  
    $sqldb->drop_index( 
      table => $table_name, columns => @columns
    );
      

•   $sqldb->create_index( 
      name => $index_name, table => $table_name, columns => @columns
    );
  
    $sqldb->drop_index( 
      name => $index_name
    );
      

Call, Create and Drop Stored Procedures

These methods are all subclass hooks. Fail with message "DBMS-Specific Function".

Public Methods: These methods create, drop, and use stored procedures.

fetch_storedproc()

  $sqldb->fetch_storedproc( $proc_name, @arguments ) : $rows
    

do_storedproc()

  $sqldb->do_storedproc( $proc_name, @arguments ) : $row_count
    

create_storedproc()

  $sqldb->create_storedproc( $proc_name, $definition )
    

drop_storedproc()

  $sqldb->drop_storedproc( $proc_name )
    

Create and Drop Databases

Public Methods: These methods create and drop database partitions.

create_database()

  $sqldb->create_database( $db_name )
    

Fails with message "DBMS-Specific Function".

drop_database()

  $sqldb->drop_database( $db_name )
    

Fails with message "DBMS-Specific Function".

Accessing the DBH

get_dbh()

  $sqldb->get_dbh () : $dbh
    

Get the current DBH

dbh_func()

  $sqldb->dbh_func ( $func_name, @args ) : @results
    

Calls the DBI func() method on the database handle returned by get_dbh, passing the provided function name and arguments. See the documentation for your DBD driver to learn which functions it supports.

Initialization and Reconnection

_init()

  $sqldb->_init ()
    

Empty subclass hook. Called by DBIx::AnyDBD after connection is made and class hierarchy has been juggled.

reconnect()

  $sqldb->reconnect ()
    

Attempt to re-establish connection with original parameters

Checking For Connection

Internal Methods: These methods are invoked automatically.

detect_any()

  $sqldb->detect_any () : $boolean
  $sqldb->detect_any ( 1 ) : $boolean
    

Attempts to confirm that values can be retreived from the database, allowing us to determine if the connection is working, using a server-specific "trivial" or "guaranteed" query provided by sql_detect_any.

Catches any exceptions; if the query fails for any reason we return a false value. The reason for the failure is logged via warn() unless an additional argument with a true value is passed to surpress those error messages.

sql_detect_any()

  $sqldb->sql_detect_any : %sql_select_clauses
    

Subclass hook. Retrieve something from the database that is guaranteed to exist. Defaults to SQL literal "select 1", which may not work on all platforms. Your database driver might prefer something else, like Oracle's "select 1 from dual".

check_or_reconnect()

  $sqldb->check_or_reconnect () : $dbh
    

Confirms the current DBH is available with detect_any() or calls reconnect().

Generic Query Execution

  $db->do_sql('insert into table values (?, ?)', 'A', 1);
  my $rows = $db->fetch_sql('select * from table where status = ?', 2);

Execute and fetch some kind of result from a given SQL statement. Internally, these methods are used by the other do_, fetch_ and visit_ methods described above. Each one calls the try_query method with the provided query and parameters, and passes the name of a result method to be used in extracting values from the statement handle.

Public Methods:

do_sql()

  $sqldb->do_sql ($sql, @params) : $rowcount
    

Execute a SQL query by sending it to the DBI connection, and returns the number of rows modified, or -1 if unknown.

fetch_sql()

  $sqldb->fetch_sql ($sql, @params) : $row_hash_ary
  $sqldb->fetch_sql ($sql, @params) : ( $row_hash_ary, $columnset )
    

Execute a SQL query by sending it to the DBI connection, and returns any rows that were produced, as an array of hashrefs, with the values in each entry keyed by column name. If called in a list context, also returns a reference to an array of information about the columns returned by the query.

fetch_sql_rows()

  $sqldb->fetch_sql_rows ($sql, @params) : $row_ary_ary
  $sqldb->fetch_sql_rows ($sql, @params) : ( $row_ary_ary, $columnset )
    

Execute a SQL query by sending it to the DBI connection, and returns any rows that were produced, as an array of arrayrefs, with the values in each entry keyed by column order. If called in a list context, also returns a reference to an array of information about the columns returned by the query.

visit_sql()

  $sqldb->visit_sql ($coderef, $sql, @params) : @results
  $sqldb->visit_sql ($sql, @params, $coderef) : @results
    

Similar to fetch_sql, but calls your coderef on each row, passing it as a hashref, and returns the results of each of those calls. For your convenience, will accept a coderef as either the first or the last argument.

visit_sql_rows()

  $sqldb->visit_sql ($coderef, $sql, @params) : @results
  $sqldb->visit_sql ($sql, @params, $coderef) : @results
    

Similar to fetch_sql, but calls your coderef on each row, passing it as a list of values, and returns the results of each of those calls. For your convenience, will accept a coderef as either the first or the last argument.

fetchsub_sql()

  $sqldb->fetchsub_sql ($sql, @params) : $coderef
    

Execute a SQL query by sending it to the DBI connection, and returns a code reference that can be called repeatedly to invoke the fetchrow_hashref() method on the statement handle.

fetchsub_sql_rows()

  $sqldb->fetchsub_sql_rows ($sql, @params) : $coderef
    

Execute a SQL query by sending it to the DBI connection, and returns a code reference that can be called repeatedly to invoke the fetchrow_array() method on the statement handle.

Statement Error Handling

try_query()

  $sqldb->try_query ( $sql, \@params, $result_method, @result_args ) : @results
    

Error handling wrapper around the internal execute_query method.

The $result_method should be the name of a method supported by that Driver instance, typically one of those shown in the "Retrieving Rows from an Executed Statement" section below. The @result_args, if any, are passed to the named method along with the active statement handle.

catch_query_exception()

  $sqldb->catch_query_exception ( $exception, $sql, \@params, 
                        $result_method, @result_args ) : $resolution
    

Exceptions are passed to catch_query_exception; if it returns "REDO" the query will be retried up to five times. The superclass checks the error message against the recoverable_query_exceptions; subclasses may wish to override this to provide specialized handling.

recoverable_query_exceptions()

  $sqldb->recoverable_query_exceptions() : @common_error_messages
    

Subclass hook. Defaults to empty. Subclasses may provide a list of error messages which represent common communication failures or other incidental errors.

Statement Handle Lifecycle

Internal Methods:

execute_query()

  $sqldb->execute_query($sql, \@params, $result_method, @result_args) : @results
    

This overall lifecycle method calls prepare_execute(), runs the $result_method, and then calls done_with_query().

The $result_method should be the name of a method supported by that Driver instance, typically one of those shown in the "Retrieving Rows from an Executed Statement" section below. The @result_args, if any, are passed to the named method along with the active statement handle.

prepare_execute()

  $sqldb->prepare_execute ($sql, @params) : $sth
    

Prepare, bind, and execute a SQL statement to create a DBI statement handle.

Uses the DBI prepare_cached(), bind_param(), and execute() methods.

If you need to pass type information with your parameters, pass a reference to an array of the parameter and the type information.

done_with_query()

  $sqldb->done_with_query ($sth) : ()
    

Called when we're done with the $sth.

Retrieving Rows from a Statement

do_nothing()

  $sqldb->do_nothing ($sth) : ()
    

Does nothing.

get_execute_rowcount()

  $sqldb->get_execute_rowcount ($sth) : $row_count
    

Returns the row count reported by the last statement executed.

fetchall_hashref()

  $sqldb->fetchall_hashref ($sth) : $array_of_hashes
    

Calls the STH's fetchall_arrayref method with an empty hashref to retrieve all of the result rows into an array of hashrefs.

fetchall_hashref_columns()

  $sqldb->fetchall_hashref ($sth) : $array_of_hashes
  $sqldb->fetchall_hashref ($sth) : ( $array_of_hashes, $column_info )
    

Calls the STH's fetchall_arrayref method with an empty hashref, and if called in a list context, also retrieves information about the columns used in the query result set.

fetchall_arrayref()

  $sqldb->fetchall_arrayref ($sth) : $array_of_arrays
    

Calls the STH's fetchall_arrayref method to retrieve all of the result rows into an array of arrayrefs.

fetchall_arrayref_columns()

  $sqldb->fetchall_hashref ($sth) : $array_of_arrays
  $sqldb->fetchall_hashref ($sth) : ( $array_of_arrays, $column_info )
    

Calls the STH's fetchall_arrayref method, and if called in a list context, also retrieves information about the columns used in the query result set.

visitall_hashref()

  $sqldb->visitall_hashref ($sth, $coderef) : ()
    

Calls coderef on each row with values as hashref, and returns a list of results.

visitall_array()

  $sqldb->visitall_array ($sth, $coderef) : ()
    

Calls coderef on each row with values as list, and returns a list of results.

fetchsub_hashref()

  $sqldb->fetchsub_hashref ($sth, $name_uc_or_lc) : $coderef
    

Returns a code reference that can be called repeatedly to invoke the fetchrow_hashref() method on the statement handle.

The code reference is blessed so that when it goes out of scope and is destroyed it can call the statement handle's finish() method.

fetchsub_array()

  $sqldb->fetchsub_hashref ($sth) : $coderef
    

Returns a code reference that can be called repeatedly to invoke the fetchrow_array() method on the statement handle.

The code reference is blessed so that when it goes out of scope and is destroyed it can call the statement handle's finish() method.

Retrieving Columns from a Statement

retrieve_columns()

  $sqldb->retrieve_columns ($sth) : $columnset
    

Obtains information about the columns used in the result set.

column_type_codes()

  $sqldb->column_type_codes - Standard::Global:hash
    

Maps the ODBC numeric constants used by DBI to the names we want to use for simplified internal representation.

To Do: this should probably be using DBI's type_info methods.

DBI Logging

DBILogging()

  $sqldb->DBILogging : $value
  $sqldb->DBILogging( $value )
    

Set this to a true value to turn on logging of DBI interactions. Can be called on the class to set a shared default for all instances, or on any instance to set the value for it alone.

Internal Methods:

log_connect()

  $sqldb->log_connect ( $dsn )
    

Writes out connection logging message.

log_start()

  $sqldb->log_start( $sql ) : $timer
    

Called at start of query execution.

log_stop()

  $sqldb->log_stop( $timer ) : ()
    

Called at end of query execution.

SQL Logging

SQLLogging()

  $sqldb->SQLLogging () : $value 
  $sqldb->SQLLogging( $value )
    

Set this to a true value to turn on logging of internally-generated SQL statements (all queries except for those with complete SQL statements explicitly passed in by the caller). Can be called on the class to set a shared default for all instances, or on any instance to set the value for it alone.

Internal Methods:

log_sql()

  $sqldb->log_sql( $sql ) : ()
    

Called when SQL is generated.

About Driver Traits

Because of the way DBIx::AnyDBD munges the inheritance tree, DBIx::SQLEngine::Driver subclasses can not reliably inherit from mixins. To work around this, we export all of the methods into their namespace using Exporter and @EXPORT.

In addition we go through some effort to re-dispatch methods because we can't rely on SUPER and we don't want to require NEXT. This isn't too complicated, as we know the munged inheritance tree only uses single inheritance.

Note: this mechanism has been added recently, and the implementation is subject to change.

Internal Methods:

NEXT()

  $sqldb->NEXT( $method, @args ) : @results
    

Used by driver traits to redispatch to base-class implementations.