How to retrieve multiple output sets using DBI

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

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;
[download]

"It's not how hard you work, it's how much you get done."

Comment on How to retrieve multiple output sets using DBI Download Code

Replies are listed 'Best First'.
Re: How to retrieve multiple output sets using DBI by hippo (Archbishop) on Feb 08, 2019 at 16:46 UTC
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;` [download] 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).`	[reply] [d/l]
Re^2: How to retrieve multiple output sets using DBI by roho (Bishop) on Feb 08, 2019 at 17:27 UTC
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. "It's not how hard you work, it's how much you get done."	[reply]
Re: How to retrieve multiple output sets using DBI by poj (Abbot) on Feb 08, 2019 at 17:44 UTC
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});` [download] poj	[reply] [d/l]
Re^2: How to retrieve multiple output sets using DBI by roho (Bishop) on Feb 08, 2019 at 18:48 UTC
Thank you, poj. That worked fine! "It's not how hard you work, it's how much you get done."	[reply]
Re: How to retrieve multiple output sets using DBI by NetWallah (Canon) on Feb 08, 2019 at 18:20 UTC
How about: (postgres example) `SELECT 'one' UNION SELECT 'two' UNION SELECT 'three'; ?column? ---------- one two three (3 rows)` [download] As a computer, I find your faith in technology amusing.	[reply] [d/l]
Re^2: How to retrieve multiple output sets using DBI by roho (Bishop) on Feb 08, 2019 at 18:51 UTC
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. "It's not how hard you work, it's how much you get done."	[reply]