Need help to rewrite query for XML

Discussion in 'PHP' started by tempex, Jul 10, 2012.

  1. #1
    Hello everybody,

    I have a little problem to display Japanese characters with Varchar type data
    All the characters seem to completely disappeared.
    So i tried to use XML as shown in 2nd code below.

    $query="SELECT * FROM XMLTABLE(
    'db2-fn:xmlcolumn(\"TERM.TERM\")/term'
    COLUMNS
        TERM [COLOR=#0000ff]VARCHAR (5000)[/COLOR] PATH 'term' ,
        DEFINITION [COLOR=#0000ff]VARCHAR (5000)[/COLOR] PATH 'definition',
        ) AS T
    Where LOWER(term) like '%$keyword%' 
    ";
    
    $stmt = db2_prepare($conn, $query);
    $result = db2_execute($stmt);
    while($row = db2_fetch_both($stmt)){
    print "\t<tr><td>$row[0]</td><td>$row[1]</td></tr>\n";}
    
    
    Code (markup):

    $query="SELECT * FROM XMLTABLE(
    'db2-fn:xmlcolumn(\"TERM.TERM\")/term'
    COLUMNS
        TERM [COLOR=#ff0000]XML[/COLOR] PATH 'term' ,
        DEFINITION [COLOR=#ff0000]XML[/COLOR] PATH 'definition',
        ) AS T
    Where LOWER(term) like '%$keyword%' 
    ";
    $stmt = db2_prepare($conn, $query);
    $result = db2_execute($stmt);
    while($row = db2_fetch_both($stmt)){
    print "\t<tr><td>$row[0]</td><td>$row[1]</td></tr>\n";
    
    Code (markup):
    And I've got this error message.
    Error message
    Warning: db2_execute(): Statement Execute Failed in .php on line
    Warning: db2_fetch_both(): Column information cannot be retrieved in.php on line

    There is no problem if I remove the
    Where LOWER(term) like '%$term%'
    Code (markup):
    , and the Japanese characters ca be displayed well.

    I guess I have to change the
    Where LOWER(term) like '%$term%'
    expression to go with XML type.

    Any idea how to do that?
    Thanks in advance.
     
    tempex, Jul 10, 2012 IP