13 Replies - 2477 Views - Last Post: 21 November 2012 - 03:14 AM Rate Topic: -----

#1 Nigge  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 17-February 12

Problem: NO-DATA-FOUND error (insert into)

Posted 16 November 2012 - 02:56 AM

Hi!
I am trying to execute an INSERT INTO SQL-command to my database from my java application.
This one actually works without problems:
String query = "UPDATE WO_TASK SET CORRECTIVE_ACTION='PERFORMED' WHERE WOT_AUTO_KEY="+jobCard;  
          try{
       PreparedStatement ps = connection.prepareStatement(query);
       ps.executeUpdate();
       insertStatus(jobCard,user,userName);
        }catch (SQLException ex){
       System.out.println("Something goes wrong in updateStatus: "+ex.getMessage());
   }   


But the following one executes an ORA-01403: no data found
I cant understand what might be wrong... The SQL-command works fine when I use it in for example SQL-DEVELOPER...
But not through my JAVA_application...

Here comes the code that generates the error:
 public void insertStatus(String jobCard,String user, String userName){
         String query = "INSERT INTO WO_TASK_STATUS(WOT_AUTO_KEY, WOS_AUTO_KEY, SYSUR_AUTO_KEY) VALUES ("+jobCard+",4,"+user+")";   
         System.out.println("query: "+query);   
         try{
       PreparedStatement ps2 = connection.prepareStatement(query);
       ps2.executeUpdate();
    //   ps2.executeNonQuery();
        }catch (SQLException ex){
       System.out.println("Something goes wrong in insertStatus: "+ex.getMessage());
        
   }  }


Many thanks for all your help!
/Niklas

Is This A Good Question/Topic? 0
  • +

Replies To: Problem: NO-DATA-FOUND error (insert into)

#2 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2780
  • View blog
  • Posts: 11,765
  • Joined: 20-September 08

Re: Problem: NO-DATA-FOUND error (insert into)

Posted 16 November 2012 - 07:11 AM

Your sql is incorrect. Use the parameters of the PreparedStatement - they are there partly to help you out with issues like quoting, which is probably the reason for your error.If the column is a character type, use ps.setString(1, "foo") (for the first parameter) else if it's an integral type use ps.setString(1, 2345)

String query = "INSERT INTO WO_TASK_STATUS(WOT_AUTO_KEY, WOS_AUTO_KEY, SYSUR_AUTO_KEY) VALUES (?,?,?)";   

Was This Post Helpful? 0
  • +
  • -

#3 Nigge  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 17-February 12

Re: Problem: NO-DATA-FOUND error (insert into)

Posted 16 November 2012 - 07:30 AM

Hm, im not sure I understand the way im supposed to use the ps.setString method, can you use it in my example above? that would be very helpful:)

Thanks!
/Niklas
Was This Post Helpful? 0
  • +
  • -

#4 pbl  Icon User is offline

  • There is nothing you can't do with a JTable
  • member icon

Reputation: 8342
  • View blog
  • Posts: 31,880
  • Joined: 06-March 08

Re: Problem: NO-DATA-FOUND error (insert into)

Posted 16 November 2012 - 01:12 PM

Assuming jobcard and user are String:

ps.setString(1, jobcard);
ps.setInt(2, 4);
ps.setString(3, user);
Was This Post Helpful? 0
  • +
  • -

#5 Nigge  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 17-February 12

Re: Problem: NO-DATA-FOUND error (insert into)

Posted 19 November 2012 - 06:23 AM

I have tried using both ps.setInt and ps.setString but still the same result (No data found)

If I however do a println on the query
and paste that into SQL-developer the query works... without any errors...
this is confusing me;)
Was This Post Helpful? 0
  • +
  • -

#6 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2780
  • View blog
  • Posts: 11,765
  • Joined: 20-September 08

Re: Problem: NO-DATA-FOUND error (insert into)

Posted 19 November 2012 - 06:54 AM

Please post your new code with our suggestions incorporated
Was This Post Helpful? 0
  • +
  • -

#7 Nigge  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 17-February 12

Re: Problem: NO-DATA-FOUND error (insert into)

Posted 20 November 2012 - 12:59 AM

