-710 error test code

This is the code I use to test the five scenarios where a -710 error can occur.

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

# Requires a working DBD::Informix environment.
# Requires a database called testdb to which you have at least the resource privilege.
# The script logs in using your local user; edit function dbconnect to use an explicit user name or password

# 1. $prepare s1 from "select c1, c2 from t710";$execute s1;$create index i1 on t710(c1);$execute s1; -------> -710 error
# 2. $prepare s1 from "select * from t710 where c1 = 10";$declare curs1 cursor for s1;$create index i1 on t710(c1);$open curs1; -------> -710 error
# 3. create procedure p1(c_a int, c_b int) returning integer;insert into A values(1001, 1001);update A set b=c_b where A.a=c_a; ==>(You have an update trigger defined on A which inserts into table Breturn 0;end procedure; create procedure p2() returning integer;define i integer;let i=p1(56, 56);create index i1 on B(b);return p1(56, 56); -- > -710 error when p1 is executedend procedure;
# You can still get -710 errors in your applications in one of the following scenarios:
# 4. The number and type of columns in your SELECT list have changed.
# 5. If you are executing a prepared DDL statement, you might get -710 errors.

if ($ARGV[0]) {
    if ($ARGV[0] eq 'all') {
        &test1;
        &test2;
        &test3;
        &test4;
        &test5;
    }
    elsif ($ARGV[0] eq '1') {
        &test1;
    }
    elsif ($ARGV[0] eq '2') {
        &test2;
    }
    elsif ($ARGV[0] eq '3') {
        &test3;
    }
    elsif ($ARGV[0] eq '4') {
        &test4;
    }
    elsif ($ARGV[0] eq '5') {
        &test5;
    }
    else {
        &print_usage;
    }
}
else {
    &print_usage;
}
print "\nExiting cleanly.\n\n";
exit 0;

sub print_usage {

    print "\nUsage: $0 {test case number}\n\n";
    exit 2;
}

sub test1 {

    print "\n".'Running test 1: $prepare s1 from "select c1, c2 from t710";$execute s1;$create index i1 on t710(c1);$execute s1; -------> -710 error'."\n\n";

    my ($dbh) = &dbconnect('testdb');

    &create_table($dbh, \'t710');

    print " Selecting values:\n\n";
    printf("%11s %11s\n", 'c1', 'c2');
    my $sql = "SELECT c1, c2 FROM t710";
    my $sth = $$dbh->prepare($sql);
    $sth->execute();
    while (my @columns = $sth->fetchrow_array()) {
        printf ("%11d %11d\n", $columns[0], $columns[1]);
    }

    print "\n Creating index:\n";
    $$dbh->do("CREATE INDEX i710_x1 ON t710 (c1)");
    print " done.\n";

    print " Re-using prepared statement to select values:\n\n";
    printf("%11s %11s\n", 'c1', 'c2');
    eval { $sth->execute(); };
    if (!$@) {
        while (my @columns = $sth->fetchrow_array()) {
            printf ("%11d %11d\n", $columns[0], $columns[1]);
        }
    }
    print " Closing statement handle:\n";
    $sth->finish();
    print " done.\n";

    &dbdisconnect($dbh);
}

sub test2 {

    print "\n".'Running test 2: $prepare s1 from "select * from t710 where c1 = 10";$declare curs1 cursor for s1;$create index i1 on t710(c1);$open curs1; -------> -710 error'."\n\n";

    my ($dbh) = &dbconnect('testdb');

    &create_table($dbh, \'t710');

    print " Preparing select statement:\n";
    my $sql = "SELECT * FROM t710";
    my $sth = $$dbh->prepare($sql);
    print " done.\n";

    print " Creating index:\n";
    $$dbh->do("CREATE INDEX i710_x1 ON t710 (c1)");
    print " done.\n";

    print " Using prepared statement to select values:\n\n";
    printf("%11s %11s\n", 'c1', 'c2');
    # $sth->execute(); opens the cursor according to DBD::Informix documentation
    eval { $sth->execute(); };

    if (!$@) {
        while (my @columns = $sth->fetchrow_array()) {
            printf ("%11d %11d\n", $columns[0], $columns[1]);
        }
    }
    print " Closing statement handle:\n";
    $sth->finish();
    print " done.\n";

    &dbdisconnect($dbh);
}

