Sql query with bind variables execution in Jdbc

Question

I have a sql query like this.

 select "DEPT"."DEPTNO" as "DEPTNO1",
"DEPT"."DNAME" as "DNAME1",
"DEPT"."LOC" as "LOC1",
"EMP"."COMM" as "COMM1",
"EMP"."EMPNO" as "EMPNO1",
"EMP"."ENAME" as "ENAME1",
"EMP"."HIREDATE" as "HIREDATE1",
"EMP"."JOB" as "JOB1",
"EMP"."MGR" as "MGR1",
"EMP"."SAL" as "SAL1"
from "EMP" , "DEPT" where "DEPT"."DEPTNO" in (:DeptNo)

//This is the Jdbc code

Class.forName(DB_DRIVER);
dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
Statement statment = dbConnection.createStatement();
result = statment.execute(query);//query is above sql query

When i run above query in Oracle sql developer works perfectly.But when i run it with above jdbc code it is throwing Not all variables bound exception. How to run above query in JDBC

post up the exact error that u get plz. post up the stack trace. post up your all code u have plz. — Kick Buttowski, Dec 16 '13 at 06:32
You have a named parameter in your query (:DeptNo), but you are not assigning it a value. — OldProgrammer, Dec 16 '13 at 06:32
ORA-01008: not all variables bound. This error i'm getting when i run above sql query in JDBC — Obulesu Bukkana, Dec 16 '13 at 06:34
Above query running in Oracle Sql developer perfectly ,not in Jdbc. — Obulesu Bukkana, Dec 16 '13 at 06:36
but at run time i'll get more number of queries and even i dont know about how many bind variables will be there. — Obulesu Bukkana, Dec 16 '13 at 06:42
above query is having :DeptNo as bind variable, as a parameter — Obulesu Bukkana, Dec 16 '13 at 06:49
`:DeptNo` is not a legal variable for a JDBC prepared statement. Please read the JavaDocs (or the JDBC tutorial) — , Dec 16 '13 at 06:59
it is the query i'm getting at runtime and need to validate that query, here i dont know how many bind variables will come in that query.I'm taking that query and need to check it is running successfully or with errors. — Obulesu Bukkana, Dec 16 '13 at 07:04
@obulesub do you have a gmail?I need more information to answer your question — SpringLearner, Dec 16 '13 at 07:12

score 6 · Answer 1 · edited Dec 16 '13 at 07:35

6

replace :deptno in your query with a ?.

and instead of instantiating statement use the following:

PreparedStatement stmt=con.prepareStatement(query);

stmt.setInt(1,deptno); //1 is for the first question mark

where deptno holds the value for which you want to execute the query.

Through PrepredStatement interface we can use parametrized query which is compiled only once and has performance advantage in comparison to the Statement interface.

edited Dec 16 '13 at 07:35

Himanshu

31,810
31
111
133

answered Dec 16 '13 at 06:50

Vijay

213
1
9

