Datatables PDO maximum parameter error

DanielSC

Hi there,
I am running a SSP datatables on a MS SQL database using a custom class as found here: https://pastebin.com/q8iJWkY8

However I've run into a bit of an issue with the search function. When I try to search anything I get the following error:
DataTables warning: table id=memListTable - An SQL error occurred: SQLSTATE[IMSSP]: Tried to bind parameter number 65536. SQL Server supports a maximum of 2100 parameters.

The table I am trying to search on hard about 50 tables and thousands of rows, but the searchable columns are only about 10 or so, which makes me wonder why it's trying to bind 65536 parameters. Does anyone have an idea about this?

Any help would be greatly appreciated, thanks!

allan

That's a lot of parameters!

I'd suggest debugging this by adding a print_r($bindings) at

        // Bind parameters
        if (is_array($bindings)) {

Perhaps also echo out the SQL that is going to be generated.

That code is a modification of our demo class, so it isn't directly supported by us, but that information should help in diagnosing the information.

Allan

DanielSC

Hi @allan ,
Thank you for your response. Upon adding the print_r as suggested, all I get is "Array ( ) Array ( ) Array ( ) ". I have added it right after the { on second line in your example.

Any help would be greatly appreciated!

Best regards

allan

That would suggest that there are no bindings at all, which doesn't make sense given the error message.

Can you show me your updated PHP script please?

Allan

DanielSC

I am not exactly sure what PHP script you are after. We run our query with:
require('ssp.php');

echo json_encode(
SSP::complex($_GET, $dbDetails, $table, $primaryKey, $columns, $filter, null)
);

@allan

Best regards

allan

The customised ssp.php script, which is where you added the print_r statements.

Allan

DanielSC

Just leaving this for anyone else running into the same issue in the future:
We replaced the total data set length function with this and it solved the problem.
// Total data set length
$resTotalLength = self::sql_exec( $db,
"",
"SELECT COUNT({$primaryKey})
FROM $table
$whereAllSql"
);