| Bind variable in select statement [message #172474] |
Tue, 16 May 2006 13:17  |
akho
Messages: 2
Registered: May 2006
|
Junior Member |
|
|
Hi,
I have a prepared statement: Select tablename.nextval from dual. where tablename is passed in to the method so we can use this as a utility. I'm converting this to use a bind variable:
stmt = conn.prepareStatement("select ? from dual");
stmt.setString(1, field); where field is tablename.nextval and this does not work.
Is there anything special that we have to do to bind select clause?
Thanks!
|
|
|
|
| Re: Bind variable in select statement [message #172505 is a reply to message #172474] |
Tue, 16 May 2006 22:15  |
oliversalmon
Messages: 5
Registered: March 2006
|
Junior Member |
|
|
Not sure if that's allowed in JDBC, why not just create the statement String dynamically and use that variable in the call i.e.
String stmt = 'a statement';
//Evaluate conditions....
//Construct String....
stmt = conn.prepareStatement(stmt);
Regards,
Oliver
|
|
|
|
| Re: Bind variable in select statement [message #172512 is a reply to message #172505] |
Wed, 17 May 2006 00:48 |
hobbes
Messages: 173
Registered: January 2006
|
Senior Member |
|
|
It looks like you're trying to get the next value from a database sequence.
You can use bind variables in the WHERE clause, but you cannot bind arguments for names of Oracle schema objects. In this case, simply prepare the string and execute it; do without the bind variable.
Tip: You can use sequence.NEXTVAL directly in the INSERT statement; there's no need to select its value into a variable first. If you're only calling this utility to get a unique id for a subsequent insert, you might avoid it altogether.
|
|
|
|
| Re: Bind variable in select statement [message #172729 is a reply to message #172474] |
Wed, 17 May 2006 15:52 |
akho
Messages: 2
Registered: May 2006
|
Junior Member |
|
|
after more testing, I found out what's happening.
stmt = conn.prepareStatement("select ? from dual");
stmt.setString(1, field); where field is tablename.nextval
the bind and execute does not cause an error. The problem is that this is equivalent to: select 'tablename.nextval' from dual, so the result is 'tablename.nextval' and not the next sequence value that I was expecting. Thus, I was getting an error in using getLong(1) to get the result.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
