Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
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


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
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: 438
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30050
    
149

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.


[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
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: how to solve this insert record problem ?
 
Similar Threads
validating Dates
Confused by Stored Procedure Error
Need help in an query development
is that any utility class that help compare date within start and end date ?
having problem with a stored procedure used called by a DAO