Quoting problem in DBI:ODBC

[Grygonos]

Here is my code. I'm using MS-Access 97' unfortunately.

    #Compile all records under a clients aliases
foreach $alias (@aliases)
{
    $query  = "SELECT collection_information.[field1], ";
       $query .= "collection_information.[field2], collection_informat
+ion.[name] ";
       $query .= "FROM collection_information WHERE CLIENT=\'" . $alia
+s ."\'";

       $sth = $dbh->prepare($query);
     $sth->execute();
        
    #For each record returned
    while(my @record = $sth->fetchrow_array)
    {
        #Insert the record into the database    
        $query  = "INSERT INTO " . $client{'name'} ."_table ";
        $query .= "([field1],[field2],[name]) VALUES ";
        $query .= "(\'".$record[0]."\',\'".$record[1]."\',\'".$record[
+2]."\')";
                    
        $dbh->do($query);
    }
    $sth->finish();
}

Ok here's my problem... the insert statement is inserting people's names in one field. Now if someone's name such as O'Sullivan occurs... then this piece of code will foobar because of the single quoting in the VALUES portion of the insert statement. However, when I try and double quote it... it tells me it expects 3 parameters.

In my early DBI experience I had this problem occur and corrected it by changing the query to double quote the values... that was under MySQL (God I miss those days). Any possible remedies to this?
Thanks in advance, Gry

[hmerrill]

Just personal preference, but you might like it. IMHO it makes it easier to read the SQL - try formatting the DBI SQL like this:

  $query  = q{
     SELECT field1,
            field2,
            name
     FROM collection_information
     WHERE CLIENT = ?
  };

  $sth = dbh->prepare($query) || die "prepare failed: $DBI::errstr\n";
  $sth->execute($alias) || die "execute failed: $DBI::errstr\n";

And, most(?) DBD:: modules support table aliasing, like this:

   $query = q{
      SELECT c.name,
             c.state,
             c.zip,
      FROM collection_information c
      WHERE c.CLIENT = ?
   }

which makes it so that you don't have to type the long table name in front of each field. In fact, when you're selecting from just one table, you don't need to use the table name at all in front of each field being selected.

HTH.

[Grygonos]

I prefer doing the sql that way too... didn't know that I could use q{} to accomplish it thanks.
Thanks for the table aliasing tip I didn't know about that
As for putting the table names in front of things, I don't like to, but I think Access bombed the last time I tried to leave them out. I'll give it a shot when I'm @ work on monday. Thanks !

[Grygonos]

please disregard this... I had always quoted my own literals... never realized that i should just
$dbh->quote($record[0]);
and so on.. sorry.

[Seumas]

Another option is to use placeholders, which is even cleaner than using $dbh->quote(). It isn't the right fit for 100% of your queries and statements, but it is for 99% of them. Check the DBI:: docs for details on how to use it. It would be something like.

eval {
   my $sth = $dbh->prepare("INSERT INTO users (fname, lname, phone) VA
+LUES(?, ?, ?)");
   $sth->execute($fname, $lname, $phone);
   $dbh->commit();