Escaping single quote in DBI::ODBC

2003-11-04 Thread Bernard Golden 
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

Re: Escaping single quote in DBI::ODBC

2003-11-04 Thread Chuck Fox 
Doubling up the single quotes will effectively escape them

[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

Re: Escaping single quote in DBI::ODBC

2003-11-04 Thread Hardy Merrill 
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