Informix produces error -1263 after system time is changed
Poster on IDS 7.31 saw error -1263 when a prepared INSERT using CURRENT for a datetime column was re-executed after the host clock was set backwards (USEOSTIME=1); the statements are prepared once at program start and never re-prepared, and the clock change was expected from introducing NTP. Replies suggested re-preparing the statements after any time change, and preferably avoiding step changes altogether by letting ntpd (or 'date -a') slew the clock gradually so applications never notice. Marco Greco asked for a real schema/SQL example as the posted code wasn't valid SQL. The poster never confirmed a fix, so no definitive resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, I experienced a problem with informix IDS version 7.31.UD4 when I changed the hosts time. The error that informix showed was -1263 . The problem I think is located to some queries that are prepared to insert some records and have the word CURRENT as default values for insertion to columns of type datetime. For example let stmt = "insert into mytab( date datetime year to second ) values( CURRENT) prepare i_stmt from stmt execute i_stmt sleep 10 ## now I turn the time back on the host execute i_stmt ## on the above execution the error is produced. Can anyone tell me how can I overcome this problem? Thanks in advance
nick.tzelepis@gmail.com wrote: > Hi, > > I experienced a problem with informix IDS version 7.31.UD4 when I > changed the hosts time. > The error that informix showed was -1263 . The problem I think is > located to some queries that are > prepared to insert some records and have the word CURRENT as default > values for insertion to > columns of type datetime. > > For example > > let stmt = "insert into mytab( date datetime year to second ) values( > CURRENT) > prepare i_stmt from stmt > > execute i_stmt > sleep 10 > > ## now I turn the time back on the host > > execute i_stmt > ## on the above execution the error is produced. > > Can anyone tell me how can I overcome this problem? > > Thanks in advance > Don't turn the time back :-/ Why are you turning the "time back"?
Does it happen if you reprepare the statement? If it doesn't you'll just need to reprepare statements after you change the system time on the machine. nick.tzelepis@gmail.com wrote: > Hi, > > I experienced a problem with informix IDS version 7.31.UD4 when I > changed the hosts time. > The error that informix showed was -1263 . The problem I think is > located to some queries that are > prepared to insert some records and have the word CURRENT as default > values for insertion to > columns of type datetime. > > For example > > let stmt = "insert into mytab( date datetime year to second ) values( > CURRENT) > prepare i_stmt from stmt > > execute i_stmt > sleep 10 > > ## now I turn the time back on the host > > execute i_stmt > ## on the above execution the error is produced. > > Can anyone tell me how can I overcome this problem? > > Thanks in advance
Hi, > > I experienced a problem with informix IDS version 7.31.UD4 > when I changed the hosts time. > The error that informix showed was -1263 . The problem I > think is located to some queries that are prepared to insert > some records and have the word CURRENT as default values for > insertion to columns of type datetime. > > For example > > let stmt = "insert into mytab( date datetime year to second ) values( > CURRENT) > prepare i_stmt from stmt > > execute i_stmt > sleep 10 > > ## now I turn the time back on the host > > execute i_stmt > ## on the above execution the error is produced. > > Can anyone tell me how can I overcome this problem? > > Thanks in advance How are you changing the time and by what amount. If the delta is not too huge then date +a will allow you to drift the time to the new setting and the 4GL will have no idea this is happening. Otherwise you need to re-prepare your statements (probably) Paul Watson Tel: +44 1414161772 Mob: +44 7818003457 Web: www.oninit.com Growing Old is Mandatory Growing Up is Optional Attend IDUG 2007 San Jose, North America May 6-10, 2007 Visit http://www.iiug.org/conf for more information. >
Thanks guys for the responses. Actually the queries are prepared at the start of the program and never reprepared again. Also we are trying to set NTP on the server room and the time will probably go back because of the protocol.
Also I forgot to mention that the onconfig USEOSTIME is set to 1 (gets time from the host)
How the time is adjusted on ntp startup will depend on your config, ntp is supposed to drift the time is you run as daemon, if you use ntpdate then it will apply a delta Cheers Paul Paul Watson Tel: +44 1414161772 Mob: +44 7818003457 Web: www.oninit.com Growing Old is Mandatory Growing Up is Optional Attend IDUG 2007 San Jose, North America May 6-10, 2007 Visit http://www.iiug.org/conf for more information. > -----Original Message----- > From: nick.tzelepis@gmail.com [mailto:nick.tzelepis@gmail.com] > Posted At: 12 December 2006 10:08 > Posted To: comp.databases.informix > Conversation: Informix produces error -1263 after system time > is changed > Subject: Re: Informix produces error -1263 after system time > is changed > > > Thanks guys for the responses. > > Actually the queries are prepared at the start of the program > and never reprepared again. Also we are trying to set NTP on > the server room and the time will probably go back because of > the protocol. >
> Actually the queries are prepared at the start of the program and never > reprepared again. Also we are trying to set NTP on the server room and > the time will probably go back because of the protocol. The NTP daemon will [should] adjust the time in tiny increments to get to the desired time. Your applications will never notice. Assuming of course you are starting with a reasonably correct time, otherwise the NTP daemon will refuse to do anything.
Yes I agree with you but my problem is that the CURRENT keyword after the time change and according to the finderr ot 1263 error, has an incorrect value. Do you have any ideas about this?
TBP wrote: > nick.tzelepis@gmail.com wrote: > >>Hi, >> >>I experienced a problem with informix IDS version 7.31.UD4 when I >>changed the hosts time. >>The error that informix showed was -1263 . The problem I think is >>located to some queries that are >>prepared to insert some records and have the word CURRENT as default >>values for insertion to >>columns of type datetime. >> >>For example >> >>let stmt = "insert into mytab( date datetime year to second ) values( >>CURRENT) >>prepare i_stmt from stmt >> >>execute i_stmt >>sleep 10 >> >>## now I turn the time back on the host >> >>execute i_stmt >>## on the above execution the error is produced. >> >>Can anyone tell me how can I overcome this problem? >> >>Thanks in advance >> > > Don't turn the time back :-/ > > Why are you turning the "time back"? And more - that's not valid sql: insert into (date) values (current year to second) is, though. Please provide a real life example that shows the issue. table schema and actual sql. What is a -1263? Do not assume that your reader knows the meaning of every informix error message. What's the definition of turning time back? Changing the timezone? through any suitable application of stime (2)? And on what OS? What's your setting of USEOTIME? In short, you may want to read the smart questions FAQ. Finding an appropriate URL is left as an excercise to the reader. -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm
Related threads
- java.sql.SQLException: Could not position within a table
- Table locking problem.
- Error 1263 from dbaccess