Solved

How do I execute multiple DB2 statements in Perl together

Posted on 2011-03-10
27
1,505 Views
Last Modified: 2012-05-11
Hi,

I want to execute multiple SQL queries one after another in Perl. The Queries are in DB2 and run together when they are separated by ";". But in Perl, it seems to take only the first query.

Here I have 2 queries in a merged cell separated by ";" and if any one of them run and return results, it should go into the array and the array would not be null and the test would be passed.

so ID = 0 in first query returns no rows, but ID = 302 returns a row.

But since the first query does not return results and second one does, it is not going in the array.

I cannot break them into 2 queries as one is related to the other. The queries can also be - one creates a temporary table and the next inserts into the table, both separated by ";" just like how a normal DB2 query runs.

How can I do it? I tried with a while loop also but did not work.    
#!/usr/bin/perl5.10

use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::WriteExcel;
use strict;
use IO::Scalar;
use Data::Dumper;
use Carp;
use DBI;
use DBD::MSDB2;

my $all_parser   = Spreadsheet::ParseExcel->new();
my $all_workbook = $all_parser->parse("TestQuery.xls");
my $all_worksheet = $all_workbook->worksheet("Check");

my ( $all_row_min, $all_row_max ) = $all_worksheet->row_range();


for my $all_row ($all_row_min+1 .. $all_row_max) {
my $actvcell =  $all_worksheet->get_cell($all_row,2);
next unless defined $actvcell;
$actvcell = $actvcell->value;

my $sel_dbh = DBI->connect("$server", '', '', { 'RaiseError' => 1 }) or
          die "Can't connect to database: $DBI::errstr";

my $sel_sth = $sel_dbh->prepare("$actvcell");
$sel_sth->execute();
my @sel_data =  $sel_sth->fetchrow_array;

if(grep(/ /, @sel_data) == @sel_data) { push @sel_data, 0};

print "\nThe array is @sel_data\n";

if ("@sel_data" ne "0") {

  print "\n\nTest Passed\n\n";

} ### if @sel_data ne 0

else {print "\n\nTest Failed\n\n";}

}

Open in new window

TestQuery.xls
0
Comment
Question by:sunny82
  • 17
  • 10
27 Comments
 
LVL 26

Expert Comment

by:wilcoxon
Comment Utility
Why can't you separate the queries?  They are completely independent.

Alternately, you can change them to "select * from ID where s.ID in (0,302);".

DB2 does not support multiple statements (only Sybase and SQL Server do) so you need to either rework the sql in the xls or rework the code.

One way of doing that is below..
#!/usr/bin/perl5.10

use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::WriteExcel;
use strict;
use IO::Scalar;
use Data::Dumper;
use Carp;
use DBI;
use DBD::MSDB2;

my $all_parser   = Spreadsheet::ParseExcel->new();
my $all_workbook = $all_parser->parse("TestQuery.xls");
my $all_worksheet = $all_workbook->worksheet("Check");

my ( $all_row_min, $all_row_max ) = $all_worksheet->row_range();


for my $all_row ($all_row_min+1 .. $all_row_max) {
    my $actvcell =  $all_worksheet->get_cell($all_row,2);
    next unless defined $actvcell;
    $actvcell = $actvcell->value;

    my $sel_dbh = DBI->connect("$server", '', '', { 'RaiseError' => 1 }) or
          die "Can't connect to database: $DBI::errstr";

    my @sql = grep { defined $_ and not /^\s*$/ } split /;/, $actvcell;
    my @sql_data;
    foreach my $sql (@sql) {
        my $sel_sth = $sel_dbh->prepare("$actvcell");
        $sel_sth->execute();
        push @sel_data, $sel_sth->fetchrow_array;
        $sel_sth->finish;
    }

    # XXX - what are you trying to do here?  i'm pretty sure this is wrong
    if(grep(/ /, @sel_data) == @sel_data) { push @sel_data, 0};

    print "\nThe array is @sel_data\n";

    if ("@sel_data" ne "0") {

        print "\n\nTest Passed\n\n";

    } ### if @sel_data ne 0

    else { print "\n\nTest Failed\n\n"; }

}

