DBD::ODBC - ODBC Driver for DBI

  use DBI;

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

See DBI for more information.

Change log/recent updates now in DBD::ODBC::Changes.pm

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

 Also, while this document documents features, etc, I'm really trying to keep
 the FAQ type questions answered in the DBI FAQ itself... see
 http://dbi.perl.org for more information.

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.

Version Control

 I have recently placed all of the DBD::ODBC source code
 under version control at svn.perl.org.  If you would like
 to use the "bleeding" edge version, you can get the latest
 from svn.perl.org via Subversion version control.  Note that I
 don't guarantee that this version is any different than what
 you get from the tarball from CPAN, but hey, it might be :)

 You may read about Subversion from:
   http://subversion.tigris.org

 You can get a subversion client from there and check dbd-odbc out via:

   svn checkout http://svn.perl.org/modules/dbd-odbc/trunk <your directory name here>

 Which will, after a short bit a of work, grab all the files into your
 local directory tree that you name.  For now, you can authenticate via the guest
 user and guest password.  That will change to be unauthenticated in the future for
 simple read-only checkout.

Contributing

 I am usually very open to contributions, but I have tended
 to get behind.  That's one strong reason why I've put everything
 in a shared version control environment.

 Please use Subversion (see above) to appropriately get the latest
 versions.

 Please, before submitting a patch:
    svn update
    <test your patch>
    svn diff > describe_my_diffs.patch

 and send the resuting file to me...

 It's probably best to send to dbi-users@perl.org, as I monitor that group.

Private DBD::ODBC 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 CW@row; while (@row = CW$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: CW$dbh->{odbc_ignore_named_placeholders} = 1; CW$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: CW$dbh->prepare($sql, { odbc_exec_direct => 1}); and CW$dbh->{odbc_exec_direct} = 1; When CW$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). This can also be used for procedures under MS SQL Server (Sybase too, probably) to obtain messages from system procedures such as DBCC. Check t/20SQLServer.t and mytest/testerrhandler.pl 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: > CW$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_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:ODBC:$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]);
 }

See t/20SqlServer.t for an example.

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. See t/20SqlServer.t for an example.

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::ODBC is now 3.x, this can be used to force 2.x behavior via something like: my CW$dbh = DBI->connect(dbi:ODBC:$DSN, CW$user, CW$pass, { odbc_version => 2});

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 CW$DSN = 'driver=Microsoft Access Driver (*.mdb);dbq=\\\\cheese\\g$\\perltest.mdb'; my CW$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

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 CW$DSN = 'driver=Microsoft Access Driver (*.mdb);dbq=\\\\cheese\\g$\\perltest.mdb'; my CW$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 CW$DSN = 'driver={SQL Server};Server=server_name; database=database_name;uid=user;pwd=password;'; my CW$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

   For Linux/Unix 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

   And another:
        OpenRDA

        http://www.atinet.com/support/openrda_samples.asp

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: CW$dbh->{LongReadLen} = 20000; CW$sth = CW$dbh->prepare(select long_col from big_table); CW$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
        http://www.atinet.com/support/openrda_samples.asp

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);