Why does the Binding fail?

mmcnair80

I have my website set up using Server Side Processing. I am using a slightly modified version of the ssp.class.php file, I call it FilterSort.class.php. I modified it so that it uses PDO::SQLSRV instead of MySql. I also use a modified version of the Server Side file from here. I'll add them at the end of this question. I can pull a report and display the table no problem. I can even sort, on one of multiple columns. I can not however use the Individual column searching (text inputs) like from here. I get the following error every time:

"error":"A SQL error has occurred: SQLSTATE[IMSSP]: Tried to bind parameter number 0. SQL Server supports a maximum of 2100 parameters. "

I can not figure out what the problem is.

I've attached what I think are the pertinent files. (I tried just pasting the contents, but went way over the text character limit). I'll show some of it here though.

Initialization:

<script type="text/javascript" class="init">
    $(document).ready(function () {
        // Setup - add a text input to each footer cell
        $('#DataTable tfoot th').each(function () {
            var title = $(this).text();
            $(this).html('<input type="text" placeholder="Search ' + title + '" />');
        });
        console.log("<?php echo $hsql; ?>");

        var table = $('#DataTable').DataTable({
                "lengthMenu" : [[25, 50, 75, 100, 150], [25, 50, 75, 100, 150]],
                "ScrollX" : true,
                "dom" : '<"top"Biflp<"clear">>rt<"bottom"ip<"clear">>',
                "buttons" : [{
                        extend : 'collection',
                        text : 'Selection',
                        buttons : ['selectAll', 'selectNone']
                    }, {
                        extend : 'collection',
                        text : 'Export',
                        buttons : ['excel', 'csv', 'pdf']
                    }
                ],
                "fixedHeader" : {
                    header : true,
                    footer : true
                },
                "select" : true,
                "processing" : true,
                "serverSide" : true,
                "ajax" : {
                    "url" : "ServerSide.php?PageName=<?php echo $Page; ?>"
                }
            });

        // Apply the search
        table.columns().every(function () {
            var that = this;

            $('input', this.footer()).on('keyup change', function () {
                if (that.search() !== this.value) {
                    that
                    .search(this.value)
                    .draw();
                }
            });
        });
    });
</script>

The table:

<table id="DataTable" class="display nowrap" style="width: 100%; border: 1px">
    <thead>
        <tr>
            <?php
            foreach($headings as $heading)
            {?>
            <th class="cell"><?php echo $heading; ?></th><?php
            }?>
        </tr>
    </thead>
    <tfoot>
        <tr>
            <?php
            foreach($headings as $heading)
            {?>
            <th class="cell"><?php echo $heading; ?></th><?php
            }?>
        </tr>
    </tfoot>
</table>

Those are what is in the Initialize.php and ReportPage.php.

allan

How many columns do you have in your table? I'd suggest adding a little debug to your server-side code that will check how many times parameters are bound - it sounds like it is running into a hard limit in SQL Server.

Allan

mmcnair80

I can test with a table that only has 3 columns to make it easier, but where would I put the debugging? I already added this self::fatal( "A SQL error has occurred: ".$e->getMessage() . " " . var_dump($bindings)) into the sql_exec function. I've also tried print_r($bindings) and print_r($stmt) in the error message too. all of them seem to e working fine to me. Except that the $stmt doesn't have a where clause like I would exepct it to. This is the print out in the response:

D:\WebContent\engsys.corp.ftr.com\dev\FrontierReports\FilterSort.class.php:296:
array (size=1)
  0 => 
    array (size=3)
      'key' => string ':binding_0' (length=10)
      'val' => string '%3%' (length=3)
      'type' => int 2

Array ( [0] => Array ( [key] => :binding_0 [val] => %3% [type] => 2 ) ) PDOStatement Object ( [queryString] => SELECT COUNT(id) FROM dbo.TableHeadings ) {"error":"A SQL error has occurred: SQLSTATE[IMSSP]: Tried to bind parameter number 0. SQL Server supports a maximum of 2100 parameters. 1 1"}

From the line 296:

self::fatal( "A SQL error has occurred: ".$e->getMessage() .  "   " . var_dump($bindings) . "    " . print_r($bindings) . "    " . print_r($stmt));

