Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2014
    Posts
    2

    Arrow Unanswered: Informix 11.7 - procedure alter sequence restart dynamically

    Hi

    I'm facing a problem creating a procedure doing exactly what is in the subject.
    Basically what I would like to do is :

    alter sequence my_sequence RESTART select max(my_id) from mytable;

    But I figured it can't be that simple. Indeed this doesn't work. So I made a simple procedure :

    CREATE PROCEDURE upd_my_seq()

    DEFINE max_my_id DECIMAL(19,0);
    SELECT MAX(my_id) INTO max_my_id FROM my_table;
    ALTER SEQUENCE my_sequence RESTART max_my_id;

    END PROCEDURE ;


    Unfortunately it didn't work either (procedure compiling error):
    201: A syntax error has occurred.

    Eventually I managed to compile a correct and more sophisticated procedure :

    CREATE PROCEDURE upd_my_seq()

    DEFINE max_my_id INTEGER;
    DEFINE req VARCHAR;

    SELECT MAX(my_id) INTO max_my_id FROM my_table;
    LET req = "ALTER SEQUENCE my_sequence RESTART ?";
    PREPARE prep_req FROM req;
    DECLARE cur_maxid CURSOR FOR prep_req;
    OPEN cur_maxid USING max_my_id;
    FETCH prep_req;

    END PROCEDURE;


    Now this procedure compiles ok. But once again I have an error when executing the procedure :
    201: A syntax error has occurred.

    Does anyone has an idea ?

    Thanks

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Jeremy,

    alter sequence requires a literal value, and does not accept variables

    try this:
    CREATE PROCEDURE updd_my_seq ()
    DEFINE max_my_id BIGINT;
    DEFINE req char(128);
    --
    set debug file to './debug' ;
    trace on ;
    SELECT MAX(my_id) INTO max_my_id FROM my_table;
    LET req = "ALTER SEQUENCE my_sequence RESTART " || max_my_id ;
    EXECUTE IMMEDIATE req ;
    END PROCEDURE;
    and check what happens in the "debug" file
    It even works in 11.50

    Regards
    Eric

  3. #3
    Join Date
    Feb 2014
    Posts
    2

    Works fine !

    Thx a lot Eric, it works like a charm !

  4. #4
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    my pleasure :-)

    EXECUTE IMMEDIATE can do a lot of handy things, and many time have you got rid of those PREPARE DECLARE OPEN FETCH sequences.

    E.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •