FAQ
God morning!

I am a novice DBI user and would appreciate your help with a challenge I
have.

Basically, I need toquery an informix table and return a list qualified
records. This I can as I can loop through the hash and print the data.
What I need to do, once i have the qualified records, is modify a value in
each record and write it back to the table.

Here is the code that retrieves and displays my data:

$dbserver = "odev";
$dbname = 'arsystem';
$t_table1 = 't17';
#
####################
print "DBSERVER = $dbserver\n";
print "DBNAME = $dbname\n";
print "T-TABLE = $t_table1\n\n\n";

### Connect to Remedy DB via perl DBI (bypasses Remedy)
#DBI->trace(3, "dbitrace.txt"); # Trace for edification
$ENV{'INFORMIXDIR'} = '/2001/informix';
$ENV{'INFORMIXSERVER'} = $dbserver; # Environment vars need to be set:
$server = $dbserver; # $ENV{'INFORMIXSERVER'};
$dbh = DBI->connect("dbi:Informix:$dbname") || die $DBI::errstr;

### DBI query
$dbh->{RaiseError} = 1; # On error, Automatically print error message and
die
$dbh->{AutoCommit} = 0; # Don't commit until called for
$sql = "select c1 from $t_table1 where c1= '268096'";
$sth = $dbh->prepare( $sql ) or die $sth->errstr;
$sth->execute;

while(($request_id) = $sth->fetchrow()){
print " RequestID ='$request_id'\n";
$newID = "0".$request_id;
print " New Request ID = $newID\n";
}

$result = $dbh->disconnect;

I do receive an error at the end of the while loop, after the last record
that says:

DBD::Informix::st fetchrow failed: SQL: -1829: <<Failed to locate SQL error
message>> at rqst-test.pl line 55.

I've tried to nest another query in the while loop to do the update of the
table but everything seems to fall apart. Any help for a novice is greatly
appreciated!

Steve McDonald

Search Discussions

  • Jonathan Leffler at Jan 18, 2002 at 5:34 am

    Steve McDonald wrote:
    God morning!

    I am a novice DBI user and would appreciate your help with a challenge I
    have.

    Basically, I need toquery an informix table and return a list qualified
    records. This I can as I can loop through the hash and print the data.
    What I need to do, once i have the qualified records, is modify a value in
    each record and write it back to the table.

    Here is the code that retrieves and displays my data:

    $dbserver = "odev";
    $dbname = 'arsystem';
    $t_table1 = 't17';
    #
    ####################
    print "DBSERVER = $dbserver\n";
    print "DBNAME = $dbname\n";
    print "T-TABLE = $t_table1\n\n\n";

    ### Connect to Remedy DB via perl DBI (bypasses Remedy)
    #DBI->trace(3, "dbitrace.txt"); # Trace for edification
    $ENV{'INFORMIXDIR'} = '/2001/informix';
    $ENV{'INFORMIXSERVER'} = $dbserver; # Environment vars need to be set:
    $server = $dbserver; # $ENV{'INFORMIXSERVER'};
    $dbh = DBI->connect("d
    bi:Informix:$dbname") || die $DBI::errstr;
    $dbh = DBI->connect("dbi:Informix:$dbname",'','',{RaiseError => 1});

    # This saves messing with the || die and 'or die' stuff later.
    ### DBI query
    $dbh->{RaiseError} = 1; # On error, Automatically print error message and
    die
    $dbh->{AutoCommit} = 0; # Don't commit until called for
    $sql = "select c1 from $t_table1 where c1= '268096'";
    $sth = $dbh->prepare( $sql ) or die $sth->errstr;
    $sth->execute;

    while(($request_id) = $sth->fetchrow()){
    print " RequestID ='$request_id'\n";
    $newID = "0".$request_id;
    print " New Request ID = $newID\n";
    }

    $result = $dbh->disconnect;

    I do receive an error at the end of the while loop, after the last record
    that says:

    DBD::Informix::st fetchrow failed: SQL: -1829: <<Failed to locate SQL error
    message>> at rqst-test.pl line 55.
    This means that there is a problem of some sort with your environment,
    but it probably is not critical.
    I've tried to nest another query in the while loop to do the update of the table but everything seems to fall apart. Any help for a novice is greatly appreciated!
    $st1 = $dbh->prepare("select c1 from $t_table1 where c1= '268096' for update");
    $st1->execute;
    $cn = $st1->{CursorName); # Check spelling of attribute!
    $st2 = $dbh->prepare("update $t_table1 set x1 = ? where current of $cn");
    while (($row = $st1->fetchrow()))
    {
    $st2->execute($new_value_for_column_x1);
    }



    The key parts are the 'for update' clause which makes it easy to modify the row you are looking at, and the 'where current of' clause which actually does the modification. You might be able to embed $st1->{CursorName} directly in the string. The name of the cursor is fixed by DBD::Informix.

    --
    Jonathan Leffler (jleffler@earthlink.net, jleffler@informix.com)
    Guardian of DBD::Informix 1.00.PC1 -- see
    http://www.cpan.org/
    #include <disclaimer.h>

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedJan 16, '02 at 5:18p
activeJan 18, '02 at 5:34a
posts2
users2
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase