Niall Litchfield wrote:
> gonkowonko_at_gmail.com wrote:
>
>> I need help
>>
>> I want to insert a value into a ORacle table that has a ' in it for
>> example "Children's"
>>
>> i do a replace in VB to
>> courseName = Replace(courseName, "'", "''") and this works for SQL
>> server but doesnt work for oracle as i get the error message ORA-00917
>> missing comma error.
>>
>> I have searched and search but cant find nothing to help so please if
>> anyone has advice please let me know
>>
>> Gaz
>
>
> I have seen your follow up, but you are likely writing sub-standard code
> against Oracle. Your question is only really likely to arise if later
> you issue code like the following
>
>
> strSQL = "select column from tab where course_name = " & courseName
>
> ...
>
> rst = cmd.execute(strSQL)
>
> You should be writing this as
>
> strSQL = ""select column from tab where course_name = :courseName"
> cmd.parameters.add("courseName")
> cmd.parameters.("courseName").value = courseName
>
> rst = cmd.execute(strSQL)
>
> that is rather than using string substitution using bound variables.
>
> If you are doing the first then every execution of your program will
> require the sql statement to be reparsed and reoptimized before
> execution. Using bind variables this should only be done the once. In
> addition you'll gain the following useful benefits.
>
> 1. You can throw away your checks for special characters.
> 2. You protect yourself against sql injection
> 3. You can ensure that the right type of parameter is passed to the
> sqlstatement.
>
> Not using bind variables is the number one sin that developers new to
> Oracle commit.
>
>
and not reading the date of a post is the number one sin that I have
committed here. Apologies.
--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
Received on Sun Oct 09 2005 - 08:09:48 CDT
