Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2007
    Posts
    265

    Unanswered: PREPARE statement with VARCHAR variable

    I'm trying to EXECUTE a prepared statement that has a combination of INTEGER and VARCHAR values and am getting an error. Can you help.. thanks!


    PID and PVAL are INTEGERS and PNAME is VARCHAR(24).
    I have SET name = 'Warehouse' for example --

    SET insertStatement = 'INSERT INTO ' || insertTable
    || ' (PID, PNAME, PVAL)'
    || ' VALUES ('|| CHAR(100) || ', ' || name || ', ' || CHAR(100) || ')';

    PREPARE s1 FROM insertStatement;

    I get this error :

    SQL0206N "Warehouse" is not valid in the context where it is used.
    SQLSTATE=42703



    Btw, it works just fine when name is an INTEGER type and I execute this --

    For example, SET name = 12;

    SET insertStatement = 'INSERT INTO ' || insertTable
    || ' (PID, PNAME, PVAL)'
    || ' VALUES ('|| CHAR(100) || ', ' || CHAR(name) || ', ' || CHAR(100) || ')';

    PREPARE s1 FROM insertStatement;

    Also, the column type of PNAME in the insertTable is VARCHAR(24) in the first case and INTEGER in the second case.
    Last edited by db2user24; 06-01-10 at 17:29.

  2. #2
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    on the host (mainframe),
    PNAME would have to be a group item consisting of:
    2 bytes of binary providing the length of the varchar - warehouse - 9 in this case
    and
    a string item to contain the actual value 'warehouse'.
    Dick Brenholtz, Ami in Deutschland

  3. #3
    Join Date
    Nov 2007
    Posts
    265
    Hi, not sure I understand what you mean.. I think I have a syntax error in the 1st INSERT statement.. but don't know how to fix it.

  4. #4
    Join Date
    Nov 2007
    Posts
    265
    Update -- the only way I could get it to work is by setting it up this way :

    PID and PVAL are INTEGERS and PNAME is VARCHAR(24).
    I have SET name = 'Warehouse' for example --

    SET insertStatement = 'INSERT INTO ' || insertTable
    || ' (PID, PNAME, PVAL)'
    || ' VALUES ('|| CHAR(100) || ', ? , ' || CHAR(100) || ')';

    PREPARE s1 FROM insertStatement;
    EXECUTE s2 USING name;


    But there must be a way to have 'name' directly in the insertStatement variable.. any thoughts?

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This must be a common mistake when using dynamic SQL.

    Suppose, the value of insertStatement after SET statement.
    If, the value of name was 'Warehouse',
    insertStatement would be...
    Code:
    INSERT INTO <insertTable> (PID, PNAME, PVAL)
     VALUES (100        , Warehouse, 100        )
    (where <insertTable> is the value of insertTable.)

    So, you should include quotation marks around name, like...
    SET insertStatement = 'INSERT INTO ' || insertTable
    || ' (PID, PNAME, PVAL)'
    || ' VALUES ('|| CHAR(100) || ', ''' || name || ''', ' || CHAR(100) || ')';

  6. #6
    Join Date
    Nov 2007
    Posts
    265
    Quote Originally Posted by tonkuma View Post
    So, you should include quotation marks around name, like...
    SET insertStatement = 'INSERT INTO ' || insertTable
    || ' (PID, PNAME, PVAL)'
    || ' VALUES ('|| CHAR(100) || ', ''' || name || ''', ' || CHAR(100) || ')';


    Thank you! Actually I put two extra single quotes on each end ( not double quotes) and it works.. thanks much!

Posting Permissions

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