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 ([EMAIL PROTECTED], [EMAIL PROTECTED])
Guardian of DBD::Informix 1.00.PC1 -- see
http://www.cpan.org/
#include <disclaimer.h>
