How to retrieve multiple output sets using DBI

[roho]

I am sending multiple select statements to SQL Server via DBI (version 1.636) using Strawberry Perl (version v5.24.1) on Windows 10. The output returned is the result of the first select statement ONLY. How can I retrieve the output from ALL the select statements?

I expected to receive:
one
two
three
as the output, but received only the first output, "one".

Questions:
1. Is this possible using DBI?
2. If so, how can it be accomplished?

The following minimal set of code demonstrates the issue.

use warnings;
use strict;
use DBI;

my $usr = '<domain userid>';
my $psw = '<domain password>';

my $dsn = 'dbi:ODBC:Driver={SQL Server};<servername>;Database=master;T
+rusted_Connection=yes';
my $dbh = DBI->connect($dsn, $usr, $psw, { RaiseError => 1 });

my $cmd = q(select 'one';select 'two';select 'three';);
my $sth = $dbh->prepare($cmd);
$sth->execute();

while (my @data = $sth->fetchrow_array) {
    print "@data\n";
}
$sth->finish;

[hippo]

If you split it up so that you are only executing one SQL statement at a time, it will work fine. (using SQLite here to make it an SSCCE)

#!/usr/bin/env perl 
use strict;
use warnings;
use DBI;

my $dbh = DBI->connect('dbi:SQLite:dbname=/tmp/testdb','','') or die;
my @data;

my $sth = $dbh->prepare ('select ?');
for my $arg (qw/one two three/) {
        $sth->execute ($arg);
        push @data, $sth->fetchrow_array;
}
print "$_\n" for @data;

I expect that DBI just ignores everything after the first statement if you try to do it all at once. Another alternative would be a stored procedure but the best approach depends on what you're really trying to do.

Update: Confirmed. DBI says:

Multiple SQL statements may not be combined in a single statement handle ($sth), although some databases and drivers do support this (notably Sybase and SQL Server).

[roho]

Thanks, hippo. If it was just straight SQL I could do that, but I am actually executing a Stored Procedure in my code at $work which generates multiple outputs. The 3 select statements in my example were for brevity's sake.

[poj]

This stackoverflow question suggests this might work if you are using ODBC.

do {
   while (my @row = $sth->fetchrow_array()) {
      print "@row\n";
   }
}
while ($sth->{odbc_more_results});

poj

[roho]

Thank you, poj. That worked fine!

[NetWallah]

How about: (postgres example)

SELECT 'one' UNION SELECT 'two' UNION SELECT 'three';
 ?column?
----------
 one
 two
 three
(3 rows)

[roho]

Thanks NetWallah, but I should have mentioned that the multiple selects in my example were just for brevity. I am actually executing a Stored Procedure that generates multiple output sets. The solution suggested by poj displays all the output sets.