2

Okay, so I've spent a couple days trolling google and finding absolutely nothing on this. Basically I'm using a prepared statement that has a couple joins and matches based on several LIKEs, an IN, and a FREETEXT. I'm building the query dynamically to support the IN condition. I think some code will help

public function searchForProjWithTags(Array $dSearchArgs, $dTags) {
  $bSplitTags = preg_split ("/[,]/", $dTags);

  $bCount = count($bSplitTags);
  $bQuestionMarks = "?" . str_repeat (",?", $bCount-1);

  $bSql = "SELECT DISTINCT Foo.Foo_ID, Foo.Blah1, Foo.Blah2, Foo.Blah3, 
    Foo.Blah4, Foo.Blah5, Foo.Blah6, Foo.Blah7, Foo.Blah8, Foo.Blah9, 
    Foo.Blah10
    FROM Foo INNER JOIN
    TaggedFoo ON Foo.Foo_ID = TaggedFoo.Foo_ID INNER JOIN
    Tag ON TaggedFoo.Tag_ID = Tag.Tag_ID
    WHERE Foo.Blah2 LIKE ?
      AND Foo.Blah3 LIKE ?
      AND Foo.Blah4 LIKE ?
      AND Foo.Blah5 LIKE ?
      AND Foo.Blah6 LIKE ?
      AND Foo.Blah7 LIKE ?";

  if($dSearchArgs["Blah8"] != "") {
    $bSql .= "AND FREETEXT (Foo.Blah8, ?)";
  }

  $bSql .= "AND Tag.Tag_ID IN (" . $bQuestionMarks . ")
      ORDER BY Foo.Blah2 ASC,
      Foo.Blah3 ASC,
      Foo.Blah5 ASC";

  if(!$bStmt = $this->getPDO()->prepare($bSql)) {
    print_r($this->getPDO()->errorInfo());
  }


  $bStmt->bindValue(1, "%" . $dSearchArgs ["Blah2"] . "%");
  $bStmt->bindValue(2, "%" . $dSearchArgs ["Blah3"] . "%");
  $bStmt->bindValue(3, "%" . $dSearchArgs ["Blah4"] . "%");
  $bStmt->bindValue(4, "%" . $dSearchArgs ["Blah5"] . "%");
  $bStmt->bindValue(5, "%" . $dSearchArgs ["Blah6"] . "%");
  $bStmt->bindValue(6, "%" . $dSearchArgs ["Blah7"] . "%");
  if($dSearchArgs["Blah8"] != "") {
    $bStmt->bindValue(":s", $dSearchArgs["Blah8"]);
  }
  for($i = 0; $i < $bCount; $i ++) {
    $bStmt->bindValue (7 + $i, $bSplitTags [$i]);
  }
  echo "<br><br>" . $bSql . "<br><br>";
  $bStmt->debugDumpParams();
  if(!$bStmt->execute()) {
    print_r($bStmt->errorInfo());
  }

  return $bStmt->fetchAll(PDO::FETCH_ASSOC);
}

So, I've got some probes at the end there so I can see what's going on. These are their results: the echo of $bSql gives the whole SQL string, what should be sent to the DBMS. However, the debugDumpParams() chops off the statement at character 433, it literally end like "...AND Foo.Blah3 L". I have no idea why it would truncate after character 433 and in the middle of LIKE (no special chars, etc.).

The second print_r says Array ( [0] => IMSSP [1] => -29 [2] => Tried to bind parameter number 0. SQL Server supports a maximum of 2100 parameters. ). I have no idea what this means and google wasn't much help either.

So, anyone have any idea why this is happening? I've literally spent hours trying to find something without even coming close. If I run the query manually, it works.

For reference I'm using PHP version 5.4.21, Apache 2.4.6, php_pdo_sqlsrv_54_ts.dll as my PDO driver, and Microsoft SQL Server 2012.

user3147515
  • 59
  • 5
  • Please provide an example of function parameters so we can test-run this. – cen Dec 31 '13 at 02:00
  • $aSearchArgs = array("blah2" => $_POST["blah2"], "blah3" => $_POST["blah3"], "blah4" => $_POST["blah4"], "blah5" => $_POST["blah5"], "blah6" => $_POST["blah6"], "blah7" => $_POST["blah7"], "blah8" => $_POST["blah8"]); if(!empty($_POST["tags"])) { $aTags = implode(",", $_POST["tags"]); $aRs = searchForProjWithTags($aSearchArgs, $aTags); } – user3147515 Dec 31 '13 at 13:13
  • Well that looks like crap... both parameters are an array. $aSearchArgs is an associative array of strings between 1 and about 5 characters long (The fields in the table are nvarchar(50)). $aTags is an array of integers. – user3147515 Dec 31 '13 at 13:16
  • Here's some actual test data: $aSearchArgs = array("blah2" => "R", "blah3" => "X", "blah4" => "47", "blah5" => "O", "blah6" => "23829", "blah7" => "AB", "blah8" => "Here is some test text"); $aTags = array(1, 2, 3, 4); – user3147515 Dec 31 '13 at 13:22
  • I figured it out. When I changed everything to question marks I forgot to changed the :s in the if condition for the FREETEXT search. What I did is put a $j = 0 before that condition and $j = 1 in that condition. I then did the bindValue in the for loop to 7 + $i + $j. Stupid mistake that cost me a few hours. Hopefully this helps anyone else who gets that error. Look through your code hard. – user3147515 Dec 31 '13 at 14:41

0 Answers0