0

I have the following code and it seems that it's not working.

CallableStatement cs = dbc.prepareCall("" +
                    "DECLARE " +
                    "transno numeric (9,0); " +
                    "jobno numeric (9,0); " +
                    "supcost numeric (9,0); " +
                    "message varchar (20) := 'All good'; " +
                    "BEGIN " +
                    "transno := ?; " +
                    "jobno := ?; " +
                    "supcost := ?; " +
                    "INSERT INTO JOB_TRANSACTION" +
                    "   VALUES (transno, jobno); " +
                    "INSERT INTO TRANSACTION " +
                    "   VALUES (transno, supcost); " +
                    "UPDATE ASSEMBLY_ACC ac" +
                    "   SET cost = cost + supcost " +
                    "   WHERE EXISTS(SELECT * FROM Manufacturing m WHERE m.job_no = jobno and m.assembly_id = ac.assembly_id); " +
                    "? := message; " +
                    "END;");
            cs.setInt(1, Integer.parseInt(trans_no));
            cs.setInt(2, Integer.parseInt(job_no));
            cs.setInt(3, Integer.parseInt(sup_cost));
            cs.registerOutParameter(4, Types.VARCHAR);
            cs.execute();
            System.out.println(cs.getString(4));

Where trans_no, job_no and sup_cost are all strings that already contain a value.

I think it is getting stuck in the UPDATE statement, however I cannot think of another way to express the condition.

What I intend to do is that every time a transaction with its respective supply-cost sup_cost is made, it will simultaneously update the cost of three accounts, one of them being Assembly_Acc (acc_no, assembly_id, cost) (for simplicity sake, we will work with one only), and in order to know which Assembly_Acc to update, we look up a table Manufacturing (job_no, assembly_id, etc..) for the assembly_id with the job_no (jobno in the query) which is a primary key and we already count with from a beginning. Once the assembly_id is found, it would be used to discern between all the Assembly_Acc (which happens to have a unique assembly_id)

I hope it doesn't sound to complicated, but could you help me make it better? I am really in an urgency and I'm not sure how to follow.

Ren
  • 4,594
  • 9
  • 33
  • 61
  • Did you check your SQL statements seperatly to ensure that they are correct and do what you want? – Armunin Nov 26 '13 at 09:47
  • I haven't checked the update statement separately, but the other two work just fine. There is a lot of key referencing going on in the database, so it's also a bit difficult. – Ren Nov 26 '13 at 09:54
  • If you say, all is working fine without the update-statement, I would suggest making sure, that your update-statement is working. Not sure the `cost = cost + supcost` is working. Could be something like `cost = (select cost from XY) + supcost` – Armunin Nov 26 '13 at 10:01

1 Answers1

1

Your PL/SQL code should be put into a stored procedure, then the java CallableStatement would call that procedure passing in transno, jobno, and supcost as parameters.

See this question: PreparedStatements or callableStatements

Community
  • 1
  • 1
roartechs
  • 1,315
  • 1
  • 12
  • 15