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

I am trying to use DBI and DBD:ODBC to run an SQL query. Both the server and my client code are running under Windows XP. I am running perl under cygwin on the client side. I have successfully run some INSERT queries but am having problems with a SELECT which returns no rows. The same SELECT query runs fine in SQLCMD. I think I must be missing some important step. My code is 
#!/usr/bin/perl -w
use strict;
use DBI;
my $dsn = 'driver={SQL Server};server=myserver;database=mydatabase;uid
+=myuid;pwd=mypassword;'; 
my $dbh = DBI->connect("dbi:ODBC:$dsn") || die "Couldn't open database
+: $DBI::errstr\n";
my $sql= 'SELECT count(items) FROM mytable ;';
my $sth=$dbh->prepare($sql);
my $rtv=$sth->execute() || die "Couldn't execute statement: $DBI::errs
+tr\n";
my @row = $sth->fetchrow_array();
print $row[0]."\n";
$dbh->disconnect();

[download]

Replies are listed 'Best First'.
Re: cygwin DBI DBD:ODBC MS-SQL select returns no rows
by kennethk (Abbot) on Dec 04, 2008 at 02:01 UTC

    How do you mean "having problems"?

    If it is that you are returning a blank line, your 0 numerical value returned by count might be being printed as a null string. If that's the case, you can force it into a numerical context, for example by print $row[0]+0,"\n";

    Update: To get better diagnostics on DBI's behaviors, I would highly recommend modifying your connect statement to the following:

    my $dbh = DBI->connect("dbi:ODBC:$dsn",
                       {
                        PrintError => 1,
                        RaiseError => 0,
                        AutoCommit => 0
                       }
                      )
    || die "Couldn't open database";

    [download]

[reply]
[d/l]
[select]

      Please, please, please use the meme foo or die, not foo || die. In some places || will do what you want, in others it will not. See the main thread on Re^4: selectrow_array() confusion (or vs ||) for an example that bit someone.

      --MidLifeXis

[reply]
[d/l]
[select]
Re: cygwin DBI DBD:ODBC MS-SQL select returns no rows
by Anonymous Monk on Dec 04, 2008 at 07:52 UTC
    You can always turn on trace 
    DBI->trace(99999);

    [download]
[reply]
[d/l]
Re: cygwin DBI DBD:ODBC MS-SQL select returns no rows
by MidLifeXis (Monsignor) on Dec 04, 2008 at 16:28 UTC
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom