SERIAL8 to INT8?

I am having a problem with a UDR. In this UDR I am trying to return a serial8 value from a table with an INT8. However when I execute this function I only receive a zero back. When I make the table variable a serial instead of serial8 I can get back the correct value. Any suggestions? Thanks for the help, Alexander -- TABLE: CREATE TABLE seq ( ID serial8 NOT NULL, ts_creation datetime year to fraction NOT NULL ); ALTER TABLE seq ADD CONSTRAINT PRIMARY KEY (ID);---- FUNCTION: CREATE FUNCTION udr( ) RETURNING INT8; DEFINE id INT8; BEGIN WORK; INSERT INTO seq (ts_creation) VALUES (CURRENT); LET id = DBINFO('sqlca.sqlerrd1'); COMMIT WORK; RETURN id; END FUNCTION; Sent via Deja.com http://www.deja.com/

I'm a begginer in INFORMIX, but I've done this before in this way: SELECT MAX( ID )INTO locaInt8Var FROM seq; That's just after you've inserted... I hope you find this helpfull... In article <94i242$3e1$1@nnrp1.deja.com>, aterrill@inrelation.com wrote: > I am having a problem with a UDR. In this UDR I am trying to return a > serial8 value from a table with an INT8. However when I execute this > function I only receive a zero back. When I make the table variable a > serial instead of serial8 I can get back the correct value. Any > suggestions? > > Thanks for the help, > > Alexander > > -- > TABLE: > > CREATE TABLE seq ( > ID serial8 NOT NULL, > ts_creation datetime year to fraction NOT NULL > );> > ALTER TABLE seq > ADD CONSTRAINT PRIMARY KEY (ID);> ---- > FUNCTION: > > CREATE FUNCTION udr( ) > RETURNING INT8;> DEFINE id INT8; > > BEGIN WORK; > INSERT INTO seq (ts_creation) VALUES (CURRENT);> LET id = DBINFO('sqlca.sqlerrd1'); > COMMIT WORK; > RETURN id; > > END FUNCTION; > > Sent via Deja.com > http://www.deja.com/ > Sent via Deja.com http://www.deja.com/

aterrill@inrelation.com wrote: > > I am having a problem with a UDR. In this UDR I am trying to return a > serial8 value from a table with an INT8. However when I execute this > function I only receive a zero back. When I make the table variable a > serial instead of serial8 I can get back the correct value. Any > suggestions? > > Thanks for the help, > > Alexander > > -- > TABLE: > > CREATE TABLE seq ( > ID serial8 NOT NULL, > ts_creation datetime year to fraction NOT NULL > );> > ALTER TABLE seq > ADD CONSTRAINT PRIMARY KEY (ID);> ---- > FUNCTION: > > CREATE FUNCTION udr( ) > RETURNING INT8;> DEFINE id INT8; > > BEGIN WORK; > INSERT INTO seq (ts_creation) VALUES (CURRENT);> LET id = DBINFO('sqlca.sqlerrd1'); > COMMIT WORK; > RETURN id; > > END FUNCTION; The reason you're getting 0 back is that sqlca.sqlerrd1 is a 4-byte quantity and not the 8-byte quantity you need to determine the current value of the SERIAL8 field. That's the easy bit. What's harder is determining what you should use instead. However, a quick look at the manual for DBINFO (at least in the 9.1 hard copy I looked at) suggests that you didn't look at the manual - the answer is pretty obvious when you do. Hint: it is a type name. -- 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!"

Duh. I didn't even have to pick up a book to realize how blind I was. Thanks for the help. We originally designed everything for the 4-byte serial variable, but realized that might not be enough for our needs. When switching to an 8-byte solution, I guess I overlooked thinking that sqlca.sqlerrd1 could be a problem. I remembered the handbook talking about DBINFO and serial8s. I guess I should have questioned the all powerful sqlca.sqlerrd1 and realized that sqlca.sqlerrd1 != serial8. Thanks for the prod, Alexander In article <3A6CD37E.212A456A@informix.com>, Jonathan Leffler <jleffler@informix.com> wrote: > aterrill@inrelation.com wrote: > > > > I am having a problem with a UDR. In this UDR I am trying to return a > > serial8 value from a table with an INT8. However when I execute this > > function I only receive a zero back. When I make the table variable a > > serial instead of serial8 I can get back the correct value. Any > > suggestions? > > > > Thanks for the help, > > > > Alexander > > > > -- > > TABLE: > > > > CREATE TABLE seq ( > > ID serial8 NOT NULL, > > ts_creation datetime year to fraction NOT NULL > > );> > > > ALTER TABLE seq > > ADD CONSTRAINT PRIMARY KEY (ID);> > ---- > > FUNCTION: > > > > CREATE FUNCTION udr( ) > > RETURNING INT8;> > DEFINE id INT8; > > > > BEGIN WORK; > > INSERT INTO seq (ts_creation) VALUES (CURRENT);> > LET id = DBINFO('sqlca.sqlerrd1'); > > COMMIT WORK; > > RETURN id; > > > > END FUNCTION; > > The reason you're getting 0 back is that sqlca.sqlerrd1 is a 4-byte > quantity and not the 8-byte quantity you need to determine the current > value of the SERIAL8 field. > > That's the easy bit. > > What's harder is determining what you should use instead. However, a > quick look at the manual for DBINFO (at least in the 9.1 hard copy I > looked at) suggests that you didn't look at the manual - the answer is > pretty obvious when you do. Hint: it is a type name. > > -- > 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!" > Sent via Deja.com http://www.deja.com/