Where else can I put debugging that would catch how and what is being bound and how many times?

allan

If you have a look in the SSP class you will find a function called bind. That would be the best place to add any debug.

With 3 columns you are unlikely to hit the 2100 binding limit. The question is, is it really binding 2100+ parameters in the full use case, and if it is, what are those bindings?

Allan

mmcnair80

I added a print_r($a) inside the bind function. In the console it only prints it once. I can only assume that it's only being called once and so not trying to bind more than the 2100 limit.

I don't see how or where it might be trying to bind more than the one time, or at most with this table 3 times.

bind:

static function bind ( &$a, $val, $type)
{
    $key = ':binding_'.count( $a );

    $a[] = array(
        'key' => $key,
        'val' => $val,
        'type' => $type
    );
        print_r($a);
    return $key;
}

This is what $a prints when just one column is filtered:

Array ( [0] => Array ( [key] => :binding_0 [val] => %1% [type] => 2 ) )

and this is what is printed when a second column is filtered:

Array ( [0] => Array ( [key] => :binding_0 [val] => %1% [type] => 2 ) ) Array ( [0] => Array ( [key] => :binding_0 [val] => %1% [type] => 2 ) [1] => Array ( [key] => :binding_1 [val] => %1% [type] => 2 ) )

I get the same error regardless, but it looks like there are 2 arrays and there should only be one when I'm trying to filter on 2 columns. That is probably a secondary problem and might be fixed by fixing the first.

tangerine

I don't think this is about 2100 parameters. The error message says:

Tried to bind parameter number 0.

Have you examined what is being passed in to your server-side class method?

mmcnair80

Tangerine, Yes, i've checked what is being sent to my server-side class method. (see attached screen shot)

It is sending what appears to me to be correct data. It has just the 3 columns in the test table and has just the one search value. I don't know what else the problem could be?

In my previous comment I show what the bind function is getting and what the results are. There has to be something wrong somewhere, I just don't know where??

allan

but it looks like there are 2 arrays and there should only be one when I'm trying to filter on 2 columns.

You are trying to filter on two columns, so there should be two bindings.

