aspose file tools*
The moose likes JDBC and the fly likes SQLException: bind variable does not exist Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQLException: bind variable does not exist" Watch "SQLException: bind variable does not exist" New topic
Author

SQLException: bind variable does not exist

karthik swamy
Ranch Hand

Joined: Mar 14, 2011
Posts: 42
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

Joined: Oct 23, 2005
Posts: 3710
    
    5

Please do not create 3 threads with identical messages.


My Blog: Down Home Country Coding with Scott Selikoff
karthik swamy
Ranch Hand

Joined: Mar 14, 2011
Posts: 42
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

Joined: Mar 18, 2011
Posts: 312

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


S.D. MADHAN
Not many get the right opportunity !
karthik swamy
Ranch Hand

Joined: Mar 14, 2011
Posts: 42
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

Joined: Mar 18, 2011
Posts: 312

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

Joined: Mar 14, 2011
Posts: 42
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

Joined: Feb 22, 2011
Posts: 75
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

Joined: Mar 14, 2011
Posts: 42
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

Joined: Mar 18, 2011
Posts: 312

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

Joined: Mar 14, 2011
Posts: 42
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

Joined: Mar 18, 2011
Posts: 312

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

Joined: Mar 14, 2011
Posts: 42
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

Joined: Mar 18, 2011
Posts: 312

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

Joined: Mar 14, 2011
Posts: 42
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

Joined: Mar 18, 2011
Posts: 312

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

Joined: Mar 14, 2011
Posts: 42
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

Joined: Mar 14, 2011
Posts: 42
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQLException: bind variable does not exist