Prepared Statements and sequences.

This message was edited 2 times. Last update was at 02/03/2006 13:28:55

Basically, I'm trying to figure out how to use a PreparedStatement with an oracle sequence.

so....

i tried setString(1, "myOraclesequence.nextval") but i get an sql exception
because id is a number type in oracle and i'm sending it as a string. Java obviously won't compile something like

setInt(1, myOraclesequence.nextval);

My search yeilded this

While the solution there makes sense, I looking for something that doesn't require a db change and all the forms, essays, and tps reports that go along with a db change.

Is there something I can wrap my sequence in?

Here's the reason for the change so maybe an alternate solution that i'm not seeing would be appropriate.

Basically, we're trusting data from an outside system (they send us an xml file with data). Well, they are sending us bad data ( ' in some of the fields) this causes an sql exception because the ' aren't escaped.
I know it'd take less than 2 seconds to add a method to escape the SQL characters, but i'm trying to avoid it. We have enough gum and duck tape holding this application together.

Thanks

*edit* probably not the best topic to have posted this one under, more regular programing than jdbc i suppse.

This message was edited 1 time. Last update was at 02/03/2006 13:35:11

What is your DDL like? The reason I ask is that I usually don't actually insert into sequence columns - I let the database get the next sequence.

However, there are times when you have a foreign key constraint that requires you to get the sequence for a child table. In that case you'll need to first select the next value for the sequence:

Once you have that you can use it in the parent insert and in any child inserts. The downside is that you have to do a select before the insert but you could put that off into another method to make it a bit easier.

This message was edited 1 time. Last update was at 02/03/2006 14:08:34

Hmm, we've def jumped out the realm of my DB knowledge!

The application is pretty basic. All it does is insert, update, and select on the just a few tables. So i'm not sure what you mean by DDL

What we were doing before hand was something like

That didn't cause problems, but i'd have to manually escape the chars.

Unless i'm not understanding a parent child table relationship, couldn't a possibilty arise that when i pulled back the nextval to insert, i could get an insert from a different user which would cause an exception when i tried to insert?

ugh..that's clumsy.. basically i'm asking if there is a race condition.

Normal:
get nextval query
use nextval in insert

Couldn't this happen?:
get nextval query (user A)
get nextval query (user B)
User A inserts using nextval //works
User B inserts using nextval //throws the unique key exception

The db doesn't have a default value (of mysequence.nextval) for the Key, so it's expecting something. That might be an okay solution, i'll use some fancy buzzwords and terms like "performance impact" when i talk to my boss, so we'll see.

Thanks

The seq.NEXTVAL will return you a unique sequence that you can use. Even if multiple processes are getting the same sequence they are guaranteed to each get a unique number - that is kinda the definition of a sequence.

But now that I understand the problem a little better if I can make a suggestion - use the prepared statement for everything except the sequence:

and that should make things pretty easy. Note that I haven't tried this but I think it should work.

stdunbar wrote:The seq.NEXTVAL will return you a unique sequence that you can use. Even if multiple processes are getting the same sequence they are guaranteed to each get a unique number - that is kinda the definition of a sequence.

duh, what was i thinking

- use the prepared statement for everything except the sequence:

Hmm, i wouldn't have thought of that one in a million years.

I gave it a shot and i *think* it's working. i'm still getting an error, but it's a longish query so i don't think it's related. I'll continue debugging on Monday update with a yes or no (it works) for posterity.

Looks like everything is working. The other problem i encountered was a

java.sql.SQLException: ORA-01858: a non-numeric character was found where a numeric was expected

exception when i tried to set "sysdate" as an oracle date field. While I would have expected a different error message, I worked around this by using the same method suggest by stdunbar for the seq.nextval problem.