• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

java.sql.BatchUpdateException

Is there any way to print PreparedStatement with binding data ?  This will help me to debug a runtime exception I'm facing.

Presently, I'm using datasource and JNDI name configured in JBOSS server to establish database connection.  I can not change the database driver configured in the server.

In my code , I do the following ..

ps = con.prepareStatement(<update-sql query>)

loop
 param1=//get data from list
 param2 =///get data from list
 ps.setString(1,param1)
 ps.setString(2,param2)
System.out.println(ps.toString()) ---> This prints org.jboss.resource.adapter.jdbc.jdk6.WrappedPreparedStatementJDK6@1f1640f
 ps.addBatch();
end loop

ps.executeBatch();

I'm getting an Exception :

java.sql.BatchUpdateException: Cannot add or update a child row: a foreign key constraint fails .
The exception  is quite understandable . And so,  I'd like to reproduce the query with the binding data to troubleshoot further to find out query with values failed to update.



0
cofactor
Asked:
cofactor
  • 5
  • 4
  • 2
  • +1
2 Solutions
 
objectsCommented:
don't believe so, the binding is typically done by the driver.
instead look at enabling sql logging on the database server
0
 
CEHJCommented:
>>Is there any way to print PreparedStatement with binding data ?  

No. It would be easy enough to log debug the statement that's about to be executed though
0
 
cofactorAuthor Commented:
>>>instead look at enabling sql logging on the database server

How do I do that in MySQL 5  server ?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
CEHJCommented:
e.g.
System.out.printf("Statement is 'insert into foo (x,y) values (%s, %s)'\n", param1, param2);

Open in new window

0
 
cofactorAuthor Commented:
>>>It would be easy enough to log debug the statement that's about to be executed though

its a batch update.   Could you please tell , how it would be helpful to debug ?

 I can put some print for param1, param2 ...but  again all those will be added to the ps.addBatch() ....so its very difficult which query failed at the execution time of ps.executeBatch()
0
 
cofactorAuthor Commented:
>>>System.out.printf("Statement is 'insert into foo (x,y) values (%s, %s)'\n", param1, param2);

Yes. I understand that . but these are not executed right away ..correct ?. These are added to the batch.  

so, exact query which failed would be very much helpful .  
0
 
CEHJCommented:
>>.so its very difficult which query failed ...

You could use the return values from executeBatch, but it's probably going to be easier to log at the db. Just make sure you don't leave logging turned up too hight when you've done
0
 
for_yanCommented:
Well,not very oiriginal suggestion, but maybe you want to  execute them one by one for the sake of debugging and then return to batch after debugging.

More stuff about inability to printout prepared statement:
http://www.velocityreviews.com/forums/t126331-print-out-preparedstatement.html
0
 
cofactorAuthor Commented:
>>>but maybe you want to  execute them one by one for the sake of debugging
gotcha , the mistake was  in the PreparedStatement  placeholder  .

I had
ps.setString(7,pvo.get(k).getProgramId());

This should be
ps.setString(8,pvo.get(k).getProgramId());

This works now . Errors vanished.



But I'have landed in  another trouble. Here is my current approach
loop
ps.setString(1,param1)
ps.setString(2,param2)      
.......................
...........................
//ps.addBatch();

boolean success=ps.execute();  

LOGGER.info("#### success ="+ success);  // this prints false , although I see changes in db!
end loop

//ps.executeBatch();


I'm doing individual updates  and inserts.  I dont rely on the return of boolean success.   In the server console it returns false , But I see the updates/ inserts of data has taken place in database .  This  execute() method does not return correctly . Is it a bug ?

I'm using ps.execute()  for insert and update.  Why this does not return correctly ? Or I'm missing something ? whats the correct approach ?
0
 
CEHJCommented:
It should be ps.executeUpdate
0
 
for_yanCommented:
ps.execute returns true and false not in the way you expect - it is not success or failure - more complicated:  check
http://download.oracle.com/javase/1.4.2/docs/api/java/sql/PreparedStatement.html

 executeUpdate which returns integer number of affected rows
0
 
cofactorAuthor Commented:
I dont have access to mysql query logs . I had to do it through java end.  I liked the tricks posted by for_yan.

That was helpful .That solved my problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now