I'm not familiar with DBD::ODBC, but using placeholders would
fix that (all quoting issues) problem, and can only help
performance - no good reason not to use placeholders, assuming
DBD::ODBC supports them. Read about them by doing
perldoc DBI
and search(using the forward slash "/") for "Placeholder".
That would make your code look something like this:
$sql = qq{
UPDATE asc_contacts
SET $subscriberhash{$contactvar} = ?
WHERE asc_contacts.contact_id = ?
};
my $sth = $dbh->prepare($sql)
or error_handler ("Prepare failed - blah blah. Error was $DBI::errstr");
my $rows_affected = $sth->execute(
$tree->{contact}->{$contactvar},
$tree->{contact}->{contact_id}
) or error_handler ("Execute failed - blah blah: error was $DBI::errstr");
And read about DBD::ODBC by doing
perldoc DBD::ODBC
HTH.
--
Hardy Merrill
Red Hat, Inc.
Bernard Golden [EMAIL PROTECTED] wrote:
> I am attempting to update a SQLServer db and getting an error when the value
> contains a quote (apostrophe) as can be seen from the following error
> message.
>
>
>
> Error is Update contact
> information failed, column is asc_contacts.title, value is vp's -1
> ([Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
> near 's'. (SQL-42000) [Microsoft][ODBC SQL Server Driver][SQL
> Server]Unclosed quotation mark before the character string ''.
> (SQL-42000)(DBD: Execute immediate failed err=-1)), occurred at
> Subscriber module line 891
>
> Here is the code for the update:
>
> my $rows = $dbh->do ("UPDATE asc_contacts set $subscriberhash{$contactvar}
> = '$tree->{contact}->{$contactvar}'
> where asc_contacts.contact_id = '$tree->{contact}->{contact_id}'")
> or error_handler ("Update contact information failed, column is
> $subscriberhash{$contactvar}, value is $tree->{contact}->{$contactvar}
> $DBI::err ($DBI::errstr)", __LINE__);
>
>
> In reading Paul DuBois's MySQL book he notes the quote() method as a way
> around the problem; however, I am not sure if that mechanism will work with
> SQLServer.
>
> If it is appropriate, I would change the code to:
>
> my $varvalue = $dbh->quote($tree->{contact}->{$contactvar});
> my $rows = $dbh->do ("UPDATE asc_contacts set $subscriberhash{$contactvar} =
> $varvalue
> where asc_contacts.contact_id = '$tree->{contact}->{contact_id}'")
> or error_handler ("Update contact information failed, column is
> $subscriberhash{$contactvar}, value is $tree->{contact}->{$contactvar}
> $DBI::err ($DBI::errstr)", __LINE__);
>
> Can anyone give me some advice as to whether this will work?
>
> Many thanks.
>
> Bernard Golden
