Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

How to retrieve multiple output sets using DBI

by roho (Canon)
on Feb 08, 2019 at 16:30 UTC ( #1229625=perlquestion: print w/replies, xml ) Need Help??
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;

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

Replies are listed 'Best First'.
Re: How to retrieve multiple output sets using DBI
by hippo (Canon) 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;

    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).
      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."

Re: How to retrieve multiple output sets using DBI
by poj (Monsignor) 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});
    poj
      Thank you, poj. That worked fine!

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

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)

                    As a computer, I find your faith in technology amusing.

      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."

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1229625]
Approved by Corion
Front-paged by haukex
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (7)
As of 2019-02-22 20:46 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    I use postfix dereferencing ...









    Results (119 votes). Check out past polls.

    Notices?