Re: ESQL: modify next SERIAL value

Rudy Fernandes wrote: > Jens Schacherl wrote: > > I've read all postings about this topic I found with deja but still it's > > not working. > > I'm trying to reset the SERIAL column 'lfdnr' after deleting a > > consecutive number of rows using this code: > > > > ... > > max_nr++; > > EXEC SQL ALTER TABLE basiszahlen MODIFY lfdnr SERIAL(max_nr); > > ... > > > > The ESQL compiler gives me a syntax error in the ALTER TABLE statement: > > Error -33051: esqlc - Syntax error on identifier or symbol 'max_nr'. > > Prepare the statement and it should go through OK. You cannot use variables in DDL statements (such as ALTER TABLE, CREATE TABLE, GRANT, etc). Hence the PREPARE is the way to go, but... > e.g. > > sprintf(l_sql_stmt, "%s%d%s", > "ALTER TABLE basiszahlen MODIFY (lfdnr SERIAL(", max_nr, "));"); > > EXEC SQL PREPARE alter_stmt FROM :l_sql_stmt; > if (strncmp(SQLSTATE, "00", 2) > 2) > printf("Alter : SQLCODE is %d\\n", SQLCODE); > > EXEC SQL EXECUTE alter_stmt; > if (strncmp(SQLSTATE, "00", 2) > 2) > printf("Execute : SQLCODE is %d\\n", SQLCODE); Once the counter is set to some value, X, you cannot alter it to some lower value Y unless you first reset it to one and then alter it to Y. And you have to do that by wrapping the counter, which means you need to insert a dummy row: ALTER TABLE basiszahlen MODIFY(lfdnr SERIAL(2147483646)); INSERT INTO basiszahlen(lfdnr) VALUES(0); -- 2147483647 INSERT INTO basiszaheln(lfdnr) VALUES(0); -- 1 ALTER TABLE basiszahlen MODIFY(lfdnr SERIAL(max_nr)); Where, of course, you have to clean up the max_nr in a prepared statement, etc. If you do all this in a transaction, you should be fine. You might need to worry about whether the inserts fail -- I assume they did. With Solaris 7 and IDS 9.21.UC1, I can do the following to get the results you want: create table x(y serial not null primary key, z char(10) not null); insert into x values(123, "123"); insert into x(y) values (0); -- fails; can't insert null into z insert into x values(0, "125"); select * from x; delete from x; -- Does not work! alter table x modify(y serial(100)); insert into x values(0, "126"); select * from x; delete from x; alter table x modify(y serial(2147483646)); insert into x(y) values(0); -- fails; counter at 2147483647 insert into x(y) values(0); -- fails; counter at 1 insert into x values(0, "1"); select * from x; alter table x modify(y serial(100)); insert into x values(0, "100"); select * from x; drop table x; 2147483646 is 2^31-2; you can probably use 2^31-1 and be OK, but there used to be a bug (many years ago) such that inserting 2^31-1 directly broke things. I guess I should learn to be a little less cautious about these things... Note that the serial is incremented (irrevocably) before the insert fails. This is a border line bug; using it means you're exploiting a nasty dependency on the sequence in which things occur inside the engine. If you want to do the job properly, you insert rows successfully; that's guaranteed to work. -- 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!"

This turns out to be more complicated than I thought... Think I'll try to get permissions to alter that bloody serial to a long and do my own incrementing, the table is not that big and important. Or I simply ignore the gaps ;-) Thanks again, Jens