simply insert into iseries database - IBM AS400

This is a discussion on simply insert into iseries database - IBM AS400 ; Hi, i am rookie with iserires database, but this procedure should working, but i cannot get it to working where is problem CREATE PROCEDURE inserttest () DECLARE i INTEGER; SET i = 1; WHILE i INSERT INTO mylibrary.file VALUES (i); ...

+ Reply to Thread
Results 1 to 5 of 5

Thread: simply insert into iseries database

  1. simply insert into iseries database

    Hi,

    i am rookie with iserires database, but this procedure should working,
    but i cannot get it to working where is problem

    CREATE PROCEDURE inserttest ()

    DECLARE i INTEGER;
    SET i = 1;
    WHILE i <= 100
    INSERT INTO mylibrary.file VALUES (i);

    SET i = i + 1;

    END WHILE;
    END PROCEDURE;

    thank for any help

  2. Re: simply insert into iseries database

    > i cannot get it to working where is problem

    If you posted the error messages you are seeing, maybe we could help.

    At this stage, if I had to guess, I would suggest you try mylibrary/
    file instead of mylibrary.file

  3. Re: simply insert into iseries database

    wassil@post.cz wrote:
    >
    > I am rookie with iSeries database, but this procedure should
    > be working, but I cannot get it to working where is problem
    >
    > CREATE PROCEDURE inserttest ()
    >
    > DECLARE i INTEGER;
    > SET i = 1;
    > WHILE i <= 100
    > INSERT INTO mylibrary.file VALUES (i);
    > SET i = i + 1;
    > END WHILE;
    > END PROCEDURE;
    >
    > thank for any help


    Adding the required LANGUAGE SQL clause would be a good start.
    http://publib.boulder.ibm.com/infoce...zmstcrtpsf.htm

    I do not recall ever specifying any delimiter for END of the compound
    statement of the routine body; i.e. the second reference to /PROCEDURE/
    may be an error, as a reserved word, where only a label [matching the
    label assigned to the procedure] is allowed.
    http://publib.boulder.ibm.com/infoce...mpoundstmt.htm

    I believe the semicolon for the END of the compound statement is an
    error as well. IIRC a semicolon there, is only valid to delimit for
    further statements, for example as is allowed in a script environment
    which allowed processing contiguous statements. An example CREATE
    PROCEDURE is shown at the bottom of:
    http://publib.boulder.ibm.com/infoce...mpoundstmt.htm

    If this procedure is actually intended for use instead of just
    practice, a recursive query can easily produce that result.

    Regards, Chuck

  4. Re: simply insert into iseries database

    Hi,

    i am sorry for my question but here is my procedure and error message

    CREATE PROCEDURE inserttest ()

    LANGUAGE SQL

    DECLARE i INTEGER;
    SET i = 1;
    WHILE i <= 100
    INSERT INTO wasdavcol.tabulka VALUES (i);
    SET i = i + 1;
    END WHILE;
    END

    ***Error MSG***

    > CREATE PROCEDURE inserttest () LANGUAGE SQL DECLARE i INTEGER


    SQL State: 42601
    Vendor Code: -104
    Message: [SQL0104] Token I was not valid. Valid tokens: GLOBAL.
    Cause . . . . . : A syntax error was detected at token I. Token I
    is not a valid token. A partial list of valid tokens is GLOBAL. This
    list assumes that the statement is correct up to the token. The error
    may be earlier in the statement, but the syntax of the statement
    appears to be valid up to this point. Recovery . . . : Do one or
    more of the following and try the request again: -- Verify the SQL
    statement in the area of the token I. Correct the statement. The
    error could be a missing comma or quotation mark, it could be a
    misspelled word, or it could be related to the order of clauses. -- If
    the error token is , correct the SQL statement
    because it does not end with a valid clause.


    Any idea?

    thanks




  5. Re: simply insert into iseries database

    Hi,

    if the statement you specified is the complete Create Procedure
    Statement, you miss the Begin of the Compound Statement.

    Just add Begin before the Declare-Statement.

    Birgitta

    On Jul 7, 1:54*pm, was...@post.cz wrote:
    > Hi,
    >
    > i am sorry for my question but here is my procedure and error message
    >
    > CREATE PROCEDURE inserttest ()
    >
    > LANGUAGE SQL
    >
    > DECLARE i INTEGER;
    > * *SET i = 1;
    > * *WHILE i <= 100
    > * * * INSERT INTO wasdavcol.tabulka VALUES (i);
    > * * * SET i = i + 1;
    > * *END WHILE;
    > END
    >
    > ***Error MSG***
    >
    > > CREATE PROCEDURE inserttest () * LANGUAGE SQL * DECLARE i INTEGER

    >
    > SQL State: 42601
    > Vendor Code: -104
    > Message: [SQL0104] Token I was not valid. Valid tokens: GLOBAL.
    > Cause . . . . . : * A syntax error was detected at token I. *Token I
    > is not a valid token. *A partial list of valid tokens is GLOBAL. *This
    > list assumes that the statement is correct up to the token. *The error
    > may be earlier in the statement, but the syntax of the statement
    > appears to be valid up to this point. Recovery *. . . : * Do one or
    > more of the following and try the request again: -- Verify the SQL
    > statement in the area of the token I. Correct the statement. *The
    > error could be a missing comma or quotation mark, it could be a
    > misspelled word, or it could be related to the order of clauses. -- If
    > the error token is , correct the SQL statement
    > because it does not end with a valid clause.
    >
    > Any idea?
    >
    > thanks



+ Reply to Thread