SQLException: bind variable does not exist

Hello ,

Since i hope my code is right but then also im getting this SQLException: bind variable does not exist
here is my code;

String query="SELECT PROCESS_ID,NAME FROM SYS_FILEINFO WHERE to_date(CREATION_DATE) BETWEEN TO_DATE(?,'DD-MM-YYYY') AND TO_DATE(?,'DD-MM-YYYY') AND PROCESS_ID LIKE '?%'";

pstmt=con.prepareStatement(query);
pstmt.setString(1,FormDate);
pstmt.setString(2,ToDate);
pstmt.setString(3,procId);
records.runQuery(con,pstmt);

So please suggest me the solutions
Thanks,
Karthik G

Please do not create 3 threads with identical messages.

Scott Selikoff wrote:Please do not create 3 threads with identical messages.

not gettting properly
Just reply in quit detail

I believe, ? symbol is wildcard symbol in SELECT statement, hence, you may need to somehow escape it within the query.

Madhan Sundararajan Devaki wrote:I believe, ? symbol is wildcard symbol in SELECT statement, hence, you may need to somehow escape it within the query.

Can't we use ? symbol in like keyword because it is giving me this error in that place only.

When I said "escape" I meant, you need to put some character such as "\" or something that will allow the wild-card to be treated as java place-holder. I hope, you understand what is being conveyed.

Madhan Sundararajan Devaki wrote:When I said "escape" I meant, you need to put some character such as "\" or something that will allow the wild-card to be treated as java place-holder. I hope, you understand what is being conveyed.

yes sir tried it by using '\' one but it is used only to match the pattern after _
i need to match MIP.8767.Ade446
like this is my data l used like(MIP%) now suggest me the replacement keyword which can found like this data

karthik swamy wrote:

Madhan Sundararajan Devaki wrote:I believe, ? symbol is wildcard symbol in SELECT statement, hence, you may need to somehow escape it within the query.

Can't we use ? symbol in like keyword because it is giving me this error in that place only.

Why don't you append % to procId string and have your SQL string as LIKE ?

Sudheer Bhat wrote:

karthik swamy wrote:

Madhan Sundararajan Devaki wrote:I believe, ? symbol is wildcard symbol in SELECT statement, hence, you may need to somehow escape it within the query.

Can't we use ? symbol in like keyword because it is giving me this error in that place only.

Why don't you append % to procId string and have your SQL string as LIKE ?

HOw to append % with String How is it possible
i used like this procID"%" and also procId+"%"
but not working

You may re-write your query as follows,

String query="SELECT PROCESS_ID,NAME FROM SYS_FILEINFO WHERE to_date(CREATION_DATE) BETWEEN TO_DATE(?,'DD-MM-YYYY') AND TO_DATE(?,'DD-MM-YYYY') AND PROCESS_ID LIKE 'MIP.%'";

(OR like the following)

String PREFIX = "MIP."; // You can use some function to find out the prefix and substitute here.
String query="SELECT PROCESS_ID,NAME FROM SYS_FILEINFO WHERE to_date(CREATION_DATE) BETWEEN TO_DATE(?,'DD-MM-YYYY') AND TO_DATE(?,'DD-MM-YYYY') AND PROCESS_ID LIKE '" + PREFIX + "%'";

Madhan Sundararajan Devaki wrote:You may re-write your query as follows,

String query="SELECT PROCESS_ID,NAME FROM SYS_FILEINFO WHERE to_date(CREATION_DATE) BETWEEN TO_DATE(?,'DD-MM-YYYY') AND TO_DATE(?,'DD-MM-YYYY') AND PROCESS_ID LIKE 'MIP.%'";

(OR like the following)

String PREFIX = "MIP."; // You can use some function to find out the prefix and substitute here.
String query="SELECT PROCESS_ID,NAME FROM SYS_FILEINFO WHERE to_date(CREATION_DATE) BETWEEN TO_DATE(?,'DD-MM-YYYY') AND TO_DATE(?,'DD-MM-YYYY') AND PROCESS_ID LIKE '" + PREFIX + "%'";

yeah you are right but this logic i have already applied
i need to use it with prepareStatement then how can i replace it.

The re-written queries can be used in a prepared statement.

In the first case, you can use the query as it is.

In the second case, just ensure that you populate the value of PREFIX, before calling the con.prepareStatement API.

Madhan Sundararajan Devaki wrote:The re-written queries can be used in a prepared statement.

In the first case, you can use the query as it is.

In the second case, just ensure that you populate the value of PREFIX, before calling the con.prepareStatement API.

i replaced also by taking the whole string in one and then i replaced and i used System.out.println method then it is printing the value when i tail it in jboss
but not getting the actual result
so here is my code how i replaced it
so suggest me where im wrong

String strProcID="'"+procId+"%'";
System.out.println("strProcID====>"+strProcID)
String query="SELECT PROCESS_ID,NAME FROM SYS_FILEINFO WHERE to_date(CREATION_DATE) BETWEEN TO_DATE(?,'DD-MM-YYYY') AND TO_DATE(?,'DD-MM-YYYY') AND PROCESS_ID LIKE ?;
System.out.println("query value ===="+query);
pstmt=con.prepareStatement(query);
pstmt.setString(1,FormDate);
pstmt.setString(2,ToDate);
pstmt.setString(3,strProcID);

Please re-write as follows,

String query="SELECT PROCESS_ID,NAME FROM SYS_FILEINFO WHERE to_date(CREATION_DATE) BETWEEN TO_DATE(?,'DD-MM-YYYY') AND TO_DATE(?,'DD-MM-YYYY') AND PROCESS_ID LIKE " + strProcID;

Also, you need not set the third argument in the pstmt object.

Madhan Sundararajan Devaki wrote:Please re-write as follows,

String query="SELECT PROCESS_ID,NAME FROM SYS_FILEINFO WHERE to_date(CREATION_DATE) BETWEEN TO_DATE(?,'DD-MM-YYYY') AND TO_DATE(?,'DD-MM-YYYY') AND PROCESS_ID LIKE " + strProcID;

Also, you need not set the third argument in the pstmt object.

but Sir i need to use it with prepareStatement only means to replace the above statement with ? in sql statement
or is it not possible to use in such case

The re-written query does not affect the creation/execution of PreparedStatement! What do you think will happen if you use the re-written query?

Madhan Sundararajan Devaki wrote:The re-written query does not affect the creation/execution of PreparedStatement! What do you think will happen if you use the re-written query?

actually i dont want to show the value to println in backend server
if i use the rewritten query it will print
so any hw thanks for helping me i got the solution.

Thank you,
Karthik G.

Sudheer Bhat wrote:

karthik swamy wrote:

Madhan Sundararajan Devaki wrote:I believe, ? symbol is wildcard symbol in SELECT statement, hence, you may need to somehow escape it within the query.

Can't we use ? symbol in like keyword because it is giving me this error in that place only.

Why don't you append % to procId string and have your SQL string as LIKE ?

Thanks your logic works fine.