Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Location
    São Paulo, Brazil
    Posts
    32

    Exclamation Unanswered: Problem with a parameter marker in a cursor

    Hi all,

    I'm with a problem to open a cursor that contains parameter markers, i have a code like that:

    DECLARE vSql VARCHAR(2000) DEFAULT '';
    DECLARE vDscParameter VARCHAR(40);
    DECLARE vDscResult VARCHAR(100);

    DECLARE vStmt STATEMENT;
    DECLARE vCur CURSOR FOR vStmt;

    SET vSql = 'SELECT name FROM tabname WHERE id = ?';

    PREPARE vStmt FROM vSql;
    OPEN vCur USING vDscParameter;
    FETCH vCur INTO vDscResult;
    CLOSE vCur;

    When I execute my procedure i got this error:

    SQL0301N The value of input host variable or parameter number "1" cannot be
    used because of its data type. SQLSTATE=07006

    Someone can help me?

    Thanks a lot.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The thing is that DB2 doesn't know the data type of the value to be pasted in for the parameter marker during statement PREPARE. You have to explicitly cast the parameter marker to some type:
    Code:
    SET vSql = 'SELECT name FROM tabname WHERE id = CAST(? AS VARCHAR(40))';
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Nov 2004
    Location
    São Paulo, Brazil
    Posts
    32
    Thanks a lot, now it works without the error message.

  4. #4
    Join Date
    May 2003
    Posts
    113
    I think the original query also works. DB2 should be able to associate the data type of 'ID' to this untype parameter marker.

    added on later: oh. I got it. The ID must be another datatype, which can't host varchar(40) data

    Quote Originally Posted by stolze
    The thing is that DB2 doesn't know the data type of the value to be pasted in for the parameter marker during statement PREPARE. You have to explicitly cast the parameter marker to some type:
    Code:
    SET vSql = 'SELECT name FROM tabname WHERE id = CAST(? AS VARCHAR(40))';
    Last edited by nidm; 06-24-08 at 22:24.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You're right. In many situations, DB2 can derive the data type of the parameter marker from other sources, e.g. the column types. Sometimes, this is not possible - e.g. function parameters. Here I would expect this to work as well.

    The question is which statement is raising the error. If it is the PREPARE, then DB2 cannot derive the data type for some reason. If it is the OPEN, then a type mismatch may be the culprit and the CAST is suppressing this.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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