in reply to FreeTDS (Linux) vs. SQLSRV32.dll (Windows) via ODBC

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.

Replies are listed 'Best First'.
Re^2: FreeTDS (Linux) vs. SQLSRV32.dll (Windows) via ODBC
by pvbcharon (Beadle) on May 26, 2010 at 15:03 UTC
    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]

        According to http://search.cpan.org/~timb/DBI-1.611/DBI.pm#finish:

        When all the data has been fetched from a SELECT statement, the driver should automatically call finish for you. So you should not normally need to call it explicitly except when you know that you've not fetched all the data from a statement handle.

        I know that there isn't any more data. FreeTDS seems to disagree though...

[reply]
      That makes sense, it was waiting for more data to be returned, when you executed another query... Glad you got it worked out!
[reply]

        Well, except there isn't any more data... I think I'll ask the FreeTDS guys whether they know anything about it...

[reply]

In Section Seekers of Perl Wisdom