- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Increment the date field by a second and Inser...
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-31-2003 07:48 AM
тАО07-31-2003 07:48 AM
Increment the date field by a second and Insert.
I am writing a Microfocus COBOL program. I am building up records and inserting into an Oracle table which is giving me unique constraint error because of one of the date filed has the same value. I would like to save sysdate and compare it with the current sysdate value. If it is the same can I increment the sysdate while inserting into an oracle table? How do I define it in COBOL to store the date to increment, and how can I use it in the Oracle insert statement?
Any help is appreciated. Thanks.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-31-2003 08:13 AM
тАО07-31-2003 08:13 AM
Re: Increment the date field by a second and Insert.
MF COBOL does have a CURRENT-DATE function that has hundredth of a second resolution BUT the hundredth second part is not supported on all platforms. If the hundedth part always returns 00 then you could increment this portion if an insert fails.
A much smarter method would be to include a SEQUENCE as part of your unique constraint (index). Oracle sequences are very easy to setup and that would completely solve your uniqueness problem.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-31-2003 09:21 AM
тАО07-31-2003 09:21 AM
Re: Increment the date field by a second and Insert.
use the Oracle object sequence to generate a unique id for your record.
TIMESTAMP used to be (and may still be) a pb for databases such as DB2 as it may not be unique !
Then sequence objects can cached into memory increasing performance.
Rgds,
JL
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО07-31-2003 11:12 PM
тАО07-31-2003 11:12 PM
Re: Increment the date field by a second and Insert.
you can try to do a "sysdate+.001", like in the example below:
yd@MYDB.MU> select to_char(sysdate,'dd/mm/yyyy hh24:mi'), to_char(sysdate+.001,'dd/mm/yyyy hh24:mi')
2* from dual
TO_CHAR(SYSDATE, TO_CHAR(SYSDATE+
________________ ________________
01/08/2003 11:12 01/08/2003 11:14
Elapsed: 00:00:00.00
yd@MYDB.MU>
hope this helps!
regards
Yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-02-2003 07:50 PM
тАО08-02-2003 07:50 PM
Re: Increment the date field by a second and Insert.
If you expect most inserts to work, and only a few to cause duplicate key values (perhaps 1 in 20 or better) then I would consider to simply always attempt the plain insert and just handle the specific condition.
If, and only if, you do get the constraint violation error, then branch of into an alternate code path where you insert with sysdate + retry count. (Hmmm, I suppose you could use the same loop for the initial attempt with rery count = 0). Supposedly the round-trip trhough Oracle will inject enough time skew, but you might need a (micro-)sleep. Also, you probably want to monitor the retry count and give up after a while (10 times?) and/or start sleeping longer between retries.
hth,
Hein.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО08-04-2003 02:14 PM
тАО08-04-2003 02:14 PM
Re: Increment the date field by a second and Insert.
I would recommend looking at your current constraint situation though. I wouldn't place a unique constraint on just the date field, as you won't gain anything from it other than enforcing that no single date can be at the same time. Why dont put the unique constraint across the date field and another column, or across some other columns.
Thanks,
Brian