Moreover, there should be one binding per column for the global filter, and one binding per column for the column filters. So if you have more than 1050 columns (I still don't know how many columns your table has) then you would run into this hard limit in SQL Server.

Allan

mmcnair80

I don't have any table with that many columns. The most that I would have would be 160 columns, so I shouldn't reach that limit at all.

Something that Tangerine said caught my attention though. He thinks that it doesn't have to do with the binding too many, but with binding the first one.

Should the $stmt, from the sql_exec, have the sql with a where statement?
Right now it's a query that is counting the id, but has no where statement.

I don't understand how it's supposed to be binding and filtering anyway since the bind key is not a column header, though it seems to be sending the correct filter on the correct column to the server. It's just not binding correctly?

allan

Generally there needs to be three queries for server-side processing:

1. The query to get the data for the current page with the limit and conditions applied
2. The query to get the number of rows in the result set with the conditions
3. The query to get the number of rows in the result set without the conditions.

Allan

mmcnair80

I'm pretty sure that those three queries are happening, even when I try to add a filter on one of the columns. I get data back and it is formatted into the DataTables format the first time. It's only when I try to use the column filtering that this fails. I still have the correct parameters being sent to the server side processing file. They are just failing when attempting to bind the parameters. I'm at a loss as to what else I can do to get this working.

Is there a mistake in my code that is not allowing the binding to work?

I attached them in the original question at the top.

mmcnair80

I think I might have figured out where the problem is. In the function sql_exec

    static function sql_exec ( $db, $bindings, $sql=null )
    {
        // Argument shifting
        if ( $sql === null ) 
        {
            $sql = $bindings;
        }
        //I think the problem is here. It is trying to prepare something that is not actually
        // a query. It is just an array with 3 values, so SQL doesn't know what to do with it.
        $stmt = $db->prepare( $sql );

        // Bind parameters
        if ( is_array( $bindings ) )
        {
            for ( $i=0, $ien=count($bindings) ; $i<$ien ; $i++ )
            {
                $binding = $bindings[$i];
                $stmt->bindValue( $binding['key'], $binding['val'], $binding['type'] );
            }
        }

        // Execute
        try
        {
            $stmt->execute();
        }
        catch (PDOException $e)
        {
            self::fatal( "A SQL error has occurred: ".$e->getMessage())
        }

        // Return all
        return $stmt->fetchAll( PDO::FETCH_BOTH );
    }

I think that the line where it is preparing the $stmt is where it is going wrong. It's trying to prepare an array that does not have valid SQL in it. It's just three values for creating a where clause. The next problem is that if this is where the error is, I have no idea how to fix it.

mmcnair80

I've dug a little further. It looks like the variable $bindings never gets set to anything, even after I try to filter on a column. When filtering a column how is it supposed to set the value of the $bindings variable?

allan

This is how it does it for column filters in the unmodified script.

If the bindings aren't being populated, there is a problem with the bind method.

Allan

mmcnair80

I have finally found where the actual problem is!

In the complex function there is a variable $whereAll. This is never filled. When the line $resTotalLength = self::sql_exec( $db, $bindings, "SELECT COUNT({$primaryKey}) FROM $table ".$whereAllSql); sends it's info to the sql_exec function and it tries to bind the search values, there is no value in the query like :binding_0 for it to bind to. That is where the error is happening.

Now that I've found it, any idea how to fix that?

mmcnair80

I got it to work, but not sure that it's the best way to do it. I ended up modifying my FilterSort.class.php (which is already a modified version of your ssp.class.php) so that the complex function now is like this:

static function complex ( $request, $conn, $table, $primaryKey, $columns, $whereResult=NULL, $whereAll=NULL )
{
    $bindings = array();
    $db = self::db( $conn );
    $localWhereResult = array();
    $localWhereAll = array();
    $whereAllSql = '';
    
    // Build the SQL query string from the request
    $limit = self::limit( $request, $columns );
    $order = self::order( $request, $columns );
    $where = self::filter( $request, $columns, $bindings );
    
    $whereResult = self::_flatten( $whereResult );
    $whereAll = self::_flatten( $whereAll );

    if ( $whereResult )
    {
        $where = $where ? $where .' AND '.$whereResult : ' WHERE '.$whereResult;
    }

    if ( $whereAll )
    {
        $where = $where ? $where .' AND '.$whereAll : ' WHERE '.$whereAll;
        $whereAllSql = ' WHERE '.$whereAll;
    }

    // Main query to actually get the data
    $data = self::sql_exec( $db, $bindings,"SELECT ".implode(", ", self::pluck($columns, 'db'))." FROM $table$where$order$limit");

    // Data set length after filtering
    $resFilterLength = self::sql_exec( $db, $bindings,"SELECT COUNT({$primaryKey}) FROM $table $where" );
    $recordsFiltered = $resFilterLength[0][0];
    
    // Total data set length
    $resTotalLength = self::sql_exec( $db, ""/*$bindings*/, "SELECT COUNT({$primaryKey}) FROM $table ".$whereAllSql);
    $recordsTotal = $resTotalLength[0][0];

    // Output
    return array(
        "draw"            => isset ( $request['draw'] ) ?
            intval( $request['draw'] ) :
            0,
        "recordsTotal"    => intval( $recordsTotal ),
        "recordsFiltered" => intval( $recordsFiltered ),
        "data"            => self::data_output( $columns, $data )
    );
}

And in my ServerSide.php file I'm sending a static array for $whereAll like this:

$whereAll[] = "id like '%%'";

So I update the lines:

$resFilterLength = self::sql_exec( $db, $bindings,"SELECT COUNT({$primaryKey}) FROM $table $whereAllSql" );

To:

$resFilterLength = self::sql_exec( $db, $bindings,"SELECT COUNT({$primaryKey}) FROM $table $where" );

And:

$resTotalLength = self::sql_exec( $db, $bindings, "SELECT COUNT({$primaryKey}) FROM $table ".$whereAllSql);

To:

$resTotalLength = self::sql_exec( $db, ""/*$bindings*/, "SELECT COUNT({$primaryKey}) FROM $table ".$whereAllSql);

Now it is working, I can filter on any column. The downfall now is that the global search is not working. I might just take that out (if I can) and just use the column searches.