aspose file tools
The moose likes JDBC and Relational Databases 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 and Relational Databases
Bookmark "how to solve this insert record problem ?" Watch "how to solve this insert record problem ?" New topic
Author

how to solve this insert record problem ?

Nakata kokuyo
Ranch Hand

Joined: Apr 13, 2005
Posts: 448
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
Marshal

Joined: May 26, 2003
Posts: 31779
    
192

Nakata,
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.


[OCA 8 book] [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
hi,

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 >=


Regards,
Pradeep Jaladi

"rise above the Rest"


Pradeep.Jaladi
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how to solve this insert record problem ?