sub test3 {

    print "\n".'Running test 3: create procedure p1(c_a int, c_b int) returning integer;insert into A values(1001, 1001);update A set b=c_b where A.a=c_a; ==>(You have an update trigger defined on A which inserts into table B return 0;end procedure; create procedure p2() returning integer;define i integer;let i=p1(56, 56);create index i1 on B(b);return p1(56, 56); -- > -710 error when p1 is executedend procedure;'."\n\n";

    my ($dbh) = &dbconnect('testdb');

    &create_table($dbh, \'a');
    &create_table($dbh, \'b');

    my ($exists) = $$dbh->selectrow_array("SELECT COUNT(*) FROM sysprocedures WHERE procname=\'p1\'");
    if ($exists) {
        print " Dropping existing procedure \'p1\':\n";
        $$dbh->do("DROP PROCEDURE p1");
        print " done.\n";
    }
    print " Creating procedure \'p1\':\n";
    $$dbh->do("CREATE PROCEDURE p1 (c_a INT, c_b INT) RETURNING INTEGER; INSERT INTO a VALUES (1001, 1001); UPDATE a SET c2=c_b WHERE a.c1=c_a; RETURN 0; END PROCEDURE");
    print " done.\n";

    ($exists) = $$dbh->selectrow_array("SELECT COUNT(*) FROM sysprocedures WHERE procname=\'p2\'");
    if ($exists) {
        print " Dropping existing procedure \'p2\':\n";
        $$dbh->do("DROP PROCEDURE p2");
        print " done.\n";
    }
    print " Creating procedure \'p2\':\n";
    $$dbh->do("CREATE PROCEDURE p2 () RETURNING INTEGER; DEFINE i INTEGER; LET i=p1(56, 56); CREATE INDEX i1 ON b(c2); RETURN p1(56, 56); END PROCEDURE");
    print " done.\n";

    print " Creating update trigger on table \'a\' which inserts into table \'b\':\n";
    $$dbh->do("CREATE TRIGGER ta UPDATE OF c2 ON a REFERENCING NEW AS post FOR EACH ROW (INSERT INTO b (c1, c2) VALUES (post.c1, post.c2))");
    print " done.\n";

    print " Preparing statement to execute procedure \'p2\':\n";
    my $sql = "EXECUTE PROCEDURE p2 ()";
    my $sth = $$dbh->prepare($sql);
    print " done.\n Executing procedure \'p2\':\n";
    $sth->execute();
    my ($return_code);
    $sth->bind_columns(undef, \$return_code);
    eval { $sth->fetch(); };
    print " done.\n";
    print " Closing statement handle:\n";
    $sth->finish();
    print " done.\n";

    &dbdisconnect($dbh);
}

sub test4 {

    print "\nRunning test 4: The number and type of columns in your SELECT list have changed.\n\n";

    my ($dbh) = &dbconnect('testdb');

    &create_table($dbh, \'t710');

    print " Preparing select statement from table \'t710\':\n";
    my $sql = "SELECT c1, c2 FROM t710";
    my $sth = $$dbh->prepare($sql);
    print " done.\n";

    print " Adding extra column to table \'t710\':\n";
    $$dbh->do("ALTER TABLE t710 ADD c3 INT");
    print " done.\n";

    print " Selecting from table \'t710\' using prepared statement:\n\n";
    printf("%11s %11s\n", 'c1', 'c2');
    eval { $sth->execute(); };
    if (!$@) {
        while (my @columns = $sth->fetchrow_array()) {
            printf ("%11d %11d\n", $columns[0], $columns[1]);
        }
    }
    print " done.\n";
    print " Closing statement handle:\n";
    $sth->finish();
    print " done.\n";

    &dbdisconnect($dbh);
}

sub test5 {

    print "\nRunning test 5: If you are executing a prepared DDL statement, you might get -710 errors.\n\n";

    my ($dbh) = &dbconnect('testdb');

    &create_table($dbh, \'t710');

    print " Prepare a statement to add column \'c3\' to table \'t710\':\n";
    my $sql = "ALTER TABLE t710 ADD c3 INT";
    my $sth = $$dbh->prepare($sql);
    print " done.\n";

    print " Add a column \'c4\' to table t710:\n";
    $$dbh->do("ALTER TABLE t710 ADD c4 INT");
    print " done.\n";

    print " Execute the prepared statement to add column \'c3\' to table \'t710\':\n";
    eval { $sth->execute(); };
    print " done.\n";
    print " Closing statement handle:\n";
    $sth->finish();
    print " done.\n";

    &dbdisconnect($dbh);
}

sub create_table () {

    my $dbh = shift;
    my $tabname = shift;

    my ($exists) = $$dbh->selectrow_array("SELECT COUNT(*) FROM systables WHERE tabname=\'$$tabname\'");
    if ($exists) {
        print " Dropping existing table \'$$tabname\':\n";
        $$dbh->do("DROP TABLE $$tabname");
        print " done.\n";
    }
    print " Creating table \'$$tabname\':\n";
    $$dbh->do("CREATE TABLE $$tabname (c1 int, c2 int)");
    print " done.\n Inserting some data into table \'$$tabname\':\n";
    $$dbh->do("INSERT INTO $$tabname (c1, c2) VALUES (10, 10)");
    $$dbh->do("INSERT INTO $$tabname (c1, c2) VALUES (20, 20)");
    $$dbh->do("INSERT INTO $$tabname (c1, c2) VALUES (30, 30)");
    $$dbh->do("INSERT INTO $$tabname (c1, c2) VALUES (56, 56)");
    print " done.\n";
}

sub dbconnect () {

    my $database = shift;

    my %attr = (
        'PrintError' => 1,
        'RaiseError' => 1,
        'AutoCommit' => 1,
        'ChopBlanks' => 1,
        'ix_WithoutReplication' => 0,
    );

    my $dbh = DBI->connect("dbi:Informix:$database", '', '', \%attr) or die ("$DBI::errstr\n\n");
    $dbh->do('SET LOCK MODE TO WAIT 10') or die ("Can\'t set lock mode: $DBI::errstr\n");

    return (\$dbh);
}

sub dbdisconnect () {

    my $dbh = shift;
    $$dbh->disconnect();
}

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s