Not Exists SQL statement error

[vien]

I came across this error when trying to use the "NOT EXISTS" statement in my SQL query. Crap, I have double checked with the codes I found on the internet but still to no avail

this is the error generated by Eclipse, FYI I'm implementing MySQL inside my Java program:

java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where not exists (select * from commitee_details where peoplewise_id = 112233) v' at line 1

This is the code that causes the error:

String sql2 = "insert into commitee_details (peoplewise_id, name) where not exists (select * from commitee_details where peoplewise_id = 112233) values (112233, 'jim');" ;
stmt.execute(sql2);

[vien]

Used a different approach to avoid the NO EXISTS sql error. well at least I'm still getting the results I wanted but use a couple lines of code instead of one

I'm trying to insert values into a table where the insertion will include a Primary Key value. Therefore the program needs to check whether the PK value has existed inside the table or not, else it will cause an Duplicate value error. These are the codes I used:

//checks whether the peoplewise_id already exists inside commitee_details table
String sql5 = "select peoplewise_id from commitee_details where peoplewise_id = 112233;";

ResultSet result = stmt.executeQuery(sql5);

if (result.next() != true) {
//if no rows are returned from the sql5 statement then do the insert statement below
String sql2 = "insert into commitee_details (peoplewise_id, name)values (112233, 'jim');";
stmt.execute(sql2);
}

[fyire]

u can try to just insert into the table without checking anyways. U'll get an SQLException raised when there's a PK conflict. when you get an exception, here's how you handle it:

SQLException has a method that allows u to get the exception code for the exception raised. this is vendor specific, so you will nee dto check the mysql documentation on the error codes for their JDBC driver. if it the error code returned matches the error code for PK conflict, then you know that the error is caused by that, so you can handle it accordingly.

alternatively, you can use hibernate (www.hibernate.org) do handle ya database handling work without you needing to do a single line of SQL

[vien]

I did enter all the codes above into try catch block, if I don't Eclipse will generate a compile error anyway I can't have SQLExceptions else I will not be able to do all inserts required. So I really must check before inserting into the table.

Is hibernate a freeware? I'm doing it for my company so I can't use products without license

[fyire]

Originally Posted by vien

I did enter all the codes above into try catch block, if I don't Eclipse will generate a compile error anyway I can't have SQLExceptions else I will not be able to do all inserts required. So I really must check before inserting into the table.

Is hibernate a freeware? I'm doing it for my company so I can't use products without license

actually, that's the beauty of try/catch blocks. in the try, do place the code that might fail (such as the insertion that may have the PK violation), then you clean up any messes caused by errors inside the catch block, and in the finally block, that's where it all comes to an end, such as commiting the series of sql statements that's been done and so on.

yup, hibernate is free