NAME

DBD::TimesTen - TimesTen Driver for DBI

SYNOPSIS

  use DBI;

  $dbh = DBI->connect('dbi:TimesTen:DSN', 'user', 'password');

See DBI for more information.

DESCRIPTION

Notes:

An Important note about the tests!

 Please note that some tests may fail or report they are
 unsupported on this platform.
   
 Also note that some tests may be skipped, such as
 t/09multi.t, if your driver doesn't seem to support
 returning multiple result sets.  This is normal.

Private DBD::TimesTen Attributes

odbc_more_results (applies to statement handle only!)

Use this attribute to determine if there are more result sets available. SQL Server supports this feature. Use this as follows:

do { my @row; while (@row = $sth->fetchrow_array()) { # do stuff here } } while ($sth->{odbc_more_results});

Note that with multiple result sets and output parameters (i.e. using bind_param_inout, don't expect output parameters to be bound until ALL result sets have been retrieved.

odbc_ignore_named_placeholders

Use this if you have special needs (such as Oracle triggers, etc) where :new or :name mean something special and are not just place holder names You must then use ? for binding parameters. Example: $dbh->{odbc_ignore_named_placeholders} = 1; $dbh->do("create trigger foo as if :new.x <> :old.x then ... etc");

Without this, DBD::TimesTen will think :new and :old are placeholders for binding and get confused.

odbc_default_bind_type

This value defaults to 0. Older versions of DBD::TimesTen assumed that the binding type was 12 (SQL_VARCHAR). Newer versions default to 0, which means that DBD::TimesTen will attempt to query the driver via SQLDescribeParam to determine the correct type. If the driver doesn't support SQLDescribeParam, then DBD::TimesTen falls back to using SQL_VARCHAR as the default, unless overridden by bind_param()

odbc_force_rebind

This is to handle special cases, especially when using multiple result sets. Set this before execute to "force" DBD::TimesTen to re-obtain the result set's number of columns and column types for each execute. Especially useful for calling stored procedures which may return different result sets each execute. The only performance penalty is during execute(), but I didn't want to incur that penalty for all circumstances. It is probably fairly rare that this occurs. This attribute will be automatically set when multiple result sets are triggered. Most people shouldn't have to worry about this.

odbc_async_exec

Allow asynchronous execution of queries. Right now, this causes a spin-loop (with a small "sleep") until the sql is complete. This is useful, however, if you want the error handling and asynchronous messages (see the err_handler) below.

odbc_exec_direct

Force DBD::TimesTen to use SQLExecDirect instead of SQLPrepare() then SQLExecute. There are drivers that only support SQLExecDirect and the DBD::TimesTen do() override doesn't allow returning result sets. Therefore, the way to do this now is to set the attributed odbc_exec_direct. There are currently two ways to get this: $dbh->prepare($sql, { odbc_exec_direct => 1}); and $dbh->{odbc_exec_direct} = 1; When $dbh->prepare() is called with the attribute "ExecDirect" set to a non-zero value dbd_st_prepare do NOT call SQLPrepare, but set the sth flag odbc_exec_direct to 1.

odbc_err_handler

Allow errors to be handled by the application. A call-back function supplied by the application to handle or ignore messages. If the error handler returns 0, the error is ignored, otherwise the error is passed through the normal DBI error handling structure(s).

The callback function takes three parameters: the SQLState, the ErrorMessage and the native server error.

odbc_SQL_ROWSET_SIZE

Here is the information from the original patch, however, I've learned since from other sources that this could/has caused SQL Server to "lock up". Please use at your own risk!

SQL_ROWSET_SIZE attribute patch from Andrew Brown > There are only 2 additional lines allowing for the setting of > SQL_ROWSET_SIZE as db handle option. > > The purpose to my madness is simple. SqlServer (7 anyway) by default > supports only one select statement at once (using std ODBC cursors). > According to the SqlServer documentation you can alter the default setting > of > three values to force the use of server cursors - in which case multiple > selects are possible. > > The code change allows for: > $dbh->{SQL_ROWSET_SIZE} = 2; # Any value > 1 > > For this very purpose. > > The setting of SQL_ROWSET_SIZE only affects the extended fetch command as > far as I can work out and thus setting this option shouldn't affect > DBD::TimesTen operations directly in any way. > > Andrew >

SQL_DRIVER_ODBC_VER

This, while available via get_info() is captured here. I may get rid of this as I only used it for debugging purposes.

odbc_cursortype (applies to connect only!)

This allows multiple concurrent statements on SQL*Server. In your connect, add { odbc_cursortype => 2 }. If you are using DBI > 1.41, you should also be able to use { odbc_cursortype => DBI::SQL_CURSOR_DYNAMIC } instead. For example:

 my $dbh = DBI->connect("dbi:TimesTen:$DSN", $user, $pass, { RaiseError => 1, odbc_cursortype => 2});
 my $sth = $dbh->prepare("one statement");
 my $sth2 = $dbh->prepare("two statement");
 $sth->execute;
 my @row;
 while (@row = $sth->fetchrow_array) {
    $sth2->execute($row[0]);
 }

odbc_query_timeout

This allows the end user to set a timeout for queries on the ODBC side. After your connect, add { odbc_query_timeout => 30 } or set on the dbh before executing the statement. The default is 0, no timeout. Note that some drivers may not support this attribute.

odbc_version (applies to connect only!)

This was added prior to the move to ODBC 3.x to allow the caller to "force" ODBC 3.0 compatibility. It's probably not as useful now, but it allowed get_info and get_type_info to return correct/updated information that ODBC 2.x didn't permit/provide. Since DBD::TimesTen is now 3.x, this can be used to force 2.x behavior via something like: my $dbh = DBI->connect("dbi:TimesTen:$DSN", $user, $pass, { odbc_version => 2});

Private DBD::TimesTen Functions

GetInfo (superceded by get_info(), the DBI standard)

This function maps to the ODBC SQLGetInfo call. This is a Level 1 ODBC function. An example of this is:

  $value = $dbh->func(6, GetInfo);

This function returns a scalar value, which can be a numeric or string value. This depends upon the argument passed to GetInfo.

SQLGetTypeInfo (superceded by get_type_info(), the DBI standard)

This function maps to the ODBC SQLGetTypeInfo call. This is a Level 1 ODBC function. An example of this is:

  use DBI qw(:sql_types);

  $sth = $dbh->func(SQL_ALL_TYPES, GetInfo);
  while (@row = $sth->fetch_row) {
    ...
  }

This function returns a DBI statement handle, which represents a result set containing type names which are compatible with the requested type. SQL_ALL_TYPES can be used for obtaining all the types the ODBC driver supports. NOTE: It is VERY important that the use DBI includes the qw(:sql_types) so that values like SQL_VARCHAR are correctly interpreted. This "imports" the sql type names into the program's name space. A very common mistake is to forget the qw(:sql_types) and obtain strange results.

GetFunctions

This function maps to the ODBC API SQLGetFunctions. This is a Level 1 API call which returns supported driver funtions. Depending upon how this is called, it will either return a 100 element array of true/false values or a single true false value. If it's called with SQL_API_ALL_FUNCTIONS (0), it will return the 100 element array. Otherwise, pass the number referring to the function. (See your ODBC docs for help with this).

SQLColumns

Support for this function has been added in version 0.17. It looks to be fixed in version 0.20.

Use the DBI statement handle attributes NAME, NULLABLE, TYPE, PRECISION and SCALE, unless you have a specific reason.

Connect without DSN The ability to connect without a full DSN is introduced in version 0.21.

Example (using MS Access): my $DSN = 'driver=Microsoft Access Driver (*.mdb);dbq=\\\\cheese\\g$\\perltest.mdb'; my $dbh = DBI->connect("dbi:TimesTen:$DSN", '','') or die "$DBI::errstr\n";

SQLStatistics

SQLForeignKeys

See DBI's get_foreign_keys.

SQLPrimaryKeys

See DBI's get_primary_keys

SQLDataSources

Handled, currently (as of 0.21), also see DBI's data_sources()

SQLSpecialColumns

Handled as of version 0.28

Others/todo?

Level 1

    SQLTables (use tables()) call

Level 2

    SQLColumnPrivileges
    SQLProcedureColumns
    SQLProcedures
    SQLTablePrivileges
    SQLDrivers
    SQLNativeSql

Frequently Asked Questions

Answers to common DBI and DBD::TimesTen questions:

How do I read more than N characters from a Memo | BLOB | LONG field?

See LongReadLen in the DBI docs.

Example: $dbh->{LongReadLen} = 20000; $sth = $dbh->prepare("select long_col from big_table"); $sth->execute; etc

Almost all of my tests for DBD::TimesTen fail. They complain about not being able to connect or the DSN is not found.

Please, please test your configuration of ODBC and driver before trying to test DBD::TimesTen. Most of the time, this stems from the fact that the DSN (or ODBC) is not configured properly. iODBC comes with a odbctest program. Please use it to verify connectivity.