This week's book giveaway is in the Cloud forum.
We're giving away four copies of The Business Blockchain and have William Mougayar on-line!
See this thread for details.
Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How can we know exact position of exception

 
Hareesh Ram Chanchali
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am using the executeUpdate for insertion of a row with the syntax

" Insert into Tab_name (column_list) values (value_list)"

If any integrity constraint is violated then it is giving an exception.

But how can we know the exact column at which the integrity constraint has been violated

How can we diffrentiate between primary key and unique key violations since it is telling Unique key constraint violation


Plz tell me if u have any ideas

Same problem with other constraints like Check constraints,not null,referentila constraints

My idea is to provide clear and accurate information for the user about the right place of exception and hence we can even suggest him the necessary solution also

Thanks in advance
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
printStackTrace is the answer !! and in stackTrace you will get constraint name as well.





hope this helps.
 
Hareesh Ram Chanchali
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
shailesh,

should i reverse engineer that constraint name
by the by is there any specific method to get the constraint name in the exception
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Hareesh Ram Chanchali:
should i reverse engineer that constraint name
by the by is there any specific method to get the constraint name in the exception
Sadly, the constraint name is not extracted and exposed by the base SQLException class. I suppose it's conceivable that each driver could provide a method to get at it. Instead, what I've done is create a utility class that will do it for the databases I use (currently, just Oracle ).

The first step to happiness is to name all of your constraints yourself instead of letting the database generate them. This way you can define constants on which you can depend.
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hareesh,

I am in cent percent agreement with David.

as David said name your constraint yourself.
All oracle exception messages contains an error code for exception.

At particular operation you can take help of these error code

like

if error code is 1 that tell you that unique constraint violated, similarly
you can do it for other error codes and constraint.

I use a static hashmap for this purpose with help of utility class as David


catch(SQLException se)
{
if((se.getErrorCode() == 1) && ((se.getMessage().toUpperCase().indexOf("UNIQUE")) > -1) && ((se.getMessage().indexOf("constraintName")) > -1))
{

// you can send your own custom message
}
else
{
se.printStackTrace();
throw se;
}
}

thanks..
 
Hareesh Ram Chanchali
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
David and others,
Thank for reply

But I am not available with the Constraint names since my application is generic tool.

I am getting all the constraint info using Reverse Engineering the data model(By using System views like DBA_constraints....)

what I need exactly is to identify at which column the bug is raised ...
So that I can provide our own clear customized message for the user of the tool.

So I am asking about any provision is there to get the column name from the stack trace (It is giving for whole row and I cannot give proper message about the particular column)
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You use the exception message to get the name of the constraint. Use the constraint name to look up its definition (table and list of columns) from the system catalog. This will tell you which columns are causing the problem.

Clearly, for a multi-column constraint there is no "single column" responsible for the violation since the columns are considered together as a whole. That's simply the best that you can do as far as I can see.
 
Hareesh Ram Chanchali
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
david,

is there any specific mthod like e.--- to get constraint name from SQLException

thanks for ur reply
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sadly, no. Here's the utility class I used. I had to remove all the logging stuff, so you may need to tweak it if it doesn't compile.
 
Srinivasa Raghavan
Ranch Hand
Posts: 1228
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hareesh,
If you have tools or editors like TOAD.It'll help you a lott. In TOAD you can see all the constrainsts listed in a tab and you can get the columns & tables involved in the constraints.

So get constraint name either through stack trace or by any other means, and do a look up in Toad or any other editor ment for SQL / PLSQL.
HTH.
 
Hareesh Ram Chanchali
Ranch Hand
Posts: 110
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi srinivas,

I should embed the code in my program to handle those constraints...
whatever you are telling is ok but what about when i should use them programmatically in java...in exception handling

what david has given me is somewhat helpful

Thank you david
 
Srinivasa Raghavan
Ranch Hand
Posts: 1228
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hareesh,

Well !! I thought in a different way. YEP even i do go with david, that sounds good.
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Hareesh Ram Chanchali:
I am getting all the constraint info using Reverse Engineering the data model(By using System views like DBA_constraints....)
. . .
what david has given me is somewhat helpful
Thank you david
You're welcome. Let us know if you have any questions putting it all together.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic