Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2006
    Posts
    1

    Question Unanswered: Parameter Marker with LIKE

    Hello anybody,

    I have the following problem with using parameter markers combined with LIKE and a wildcard (e. g. "%MANN") search.
    It's dynamic embedded SQL in C++.
    Here's the SQL-String:
    SELECT NACHNAME, VORNAME, PERSNR, FROM MITARBEITER WHERE NACHNAME LIKE CAST(? AS CHAR(30))

    I don't know how much parameters exists, so I do the following:
    EXEC SQL DESCRIBE INPUT STMT1 INTO :*sucheDa;
    ...
    sucheDa->sqlvar[i].sqldata = ...
    ...
    EXEC SQL OPEN CSR_SEARCH USING DESCRIPTOR:*sucheDA;

    If I check the SQLDA (sucheDa) I see that the SQLTYPE is 456 instead of 453 an the length is 4000 instead 30.
    This is strange, because I use typed Parameter Marker.
    According to the SQL Reference, VARCHAR(4000) should be set when using untyped Parameter Marker.
    What datatype should I set to "sqldata"?
    I've tested many possibilities, but nothing works.

    One thing is interesting. If I use instead of the SQLDA a host variable (see the following code) it works:

    EXEC SQL OPEN CSR_SEARCH USING :NACHNAME;

    (NACHNAME is a char-Array; value of NACHNAME is "%MANN")

    Can anybody help me? Maybe with an example?
    Thanks!!!

    Frank

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Have you considered using LOCATE function ...

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Code:
    ... WHERE col LIKE CAST(? AS CHAR(30))
    This will most likely not give the desired result:
    suppose the host variable has the following content: '%MANN'
    then the WHERE clause will see LIKE '%MANN '
    (i.e., with 25 spaces at the end), and a match will only be found when there are exactly 25 spaces after MANN.
    You could maybe try something like
    Code:
    WHERE col LIKE RTRIM(?)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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