3

I'm trying to search all tables and columns for a certain value and I'm using php connection to IBM DB2 database. I was wondering if the error is cause because of multiple usage of union.

This is my error message :

Improper use of a string column, host variable, constant, or function "NAMABARANG". SQLSTATE=42907 SQLCODE=-134

And here is my code : 

$keyword=$_GET['keyword'];
$query="SELECT * FROM INVENTORY WHERE NAMABARANG LIKE '%".$keyword."%'".
    " UNION ALL SELECT * FROM INVENTORY WHERE ARRIVALDATE LIKE '%".$keyword."%'".
     " UNION ALL SELECT * FROM INVENTORY WHERE PAPERNUMBER LIKE '%".$keyword."%'".
     " UNION ALL SELECT * FROM INVENTORY WHERE SERIALNUMBER LIKE '%".$keyword."%'".
     " UNION SELECT * FROM INVENTORY WHERE CONDITION LIKE '%".$keyword."%'".
     " UNION ALL SELECT * FROM INVENTORY WHERE LOCATION LIKE '%".$keyword."%'".
     " UNION ALL SELECT * FROM INVENTORY WHERE CONFIRMATIONDATE LIKE '%".$keyword."%'".
     " UNION ALL SELECT * FROM INVENTORY WHERE BARCODE LIKE '%".$keyword."%'".
     " UNION ALL SELECT * FROM USERANDPASSWORD WHERE USERNAME LIKE '%".$keyword."%'";
     " UNION ALL SELECT * FROM USERANDPASSWORD WHERE ACCESS LIKE '%".$keyword."%'";
Stephen Adipradhana
  • 147
  • 1
  • 5
  • 13

1 Answers1

3

See info on that error message here. The problem appears to be that NAMABARANG is one of the long character/clob types, and it is illegal to have that in a query that performs grouping. This query performs grouping because one of the sub-queries uses UNION rather than UNION ALL. UNION groups when it removes duplicate rows.

A query like this should probably not return all columns (SELECT *). Instead, only return what you really need. Possibly an ID column that can identify each row that matched, and the name of the column and table that matched. This will remove the error.

A few other points:

  • Your code is very unsafe! You are taking a parameter directly from a form and inserting it into SQL. Someone can use this to do all sorts of evil things, even delete tables from your database. Please take a look at the first answer to this question for the safe way of using user-entered data in queries.
  • You are unioning rows from two different tables Does USERANDPASSWORD have the exact same columns as INVENTORY? If not, this union will not work. Solves this by not selecting *, instead select a similar set of columns from each table.
  • UNION or UNION ALL? Do you want to return multiple matches from a single row if more than one column contains the search string? If so, use UNION ALL. Otherwise, use UNION to remove duplicates.
Community
  • 1
  • 1
  • big thanks for the help, the error is solved now. I would like to remove multiple matches record, but I'm using long varchar for the location and condition field, is there any other way than to edit the field data type? – Stephen Adipradhana Apr 02 '13 at 15:10
  • @StephenAdipradhana, `long varchar` is deprecated in recent versions of DB2. You probably shouldn't be using it. Regular `varchar` can hold 32K bytes (as much as long varchar) in recent versions. Check the manual for your specific version for more information. –  Apr 03 '13 at 06:39
  • I tried to alter the table using data studio 3.2 to varchar(100), but it failed, the error message is : ALTER TABLE "DB2ADMIN.INVENTORY" specified attributes for column "NAMABARANG" that are not compatible with the existing column.. SQLCODE=-190, SQLSTATE=42837, DRIVER=3.64.106 – Stephen Adipradhana Apr 03 '13 at 14:38
  • @StephenAdipradhana, perhaps you have data with more than 100 characters in it? Or perhaps it simply doesn't let you alter the column from long varchar to something else? Another option would be to create a new column, then copy the values in with an update, then delete the old column, then rename the new column. –  Apr 03 '13 at 14:43
  • 1
    I've found the solution by changing the long varchar to char(100), it seems changing the long varchar to varchar is not allowed. Thanks for the help. – Stephen Adipradhana Apr 03 '13 at 14:51