An unexpected token “SELECT” SQL CODE =-104, SQLSTATE=42601, DRIVER=3.69.66

Question

I have a stored procedure which I need to change into DB2 SQL from Sybase SQL. Below code is for the Sybase SQL.

CREATE PROC TAS_TEST
(
    @LOGIN_ID VARCHAR(20),
    @NAME     VARCHAR(100),
    @C_NAME   VARCHAR(100),
    @USER     VARCHAR(20),
    @DEBUG    BIT_FLAG=0
)
AS
BEGIN

    DECLARE @TOKEN_EXPIRY_DATE DATETIME,
        @TOKEN VARCHAR(36),
        @ERR INT,
        @ERROR_MSG VARCHAR(200)
        
    SET @LOGIN_ID = LTRIM(RTRIM(@LOGIN_ID))
    
    IF @LOGIN_ID IS NULL
    BEGIN
        RAISEERROR 20000 'LOGIN ID IS MUST:[%1!]',@LOGIN_ID
        RETURN -1
    END
    IF @NAME IS NULL
    BEGIN
        RAISEERROR 20000 'NAME IS MUST:[%1!]',@NAME
        RETURN -1
    END
    IF @C_NAME IS NULL
    BEGIN
        RAISEERROR 20000 'C_NAME IS MUST:[%1!]',@C_NAME
        RETURN -1
    END
    IF @USER IS NULL
    BEGIN
        RAISEERROR 20000 'USER IS MUST:[%1!]',@USER
        RETURN -1
    END
    
    SELECT
        @TOKEN = TOKEN,
        @TOKEN_EXPIRY_DATE = TOKEN_EXPIRY_DATE
    FROM TOKEN_DETAILS
    WHERE
        LOGIN_ID = @LOGIN_ID and
        NAME = @NAME and
        C_NAME = @C_NAME
        
    
    IF(@TOKEN IS NULL)
    BEGIN
        IF @DEBUG = 1 BEGIN
            PRINT "No token exists"
        END
        
        SELECT @TOKEN = NEWID(1)
        SELECT @TOKEN_EXPIRY_DATE = DATEADD(HOUR,12,GETUTCDATE())
        
        INSERT INTO TOKEN_DETAILS(
            LOGIN_ID,
            TOKEN,
            NAME,
            C_NAME,
            TOKEN_EXPIRY_DATE,
            CREATED_DATE,
            CREATED_BY
        )VALUES(
            @LOGIN_ID,
            @TOKEN,
            @NAME,
            @C_NAME,
            @TOKEN_EXPIRY_DATE,
            GETUTCDATE(),
            @USER
        )
        IF(@@ERROR !=0 )
        BEGIN
            SELECT @ERROR_MSG = CONVERT(CHAR(26),GETDATE(),109)+
            " - Error while inserting - "+ CONVERT(VARCHAR,@@ERROR)
            RAISEERROR 30000 @ERROR_MSG
        RETURN -1
        END
    END
    
    ELSE IF(@TOKEN_EXPIRY_DATE < GETUTCDATE())
    BEGIN
        IF @DEBUG = 1 BEGIN
            PRINT "TOKEN EXPIRES"
        END
        
        SELECT @TOKEN = NEWID(1)
        SELECT @TOKEN_EXPIRY_DATE = DATEADD(HOUR,12,GETUTCDATE())
        
        UPDATE
            TOKEN_DETAILS
        SET
            TOKEN = @TOKEN,
            TOKEN_EXPIRY_DATE = @TOKEN_EXPIRY_DATE
        WHERE
            LOGIN_ID = @LOGIN_ID and
            NAME = @NAME and
            C_NAME = @C_NAME
            
        IF(@@ERROR !=0 )
        BEGIN
            SELECT @ERROR_MSG = CONVERT(CHAR(26),GETDATE(),109)+
            " - Error while UPDATING - "+ CONVERT(VARCHAR,@@ERROR)
            RAISEERROR 30000 @ERROR_MSG
        RETURN -1
        END
    END
    
    ELSE IF @DEBUG = 1 
    BEGIN
        PRINT "TOKEN EXIST"
    END
    SELECT @TOKEN AS 'TOKEN', @TOKEN_EXPIRY_DATE AS 'TOKEN_EXPIRY_DATE'
END
GO

GRANT EXECUTE TAS_TEST TO ADMIN
GO

This is the converted DB2 SQL which I have written for the above Sybase SQL.

CREATE OR REPLACE PROCEDURE TAS_TEST
(
    LOGIN_ID VARCHAR(20),
    NAME      VARCHAR(100),
    C_NAME    VARCHAR(100),
    USER      VARCHAR(20),
    DEBUG     SMALLINT DEFAULT 0
)

LANGUAGE SQL

BEGIN

DECLARE TOKEN_EXPIRY_DATE TIMESTAMP;
DECLARE TOKEN VARCHAR(36);
DECLARE ERR INT;
DECLARE ERROR_MSG VARCHAR(200);

SET LOGIN_ID = TRIM(LOGIN_ID);

IF LOGIN_ID IS NULL THEN
    SIGNAL SQLSTATE VALUE '20000'
    SET MESSAGE_TEXT = 'LOGIN ID IS MUST:';
END IF;
IF NAME IS NULL THEN
    SIGNAL SQLSTATE VALUE '20000'
    SET MESSAGE_TEXT = 'NAME IS MUST:';
END IF;
IF C_NAME IS NULL THEN
    SIGNAL SQLSTATE VALUE '20000'
    SET MESSAGE_TEXT = 'C_NAME IS MUST:';
END IF;
IF USER IS NULL THEN
    SIGNAL SQLSTATE VALUE '20000'
    SET MESSAGE_TEXT = 'USER IS MUST:';
END IF;


SELECT
    TOKEN = TOKEN,
    TOKEN_EXPIRY_DATE = TOKEN_EXPIRY_DATE
FROM TOKEN_DETAILS
WHERE
    LOGIN_ID = LOGIN_ID and
    NAME = NAME and
    C_NAME = C_NAME;
    
IF(TOKEN IS NULL) THEN
BEGIN
    IF DEBUG = 1 THEN
        PRINT 'No token exists';
    END IF;
    
    SELECT TOKEN = GENERATE_UNIQUE(1) FROM SYSIBM.SYSDUMMY1;
    SELECT TOKEN_EXPIRY_DATE = TIMESTAMPADD(CURRENT_TIMESTAMP) FROM SYSIBM.SYSDUMMY1;
    
    INSERT INTO TOKEN_DETAILS(
        LOGIN_ID,
        TOKEN,
        NAME,
        C_NAME,
        TOKEN_EXPIRY_DATE,
        CREATED_DATE,
        CREATED_BY
    )VALUES(
        LOGIN_ID,
        TOKEN,
        NAME,
        C_NAME,
        TOKEN_EXPIRY_DATE,
        CURRENT_TIMESTAMP,
        USER
    );
END;
END IF;

IF(TOKEN_EXPIRY_DATE < CURRENT_TIMESTAMP)
BEGIN
    IF DEBUG = 1 THEN
        PRINT "TOKEN EXPIRES, UPDATING";
    END IF;
    
    SELECT TOKEN = GENERATE_UNIQUE(1) FROM SYSIBM.SYSDUMMY1;
    SELECT TOKEN_EXPIRY_DATE = TIMESTAMPADD(CURRENT_TIMESTAMP) FROM SYSIBM.SYSDUMMY1;
    
    UPDATE
        TOKEN_DETAILS
    SET
        TOKEN = TOKEN,
        TOKEN_EXPIRY_DATE = TOKEN_EXPIRY_DATE
    WHERE
        LOGIN_ID = LOGIN_ID and
        NAME = NAME and
        C_NAME = C_NAME;
END;
END IF;

IF (DEBUG = 1) THEN 
BEGIN
    PRINT "TOKEN EXIST";
END;
END IF;
SELECT TOKEN, TOKEN_EXPIRY_DATE FROM SYSIBM.SYSDUMMY1;
END;

GRANT EXECUTE TAS_TEST TO ADMIN;

While running the above db2 query I am getting this error.

[42601][-104] An unexpected token "SELECT" was found following "". Expected may include, " END IF".. SQL CODE =-104, SQLSTATE=42601, DRIVER=3.69.66

Can someone please help me out where I am doing it wrong. It would be of great help.

Answer 1

There are multiple issues in your code will prevent compilation on Db2.

Consider getting some education for ANSI SQL PL (the syntax used by Db2-LUW).

Learn from the many example SQL PL procedures that IBM supplies in the samples directory of Db2-LUW server, and in the Db2-LUW Knowledge Centre, and on github.

Get your code reviewed by a competent or experienced SQL PL developer.

Your profile says "CapGemini consultant", so such skills should be available in your company.

Remember that Stackoverflow is not a code-writing service.

Some suggestions:

• A PRINT statement is not valid in ANSI SQL PL. For Db2-LUW many people use debugging call dbms_output.put_line('some message'); along with set serveroutput on for the session.

• use SET statement to assign a variable from a function. Example: SET v_token = GENERATE_UNIQUE();

• Only use internal BEGIN..END blocks when you want block-specific exception-handling or condition-handling. It is not necessary to use BEGIN...END blocks just to delimit a group of statements inside an IF.

• Do not use reserved words as either variable names or column names (like USER)

• Consider using a naming convention for the parameters and local variables. A naming convention might use some indicator that a name is local v_* , or to indicate that a name is a parameter to the routine p_*.

• In a routine, use SELECT ... INTO ... to assign variables from singleton select.

• To alias a result-set column, use "colName AS colAlias" syntax

• To return a result set, specify that explicitly in the signature and declare and open a cursor at the end. Your line SELECT TOKEN, TOKEN_EXPIRY_DATE FROM SYSIBM.SYSDUMMY1; is not valid.

• The Db2 GENERATE_UNIQUE() function does not accept a parameter.

• Instead of TIMESTAMPADD , consider using CURRENT TIMESTAMP + 12 HOURS.

• To test for the absence of a row after a query, either test for SQLCODE 100 explicitly or use a condition handler.

• Consider doing the upsert as part of a single MERGE statement, which can make code clearer and simplify error detection and recovery.