Re: bind variables from java code

rainyday333_at_hotmail.com (Jaan Marck) wrote in message news:<cf59f633.0305230954.3595c7c4_at_posting.google.com>...
> Hmm, are you sure your first approach will work? I suspect not. Only
> the second will work. JDBC does not have 'host variables' which you
> can embed directly into the text of SQL statements. Even if it did,
> you'd still need some form of indicating that a variable is a host
> variable - e.g. the colon used in embedded sql.

I am unable to understand your logic about first approach as non-workable but second as workable... plus your reference to 'host variables'??

First approach will not only work BUT it is also the optimized way of calling a stored procedure because precompiled CallableStatement is returned which can be used many times without further compilation. Following example is compiled once and executed 100 times,

Connection conn = .......;
CallableStatement stmt =

    conn.prepareCall ("begin my_package.proc (?); end;"); for (int i = 1; i <= 100; i++) {

   stmt.setInt(1, i);
   stmt.execute();
}
stmt.close();
conn.close();

Second approach is not only syntactically wrong BUT it must be compiled everytime it is called... because first two variables are not bound.

"{ CALL SC_FILL_PRESENCE(v_udptype, v_ip, ?, ?) }"; This syntax is incorrect. If the idea is to build a string by embedding variables, then this will not work in any modern programming language. You would have to concatenate the variables e.g. "{ CALL SC_FILL_PRESENCE(" + v_udptype + "," + v_ip + ", ?, ?) }";

Regards
/Rauf Sarwar Received on Sun May 25 2003 - 07:34:22 CEST