Of course, here´s the method that causes my problem:

        public void insertStatus(String jobCard,String user, String userName){
         String query = "INSERT INTO WO_TASK_STATUS(WOT_AUTO_KEY, WOS_AUTO_KEY, SYSUR_AUTO_KEY) VALUES (?,?,?)";  
        // System.out.println("query: "+query);
         int job = Integer.parseInt(jobCard);
         int us = Integer.parseInt(user);
       
         try{
       PreparedStatement ps2 = connection.prepareStatement(query);
       ps2.setInt(1, job);
       ps2.setInt(2,4);
         ps2.setInt(3, us);
         
         
       ps2.execute();
    //   ps2.executeNonQuery();
        }catch (SQLException ex){
       System.out.println("Något blir fel i insertStatus: "+ex.getMessage());
        


I have a suspicion that there might be a trigger that fails. But it feels a bit strange that it fails when I perform the query from my java application but not from SQL- DEVELOPER for example.

I hope we can solve this:)

/Niklas
Was This Post Helpful? 0
  • +
  • -

#8 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2780
  • View blog
  • Posts: 11,765
  • Joined: 20-September 08

Re: Problem: NO-DATA-FOUND error (insert into)

Posted 20 November 2012 - 03:34 AM

Try calling executeUpdate instead. Make sure you close the Statement
Was This Post Helpful? 0
  • +
  • -

#9 Nigge  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 17-February 12

Re: Problem: NO-DATA-FOUND error (insert into)

Posted 20 November 2012 - 05:16 AM

I have tried ExecuteUpdate as well with the same result...
(Have tried, executeQuery, execute, and executeupdate...)

What do you mean by "close the statement"?:)
Was This Post Helpful? 0
  • +
  • -

#10 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2780
  • View blog
  • Posts: 11,765
  • Joined: 20-September 08

Re: Problem: NO-DATA-FOUND error (insert into)

Posted 20 November 2012 - 06:05 AM

I mean
ps2.close();

Can you also post the result of ex.printStackTrace();

This post has been edited by g00se: 20 November 2012 - 06:06 AM

Was This Post Helpful? 0
  • +
  • -

#11 Nigge  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 17-February 12

Re: Problem: NO-DATA-FOUND error (insert into)

Posted 20 November 2012 - 08:25 AM

Certainly here comes the printout:

Quote

ORA-01403: no data found
ORA-01403: no data found
ORA-06512: at "TRAIN.QC_SC_PKG", line 1686
ORA-06512: at "TRAIN.TIUD_WO_TASK", line 83
ORA-04088: error during execution of trigger 'TRAIN.TIUD_WO_TASK'
ORA-06512: at "TRAIN.RL_WO_PKG", line 1389
ORA-06512: at "TRAIN.RL_WO_PKG", line 1402
ORA-06512: at line 1
ORA-06512: at "TRAIN.QC_TRIG_PKG", line 152
ORA-06512: at "TRAIN.QC_TRIG_PKG", line 100
ORA-06512: at "TRAIN.TAS_WO_TASK_STATUS", line 7
ORA-04088: error during execution of trigger 'TRAIN.TAS_WO_TASK_STATUS'


This hints me that there is the trigger TIUD_WO_TASK that brings me trouble...
(well it says so in the printout) But the question is why it troubles me when i access it through my java application and not through SQL-DEVELOPER?
Was This Post Helpful? 0
  • +
  • -

#12 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2780
  • View blog
  • Posts: 11,765
  • Joined: 20-September 08

Re: Problem: NO-DATA-FOUND error (insert into)

Posted 20 November 2012 - 09:15 AM

Yes, that's an error internal to Oracle and (possibly) nothing to do with the Java. I would run it by the Oracle people. That IS an actual table is it, in your query?
Was This Post Helpful? 0
  • +
  • -

#13 Nigge  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 17-February 12

Re: Problem: NO-DATA-FOUND error (insert into)

Posted 21 November 2012 - 01:04 AM

Yes it is. As I said, it works fine when i enter the EXACT same query in any SQL-editor... then I get no errors... the error only occurs when I try to perform the query from the java application.
Was This Post Helpful? 0
  • +
  • -

#14 g00se  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2780
  • View blog
  • Posts: 11,765
  • Joined: 20-September 08

Re: Problem: NO-DATA-FOUND error (insert into)

Posted 21 November 2012 - 03:14 AM

I don't think your error has anything to do with your Java code. Check the Oracle area
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1