How to handle "select a,b where c=' 1 & 2' in a 'hard coded' odbc query

[jhaxo]

I am maintaining an asp page that builds a select query as a plain old string and executes it using oracle odbc.

There is basically too much code to change over to stored proccedures or other nice means.

How can I escape a & ? Using sql+ there is an escape on command, is there a similar concept for odbc? Is this an odbc question rather than oracle?

[debasisdas]

can you kindly post the query for my reference.

[jhaxo]

can you kindly post the query for my reference.

sorry i did not get an email notification.
That is really the query.

"select a,b from mytable where c=' 1 & 2' ".
c is a varchar, a and b are columns . I don't have the freedom to do a sp or p, or a parameterized command. And worse still, it has to work with Oracle and Access.

I did discover there is an escape concept in sql+ that you can turn on and then \& is interpreted as a character and not a place holder for a parameter.

[amitpatel66]

sorry i did not get an email notification.
That is really the query.

"select a,b from mytable where c=' 1 & 2' ".
c is a varchar, a and b are columns . I don't have the freedom to do a sp or p, or a parameterized command. And worse still, it has to work with Oracle and Access.

I did discover there is an escape concept in sql+ that you can turn on and then \& is interpreted as a character and not a place holder for a parameter.

If you change your query to something like this:

[code=oracle]

select a,b from mytable where c IN ('1','2')

[/code]

[jhaxo]

If you change your query to something like this:

[code=oracle]

select a,b from mytable where c IN ('1','2')

[/code]

no, sorry c is a varchar that has the literal value '1 & 2'.
like 'burger & fries'.

[amitpatel66]

no, sorry c is a varchar that has the literal value '1 & 2'.
like 'burger & fries'.

Try this way:

[code=oracle]

SQL> SET SCAN OFF
SQL> declare
2 c varchar2(20) := '1 & 2 & 3';
3 v_output VARCHAR2(2);
4 s_sql VARCHAR2(200);
5 BEGIN
6 c:= CHR(39)||REPLAC E(c,' & ',',')||CHR(39) ;
7 s_sql:= 'SELECT ''x'' FROM DUAL WHERE ''x'' NOT IN ('||c||')';
8 EXECUTE IMMEDIATE s_sql INTO v_output;
9 dbms_output.put _line('Format of IN parameter:'||c) ;
10 dbms_output.put _line('Output using Formatted IN:'||v_output) ;
11 end;
12 /
Format of IN parameter:'1,2, 3'
Output using Formatted IN:x

PL/SQL procedure successfully completed.

SQL>

[/code]


Remember in the above example the 2 single colons are used in the select statement and not a double quote

[amitpatel66]

Something like this also will work:

[code=oracle]

SQL> SET SCAN OFF
SQL> select * from dual where 'x' not in (REPLACE('1 & 2',' & ',','))
2 /

D
-
X

SQL>

[/code]