Re: insert current + 3 units second

On Wed, 22 Nov 2000, Ousmanov (Julia) wrote: >Hi Jonathan, > >Thanks for your reply, >but in my case the VALUES list doesn't except SP call, it complains on >the brackets used. I'm trying to do it in dbaccess. I should have qualified my observation with: I tested using ESQL/C 9.40 (CSDK 2.50) against Foundation 9.21 on Solaris 7. I was using SQLCMD rather than DB-Access, but that should be largely immaterial. If it does not work for you, it is because you are using an earlier version of the database...and it is interesting to know that this is an area that has changed (for the better). I was expecting my test to fail; I was pleasantly surprised that it worked. Julia, In your original email, you said you need both 9.21 and 7.22. You need to think seriously about upgrading from 7.22 (I'm not even sure that it is Y2K compliant) to 7.31. However, that is not a complete solution; I tested my 7.31 server and it rejects the INSERT statement. So, I seriously doubt that you're going to be able to do what you want with DB-Access in both versions of the database. >Jonathan Leffler wrote: >> "Ousmanov (Julia)" wrote: >> > Dbaccess complains on a "+" sign of the statement. >> > But there is no problem with the syntax at all if I use update instead >> > of insert. >> > e.g. UPDATE sb_cache set tstamp = CURRENT + 5 UNITS SECOND works >> > perfectly. >> >> The trouble is that the values in the VALUES list of an INSERT statement >> must be simple values and are not allowed to be expressions in general. >> This is a (bad) hangover from the earliest days of SQL (say the >> mid-80s). One primary use of the VALUES list was in ESQL/C (or ESQL >> PL/I) with a question-mark place holder for each value, and the only way >> to supply the value was to give the name of a host variable as the value >> -- not an expression. However, it isn't quite that simple; there are >> some expressions you can use in a VALUES list, such as MDY(12,31,1899). >> I've not gone poking around the manuals to see how the subset of valid >> expressions is documented. The UPDATE statement accepts full >> expressions, hence the UPDATE works. >> >> How to work around this? You can use a stored procedure in the >> VALUES list, as shown: >> >> SQL[1677]: create procedure inctime(i integer) returning datetime year >> to second; >> > return current year to second + i units second; >> > end procedure; >> SQL[1678]: create temp table t1 (dt datetime year to second not null); >> SQL[1679]: insert into t1 values(inctime(3)); >> SQL[1680]: select * from t1; >> 2000-11-21 16:37:55 >> SQL[1681]: >> >> Have fun... >> >> > "Parker, Jack" wrote: >> > > Try >> > > >> > > Current hour to second + 3 units second. >> >> > > > -----Original Message----- >> > > > From: Ousmanov (Julia) [mailto:julia@farelogix.com] >> > > > Sent: Tuesday, November 21, 2000 12:02 PM >> > > > >> > > > I have some troubles writing possibly very simple statement: >> > > > >> > > > INSERT INTO sb_cache (cmd, tstamp) VALUES("test cmd", CURRENT>> > > > + 5 UNITS SECOND); >> > > > >> > > > table sb_cache: >> > > > cmd - char32 >> > > > tstamp - datetime year to fraction(3) >> > > > >> > > > We use Informix 7.22 and 9.21 and I need this statement to >> > > > work on both of them. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"