db2-PHP-ucase?

jeepin81 · #1 (**permalink**) 01-11-08, 09:53

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 = 1 + $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 + 1 ; 

  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

stolze · #2 (**permalink**) 01-11-08, 11:06

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/

jeepin81 · #3 (**permalink**) 01-11-08, 11:20

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

Peter.Vanroose · #4 (**permalink**) 01-13-08, 15:46

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()

jeepin81 · #5 (**permalink**) 01-15-08, 12:46

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

stolze · #6 (**permalink**) 01-15-08, 13:27

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.

jeepin81 · #7 (**permalink**) 01-15-08, 13:38

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

stolze · #8 (**permalink**) 01-15-08, 15:08

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.)

Internet Services

Web Development

Digital Marketing

Consumer Tech