Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

if function in mysql

 
Supun Lakshan Dissanayake
Ranch Hand
Posts: 135
Android Java PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,
i need to add a specific record to a table if the specific record is not available in the table.
here is the query i tried

but this one is NOT working.

if a is in the table then following query should not execute otherwise it should.

Thanks!
 
Winston Gutkowski
Bartender
Pie
Posts: 10571
64
Eclipse IDE Hibernate Ubuntu
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Supun Lakshan Dissanayake wrote:I need to add a specific record to a table if the specific record is not available in the table.

Well, normally that would be a constraint on the column itself (ie, UNIQUE); then nothing would be able to INSERT a duplicate value. I forget all the ins and outs of INSERT, or whether you can apply constraints temporarily or not; but that's definitely the path I'd go.

However, this isn't really a Java question. If I find a suitable forum, I'll move it.

Winston
 
Supun Lakshan Dissanayake
Ranch Hand
Posts: 135
Android Java PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Winston Gutkowski wrote:
Well, normally that would be a constraint on the column itself (ie, UNIQUE); then nothing would be able to INSERT a duplicate value. I forget all the ins and outs of INSERT, or whether you can apply constraints temporarily or not; but that's definitely the path I'd go.

Fascinating!. I didn't even think about it.
Well, then you are suggesting me to let mysql throw java.sql.SQLException and handle it.

Thank you very much Winston!!!
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's not the best solution. By catching and ignoring any SQLException, you may easily end up ignoring an exception not caused by a duplicate key (perhaps network or disk failure, or insufficient space, or even invalid numeric or date/time format). You'll then proceed with your transaction assuming that the row you were trying to insert is already in the database, while it isn't.

Have a look at the MySQL INSERT command syntax. You can use the IGNORE keyword, but you should understand that - a bit similarly to the previous solution - it ignores or even tries to remedy wider classes of errors than just the duplicate keys.

The ON DUPLICATE KEY UPDATE clause looks promising too, but you need to update at least one column in the table. Also, it is possible that the inserted row will collide with more than one existing row (if you have at least two unique constraints on the table), the documentation unfortunately doesn't specify what happens in that case.

And, while it is not an error, it is a bad practice to not specify columns you are inserting into.
 
Supun Lakshan Dissanayake
Ranch Hand
Posts: 135
Android Java PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin Vajsar wrote:It's not the best solution. By catching and ignoring any SQLException, you may easily end up ignoring an exception not caused by a duplicate key (perhaps network or disk failure, or insufficient space, or even invalid numeric or date/time format). You'll then proceed with your transaction assuming that the row you were trying to insert is already in the database, while it isn't.

I thought getErrorCode()==1062 in java.sql.SQLException would fix it.
1062 is the error code for duplicate primary key in mysql

Martin Vajsar wrote:
Have a look at the MySQL INSERT command syntax. You can use the IGNORE keyword, but you should understand that - a bit similarly to the previous solution - it ignores or even tries to remedy wider classes of errors than just the duplicate keys.

The ON DUPLICATE KEY UPDATE clause looks promising too, but you need to update at least one column in the table. Also, it is possible that the inserted row will collide with more than one existing row (if you have at least two unique constraints on the table), the documentation unfortunately doesn't specify what happens in that case.

I didn't knew IGNORE or ON DUPLICATE KEY UPDATE clauses. BUT NOW I DO.
GREAT WORK!!!
THANK YOU VERY MUCH SIR!!!
 
Winston Gutkowski
Bartender
Pie
Posts: 10571
64
Eclipse IDE Hibernate Ubuntu
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Supun Lakshan Dissanayake wrote:Well, then you are suggesting me to let mysql throw java.sql.SQLException and handle it.

I'm not sure whether it would be SQLException (it probably is), but that's the general idea.

Thank you very much Winston!!!

You're most welcome.

Winston
 
Winston Gutkowski
Bartender
Pie
Posts: 10571
64
Eclipse IDE Hibernate Ubuntu
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin Vajsar wrote:It's not the best solution. By catching and ignoring any SQLException, you may...

Except that Supun didn't say he would ignore the exception, he said he would "handle" it.

To me, the constraint (or a trigger, see below) is far better than trying to program around it in Java, because it applies the restriction where it should be: on the data.

Supun Lakshan Dissanayake wrote:I thought getErrorCode()==1062 in java.sql.SQLException would fix it.
1062 is the error code for duplicate primary key in mysql

That's certainly one way to do it, but you need to be sure that that is indeed the code that is returned.

A "primary key" has a very specific meaning in SQL - in fact, it's another form of constraint - and it's perfectly possible to have more that one unique column in a table; especially in these days when people tend to add unique ID fields to everything (ugh).

Another possibility, if your database allows it, is to add a "trigger", which is a bit like a method that is run whenever you do something to your table. Personally, I prefer constraints, but triggers can often be customized more easily (eg, to return an unambiguous error code).

Like I say, you'd need to find out whether your database supports such things, and how they work, but it is an alternative.

All I can say is that stuff like this usually belongs in the database, since it's the "R" part of RDBMS.

Winston
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Winston Gutkowski wrote:Except that Supun didn't say he would ignore the exception, he said he would "handle" it.

Supun clarified it later, but the way it was written I thought he would "handle" all SQLExceptions that occur during the insert, which carries the risks I've mentioned. Checking on the error code makes it right, but is, unfortunately, also MySQL specific.

To me, the constraint (or a trigger, see below) is far better than trying to program around it in Java, because it applies the restriction where it should be: on the data.
...
Another possibility, if your database allows it, is to add a "trigger", which is a bit like a method that is run whenever you do something to your table. Personally, I prefer constraints, but triggers can often be customized more easily (eg. to return an unambiguous error code).

I don't think the "more easily" really applies to triggers. Triggers can be surprisingly hard to get right in concurrent environment. They seem to work flawlessly during test, but allow more users into the database, and they mysteriously fail every now and then. And they are quite easy to forget about - many of the questions posted on AskTom (an excellent Oracle site) ultimately resolve into a forgotten trigger causing unexpected/undesired side effects.

A better alternative to triggers (in my opinion) would be a stored procedure that would encapsulate the logic. The code would run next to the data (saving network roundtrips), would be really part of the database (as per your "R" note) and in case of porting the application to another database, could be reimplemented in the new database using all of the tools the new database offers (in this case, using MERGE statement if the new database supported it).

We're in perfect agreement about constraints, though. I'd try to put as many constraints to the database as possible.
 
Supun Lakshan Dissanayake
Ranch Hand
Posts: 135
Android Java PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think using insert ignore into tableName(colName) values ('value') is better than handling java.sql.SQLException because my software fails if somebody change error code for duplicate primary key.

again thanks for your time guys!
 
Winston Gutkowski
Bartender
Pie
Posts: 10571
64
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin Vajsar wrote:I don't think the "more easily" really applies to triggers. Triggers can be surprisingly hard to get right in concurrent environment. They seem to work flawlessly during test, but allow more users into the database, and they mysteriously fail every now and then. And they are quite easy to forget about - many of the questions posted on AskTom (an excellent Oracle site) ultimately resolve into a forgotten trigger causing unexpected/undesired side effects.

Yup, and that's because (IMO):
(a) People don't know how to write them or - probably, more importantly - when and WHY.
(b) Most dbs (with, in my experience, the sole exception being Progress) weren't really designed for them. It's not part of the SQL syntax - more's the pity, because it is, most definitely data-directed - and as a result they're poorly documented and understood in context.

Glad to hear that otherwise we're in sync.

Winston
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic