The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.

NAME

DBD::ODBC - ODBC Driver for DBI

SYNOPSIS

  use DBI;

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

See DBI for more information.

DESCRIPTION

Notes:

     Please note that the change log has been moved to DBD::ODBC::Changes.pm
     To easily access this documentation, use perldoc DBD::ODBC::Changes
     

    An Important note about the tests!

     Please note that some tests may fail or report they are
     unsupported on this platform.  Notably Oracle's ODBC driver
     will fail the "advanced" binding tests in t/08bind2.t.
     These tests run perfectly under SQL Server 2000. This is
     normal and expected.  Until Oracle fixes their drivers to
     do the right thing from an ODBC perspective, it's going to
     be tough to fix the issue.  The workaround for Oracle is to
     bind date types with SQL_TIMESTAMP.
       
     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::ODBC Attributes =item 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::ODBC 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::ODBC assumed that the binding type was 12 (SQL_VARCHAR). Newer versions default to 0, which means that DBD::ODBC will attempt to query the driver via SQLDescribeParam to determine the correct type. If the driver doesn't support SQLDescribeParam, then DBD::ODBC 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::ODBC 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. See t/20SQLServer.t for an example of this.

    odbc_exec_direct

    Force DBD::ODBC to use SQLExecDirect instead of SQLPrepare() then SQLExecute. There are drivers that only support SQLExecDirect and the DBD::ODBC 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_execdirect => 1}); and $dbh->{odbc_execdirect} = 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).

    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::ODBC 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_version

    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.

    Private DBD::ODBC 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 (now supports ODBC V3)

    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:ODBC:$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

Using DBD::ODBC with web servers under Win32.

General Commentary re web database access

This should be a DBI faq, actually, but this has somewhat of an Win32/ODBC twist to it.

Typically, the Web server is installed as an NT service or a Windows 95/98 service. This typically means that the web server itself does not have the same environment and permissions the web developer does. This situation, of course, can and does apply to Unix web servers. Under Win32, however, the problems are usually slightly different.

Defining your DSN -- which type should I use?

Under Win32 take care to define your DSN as a system DSN, not as a user DSN. The system DSN is a "global" one, while the user is local to a user. Typically, as stated above, the web server is "logged in" as a different user than the web developer. This helps cause the situation where someone asks why a script succeeds from the command line, but fails when called from the web server.

Defining your DSN -- careful selection of the file itself is important!

For file based drivers, rather than client server drivers, the file path is VERY important. There are a few things to keep in mind. This applies to, for example, MS Access databases.

1) If the file is on an NTFS partition, check to make sure that the Web service user has permissions to access that file.

2) If the file is on a remote computer, check to make sure the Web service user has permissions to access the file.

3) If the file is on a remote computer, try using a UNC path the file, rather than a X:\ notation. This can be VERY important as services don't quite get the same access permissions to the mapped drive letters and, more importantly, the drive letters themselves are GLOBAL to the machine. That means that if the service tries to access Z:, the Z: it gets can depend upon the user who is logged into the machine at the time. (I've tested this while I was developing a service -- it's ugly and worth avoiding at all costs).

Unfortunately, the Access ODBC driver that I have does not allow one to specify the UNC path, only the X:\ notation. There is at least one way around that. The simplest is probably to use Regedit and go to (assuming it's a system DSN, of course) HKEY_LOCAL_USERS\SOFTWARE\ODBC\"YOUR DSN" You will see a few settings which are typically driver specific. The important value to change for the Access driver, for example, is the DBQ value. That's actually the file name of the Access database.

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:ODBC:$DSN", '','') or die "$DBI::errstr\n";

The above sample uses Microsoft's UNC naming convention to point to the MSAccess file (\\\\cheese\\g$\\perltest.mdb). The dbq parameter tells the access driver which file to use for the database.

Example (using MSSQL Server): my $DSN = 'driver={SQL Server};Server=server_name; database=database_name;uid=user;pwd=password;'; my $dbh = DBI->connect("dbi:ODBC:$DSN") or die "$DBI::errstr\n";

These are in need of sorting and annotating. Some are relevant only to ODBC developers (but I don't want to loose them).

        http://www.ids.net/~bjepson/freeODBC/index.html

        http://dataramp.com/

        http://www.syware.com

        http://www.microsoft.com/odbc

   For Linux/Unix folks, compatible ODBC driver managers can be found at:
   
        http://www.easysoft.com         unixODBC driver manager source
                                        *and* ODBC-ODBC bridge for accessing Win32 ODBC sources from Linux

        http://www.iodbc.org            iODBC driver manager source

   Also, for Linux folks, you can checkout the following for another ODBC-ODBC bridge and support for iODBC.

        http://www.openlink.co.uk 
                or
        http://www.openlinksw.com 

Frequently Asked Questions Answers to common DBI and DBD::ODBC 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

What is DBD::ODBC? Why can't I connect? Do I need an ODBC driver? What is the ODBC driver manager?

These, general questions lead to needing definitions.

1) ODBC Driver - the driver that the ODBC manager uses to connect and interact with the RDBMS. You DEFINITELY need this to connect to any database. For Win32, they are plentiful and installed with many applications. For Linux/Unix, some hunting is required, but you may find something useful at:

        http://www.openlinksw.com
        http://www.easysoft.com
        http://www.intersolv.com
              

2) ODBC Driver Manager - the piece of software which interacts with the drivers for the application. It "hides" some of the differences between the drivers (i.e. if a function call is not supported by a driver, it 'hides' that and informs the application that the call is not supported. DBD::ODBC needs this to talk to drivers. Under Win32, it is built in to the OS. Under Unix/Linux, in most cases, you will want to use freeODBC, unixODBC or iODBC. iODBC was bundled with DBD::ODBC, but you will need to find one which suits your needs. Please see www.openlinksw.com, www.easysoft.com or www.iodbc.org

3) DBD::ODBC. DBD::ODBC uses the driver manager to talk to the ODBC driver(s) on your system. You need both a driver manager and driver installed and tested before working with DBD::ODBC. You need to have a DSN (see below) configured *and* TESTED before being able to test DBD::ODBC.

4) DSN -- Data Source Name. It's a way of referring to a particular database by any name you wish. The name itself can be configured to hide the gory details of which type of driver you need and the connection information you need to provide. For example, for some databases, you need to provide a TCP address and port. You can configure the DSN to have use information when you refer to the DSN.

Where do I get an ODBC driver manager for Unix/Linux?

DBD::ODBC comes with one (iODBC). In the DBD::ODBC source release is a directory named iodbcsrc. There are others. UnixODBC, FreeODBC and some of the drivers will come with one of these managers. For example Openlink's drivers (see below) come with the iODBC driver manager. Easysoft supplies both ODBC-ODBC bridge software and unixODBC.

How do I access a MS SQL Server database from Linux?

Try using drivers from http://www.openlinksw.com or www.easysoft.com The multi-tier drivers have been tested with Linux and Redhat 5.1.

How do I access an MS-Access database from Linux?

I believe you can use the multi-tier drivers from http://www.openlinksw.com, however, I have not tested this. Also, I believe there is a commercial solution from http://www.easysoft.com. I have not tested this.

If someone does have more information, please, please send it to me and I will put it in this FAQ.

Almost all of my tests for DBD::ODBC 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::ODBC. 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.

For Unix -> Windows DB see Tom Lowery's write-up.

http://tlowery.hypermart.net/perl_dbi_dbd_faq.html#HowDoIAccessMSWindowsDB

I'm attempting to bind a Long Var char (or other specific type) and the binding is not working. The code I'm using is below:
        $sth->bind_param(1, $str, $DBI::SQL_LONGVARCHAR);
                                 ^^^
The problem is that DBI::SQL_LONGVARCHAR is not the same as $DBI::SQL_LONGVARCHAR and that
$DBI::SQL_LONGVARCHAR is an error!

It should be:

        $sth->bind_param(1, $str, DBI::SQL_LONGVARCHAR);

3 POD Errors

The following errors were encountered while parsing the POD:

Around line 423:

=over should be: '=over' or '=over positive_number'

You can't have =items (as at line 429) unless the first thing after the =over is an =item

Around line 726:

You forgot a '=back' before '=head2'

Around line 757:

'=item' outside of any '=over'

=over without closing =back