Results 1 to 8 of 8

Thread: db2-PHP-ucase?

  1. #1
    Join Date
    Jan 2008
    Posts
    11

    Question Unanswered: db2-PHP-ucase?

    Hello All,

    I am moving this post from the PHP forum to here - here is my question:


    I am trying to create a simple search using PHP to access my DB2 database and that is all fine and good BUT it is a large DB and I want to implement some pagination and only show the first 10 records - again that is all good!

    Here is my problem:
    I want to ucase columns b/c I have no idea what people are going to search and my DB is in both upper and lowercase. So, with my newly create query to limit my results, when I add ucase or lcase i get this error:

    - Function not supported for query. SQLCODE=-255

    w/o ucase or lcase all is fine.

    Here is my code:


    PHP Code:
    <?php

      
    //connect to database
    $i5db2 db2_connect("bob",   
                         
    "lob",
                         
    "law",
                         array(
    "i5_lib"=>"files")) 
                         or die(
    "Connect error: " db2_conn_errormsg());



    //search query
    $query "select * from (SELECT PKEYWD, PITEM, PSDESC, PGENDR, PSIZE, 
    PCOLOR, PMATRL, PCCPGE, ROW_NUMBER() OVER( ORDER BY PSIZE) AS RN 
    FROM O.ITEMPICT WHERE ucase(PKEYWD) LIKE '%
    $uppercase%' OR ucase
    (PITEM) LIKE '%
    $uppercase%' OR ucase(PSDESC) LIKE '%$uppercase%' OR 
    ucase(PGENDR) LIKE '%
    $uppercase%') AS COL WHERE RN BETWEEN $s AND $limit ";


    $run=db2_prepare($i5db2$query); 
    $numresults=db2_exec($i5db2$query)
       or die(
    "Execute error: " db2_stmt_errormsg()); 

             
        
        
    $sqlCount "SELECT COUNT(*) 
              FROM O.ITEMPICT 
              WHERE PKEYWD LIKE '%
    $uppercase%' OR PITEM LIKE '%$uppercase%' OR PSDESC LIKE '%$uppercase$' OR PGENDR LIKE '%$uppercase%' ";     
                          

    $resultsC db2_exec($i5db2$sqlCount)
         or die(
    "Execute error on count query: " db2_stmt_errormsg());



    $num_results db2_fetch_array($resultsC);
    $num =  $num_results[0] ;
       


    // display what the person searched for 
    echo "<p>You searched for: &quot;" $uppercase "&quot;</p>"

    // begin to show results set 
    echo "<h3><font face=\"Arial, Helvetica, sans-serif\" color=\"red\">Here Are Your Results:</font></h3>"
    $count $s 

    // now display the results returned 
      
    while ($row db2_fetch_assoc($numresults)) { 
      
    $title $row['PITEM']; 


       
       if (@
    file_exists('catalog/'.rtrim($row['PITEM']).'.jpg')) { 
            echo 
    '<img src=\'catalog/'.  rtrim($row['PITEM']).'.jpg\' border=1 />'
            echo 
    '<br />'
            } 
           
         else{ 
          echo 
    '&nbsp;'
         } 
         
      echo 
    '<h3><font face=\"Arial, Helvetica, sans-serif\">'
      echo 
    "$count.)&nbsp;$title
      
    $count++ ; 
      echo 
    '<br />'
      echo 
    '</font></h3>'
       
    //echo '<p><h3><strong>'.($count++).'. Product Number: '; 
         //echo htmlspecialchars(stripslashes($row['PITEM'])); 
         
    echo '<br /><strong><font face=\"Arial, Helvetica, sans-serif\">Description:</strong><br /> '
         echo 
    ucfirst($row['PSDESC']); 
         echo 
    '<br /><br /><strong>Size: </strong>'
         echo 
    stripslashes($row['PSIZE']); 
          echo 
    '<br /><strong>Color: </strong>'
         echo 
    ucfirst($row['PCOLOR']); 
          echo 
    '<br /><strong>Gender: </strong>'
         echo 
    ucfirst($row['PGENDR']); 
          echo 
    '<br /><strong>Material: </strong>'
         echo 
    ucfirst($row['PMATRL']); 
         echo 
    '<br /><strong>2007 Catalog Page Number:  </strong></font><font face=\"Arial, Helvetica, sans-serif\" color=\"red\">'
         echo 
    ucfirst($row['PCCPGE']); 
         echo 
    "</font><hr>"
         echo 
    '</p>'
      } 

    $currPage = (($s/$limit) + 1); 

    //break before paging 
      
    echo "<br />"

      
    // links to other results 
      
    if ($s >= 1) { // bypass PREV link if s is 0 
      
    $prevs=($s-$lim); 
      
    $prevs2=($limit-$lim);
      print 
    "&nbsp;<a href=\"$PHP_SELF?s=$prevs&limit=$prevs2&q=$var\">&lt;&lt; 
      Prev 10</a>&nbsp&nbsp;"

      } 

    // calculate number of pages needing links 
      
    $pages=intval($num/$limit); 

    // $pages now contains int of pages needed unless there is a remainder from division 

      
    if ($num%$lim) { 
      
    // has remainder so add one page 
      
    $pages++; 
      } 

    // check to see if last page 
      
    if (!((($s+$lim)/$limit)==$pages) && $pages != 1) { 

      
    // not last page so give NEXT link 
      
    $news=$s+$lim
      
    $news2 $news 10;

      echo 
    "&nbsp;<a href=\"$PHP_SELF?s=$news&limit=$news2&q=$uppercase\">Next 10 &gt;&gt;</a>"
      } 

    $a $s + ($lim 1) ; 
      if (
    $a $num) { $a $num ; } 
      
    $b $s 
      echo 
    "<p>Showing results $b to $a of $num</p>"
       
       
    ?> 

    </body> 
    </html>

    Any thoughts or Ideas? Or possibly a different solution to accomplish pagination? Is this possibly a DB2 bug?

    A huge thanks in advance!!!
    j

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You didn't bother to tell us which DB2 version and platform you are using - I could guess it is iSeries? If it is DB2 LUW, then you can have a look here for different ideas how to do case-insensitive string comparisons: http://www.ibm.com/developerworks/db...dm-0712stolze/
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jan 2008
    Posts
    11
    Hey stolze,

    thanks for the response - my apologies. here is the system I am working on

    i5/OS - AS/400 V5R4.


    Also, as far as ucase working on my verison of db2 - I have used it in different query's such as:

    PHP Code:

    $sqlStatment 
    "SELECT * FROM O.ITEMPICT 
                                  WHERE ucase(PKEYWD) LIKE '%
    $words[$i]%' 
                                  OR ucase(PSDESC) LIKE '%
    $words[$i]%' 
                                  OR ucase(PITEM) LIKE '%
    $words[$i]%' 
                                  OR ucase(PSIZE) LIKE '%words[
    $i]%'"
    and it worked fine.

    for some reason when I attempt to limit my result w/

    ROW_NUMBER() OVER( ORDER BY )..... it errors w/ the usage of ucase/lcase. Also, using UPPER or LOWER I get an identical error SQLCODE-255.

    Any insight would be appreciated but I am thinking this might be a bug w/ DB2on the AS/400? Is there any other way to implement pagination? I come from the MySQL world and would typically use the LIMIT function.

    Thanks Again,
    j

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by jeepin81
    ... when I add ucase or lcase i get this error:
    - Function not supported for query.
    Maybe you have to call that functionality through a differently named function, like e.g. UPPER() & LOWER()
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Jan 2008
    Posts
    11
    Peter - Thanks for the reply. I have tried that and get the same error.

    I also tried this query:

    PHP Code:
    //search query
    $query "SELECT * FROM (SELECT PKEYWD, PITEM, PSDESC, PGENDR, PSIZE, PCOLOR, PMATRL, PCCPGE,
                ROW_NUMBER() OVER( ORDER BY PSIZE) AS RN FROM O.ITEMPICT) 
              AS COL WHERE RN BETWEEN 
    $s AND $limit 
              AND (WHERE ucase(PKEYWD) LIKE '%
    $uppercase%' OR ucase(PITEM) LIKE '%$uppercase%' 
              OR ucase(PSDESC) LIKE '%
    $uppercase%' OR ucase(PGENDR) LIKE '%$uppercase%')"
    w/ the ucase in the outer query I get this error:

    Warning: db2_prepare() [function.db2-prepare]: Statement Prepare Failed in /www/zendcore/htdocs/TEST/search.php on line 54

    Warning: db2_exec() [function.db2-exec]: Statement Execute Failed in /www/zendcore/htdocs/TEST/search.php on line 55
    Execute error: Token UCASE was not valid. Valid tokens: < > = <> <= !< !> != >= < > = IN NOT. SQLCODE=-104


    Is there another way to implement pagination? Or for the time being would it be easier if I just install MySQL and ran my search through there?

    Any ideas or thoughts?

    Again - thanks for all the help!
    j

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You have completely different problem. I reformatted your query so that it is easier to read:
    Code:
    SELECT *
    FROM   ( SELECT pkeywd, pitem, psdesc, pgendr, psize, pcolor, pmatrl, pccpge,
                    ROW_NUMBER() OVER ( ORDER BY psize ) AS rn
             FROM   o.itempict ) AS col
    WHERE  rn BETWEEN $s AND $limit AND
           ( WHERE ucase(PKEYWD) LIKE '%$uppercase%' OR ucase(PITEM) LIKE '%$uppercase%' 
              OR ucase(PSDESC) LIKE '%$uppercase%' OR ucase(PGENDR) LIKE '%$uppercase%' )
    You should remove the excessive WHERE keyword. Because now DB2 believes - during parsing - that the WHERE identifies a column. Next must come some sort of comparison operator in order to get a valid predicated. But "ucase" is not such an operator, resulting in the syntax error.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Jan 2008
    Posts
    11
    Ok - I think I might be a little lost here??? From what you are saying - I will not be able to use ucase/lcase in this type of query?


    I removed the excessive WHERE clause and it returned me to my original error:

    - Execute error: Function not supported for query. SQLCODE=-255


    Is there some other solution for pagination? Or would I have create an all lowercase or uppercase database for my search?


    Thanks for the help - apologies if I am being vague.


    j

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Where did I say you can't use lcase/ucase? I only said that the code you were providing was not showing valid SQL syntax, which resulted in the SQLCODE -104.

    I searched a bit for you and found this link: http://publib.boulder.ibm.com/infoce...la/rzalaml.htm
    My guess is that DB2 complains about the OLAP function here - you should verify this be retrieving the full message, including the message tokens. Why it does that in conjunction with the LCASE/UCASE, I do not know. Maybe DB2 doesn't like it if the argument for LCASE originates from a (temp) table that contains the OLAP function. (A bunch of the scalar functions don't allow OLAP functions for their input argument.)

    Maybe it is the LIKE + UCASE + OLAP. You could try the LOCATE function instead of the LIKE predicate - after all, you don't need any real pattern matching:
    http://publib.boulder.ibm.com/infoce...ale.htm#locate

    Another idea would be to unnest or further nest the subselect:
    Code:
    SELECT *
    FROM   ( SELECT t1.*, ROW_NUMBER() OVER ( ORDER BY psize ) AS rn
             FROM   ( SELECT pkeywd, pitem, psdesc, pgendr, psize, pcolor, pmatrl, pccpge
                      FROM   o.itempict
                      WHERE  UCASE(pkeywd) LIKE '%$uppercase%' OR
                             UCASE(pitem) LIKE '%$uppercase%' OR
                             UCASE(psdesc) LIKE '%$uppercase%' OR
                             UCASE(pgendr) LIKE '%$uppercase%' ) AS t1 ) AS t2
    WHERE  rn BETWEEN $s AND $limit

    p.s: Also note that you may have a serious security problem with your approach to concatenate the statement as you have done: if a user enters the string "' OR 1 = 1 OR '", then all filtering is simply bypassed. You can easily imagine that more severe things could be done that way. (This is called "SQL injection", btw.)
    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
  •