This discussion is archived
5 Replies Latest reply: Dec 3, 2012 1:42 PM by EJP RSS

JDBC call for mutiple Inserts

scottjhn Newbie
Currently Being Moderated
To simplize my issue, I present it as follows:

In Oracle, created two packages

pkg_parent insert a row to the parent_table
pkg_child insert a row to the child_table



Now in Java side:

Connection conn = null;
CallableStatement call = null;


//first insert a row to the parent table
1.
get a connection called conn

2.
call = conn.prepareCall("begin pkg_test.sp_insert_parent(?, ?); end;");
call.registerOutParameter(1, java.sql.Types.FLOAT);
call.setString(2, 'parent record');
call.execute();

//Get the primary key               
Float primaryKey = call.getFloat(1);
//set to a bean
bean.setKey(primaryKey);

//Next insert a row to the child table
call = conn.prepareCall("begin pkg_test.sp_insert_parent(?, ?); end;");
call.setFloat(1, bean.getKey());
call.setString(2, 'child record');
//When this line is executed, it raised ERROR show below
call.execute();

Error: integrity constraint violated - parent key not found.

What could be wrong? (I know I could use a "commit" in between the two sections. But for my case, I need it be all or nothing. So the commit must be issued after both inserts are successful).


P.S.
I thought the connection was created for the same user and same session.

On the Oracle side, though, the above packages were tested successfully for the same user without any commit statement in between the process.
  • 1. Re: JDBC call for mutiple Inserts
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    Use code tags when you post code.

    Why is the primary key a float?
  • 2. Re: JDBC call for mutiple Inserts
    dsurber Explorer
    Currently Being Moderated
    You didn't mention autocommit. Per the JDBC spec, autocommit is on by default.

    Douglas
  • 3. Re: JDBC call for mutiple Inserts
    Joe Weinstein Expert
    Currently Being Moderated
    Comments in no particular order:

    1 - The SQL would better be in standard JDBC format, eg: call = conn.prepareCall("{ call pkg_test.sp_insert_parent(?, ?) }");

    2 - In the post, you are calling the same procedure twice. I'll assume that's a cut-n-paste error.

    3 - Let's assume your first procedure did insert a row. What's the key value, and what is the column defined as?
    Float is a poor choice, as jshell hints. I have debugged systems that used a float as a counter, and at a high enough
    range, the increments start having to deal with precision loss, skipping an increment, or jumping by 2, 4 etc. Understand
    that when an int and a float each get 16 bits, the float has a much greater range, so pointedly, it can't cover all the int
    values in it's greater range.

    4 - Whether the connection is in autocommit mode (the default as dsurber says) or not, the second insert
    should have the contextual benefit of seeing the data including the results of the first insert, because it is
    the same user/connection. If autocommit is off, you would of course need to do a commit after everything
    went is as you desire.

    My initial bet is the float issue. Make it and the columns in the two tables an integer...
    HTH,
    Joe
  • 4. Re: JDBC call for mutiple Inserts
    rp0428 Guru
    Currently Being Moderated
    >
    What could be wrong? (I know I could use a "commit" in between the two sections. But for my case, I need it be all or nothing. So the commit must be issued after both inserts are successful).
    >
    The first thing that is wrong is that this is basically a duplicate of the thread you started a day ago.
    JDBC mutiple Inserts from java side (want to keep the session same)

    And you've posted a version of it in the database general forum.
    maintain the same session identifier for multiple sessions

    You've been ask before to not post duplicate threads and that when you post related threads you need to cross-link ALL of them so that people can have access to the answers that others have given in the other threads.

    Edit all three of your posts and provide the links to the other threads.

    As explained in your other threads this error
    >
    Error: integrity constraint violated - parent key not found.
    >
    is as clear as it can be. The parent record for the child you are trying to insert either doesn't exist at all (using the value of the key you are looking for) or it exists but was inserted and not commited by another session.

    If you inserted the parent and committed it then you should be able to use sql*plus and a new session and find it. If you can't then it wasn't committed.

    Print out the value of the key that you are trying to find and, in sql*plus, print out the value of the key for the parent record and see if they match.
  • 5. Re: JDBC call for mutiple Inserts
    EJP Guru
    Currently Being Moderated
    Duplicate, locking.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points