Results 1 to 4 of 4

Thread: Dynamic SQL

  1. #1
    Join Date
    Aug 2015
    Location
    Bucharest
    Posts
    9

    Angry Unanswered: Dynamic SQL

    Code:
    CREATE OR REPLACE PROCEDURE "BUILDINGMNG".MURIELLE(
    	IN P_TABEL VARCHAR(30),
    	IN P_COLOANA VARCHAR(30),
    	IN P_VALOARE VARCHAR(30),
    	IN P_CONDITIE VARCHAR(30),
    	IN P_ID VARCHAR(30))
    	
    LANGUAGE SQL
    DYNAMIC RESULT SETS 1
    
    P1: BEGIN
    	
    	DECLARE V_DYNSQL VARCHAR(100);
    	SET V_DYNSQL='UPDATE "BUILDINGMNG".'||P_TABEL||' SET '||P_COLOANA||'='||P_VALOARE||' WHERE '||P_CONDITIE||'='||P_ID||';';
    	EXECUTE IMMEDIATE V_DYNSQL;
    
    END P1


    ERROR:
    Code:
    Run: BUILDINGMNG.MURIELLE(VARCHAR(30), VARCHAR(30), VARCHAR(30), VARCHAR(30), VARCHAR(30))
    
    {call BUILDINGMNG.MURIELLE(?,?,?,?,?)}
    An unexpected token "" was found following "".  Expected tokens may include:  "ILS=1 WHERE ISS_ID=1".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.68.61
    Run of routine failed.
     - Roll back completed successfully.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    {call BUILDINGMNG.MURIELLE(?,?,?,?,?)} all input variables are null
    update schema.null can not be executed sql104
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Strictly to amuse me, either SELECT the value of V_DYNSQL as it is, or extend the length to 500 characters. My guess is that it is being truncated or possibly trivially corrupted by one of the parameter values and just seeing what is being executed within the procedure will help you to understand the underlying problem.

    This is a pretty common problem with dynamic SQL, especially the first time that you try to use it in any given environment.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    There are several problems with your SP:
    - SQLCODE-104 means illegal character, so you should check for something wrong in your dynamic SQL you are building
    - as suggested, it might be a problem with the parameters you send - check for NULLs
    - another problem might be the length of the parameters or variables - I am using VARCHAR(512) or VARCHAR(1024) for this, just to be sure
    - when building a dynamic SQL, you do not need the semicolon at the end - remove it: '='||P_ID||';'
    - your update will work when the columns P_COLOANA and P_CONDITIE are numbers - for CHARs or VARCHARs you will need ''' (3 single quotation marks): take a look at PREPARE and EXECUTE ... USING

    PS: use English names for your variables, as not everyone understands Romanian
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

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