view release on metacpan or  search on metacpan

FAQ  view on Meta::CPAN

=head2 Why does DBD::ODBC fail to compile with missing definitions for SQLLEN/SQLULEN?

This happens because Microsoft changed their headers to add SQLLEN/SQLULEN
types and your C headers are probably out of date. As DBD::ODBC needs
to use these types you'll need an updated MDAC Development Kit. See
" Where do I get the latest MDAC Development Kit?".

=head2 Why do I get errors with bound parameters and MS SQL Server?

See the question "Why do I get data truncated error from SQL Server
when inserting with parameters?" above. These errors are often because
of bugs in the MS SQL Server ODBC driver in its SQLBindParameter
implementation and can be worked around by specifying a type at bind
time.

e.g.,

Instead of:


  my $s = prepare(q/some sql with parameters/);
  $s->execute($param1, $param2);

try:

  my $s = prepare(q/some sql with parameters/);
  $s->bind_param(1, $param1, {TYPE => SQL_VARCHAR});
  $s->bind_param(2, $param2, {TYPE => SQL_VARCHAR});
  $s->execute;

See https://connect.microsoft.com/SQLServer/feedback/details/527188/paramater-datatype-lookup-returns-incorrectly and rt ticket 50852.

=head2 Why does my script pause for a while whenever my statement handle is destroyed (goes out of scope)?

The symptom is that sometimes when your statement handle goes out of
scope and is hence destroyed your script pauses for a while. If you
are using MS SQL Server and certain MS SQL Server ODBC Drivers this
can happen when you issue a select which would return a lot of rows
but you don't fetch them all.

The problem is that the TDS protocol (normally, without Multiple
Active Statement support, or MARS) sends all the result-set down the
socket until it is consumed by the client end. When your statement
handle is destroyed with pending results the ODBC Driver needs to read
all the results to clear the socket. In reality MS SQL Server will
only write so many rows at a time to the socket depending on its
buffer size and will occasionally look at the socket for new requests
so it is possible for ODBC Drivers which support SQLCancel to reduce
the number of rows sent by using DBI's cancel method. In this way the
statement destruction is speeded up since fewer rows you don't need
are sent.  See DBI's cancel method and if you destroy a statement
handle with pending results, call cancel before destruction.

However, you are best not selecting rows you have no intention of
retrieving.

See cancel_big_fetch.pl in the DBD::ODBC examples dir. NOTE: more recent
MS SQL Server drivers are better in this respect and sometimes the test
script cancel_big_fetch.pl shows no difference.

=head2 Why does my backup/restore/some_other_procedure in MS SQL Server not complete?

MS SQL Server batches up results in a procedure. A result may be the
output of a print or a select or in some cases even an insert/update
(see SET NOCOUNT ON|OFF). If you attempt to call a procedure using the
C<do> method and it outputs results (e.g., a print statement saying
the % completed) the procedure probably will not fully
complete. Instead you should do the following:

  $sth-prepare(call to my procedure);
  $sth->execute;
  do {
    while (my @row = $sth->fetchrow_array()) {
      # do stuff here
    }
  } while ($sth->{odbc_more_results});
  # do not forget to check $sth->err here if not using RaiseError as
  # the outer while loop will stop when there are no more results OR
  # if an error occurs.

=head2 Why do I get "The data types ntext and varchar are incompatible in the equal to operator"?

Or "The data types ntext and nvarchar(max) are incompatible in the
equal to operator".

MS SQL Server does not like equality comparisons with ntext columns.
You can get this error without using any Perl or DBD::ODBC simply by doing:

  select * from mytable where ntext_column = 'hello'

You unfortunately need to change your SQL to:

  select * from mytable where CAST(ntext_column AS nvarchar(max)) = 'hello'

=head2 Why are my integers returned as decimals?

If you are using the MS SQL Server ODBC driver and get
integers/booleans back as apparently decimals e.g. 0.00 instead of 0 and
integer primary keys as nn.nn you've probably either:

=over

=item enabled regional settings in the ODBC DSN setup (called "use regional settings with outputting..."

=item added "Regional=Yes" to your connection string.

The MS SQL Server ODBC Driver regional settings are massively flawed
and break lots of applications - turn it off.

=back

=head2 Why does Connect call fail on Ubuntu with a "undefined symbol SQLxxx" error?

Sometimes SQLxxx is SQLAllocHandle or SQLFetch but it could almost be
any ODBC API.

Did you build DBD::ODBC against iODBC? Some versions of Ubunutu Linux
seem to install the libiodbc shared object without a libiodbc.so
symbolic link. The key giveaway when you build DBD::ODBC is a warning like
this: