• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1110
  • Last Modified:

ORACLE 11G - GET RID AN APOSTROPHE IN A STRING

In Crystal Reports I have a sub report where the title is passed from the main report.  The main report passed this title INTRODUCTION TO THE SCHOOL'S DRUG ABUSE POLICY
When this value is passed to the sub report the following error appears
Failed to retrieve data from the database
Details: SQL Exception [SQL State] 4200 [Error Message] ORA-00907 missing right parenthesis [Database Vendor Code 907]

My sub report sql is SELECT * FROM table WHERE title = ‘{?title_passed_from_main_report}’
In this case the title is INTRODUCTION TO THE SCHOOL'S DRUG ABUSE POLICY

IN ORACLE or CRYSTAL, how can I replace the single quote in the title with a character?
     
0
cookiejar
Asked:
cookiejar
  • 4
  • 4
  • 3
  • +4
1 Solution
 
sdstuberCommented:
first,  use bind variables and you won't have that issue.
You are "safely" falling into a SQL Injection problem.

I say safe because are failing rather than processing the sql.

second, if you don't use binds and the risk is acceptable
simply replace the apostrophe with a 2 before inserting into your query

that is,  make SCHOOL'S  become SCHOOL''S
0
 
sdstuberCommented:
I don't think you can use bind variables directly in crystal reports, at least not most versions, maybe in something very new.

You might want to consider putting the query in a stored procedure that returns a reference cursor for crystal to process
0
 
leewv1Commented:
Can you try INTRODUCTION TO THE SCHOOL||chr(39)||S DRUG ABUSE POLICY
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
for_yanCommented:
you can just use two single quotes intead of one - it will insert one single quiote, like that:

select * from table where title = 'INTRODUCTION TO THE SCHOOL''S DRUG ABUSE POLICY'

it will select correctly if there is only ioone sibngle quote in the actual string

You can isert stuyff in thwe same way

0
 
for_yanCommented:
In the above SCHOOL''S  should have two connsecurtive single quotes, not one double quote
0
 
cookiejarAuthor Commented:
The data is being passed from the database to Crystal reports sql .  I have no control over changing the single quote to a double quote.  I have no way of knowing that the value being sent to Crystal has a single quote until the report fails.  Unless in my query, I could decode the value if a single quote exists change to a value that is accepted by ORACLE.

Could I somehow decipher if the value contains a single quote change the value to this for example, where title = 'INTRODUCTION TO THE SCHOOL||chr(39)||S DRUG ABUSE POLICY' within the SQL Statement.  If so, could someone show me how to do this?
0
 
slightwv (䄆 Netminder) Commented:
Looks like Crystal has a REPLACE function.  Just replace a single quote to two single quotes with Crystal before you pass it to the database.

Not sure how you do this for you but here is an example using Crystal replace:
http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_23994926.html
0
 
for_yanCommented:
yes, i tried in my oracle i can

select replace(a,'''','?') from table

here I use 4 (four)  single quote in arow -
as a result from my string
tt'1 (in data) i get output tt?1

It meas that you can use
replace(your_column_name, '''','anything')

here you have four signle quotes - and it will reoplace one single quote in your data into
"anything" in this case.
I'm sure you can replace and then insert replaced string
like

insert into table values (replace(you_colum_nmae,'''','"'))

and here i have four single quotes in the first case and signle quote, double quote, signle quote in the second case
and it should insert the same struing with double quote instead of signle quote
 
0
 
slightwv (䄆 Netminder) Commented:
>>yes, i tried in my oracle i can

The problem here is the variable in question is still contained inside the Crystal Reports.  The replace needs to be done BEFORE it is handed off to Oracle.
0
 
mlmccCommented:
You can change the selection formula in the subreport to be
 SELECT * FROM table WHERE title = "{?title_passed_from_main_report}"

mlmcc
0
 
cookiejarAuthor Commented:
Tmlmcc, The only problem is that the sql command in my sub report is bringing back people who do not have the title passed.


select name, dept
      from employee_table
      where dept.name = "{?dept_passed_from_main_report}"
and not exists(select 'x'
                              from courses
                                  where employee_table.emp_id = courses.emp_id
                                   and courses.title = "{?title_passed_from_main_report}")

0
 
mlmccCommented:
You may need to change it to

Where are you adding the SQL for the subreport?

mlmcc
0
 
cookiejarAuthor Commented:
I use the DATABASE EXPERT Add Command and paste my sql there and create the parameters.    I am not familiar with doing it any other way.
0
 
mlmccCommented:
If you are doing it through the ADD COMMAND all you need is

select name, dept
      from employee_table
      where dept.name = "{?dept_passed_from_main_report}"
and not exists(select 'x'
                              from courses
                                  where employee_table.emp_id = courses.emp_id
                                   and courses.title = {?title_passed_from_main_report})

mlmcc

0
 
cookiejarAuthor Commented:
What you have as the sql command statement is what I have as the SQL Command but when a title that contains an apostrophe is sent as a parameter value , the following error is encountered:
Failed to retrieve data from the database
Details: SQL Exception [SQL State] 4200 [Error Message] ORA-00907 missing right parenthesis [Database Vendor Code 907]

Are you saying remove the quotes from the parameter?   I did this, an error still occurs.


0
 
mlmccCommented:
That or enclose the title in " "

mlmcc
0
 
James0628Commented:
You said that the SQL is in a subreport and the value with the ' is being passed from the main report to the parameter in the subreport.  If so, then you can check and, if necessary, change the value in the main report.  Let's say that the main report is reading that value in title_field and you have title_field linked to the subreport parameter.  You'd create a formula in the main report that modified title_field if necessary, and link that formula to the subreport parameter instead.

 for_yan suggested replacing one single quote with two.  You could do that using:

Replace ({title_field}, "'", "''")

 That's one single-quote inside of double-quotes, and then two single-quotes inside of double-quotes.

 If you just want to remove any single-quotes from the field:

Replace ({title_field}, "'", "")

 That's one single-quote inside of double-quotes, and then two double-quotes with nothing in between.

 Or put a character in the second set of double-quotes to replace any single-quotes with that character.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now