Help me. PreparedStatement

[Felissa]

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

Code:

Connection con  = getConnectionFromSomewhere();
 
 PreparedStatement ps = con.perpareStatment( " Insert into tableA (id, name) values (?,?);
 
 ps.set???( 1, myOraclesequence.nextval); <--dunno how to do this line
 ps.setString(1, "TFECW");

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

Code:

setInt(1, myOraclesequence.nextval);

My search yeilded this link

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?

Basically, we're trusting data from an outside system. Well, they are sending us bad data 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.

Felissa

[Marcus]

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:

Code:

select sequence_name.NEXTVAL from dual

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.

Marcus

[Felissa]

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

Code:

 //this is manualy typed in so i'm sure i'm missing a ton of 's and ,s 
 //another reason i'm not a fan of donig it this way
 
 StringBuffer sql = "insert into tableA (id, name) values ( '";
 sql.append("mySequence.nextval");
 sql.append(' " , " ');
 sql.append(someMethod.getDataFromXml("name"));
 sql.append("' )"); 
 
 //set up connection stuff
 newStatement.executeUpdate(sql.toString());

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?

Thanks.

Felissa