runtime even i dont know how many bind variables that a query having. – Obulesu Bukkana Dec 16 '13 at 06:52
The query that you have mentioned above has only one bind variable and that is :deptno Are you not sure of the query? – Vijay Dec 16 '13 at 06:57
mentioned one sample query, in runtime i'll get more no. of queries which may contain several bind variable – Obulesu Bukkana Dec 16 '13 at 09:12
May I know What is the source of the query? or how the query is being generated and when does it change? – Vijay Dec 16 '13 at 10:24
Query is generated according to the report and we dont know how many bind variables it is having. According to the reports, query will change. – Obulesu Bukkana Dec 16 '13 at 11:32
Please refer the following document (http://jasperreports.sourceforge.net/sample.reference/query/) that talks about the generation of dynamic query in a Jasper Report: Refer to the paragraph that begins with the following: $X{} There are also cases when just using $P{} in the report query is not enough, because parts of the query need to be dynamically built, depending on one or more report parameter values, in order to construct a valid query..... If the reporting Tool is Something else Please Let me know! – Vijay Dec 16 '13 at 12:54

score 4 · Answer 2 · answered Dec 16 '13 at 14:07

4

You created a Query with bind variable and you never set it.

Use OraclePreparedStatement and its method setStringAtName()

statement.setStringAtName("DeptNo","<<your Value>>");

If not OraclePreparedStatement, you can just put it as ?1 in your Query string and use,

statement.setString(1,"<<your Value>>");

If in case, you don't know how many bind variables you get, you have capture the bind variables in a map and prepare a list and set it accordingly!

Else your requirement is unachievable!

answered Dec 16 '13 at 14:07

Maheswaran Ravisankar

17,652
6
47
69

OraclePreparedStatement prestmt = (OraclePreparedStatement)dbConnection.prepareStatement(query); prestmt.setStringAtName("DeptNo","DeptNo"); result = prestmt.execute(query);//throwing Attempt to set a parameter name that does not occur in the SQL: DeptNo – Obulesu Bukkana Dec 17 '13 at 09:39
Please print your Query string and confirm the name of the bind variable.. It is case sensitive. – Maheswaran Ravisankar Dec 17 '13 at 10:12

score 1 · Accepted Answer · edited Apr 10 '15 at 07:10

Get the query dynamically from the report.

From this query we need to split querystring to get number of bind variables and placing those bind variables in a HashMap.HashMap is like

            {DeptName =1, Job =1, DeptNo =1}

From this hashmap,need to replace the query bind variable with ?.For this we need to do like

   bindkey = entry1.getKey().toString();
    String bindkeyreplace =":".concat(bindkey).trim();
    String bindkeyreplacestring = "?";
    query = query.replace(bindkeyreplace, bindkeyreplacestring);

Then we will get dynamic query coming from the report with ? instead of :bindvariable

        PreparedStatement prestmt = dbConnection.prepareStatement(query);
        for (int i = 0; i < bindParamMap.size(); i++) {
             prestmt.setInt(i + 1, 0);//Setting default value to check the query is running successfully or not
        }
        result = prestmt.execute();

If in case, we don't know how many bind variables we get then this approach is running successfully for me.

score 0 · Answer 4 · edited Dec 16 '13 at 07:35

0

Use this syntax,EMP.DNAME as DNAME1. I mean your dot and as must be inside the double quotes.

edited Dec 16 '13 at 07:35

Himanshu

31,810
31
111
133

answered Dec 16 '13 at 06:38

EJ Dogar

68
10

thiyaga · Answer 5 · 2013-12-16T06:48:59.250

-1

The variable DeptNo must be bound to a value before you execute the statement like below.

DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
Statement statment = dbConnection.createStatement();
//Bind deptno to a value
statment.setParameter("DeptNo",5);
result = statment.execute(query);

You must set values for all the variables in your prepared statement othwerise you cannot execute the statement. If you receive the query to execute itself as an input then you should also get the parameters and its values also as input. Something like below

public <returnType> executeQuery(String queryStr, Map<String,Object> params) {
     //Code to create connecitno and statment from queryStr.
     //Bind deptno to a value
     for(int i=0;i<params.size(),i++) {
       //Get entry set from map  
       statment.setParameter(entryset.getKey(),entryset.getValue());
     }

     result = statment.execute(query);
     //return or work on the result      
}

edited Dec 16 '13 at 06:48

answered Dec 16 '13 at 06:38

thiyaga

251
1
7

but at run time i'll get more number of queries and even i dont know about how many bind variables will be there. – Obulesu Bukkana Dec 16 '13 at 06:41
is this method present setParameter()? – SpringLearner Dec 16 '13 at 06:42
setParameter is not directly present in PreparedStatement. Have a look at http://stackoverflow.com/questions/2309970/named-parameters-in-jdbc and http://www.javaworld.com/article/2077706/core-java/named-parameters-for-preparedstatement.html to mimic that facility. – thiyaga Dec 16 '13 at 06:58
@thiyaga how can you write statment.setParameter() – SpringLearner Dec 16 '13 at 07:00
@JqueryLearner, It is only a wapper method which was written. actual method will be either setInt ot setString or SetObject etc.. We can write a wrapper method named setParameter that checks the class type of the parameter and utilizes the corresponding method on the prepared statement. – thiyaga Dec 16 '13 at 07:07
@thiyaga Its not present in statement but Its present in preparedStatement – SpringLearner Dec 16 '13 at 07:07

Sql query with bind variables execution in Jdbc

5 Answers5

Linked