aspose file tools*
The moose likes JDBC and the fly likes how to solve this insert record problem ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to solve this insert record problem ?" Watch "how to solve this insert record problem ?" New topic

how to solve this insert record problem ?

Nakata kokuyo
Ranch Hand

Joined: Apr 13, 2005
Posts: 442
hi, good day, i having a problem, hope some one can provide me some good idea ,

problem description :
data field suppose insert into DB
- product_code , start_date, end_date

however, when insert into DB, i need to make sure start_date and end_date of record should not fall into date range which have same product_code

for example,

records in DB :

if another new record as below

this record cannot be save because the date range is fall into date range of product1 in DB

what i should do in this scenario, how to detect the date is fall into date range, so the record not to save ? really need your guide on this , thank you very much
[ February 09, 2006: Message edited by: Nakata kokuyo ]
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 31079

You'll want to do this in two steps. Preferably within a transaction.

The first step is to query for the date range:
select count(*)
from table
where product_code = ?
and start_date >= ?
and end_date <= ?

Then if the count is zero, you can insert the record.

[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
pradeep jaladi
Ranch Hand

Joined: Nov 21, 2004
Posts: 65

I think you need to check 4 cases

1) start_date <= & end_date <=
2) start_date <= & end_date >=
3) start_date >= & end_date <=
4)start_date >= & end_date >=

Pradeep Jaladi

"rise above the Rest"

I agree. Here's the link:
subject: how to solve this insert record problem ?