Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear All,

I have this code 
use Win32::ODBC;
my $DSN = 'Monday';
my $srv_tbl = 'Servers';
my $Master = 'Master';
my $Applications = 'Applications';
                   

my $EDS_DB = new Win32::ODBC($DSN) or die "Error => $!\n";
die "Sql Failed" . $EDS_DB->Error( ) . "\n" if ($EDS_DB->Sql("SELECT *
+ FROM Master"));


my $srv_name;
my $app;
while ($EDS_DB->FetchRow())
{
        my %data = $EDS_DB->DataHash();
        $srv_name =  $data{"Server_Name"};
        $app = $data{"Application"};
        print qq(Server Name is $srv_name, $app\n);    
        $EDS_DB->Sql("INSERT INTO Servers (Server_Name) VALUES ('$srv_
+name')");
}

[download]
this line $EDS_DB->Sql("INSERT INTO Servers (Server_Name) VALUES ('$srv_name')"); doesn't seem to work i.e. it only adds one entry in randon to the table "Servers" however if I comment it out then I do get a data dump of all info in the Master table!

Any help please?

Thanks

Replies are listed 'Best First'.
Re: Inserting values into MS Access
by davidrw (Prior) on Jun 06, 2005 at 16:00 UTC
    What is the error message from Win32::ODBC? Also, what are the values of $srv_name (i suspect bad quotes or something along those lines)? Try adding this debugging code (use print instead of warn if you prefer) around your Sql() call: 
    my $sql = "INSERT INTO Servers (Server_Name) VALUES ('$srv_name')";
warn "SQL: " . $sql;
my $rc = $EDS_DB->Sql($sql);
warn "Return Code: '$rc'";
warn "Error String: " . $EDS_DB->Error;

    [download]
[reply]
[d/l]
      There is no errors from ODBC, But I am trying to insert Srv values obtained from the Master table into another table called Servers.

      Now, If I use this bit of the code 
      while ($EDS_DB->FetchRow())
{
        my %data = $EDS_DB->DataHash();
        $srv_name =  $data{"Server_Name"};
        $San = $data{"SAN"};
        $platform = $data{"Platform"};
        print qq(Server Name is $srv_name,$platform,$San\n);    
        
}

      [download]
      then I do get out all the data outputed from the Master table. But If is stick this line$EDS_DB->Sql("INSERT INTO Servers (Server_Name,Platform,SAN) VALUES ('$srv_name','$platform','$San')"); just before the the end of the loop (i.e. before the last curly bracket) then only one entry (i.e. record) will be inserted into my Servers table!!

      I hope this is clear.
[reply]
[d/l]
[select]
        Since only one record is inserted, my first suspicion is that the second item you try to insert is bad in some way, hence the need for the more detailed debugging above (especially the print'ing of $sql -- what does that show? if you paste directly into access does it work?)

        My second suspicion is that the nested Sql() call is destroying the outer recordset (which is also consistent w/your 1-insert observations). To validate this, rework your code to something like this: 
        my @data;
while ($EDS_DB->FetchRow()){
  my %data = $EDS_DB->DataHash();
  push @data, \%data;
}
# if @data doesn't have > 1 element here, check $EDB_DB->Error for the
+ reason
foreach my $data (@data){
  my $sql = sprintf "INSERT INTO Servers (Server_Name) VALUES ('%s')",
+ $data->{Server_Name};
  warn "SQL: " . $sql;
  my $rc = $EDS_DB->Sql($sql);
  warn "Return Code: '$rc'";
  warn "Error String: " . $EDS_DB->Error;
}

        [download]
[reply]
[d/l]
Re: Inserting values into MS Access
by VSarkiss (Monsignor) on Jun 06, 2005 at 17:02 UTC
[reply]
[d/l]
[select]
      This sounds really cool, let try it and be right back.

      Thanks.
      Blackadder
[reply]

Back to Seekers of Perl Wisdom