Datatables PDO maximum parameter error
Datatables PDO maximum parameter error
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!
This question has an accepted answers - jump to answer
Answers
That's a lot of parameters!
I'd suggest debugging this by adding a
print_r($bindings)
atPerhaps 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
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
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
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
The customised ssp.php script, which is where you added the print_r statements.
Allan
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"
);