Open in new window

0
 
LVL 26

Expert Comment

by:wilcoxon
Comment Utility
Typo - change line 29 to @sel_data (not @sql_data).

Also, see the comment at line 37 - I'm not following what you're trying to do at line 38.
0
 

Author Comment

by:sunny82
Comment Utility
Line 38 inserts a 0 if the array returns a blank and then later marks it as Test Failed since it sees a 0.

Regarding the code which you sent, I will try it tomorrow and let you know.

I cannot separate the SQLs because there may be a case where the SQLs are connected, so trying to find a generic logic for doing it.

For example, what will happen in this case (for these SQL statements)--

create temporary table C1(col1, Col2, Col3);
insert into C1 values (1,2,3);
select * from C1 where C1.col1 = 2;

I think we cannot separate the SQLs since it creates a temporary table, hence it will vanish for the next statement handle. Will the above code work in that case?

Many thanks again for your response.
0
 
LVL 26

Expert Comment

by:wilcoxon
Comment Utility
It should work fine with separate SQLs.  temp tables don't go away until the session ends (not the current query).  I think the way I split the sql should work for all cases.

I think there's an error with line 38 (and/or 42) though...

The if compares the count of elements in @sel_data with the number of elements in @sel_data that contain an escaped non-breaking space (not that are empty).  It then pushes 0 on the end of @sel_data.

If you are checking to see if the fetch returned no data you could get rid of line 38 and change line 42 to "if (@sel_data) {".

As it is, if @sel_data contains any data (include  ) then line 42 will never be false.
0
 

Author Comment

by:sunny82
Comment Utility
Great, I'll try it tomorrow morning...
0
 

Author Comment

by:sunny82
Comment Utility
Really Sorry I could not run it on Friday as I became sick, I will definitely try it by tomorrow or Tuesday.

So sorry for the delay and many thanks for your patience. I will run it as soon as I can and let you know the results.
0
 

Author Comment

by:sunny82
Comment Utility
Shouldn't Line 31 be -

my $sel_sth = $sel_dbh->prepare("$sql");

instead of -

my $sel_sth = $sel_dbh->prepare("$actvcell");
?
0
 

Author Comment

by:sunny82
Comment Utility
Also after changing Line 31 to

my $sel_sth = $sel_dbh->prepare("$sql");

I have a huge SQL query about 400 lines where it creates temp table and then inserts into it and finally selects data from that table after joining it with many other tables.

In this scenario- I am getting an invalid cursor state error.

Here is the error --

st fetchrow_array failed: [IBM][CLI Driver] CLI0115E  Invalid cursor state. SQLSTATE=24000 at Sanity_Tests.pl line 33
0
 
LVL 26

Expert Comment

by:wilcoxon
Comment Utility
You're correct - it should be prepare($sql) [the double-quotes around $sql are unnecessary but also won't hurt anything other than a very tiny performance hit]

