How to get the result of a stored procedure using perl?

Question

I am starting in sql. I created the procedure as follows

create procedure testprocedure2 as select 'one' select 'three' select 'five' 

When I execute a query in the database, it shows three results one three five . sql query exec TEST_ABC_DB.dbo.testprocedure2

When I run the same query in Perl, it gives only one entry, which one

 $sth = $dbh->prepare("exec TEST_ABC_DB.dbo.testprocedure2"); $sth->execute(); while (@row= $sth->fetchrow_array()) { print $row[0]."\t"; print "\n"; } 

I do not know what's the problem. How can i fix this? I hope this answer helps in yesterday's question

Answer 1

Via the driver (e.g. DBD :: ODBC )

Since you are using DBD :: ODBC , you can use more_results from this driver to get the results of multiple queries in one execute .

This is an example that they show in the documentation.

 do { my @row; while (@row = $sth->fetchrow_array()) { # do stuff here } } while ($sth->{odbc_more_results}); 

If we want to do this with your sample queries, it's almost the same. You start your stored procedure, and then proceed with the do {} while construct (note that this is not a block, you cannot next from this!).

 my $sth = $dbh->prepare("exec TEST_ABC_DB.dbo.testprocedure2"); $sth->execute; do { while (my @row = $sth->fetchrow_array()) { print $row[0]."\t"; print "\n"; } } while ($sth->{odbc_more_results}); 

This should print the expected result.

 one three five 

Some other drivers also provide this. If so, you can call $sth->more_results instead of using internal elements, as described below.

---

Workaround if your driver does not support this

DBI itself cannot immediately return the result of several queries. You can run them, but you cannot get the results.

If you really need three separate queries in your procedure and want to get all the results, Shahheer and Shahzad answers to use UNION in place.

However, your example is probably contrived. You probably do not have the same number of columns in each of these queries, and you need to distinguish between the results of each of the queries.

We must modify the SQL and Perl code for this.

To make this work, you can insert additional rows, which you can later use to map each stack of results to each query.

Let's say the procedure is as follows:

 create procedure testprocedure3 as select 'one' select 'three', 'three', 'three' select 'five', 'five', 'five', 'five', 'five' 

This is another line for each request, but this should be an example. Using the UNION approach, it first becomes:

 create procedure testprocedure3 as select 'one' union all select 'three', 'three', 'three' union all select 'five', 'five', 'five', 'five', 'five' 

If you run this, it may fail. In ANSI SQL, UNION should have the same number of columns in all of its queries, so I assume SQLServer also wants this. We need to fill them with NULL s. Add them to all queries so that they match the number of columns in the column with the largest number of columns.

 create procedure testprocedure3 as select 'one', NULL, NULL, NULL, NULL union all select 'three', 'three', 'three', NULL, NULL union all select 'five', 'five', 'five', 'five', 'five' 

If we now go to it in Perl with the following code, we will get something back.

 use Data::Dumper; my $sth = $dbh->prepare("exec TEST_ABC_DB.dbo.testprocedure3"); $sth->execute; while ( my $row = $sth->fetchrow_arrayref ) { print Dumper $row; } 

We will see output similar to this (I did not run the code, but wrote the result manually):

 $VAR1 = [ 'one', undef, undef, undef, undef ]; $VAR1 = [ 'three', 'three', 'three', undef, undef ]; $VAR1 = [ 'five', 'five', 'five', 'five', 'five' ]; 

We have no way of knowing which row belongs to that part of the query. Therefore, insert the separator.

 create procedure testprocedure3 as select 'one', NULL, NULL, NULL, NULL union all select '-', '-', '-', '-', '-' union all select 'three', 'three', 'three', NULL, NULL union all select '-', '-', '-', '-', '-' union all select 'five', 'five', 'five', 'five', 'five' 

Now the result of the Perl code will look like this:

 $VAR1 = [ 'one', undef, undef, undef, undef ]; $VAR1 = [ '-', '-', '-', '-', '-' ]; $VAR1 = [ 'three', 'three', 'three', undef, undef ]; $VAR1 = [ '-', '-', '-', '-', '-' ]; $VAR1 = [ 'five', 'five', 'five', 'five', 'five' ]; 

This may not be the best choice for a separator, but it perfectly illustrates what I plan on doing. All we need to do is split this into separate results.

 use Data::Dumper; my @query_results; my $query_index = 0; my $sth = $dbh->prepare("exec TEST_ABC_DB.dbo.testprocedure3"); $sth->execute; while ( my $row = $sth->fetchrow_arrayref ) { # move to the next query if we hit the delimiter if ( join( q{}, @$row ) eq q{-----} ) { $query_index++; next; } push @{ $query_results[$query_index] }, $row; } print Dumper \@query_results; 

I defined two new variables. @query_results contains all results sorted by query number. $query_index is the index for this array. It starts at 0.

We iterate over all the resulting rows. It is important that $row is lexical here. It should be created using my in the loop header. (You use use strict , right?) If we see a separator, we increase $query_index and continue moving. If we do not, we have a regular result string, so we insert this into our @query_results array in the current query index.

The overall result is an array with arrays of arrays in it.

 $VAR1 = [ [ [ 'one', undef, undef, undef, undef ] ], [ [ 'three', 'three', 'three', undef, undef ] ], [ [ 'five', 'five', 'five', 'five', 'five' ] ], ]; 

If you have real queries that return many rows, this starts to make a lot of sense.

Of course, you do not need to save all the results. You can also simply work with the results of each query directly in your loop.

---

^{Disclaimer I did not execute any code in this answer since I do not have access to SQLServer. It may contain syntax errors in Perl as well as SQL. But he demonstrates the approach.}

Answer 2

The procedure you created returns 3 sets of results. And you get only 1 result. If you're not worried about sets, make them as one result with UNION ALL

 create procedure testprocedure2 as select 'one' union all select 'three' union all select 'five' 

Edit:

If you want to capture multiple result sets returned from a stored procedure, here is a good example explained with the MySQL database. Many datasets in MySQL stored procedures.

Answer 3

just use all joins like this, then only one table is displayed with data.