Perl DBI

I'm new to Perl. I'm trying to execute an SQL UPDATE statement .

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

I used this perl statement,

my $sth = $dbh->prepare($sql) or die "Cannot prepare: " . $dbh->errstr();            
$sth->execute() or die "Cannot Execute: " . $sth->errstr();

However I get this error message
------------------------------------------------------
SQL ERROR: Incomplete statement!
SV = PVIV(0x6a7000) at 0x5a1680
  REFCNT = 1
  FLAGS = (PADBUSY,PADMY,ROK)
  IV = 0
  RV = 0x670a80
DBD::CSV::st execute failed: dbih_getcom handle DBD::CSV::Statement=HASH(0x670a80) 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 Execute: dbih_getcom handle DBD::CSV::Statement=HASH(0x670a80) is not a DBI handle (has no magic) at /export/appl/operation/lib/perl5/site_perl/5.8.0/SQL/Statement.pm line 164.
Content-type:text/html
------------------------------------------------------

If i decipher the error message correctly, it seems to be pointing to my sql query, but my query is correct or at least i think it is because I dont see anything wrong with it.

What does the error message means? I'd like to understand this to prevent it from happening again in the future.
How do i fix this ?
JHostAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

inq123Commented:
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.
JHostAuthor Commented:
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.
inq123Commented:
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.
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

JHostAuthor Commented:
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
JHostAuthor Commented:
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;
  }
inq123Commented:
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.
inq123Commented:
Without changing logic, your code should look something like this:

use strict;

my $dir = "/export/appl/website/team/cgi-bin/operation/";
my $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 my $row_ref (@$table_ref)
{
  #process data
  my $set;
  my $numOfFields = @fields;
  #build SET values
  for(my $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;
}

If you do update on a lot of rows, you should use DBI's prepare function ($sth = $dbh->prepare($sql); $sth->execute(...)).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JHostAuthor Commented:
works fantastic  thanks inq123, for the time taken to do this
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Perl

From novice to tech pro — start learning today.