Can you try turning show sql (don't remember exact option name) on in DBI?  That way you can see exactly what sql it is failing on.

Does the 400 line sql explicitly use a cursor?  If not, I think you're into DB2-specific handling (which would be odd if it uses cursors where none was specified).  Does the 400 line sql work directly in DB2?

If the above don't help, if you throw the sql into a code attachment, I'll take a look (though I can't guarantee being able to spot the problem).
0
 

Author Comment

by:sunny82
Comment Utility
The first few statements of the SQL are like this --

DECLARE GLOBAL TEMPORARY TABLE session.DATE
   (EFFECTIVE_DTE DATE NOT NULL)
    ON COMMIT PRESERVE ROWS
    with REPLACE;
  Insert  into Session.DATE
    VALUES ('02/28/2011');
select * from Session.DATE;


Even if I run the code with just these few lines of SQL, it throws the same Invalid cursor state error. Why is that? It was running fine in case of just select statements(like in my original example) but creating a temp table, inserting into it and selecting from it is throwing the error. It runs fine in DB2.
0
 
LVL 26

Expert Comment

by:wilcoxon
Comment Utility
Could you try removing the ; after the declare and before the insert?  DB2 appears to use a weird temp table implementation (the "ON COMMIT PRESERVE ROWS" looks more like a cursor directive than a temp table directive from other databases I've used).
0
 
LVL 26

Expert Comment

by:wilcoxon
Comment Utility
It would also be helpful if you can determine exactly which statement is failing.  If you can't figure out how to get DBI to display sql on an error, you could add this line just before the $sel_dbh->prepare:

print "about to prepare:\n$sql\n";
0
 

Author Comment

by:sunny82
Comment Utility
If I remove that ; after the declare and before the insert getting this error --

[IBM][CLI Driver][DB2/LINUXX8664] SQL0104N  An unexpected token "DECLARE GLOBAL TEMPORARY TABLE SESSION.DATE" was found following "BEGIN-OF-STATEMENT".  Expected tokens may include:  "<space>".  SQLSTATE=42601


Let me try seeing which SQL is failing by giving the print
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:sunny82
Comment Utility
Giving the print statement gives me this --

about to prepare:
DECLARE GLOBAL TEMPORARY TABLE session.DATE
   (EFFECTIVE_DTE DATE NOT NULL)
    ON COMMIT PRESERVE ROWS
    with REPLACE
DBD::MSDB2::st fetchrow_array failed: [IBM][CLI Driver] CLI0115E  Invalid cursor state. SQLSTATE=24000
0
 

Author Comment

by:sunny82
Comment Utility
Its the temporary table and how its creation is done in DB2 which seems to the problem. Any workarounds for this? What do you think?
0
 

Author Comment

by:sunny82
Comment Utility
Maybe if I create a stored procedure out of all SQl queries and then call it from Perl? will that work?
0
 
LVL 26

Expert Comment

by:wilcoxon
Comment Utility
That's very odd that it is failing on the create temp table statement itself - I was pretty sure it would have been the insert or possibly the select.  I've never seen DBI fail on a sql statement that works fine in the db.

Given that, I think it's worth a try creating a stored proc and calling it from perl.

I'd be surprised if this issue is specific to perl (as DBD usually links with the C libraries provided by the db provider).  Do you know anyone that is experienced with writing programs (in any language) against DB2 that you could talk to?
0
 

Author Comment

by:sunny82
Comment Utility
I will have to look for other programmers to check that. For now, I am trying another approach. I will create a table (a permanent one) in the database and then run the code. Lets see where it fails. I will let you know the results and your suggestions. Just keeping fingers crossed :)

Many thanks as always.
0
 

Author Comment

by:sunny82
Comment Utility
Ok this what I did. I create a table called Date in the database.

After that put just 2 sql statements in the excel cell

Insert  into DATE
VALUES ('02/28/2011');
select * from DATE;

It gives the same error.
fetchrow_array failed: [IBM][CLI Driver] CLI0115E  Invalid cursor state. SQLSTATE=24000

But if I only keep
select * from DATE;

it works fine.

It seems to work only in case of select only. even does not work in case of Insert.
Maybe something missing in the code?



0
 
LVL 26

Expert Comment

by:wilcoxon
Comment Utility
Very strange.  I have no clue why DB2 would be executing a cursor to do a simple insert.

How did you define the permanent table?  I'm wondering if the cursor is an artifact of the table definition.

What happens if you only keep the insert (and get rid of the select)?

Highly unlikely to work but you could try "insert into DATE (EFFECTIVE_DTE) values ('02/28/2011')" [eg explicit insert column].
0
 

Author Comment

by:sunny82
Comment Utility
This was the table definition I created for Date, there was no cursor definition anywhere.

CREATE TABLE DATE

   (EFFECTIVE_DTE DATE NOT NULL)
    ;
 
Getting rid of the selects and just keeping the Insert statement gives the same error --
about to prepare:
insert into DATE (EFFECTIVE_DTE) values ('02/28/2011')
DBD::MSDB2::st fetchrow_array failed: [IBM][CLI Driver] CLI0115E  Invalid cursor state. SQLSTATE=24000

Maybe it is directly built into the Perl DBI::DBD modules to prevent multiple data-modification ( insert, update, delete) statements from running in a batch? What else may be reason?


   
0
 

Author Comment

by:sunny82
Comment Utility
My table definition was actually
CREATE TABLE pfnd.DATE

   (EFFECTIVE_DTE DATE NOT NULL)
    ;
where pfnd is the schema name where we all build in our codes. so maybe the cursor definition is a part of it?

or maybe Perl exclusively prevents data modification statements (not select though) because of SQL injection attacks? I cannot think of anything else.
0
 
LVL 26

Accepted Solution

by:
wilcoxon earned 500 total points
Comment Utility
Doh!  I think the problem is that you are trying to use fetchrow to do an insert.  Try this modified code (I think it includes the few fixes we talked about up a ways)...

If this fixes it, sorry for being slow today and missing it...
#!/usr/bin/perl5.10

use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
use Spreadsheet::WriteExcel;
use strict;
use IO::Scalar;
use Data::Dumper;
use Carp;
use DBI;
use DBD::MSDB2;

my $all_parser   = Spreadsheet::ParseExcel->new();
my $all_workbook = $all_parser->parse("TestQuery.xls");
my $all_worksheet = $all_workbook->worksheet("Check");

my ( $all_row_min, $all_row_max ) = $all_worksheet->row_range();


for my $all_row ($all_row_min+1 .. $all_row_max) {
    my $actvcell =  $all_worksheet->get_cell($all_row,2);
    next unless defined $actvcell;
    $actvcell = $actvcell->value;

    my $sel_dbh = DBI->connect("$server", '', '', { 'RaiseError' => 1 }) or
          die "Can't connect to database: $DBI::errstr";

    my @sql = grep { defined $_ and not /^\s*$/ } split /;/, $actvcell;
    my @sel_data;
    foreach my $sql (@sql) {
        if ($sql =~ m{\bselect\b}) {
            my $sel_sth = $sel_dbh->prepare($sql);
            $sel_sth->execute();
            push @sel_data, $sel_sth->fetchrow_array;
            $sel_sth->finish;
        } else {
            $sel_dbh->do($sql);
        }
    }

    # XXX - what are you trying to do here?  i'm pretty sure this is wrong
    if(grep(/&nbsp;/, @sel_data) == @sel_data) { push @sel_data, 0};

    print "\nThe array is @sel_data\n";

    if ("@sel_data" ne "0") {

        print "\n\nTest Passed\n\n";

    } ### if @sel_data ne 0

    else { print "\n\nTest Failed\n\n"; }

}

Open in new window

0
 

Author Comment

by:sunny82
Comment Utility
Brilliant !!!! That just did it :) wow...writing that "do" did the trick....:)
0
 

Author Comment

by:sunny82
Comment Utility
1 final question on this...

all this many create and insert statements insert the row "1 row(s) affected." into the array @sel_data. So even if the final select returns no rows, it still shows "Test Passed". How can I remove all these "1 row(s) affected." from the array so that it only considers whether the final select statement returns rows or not and accordingly display "Test Passed" or "Test Failed"
0
 

Author Comment

by:sunny82
Comment Utility
Actually I solved this. The solution was really good. Many many thanks.
0
 
LVL 26

Expert Comment

by:wilcoxon
Comment Utility
Are you sure it isn't the select(s) that are returning "1 row(s) affected"?  If it isn't a select, then nothing should get pushed onto @sel_data (the $sel_dbh->do does not do a push of anything onto @sel_data).

More generally, to remove lines like that, you can do:

@sel_data = grep { not m{^\d+\s+row\(s\)\s+affected} } @sel_data;
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now