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
