sybperl-l Archive

From: jmcallister at dtint dot com
Subject: Re: using sybperl, how to insert data that contains a quote with in it
Date: Jan 5 2001 4:11PM

To avoid syntax errors you need to double every embedded quote. For
example:

     WRONG: update customer set name = 'Bob's Diner' where id = 42
     RIGHT:  update customer set name = 'Bob''s Diner' where id = 42   --
that's two single quotes between b and s.

SQL allows you to choose either quote character (' or "). You only need to
double the one that encloses the string. For example:

     RIGHT: update customer set name = "Bob's Diner" where id = 42
     RIGHT: update customer set name = "Randall ""Tex"" Cobb" where id =
101
     ALSO RIGHT: update customer set name = 'Randall "Tex" Cobb' where id
= 101

To double the quotes in Sybperl scripts you can do it two ways (at least)
depending on whether you are using CTLib or the older DBLib.

In CTLib try something like this:
     $sql =~ s/'/''/g;

In DBLib you can use a Sybase routine to do the conversion:
     $sql = $dbh->dbsafestr($sql, "'");

Of course do this just before executing the SQL.

Good luck

James McAllister



                                                                                                            
                    "Klein, Shoshana R"                                                                     
                        
                    com>                       cc:                                                          
                    Sent by:                   Subject:     using sybperl, how to insert data that contains 
                    owner-SYBPERL-L@lis        a quote with in it                                           
                    t.cren.net                                                                              
                                                                                                            
                                                                                                            
                    01/05/01 08:44 AM                                                                       
                    Please respond to                                                                       
                    SYBPERL-L                                                                               
                                                                                                            
                                                                                                            



Below is my sql statement to insert data into a table.
the variable $in_name  contains on occasion a single quote  for example
john
o'brien.

I get back an sql error for the single quote
I've tried the following variations
anyone know how I can keep the value of the variable when it includes a
single quote




 my  $sql ="insert into amp_repos_user_app  values
                   ('$guid', '$in_aid',
                 '$in_name', '$in_login', '$in_second_login',
                  '$in_dept', '$in_user_dept_load_dt', '$upd_dtm')";




 my  $sql ="insert into amp_repos_user_app  values
                   ('$guid', '$in_aid',
                "$in_name", '$in_login', '$in_second_login',
                  '$in_dept', '$in_user_dept_load_dt', '$upd_dtm')";



my  $sql ="insert into amp_repos_user_app  values
                   ('$guid', '$in_aid',
                '"$in_name"', '$in_login', '$in_second_login',
                  '$in_dept', '$in_user_dept_load_dt', '$upd_dtm')";





    sql_query($login, $sql);