Re: bind variables from java code
Date: 24 May 2003 22:34:22 -0700
Message-ID: <92eeeff0.0305242134.51d95bca_at_posting.google.com>
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