wood burning stoves 2.0*
The moose likes JDBC and the fly likes Regarding the SQL querry generation Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Regarding the SQL querry generation" Watch "Regarding the SQL querry generation" New topic
Author

Regarding the SQL querry generation

Naveen Koneti
Greenhorn

Joined: Apr 27, 2007
Posts: 13
Hi In my project I have to construct queries and write them into sql files through java and they will be read by another program which will be executing the script line by line,but the problem has come while generating the sql file...

Now I am generating the sql file with insert statements like this

"insert into service_template_device (service_template_id, dev_type_id) select "+ serviceTemplateId + ", "+ devTypeId+ " from dual where not exists select 1 from service_template_device where service_template_id="+serviceTemplateId+ " and dev_type_id="+ devTypeId + ");" + "\r\n"

this when executed will check whether the record exists if not then it will insert other wise ignore but my requirement is I have to update the Database if the record doesnot exist.

I tried with merge statement but the problem is it is not a sequential update there are constraints in the tables.As number of sql's is huge the implementation is quite complex.Is there a simple way to do this ?

Please suggest me solution for the above problem....
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by Naveen Koneti:
Hi In my project I have to construct queries and write them into sql files through java and they will be read by another program which will be executing the script line by line,but the problem has come while generating the sql file...

Now I am generating the sql file with insert statements like this

"insert into service_template_device (service_template_id, dev_type_id) select "+ serviceTemplateId + ", "+ devTypeId+ " from dual where not exists


this when executed will check whether the record exists if not then it will insert other wise ignore but my requirement is I have to update the Database if the record doesnot exist.

I tried with merge statement but the problem is it is not a sequential update there are constraints in the tables.As number of sql's is huge the implementation is quite complex.Is there a simple way to do this ?

Please suggest me solution for the above problem....


Hi Naveen,

What you are attempting to do is beyond a DML statement (select, insert, delete, update). You have to either do the programming in your application or in a stored procedure to determine if the data exists. Using the correlated subquery is only allowed if you are populating your data with a result set from your select.

In SQL stored procedure I would do the select for my records:

SELECT 1
FROM service_template_device
WHERE service_template_id = 'serviceTemplateId'
AND dev_type_id = 'devTypeId';

-- return something here to let my application know it already exists

Provide an exception block to trap no_data_found and insert the data within the no_data_found exception block.

It is probably more efficient to have the database do it, but it would make your application less portable between RDBMS.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by Naveen Koneti:

"insert into service_template_device (service_template_id, dev_type_id) select "+ serviceTemplateId + ", "+ devTypeId+ " from dual where not exists select 1 from service_template_device where service_template_id="+serviceTemplateId+ " and dev_type_id="+ devTypeId + ");" + "\r\n"



Naveen, I understand what you are trying to do here.

insert into service_template_device (service_template_id, dev_type_id)
select serviceTemplateId, devTypeId
from dual
where not exists ( select 1
from service_template_device
where service_template_id = serviceTemplateId
and dev_type_id = devTypeId );

Should work.
Raees Uzhunnan
Ranch Hand

Joined: Aug 15, 2002
Posts: 126
What I understood is if record does not exist insert otherwise update !!

Merge is used for same purpose however complex it may be; and I don't understand what you are talking about constraints and stuff to do with merge !!

Another way to do it is call a proc ; pass all the parameters ; inside the proc inset if it fails update !!!


Sun Certified Enterprise Architect
Java Technology Blog
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Raees,

I thought the same thing as well... but after looking at the SQL the poster seems to be trying to insert if the data does not exist. There isn't any SQL to support an update.
 
 
subject: Regarding the SQL querry generation
 
Similar Threads
SQL Server bulk insert
Books or Links - Database Application
storing BLOB type in oracle db
Row locking using oracle database
CMT & Transaction Attribute