Prepared Statement Insert issue

[DB2Problem]

Hello ALL

I am using a preparedStatement and is passing values dynamically.

Now I want to "insert" values in the table based on condition - Insert only if a particular

value is not present else do not perform insert

below is query -

insert into CUSTOMER (EMAIL_ADDRESS,ZIPCODE) values (?,?)
where EMAIL_ADDRESS NOT IN (SELECT EMAIL_ADDRESS FROM CUSTOMER)

I know the above is not working, I put the above to show what i intend to achieve

 

[sedj]

What is your question exactly ? Can you elucidate on the precise area you wish for help on ?

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[stefanwagner]

I guess he wants to insert email and zip into customer, if no email exists in customer.

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung

 

[DB2Problem]

How can i be more precise then what i explained ? Which part of the issue you could not understand. It's as clear as possible, read it again and you will understand.

 

[sedj]

Well these are the questions in my mind :

Does he/she want help on general syntax if a PreparedStatement object ?
Do they want to know how to use bind variables ?
Do they need help on the SQL syntax ?
Is the SQL OK, bu they want to know how to use a PreparedStatement ?

I could go on ...

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[timw]

Basically, DB2Problem, we want to know in what way your scenario is not working. Do you get an exception thrown? If so, post it.

Tim
---------------------------
"Your morbid fear of losing,
destroys the lives you're using." - Ozzy

 

[DB2Problem]

I guess what my problem is that I am using preparedStatement and is doing an insert based on values that are dynamic, now when i am doing an insert, I want to check if the value is already in the database, if so i don't want to do an insert, if the value is not there then only i wan to do an insert.

Can it be possible or do i need to write SP

Sorry for any confusion or ??

 

[sedj]

If you can write a SP, then that would certainly be the best way (and use a CallableStatement). SP is much better than using a PreparedStatement in any situation IMO.

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[stefanwagner]

AFAIK the question is in no way related to 'prepared' or 'simple' statement.
It's a question of how to express your idea in SQL.

insert into C (E, Z) values (?,?)
where E NOT IN (SELECT E FROM C)

SELECT e FROM c may return zero, one or more lines, and you're using 'where' as if it is an 'if'.

May you use:

pstmt1 = "SELECT COUNT (*) FROM c WHERE e = ?"
pstmt2 = "INSERT INTO c (e, z) VALUES (?, ?)";

// bind stmt1, execute..., rs.next (), rs.getInt...
if (count == 0) 
{
        // bind stmt2, execute, ...
}

?

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung

 

[sedj]

Still much better done in a stored procedure if you ask me !

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[stefanwagner]

I'm trusting in your experience, sedj, but:

a) I'm not familiar with stored procedures.
b) Do simple databases have SPs (mySql, access, hsqldb)?
c) is the syntax of SPs RDBMS-independent?

If you don't need performance, plain SQL might be more simple. (?)

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung

 

[sedj]

SP's are coming in MySQL I think in 5.0 ...
They are there already I *think* in Postgres.

The syntax *can* vary between db's ... so that is a consideration - but the syntax is often similar, if not the same.

However, DB2 does support it I think (well, the OP asked whether they would have to).

SP's mean you can tweak execution plans, means you don't have arbitrary SQL running against your dbs, and also, the "behind the scenes" SQL can be adjust, without altering the webapp, and the performance is better - all major plus points for me.

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[stefanwagner]

Perhaps I should learn more about SPs and postgres.

User DBProblem seems not too interested in our solutions and debate. Probably he found a solution in another forum.

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung

 

[sedj]

Well as long as someone got something out of it eh

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk