view release on metacpan or  search on metacpan

ODBC.pm  view on Meta::CPAN

DBD::ODBC outputs tracing at levels 3 and above (as levels 1 and 2 are
reserved for DBI).

For comprehensive tracing of DBI method calls without all the DBI
internals see L<DBIx::Log4perl>.

=head2 Deviations from the DBI specification

=head3 last_insert_id

DBD::ODBC does not support DBI's last_insert_id. There is no ODBC
defined way of obtaining this information. Generally the mechanism
(and it differs vastly between databases and ODBC drivers) it to issue
a select of some form (e.g., select @@identity or select
sequence.currval from dual, etc).

There are literally dozens of databases and ODBC drivers supported by
DBD::ODBC and I cannot have them all. If you know how to retrieve the
information for last_insert_id and you mail me the ODBC Driver
name/version and database name/version with a small working example I
will collect examples and document them here.

B<Microsoft Access>. Recent versions of MS Access support I<select
@@identity> to retrieve the last insert ID.  See
http://support.microsoft.com/kb/815629. Information provided by Robert
Freimuth.

=head3 Comments in SQL

DBI does not say anything in particular about comments in SQL.
DBD::ODBC looks for placeholders in the SQL string and until 1.24_2 it
did not recognise comments in SQL strings so could find what it
believes to be a placeholder in a comment e.g.,

  select '1' /* placeholder ? in comment */
  select -- named placeholder :named in comment
    '1'

I cannot be exact about support for ignoring placeholders in literals
but it has existed for a long time in DBD::ODBC. Support for ignoring
placeholders in comments was added in 1.24_2. If you find a case where
a named placeholder is not ignored and should be, see
L</odbc_ignore_named_placeholders> for a workaround and mail me an
example along with your ODBC driver name.

=head3 do

This is not really a deviation from the DBI specification since DBI
allows a driver to avoid the overhead of creating an DBI statement
handle for do().

DBD::ODBC implements C<do> by calling SQLExecDirect in ODBC and not
SQLPrepare followed by SQLExecute so C<do> is not the same as:

  $dbh->prepare($sql)->execute()

It does this to avoid a round-trip to the server so it is faster.
Normally this is good but some people fall foul of this with MS SQL
Server if they call a procedure which outputs print statements (e.g.,
backup) as the procedure may not complete. See the DBD::ODBC FAQ and
in general you are better to use prepare/execute when calling
procedures.

In addition, you should realise that since DBD::ODBC does not create a
DBI statement for do calls, if you set up an error handler the handle
passed in when a do fails will be the database handle and not
a statement handle.

=head3 Mixed placeholder types

There are 3 conventions for place holders in DBI. These are '?', ':N'
and ':name' (where 'N' is a number and 'name' is an alpha numeric
string not beginning with a number). DBD::ODBC supports all these methods
for naming placeholders but you must only use one method throughout
a particular SQL string. If you mix placeholder methods you will get
an error like:

  Can't mix placeholder styles (1/2)

=head3 Using the same placeholder more than once

DBD::ODBC does not support (currently) the use of one named placeholder
more than once in a single SQL string. i.e.,

  insert into foo values (:bar, :p1, :p2, :bar);

is not supported because 'bar' is used more than once but:

  insert into foo values(:bar, :p1, :p2)

is ok. If you do the former you will get an error like:

  DBD::ODBC does not yet support binding a named parameter more than once

=head3 Binding named placeholders

Although the DBI documentation (as of 1.604) does not say how named
parameters are bound Tim Bunce has said that in Oracle they are bound
with the leading ':' as part of the name and that has always been the
case. i.e.,

  prepare("insert into mytable values (:fred)");
  bind_param(":foo", 1);

DBD::ODBC does not support binding named parameters with the ':' introducer.
In the above example you must use:

  bind_param("foo", 1);

In discussion on the dbi-dev list is was suggested that the ':' could
be made optional and there were no basic objections but it has not
made it's way into the pod yet.

=head3 Sticky Parameter Types

The DBI specification post 1.608 says in bind_param:

  The data type is 'sticky' in that bind values passed to execute()
  are bound with the data type specified by earlier bind_param()
  calls, if any.  Portable applications should not rely on being able