Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQLException: bind variable does not exist

 
karthik swamy
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Scott Selikoff
author
Saloon Keeper
Posts: 4020
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please do not create 3 threads with identical messages.
 
karthik swamy
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Scott Selikoff wrote:Please do not create 3 threads with identical messages.




not gettting properly
Just reply in quit detail
 
Madhan Sundararajan Devaki
Ranch Hand
Posts: 312
Java MS IE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I believe, ? symbol is wildcard symbol in SELECT statement, hence, you may need to somehow escape it within the query.
 
karthik swamy
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Madhan Sundararajan Devaki
Ranch Hand
Posts: 312
Java MS IE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
karthik swamy
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Sudheer Bhat
Ranch Hand
Posts: 75
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ?
 
karthik swamy
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Madhan Sundararajan Devaki
Ranch Hand
Posts: 312
Java MS IE
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 + "%'";
 
karthik swamy
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Madhan Sundararajan Devaki
Ranch Hand
Posts: 312
Java MS IE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
karthik swamy
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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);
 
Madhan Sundararajan Devaki
Ranch Hand
Posts: 312
Java MS IE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
karthik swamy
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Madhan Sundararajan Devaki
Ranch Hand
Posts: 312
Java MS IE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
karthik swamy
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
karthik swamy
Ranch Hand
Posts: 45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic