Escape characters for variables in sql

[Meghna Bhardwaj]

HI All,

I am trying to execute a query on Oracle db. I am unable to substitute my variables in the sql statement. The sql is:

select prod_type_nm from prod_type_dim
where (prod_type_nm like ':INTNL_DEST%' OR prod_type_nm Like ':INTNL_DOMESTIC_DEST%' );

:INTNL_DEST and :INTNL_DOMESTIC_DEST are the variables which should be replaced. However this sql does not work, when I execute it using hardcoded values its fine.

My Dao extends NamedParameterJdbcDaoSupport of the Spring Framework.

Any help will be appreciated. I can provide more details if needed.

Many thanks!

[Paul Clapham]

I wouldn't try to do that. I prefer to use features already built into Java when they exist, rather than features that don't. In this case the feature in question is a PreparedStatement. Define your query like this:

select prod_type_nm from prod_type_dim where (prod_type_nm like ? OR prod_type_nm Like ?);

and use the setString() method to set the parameters to something like "42%".