Perl DBI

That doesn't look like a perl DBI/DBD problem.  Must be the sql statement.  Are you sure the paranthesis after SET does not cause problem?  I don't think you need paranthesis.  Which DBMS are you using?  Did you test the sql statement using your DBMS sql client directly?  That'll make sure the sql is right.

I'm using CSV, just a regular text file

I'm not familiar with DBMS sql client - what is it and how do i use it.

When i remove the parathesis, I get this error
SQL ERROR:
String 'HASH(0x6d3854)' not allowed in Numeric expression!

SQL ERROR: ''?0?Finance?1?XXX-XXX-XXXX?2?Texas?3?Mail?4?Doe?5?John'' is not a valid value or is not quoted!

SV = PVIV(0x6a4658) at 0x5a0190
  REFCNT = 1
  FLAGS = (PADBUSY,PADMY,ROK)
  IV = 0
  RV = 0x6829d0
DBD::CSV::db do failed: dbih_getcom handle DBD::CSV::Statement=HASH(0x6829d0) is not a DBI handle (has no magic) at /export/appl/operation/lib/perl5/site_perl/5.8.0/SQL/Statement.pm line 164.
Cannot update dbih_getcom handle DBD::CSV::Statement=HASH(0x6829d0) is not a DBI handle (has no magic) at /export/appl/operation/lib/perl5/site_perl/5.8.0/SQL/Statement.pm line 164.

I tried updating only with one value, and it works,
However trying to update with multiple values, perl get upset and start to cry.

I dont see how this can be the sql statement since it works with just one value.

I see, it's indeed a perl problem.  The new error message means that the value you gave for those columns are not the type they're supposed to be.  In one, mysql is expecting numbers, but your code mistakenly gave it a hash reference (which is the String 'HASH(0x6d3854)').  Similar problem explains other things.  You need to print out your sql statement before execution and check in what values you used wrong types.

Simply use:

print "$sql\n";

before you prepare the statement.  Check if that statement is correct.  So far, you need to fix your code where a hashref was used in place of number.  Do this and show more of your code and I can fix it.

Keep in mind, I'm a beginner to Perl, I know and seen a much better way of doing this, but I still dont grasp the concept of Perl complicate syntaxes for example(just making it up)  /\a\/\b\/c\d/e/f/g  <- dont understand it and dont know what the purpose of it is.

I dont see how column datatypes can be enforced since the DB is just CSV, just a regular text file, unless theres something I dont know about perl, i dont see how perl can enforce datatypes on text files.


The code:
------------------
$dir = "/export/appl/website/team/cgi-bin/operation/";
$list = "CONTACTS";

my($CONNECT) = "DBI:CSV:f_dir=$dir";
my($dbh) = DBI->connect($CONNECT) or die "Cannot connect: " . $DBI::errstr;

#COLUMN NAMES
my(@fields) = (LNAME,FNAME,TITLE,TEAM,OFFICE,CELL,HOME,PAGER,LOCATION,MAILSTOP);      

#row_ref contains values SELECTED from table
my($qry) = "SELECT * FROM $list ORDER by lname, fname";
my($table_ref) = $dbh->selectall_arrayref($qry);

foreach $row_ref (@$table_ref)
{
   #process data  

   if($found){
      $set ="";
      
        $numOfFields =scalar(@fields);
      
         #build SET values
        for($n =2; $n <$numOfFields; $n++){
            $set .= $fields[$n]. "='". $$row_ref[$n] ."'";
            
                if($n < ($numOfFields - 1)){
                  $set .=", ";
            }
      }      
         #build query
      $query ="UPDATE $list SET ($set) WHERE LNAME='$lastname' AND FNAME='$firstname'";
         
         #debugging
         print $query."\n";

         #now execute it against the DB
        $dbh->do($query) or die "Cannot update " . $DBI::errstr;
   }
}
-------------------------------------
The output, after I had fiddled around with the code trying to get it to work, is now displaying

UPDATE CONTACTS SET (TITLE='Manager', TEAM='Finance', OFFICE='111-111-1111', CELL='', HOME='', PAGER='', LOCATION='Texas', MAILSTOP='XX-XXX') WHERE LNAME='Doe' AND FNAME='John'

SQL ERROR: Bad table or column name '(TITLE' has chars not alphanumeric or under
score!

SV = PVIV(0x6a4988) at 0x5a011c
  REFCNT = 1
  FLAGS = (PADBUSY,PADMY,ROK)
  IV = 0
  RV = 0x67eb34
DBD::CSV::st execute failed: dbih_getcom handle DBD::CSV::Statement=HASH(0x67eb34) is not a DBI handle (has no magic) at /export/appl/coreops/lib/perl5/site_perl/5.8.0/SQL/Statement.pm line 164.Cannot Execute: dbih_getcom handle DBD::CSV::Statement=HASH(0x67eb34) is not a DBI handle (has no magic) at /export/appl/coreops/lib/perl5/site_perl/5.8.0/SQL/Statement.pm line 164.
-----------------------------
Now the error seems to be saying bad column name, even though I know the column name does exists

This is the part of the code i'm having problem with


   if($found){
    $set ="";
   
        $numOfFields =scalar(@fields);
   
         #build SET values
       for($n =2; $n <$numOfFields; $n++){
         $set .= $fields[$n]. "='". $$row_ref[$n] ."'";
         
               if($n < ($numOfFields - 1)){
              $set .=", ";
         }
    }    
         #build query
    $query ="UPDATE $list SET ($set) WHERE LNAME='$lastname' AND FNAME='$firstname'";
       
         #debugging
        print $query."\n";

        #now execute it against the DB
       $dbh->do($query) or die "Cannot update " . $DBI::errstr;
  }

Just a quick diagnosis:

Obviously you need to get rid that paranthesis after SET because SQL ERROR pointed out that it thought '(Title' is a column name.  So that '(' must be gotten rid of (so is the ')' in the back).

$found was never true or non-zero in the code you wrote above, so the if clause will never be entered.

There are other syntactic errors that I'll correct for you a bit later.

works fantastic  thanks inq123, for the time taken to do this