Problem with ADO update

[mikrom]

Hello,
I try to use Perl and ADO to process database.
Here is my script

###################################
# Changing the value of AKTSALDDAT 
###################################

### Login Data
$csebk = 'AS_400'; 
$userid= 'user';
$pwd   = 'pass';

### using ADO
use Win32::OLE;
use Win32::OLE::Const 'Microsoft ActiveX Data Objects';
# Create a new Connection
$Conn = Win32::OLE->new("ADODB.Connection");
$RS   = Win32::OLE->new("ADODB.Recordset");
# Connection String
$DSN="PROVIDER=IBMDA400;DATA SOURCE=$csebk; USER ID=$userid; PASSWORD=$pwd";
#print "$DSN\n";
$Conn->Open($DSN);
$sql="SELECT * FROM r000100T.r000270V";
$RS->Open($sql, $Conn, 1, 3);
#get the fields
$aktsalddat=$RS->Fields->Item('AKTSALDDAT')->Value;
$ltzsalddat=$RS->Fields->Item('LTZSALDDAT')->Value;
print "\n";
print "Current values:\n";
print "----------------\n";
print "AKTSALDDAT : $aktsalddat\n";
print "LTZSALDDAT : $ltzsalddat\n";
print "\n";

print "New value of AKTSALDDAT : ";
$neuaktsalddat= <STDIN>;
chomp($neuaktsalddat);

if ($neuaktsalddat ne '') {
  # update fields
  $fields = ["AKTSALDDAT","LTZSALDDAT"];
  $values = [$neuaktsalddat,$aktsalddat];
  $RS->Update($fields,$values);
  
  if (Win32::OLE->LastError()){
    print Win32::OLE->LastError(), "\n";
  }
  else{
    print "AKTSALDDAT was changed !\n";
    print "\n";
    print "New values are:\n";
    print "---------------- \n";
    print "AKTSALDDAT : $neuaktsalddat\n";
    print "LTZSALDDAT : $aktsalddat\n";
  }
}
else {
  print "AKTSALDDAT was not changed !\n";
}

# Close Recordset
$RS->Close;
# Close connection
$Conn->Close;

(Note that the database has only one record with system settings.)

When I enter the new value of AKTSALDDAT, to update both fields by calling

  $RS->Update($fields,$values);

I get this error /Win32::OLE->LastError()/

OLE exception from "Provider":

Method is not supported by this provider.

Win32::OLE(0.1707) error 0x80040e53
    in METHOD/PROPERTYGET "Update"

What's going wrong? Why Update method is not supported?

There must be something Perl-specific, because when I use similar Python-script, with the same connection string and the same RecordSet filling $RS->Open($sql, $Conn, 1, 3) updating record works.
Can anybody help me?

 

[mikrom]

I maked some changes:
Before Updating I first close the old RecordSet for selection and then I open a new RecordSet for updating with

  $RS->Open($sql, $Conn, adOpenDynamic);

Hmmm.. it's strange but it works. I don't get any Win32::OLE error and updating record works now fine.

The complete working example is here:

###################################
# Changing the value of AKTSALDDAT
###################################

### Login Data
$csebk = 'AS_400'; 
$userid= 'user';
$pwd   = 'pass';

### using ADO
use Win32::OLE;
use Win32::OLE::Const 'Microsoft ActiveX Data Objects';
# Create a new Connection
$Conn = Win32::OLE->new("ADODB.Connection");
$RS   = Win32::OLE->new("ADODB.Recordset");
# Connection String
$DSN="PROVIDER=IBMDA400;DATA SOURCE=$csebk; USER ID=$userid; PASSWORD=$pwd";
# Open Connection
$Conn->Open($DSN);
# Open new RecordSet for Select
$sql="SELECT * FROM R000100T.R000270V";
$RS->Open($sql, $Conn, adLockOptimistic, adOpenDynamic);
# Get the Fields
$aktsalddat=$RS->Fields->Item('AKTSALDDAT')->Value;
$ltzsalddat=$RS->Fields->Item('LTZSALDDAT')->Value;
print "\n";
print "Current values:\n";
print "----------------\n";
print "AKTSALDDAT : $aktsalddat\n";
print "LTZSALDDAT : $ltzsalddat\n";
print "\n";

print "New value of AKTSALDDAT : ";
$neuaktsalddat= <STDIN>;
chomp($neuaktsalddat);
if ($neuaktsalddat ne '') {
  # Close old RecordSet	
  $RS->Close;  
  # Open new RecordSet for Update
  $RS->Open($sql, $Conn, adOpenDynamic);
  # Fill the Fields for Update    
  $fields = ["AKTSALDDAT","LTZSALDDAT"];
  $values = [$neuaktsalddat,$aktsalddat];
  # Update fields
  $RS->Update($fields,$values);

  if (Win32::OLE->LastError()){
    print Win32::OLE->LastError(), "\n";
  }
  else{
    print "AKTSALDDAT was changed !\n";
    print "\n";
    print "New values are:\n";
    print "---------------- \n";
    print "AKTSALDDAT : $neuaktsalddat\n";
    print "LTZSALDDAT : $aktsalddat\n";
  }
}
else {
  print "AKTSALDDAT was not changed !\n";
}

# Close RecordSet
$RS->Close;
# Close Connection
$Conn->Close;

I'm in Perl, so I have lot of questions abou Perl and databases:

Is there any other way except ADO, how to work from Perl with a remote database from Windows?

I heared something about DBI, but I have not found any usable example for accessing remote database on IBM iSeries (AS/400) from PC. Is this possible with DBI?

Are there any good tutorials for DBI?

 

[dmazzini]

DBI examples:

http://www.saturn5.com/~jwb/dbi-examples.html

ODBC Examples

http://www.roth.net/perl/odbc/

http://www.datadirect.com/developer/odbc/odbc_perl/index.ssp

http://www.northbound-train.com/perl/article/Article.html

dmazzini
GSM System and Telecomm Consultant