Results 1 to 3 of 3
  1. #1
    Felissa is offline Member
    Join Date
    Jun 2007
    Posts
    95
    Rep Power
    0

    Default Help me. PreparedStatement

    Basically, I'm trying to figure out how to use a PreparedStatement with an oracle sequence.
    Java 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

    Java 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:p

  2. #2
    Marcus is offline Member
    Join Date
    Jun 2007
    Posts
    92
    Rep Power
    0

    Default RE: Help me. PreparedStatement

    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:

    Java 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:cool:

  3. #3
    Felissa is offline Member
    Join Date
    Jun 2007
    Posts
    95
    Rep Power
    0

    Default RE: Help me. PreparedStatement

    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
    Java 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:p

Similar Threads

  1. PreparedStatement with java.sql.Date
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-10-2008, 11:39 AM
  2. Using PreparedStatement for insertion
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-06-2008, 09:30 AM
  3. Using PreparedStatement
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 12-22-2007, 11:24 AM
  4. PreparedStatement
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 12-05-2007, 03:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •