pvbcharon has asked for the wisdom of the Perl Monks concerning the following question:

Revered monks,

I come to you in a time of great need. I've inherited a web application running on Apache on a Windows server. It uses DBD::ODBC to connect to a Microsoft SQL Server.

I'm now working on transitioning the thing over to a Linux machine using FreeTDS through DBD::ODBC.

The problem I'm facing is that there seems to be a difference in implementation which boils down to this:

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

our $db = DBI->connect('dbi:ODBC:DATABASE', 'USER', 'PASSWORD',
  { RaiseError => 1, AutoCommit => 1 }
);

my $test=$db->prepare("select getdate()") or die($!);
$test->execute();
print $test->fetchrow_array();

my $test2=$db->prepare("select getdate()") or die($!);
$test2->execute();
print $test2->fetchrow_array();

[download]

On the Windows system I get the expected output (the date twice). On the Linux machine I get:

DBD::ODBC::st execute failed: unixODBCFreeTDSSQL ServerInvalid cursor state (SQL-24000) at testpm.cgi line 17.

twice. Line 17 is the second execute line. I get the output from the first statement but the script dies when reaching the second execute.

Now, for me this boils down to two questions:

  1. Is it a bad idea not to use a loop to fetch all data from a handle even if you know beyond reasonable doubt that there isn't any more? Meaning: Should I start rewriting the places where this occurs?
  2. Might this be a bug in FreeTDS that's worth reporting?

Thanks for your time.

Dennis

Replies are listed 'Best First'.
Re: FreeTDS (Linux) vs. SQLSRV32.dll (Windows) via ODBC
by sierpinski (Chaplain) on May 26, 2010 at 12:46 UTC
    even if you know beyond reasonable doubt that there isn't any more

    Do you know for sure that will always be the case? It would seem easier to plan for it now and using an array than wait for a year, or five, only to realize that something changed in the design or usage of your code and now more rows are required. If it always returns one row, then looping over it with one row shouldn't cause any problems.

    "Invalid cursor state" leads me to believe that there is a problem with performing two executes on a database in quick succession. Have you done any debugging between the two executes to rule out the obvious? For example, closing the connection and re-opening, or adding a sleep, or anything like that? I'm not an expert of using DBD, but I have done similiar things to what you're showing here and haven't run into this issue. 
    my $sth = $dbh->prepare("INSERT INTO tblserverattributes(hostname,attr
+name,attrvalue) VALUES (?,?,?)");

        foreach $attr( keys %{$info{'DATA'}}) {
                $sth->execute($hostname,$attr,$info{'DATA'}->{$attr});
        }

    [download]
    The code above shows multiple executes on a single prepare, although it's set up a bit differently than what you've got.

    I hope that helps.
[reply]
[d/l]
      Do you know for sure that will always be the case? It would seem easier to plan for it now and using an array than wait for a year, or five, only to realize that something changed in the design or usage of your code and now more rows are required. If it always returns one row, then looping over it with one row shouldn't cause any problems.

      No, you're right, it shouldn't and it probably won't. I'm just trying to avoid unnecessary clutter by sticking things in a loop when actually just fetching a single value. And this thing has piqued my curiosity ;o)

      Doing multiple executes on one prepared statement (as you wrote) works as expected. Also replacing the

      print $test->fetchrow_array()

      with a

      while($test->fetchrow_array()) { print; }

      works just fine (i.e. it allows the script to run completely) and prints exactly the same as before which leads me to believe that FreeTDS suspects there being more data to fetch.

      Thanks for your input!

[reply]
[d/l]
[select]
        Aren't you supposed to call $test->finish(); before prepare()ing another query?
[reply]
[d/l]
[select]
        That makes sense, it was waiting for more data to be returned, when you executed another query... Glad you got it worked out!
[reply]
Re: FreeTDS (Linux) vs. SQLSRV32.dll (Windows) via ODBC
by mje (Curate) on May 27, 2010 at 10:36 UTC

    At the very least this is a bug in freeTDS since the error message is wrong. IF there were more rows to fetch on the first statement handle then the error should be something like cannot use multiple active statements. Also the erroneous invalid cursor state is on the execute of the second statement which also makes no sense.

    DBD::ODBC will close the cursor when finish is called or when a fetch returns SQL_NO_DATA or when the statement handle goes out of scope. Your example does not fetch after the first row so it does not know to call SQLCloseCursor, you do not call finish and the statement handle does not go out of scope.

    The SQL Server ODBC driver knows the result-set is finished, presumably, because it received a TDS done packet.

    Other than freeTDS I do not know of any ODBC driver which would not work with your example.

[reply]
Re: FreeTDS (Linux) vs. SQLSRV32.dll (Windows) via ODBC
by coldguy (Sexton) on May 27, 2010 at 22:26 UTC

    I haven't worked with it for a few years, but I did a lot of stuff that used FreeTDS to talk to SQL Server a while back, and I was never able to get it to work with multiple active statements on a single connection. I would always end up with the same "Invalid cursor state" error you're seeing.

    We researched the issue pretty extensively and were unable to find a workaround, FreeTDS was missing support for the cursor types needed to make this possible. I'm guessing that this is still the case. As others have said, you'll either need to keep calling fetchrow_whatever until it returns undef or use the finish method to wrap up the current statement before you fire off another. That, or you could always open up a second connection.

    Also, watch out for NULL (\0) characters at the end of strings returned from your queries. We started seeing that come out of DBI::ODBC + FreeTDS when we moved up to SQL Server 2005.

[reply]
      Also, watch out for NULL (\0) characters at the end of strings returned from your queries. We started seeing that come out of DBI::ODBC + FreeTDS when we moved up to SQL Server 2005.

      Did you do anything about this? Did you investigate it? If you still see this problem I would like to hear about it.

[reply]
        Did you do anything about this? Did you investigate it? If you still see this problem I would like to hear about it.
        We didn't do anything about it other than s/\0$// -- it was an "Oh crap, we replaced the dead server and now the app is broke" kind of situation so I did a quick fix and never got back around to digging into the problem. I'm no longer at that position or working with MS SQL Server so I can't really tell you how it stands now.
[reply]

Back to Seekers of Perl Wisdom