Datatables PDO maximum parameter error

Datatables PDO maximum parameter error

DanielSCDanielSC Posts: 19Questions: 7Answers: 1

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

  • allanallan Posts: 63,368Questions: 1Answers: 10,449 Site admin

    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

  • DanielSCDanielSC Posts: 19Questions: 7Answers: 1

    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

  • allanallan Posts: 63,368Questions: 1Answers: 10,449 Site admin

    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

  • DanielSCDanielSC Posts: 19Questions: 7Answers: 1

    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

  • allanallan Posts: 63,368Questions: 1Answers: 10,449 Site admin

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

    Allan

  • DanielSCDanielSC Posts: 19Questions: 7Answers: 1
    Answer ✓

    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"
    );

Sign In or Register to comment.