SQL Server 2012+ SSP.class

SQL Server 2012+ SSP.class

theflarenettheflarenet Posts: 26Questions: 5Answers: 0
edited May 2018 in Free community support

Has anyone successfully ported the Server-side processing SPP.class.php from MySQL to Microsoft SQL Server 2012+ in it's entirety? Every search results to incomplete pieces without anyone sharing the full code and I'm having trouble trying to piece it together. Hoping there's one out there!

Answers

  • mfranklin3570mfranklin3570 Posts: 1Questions: 0Answers: 0

    Have you gotten an answer to this? If so, please share.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    I think you'd probably need to modify it. The only changes should be in the PDO connection string and the LIMIT / OFFSET to make it match SQL Server syntax.

    Allan

  • theflarenettheflarenet Posts: 26Questions: 5Answers: 0

    Thanks @allan. That is the issue I'm dealing with at the moment as I'm trying to piece things together. Was hoping there was already a complete example/template that I can reference.

    @mfranklin3570, unfortunately not... I'm struggling over here.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Perhaps you can post what you've got and any error message your are getting?

    Allan

  • khardykhardy Posts: 5Questions: 0Answers: 0

    I hear ya. I've been playing with this for a bit, and just using the datatables_demo example with SQL 2016 I've got the connection, query results, sorting and paging working, but the filter function is still a bit of a mystery to me. When applying a filter I'll get the error (customized so it shows the query):

    DataTables warning: table id=example - An SQL error occurred: SQLSTATE[07002]: [Microsoft][ODBC Driver 13 for SQL Server]COUNT field incorrect or syntax error
    Your query is:

    SELECT count(id) FROM datatables_demo WHERE ( first_name LIKE :binding_0 OR last_name LIKE :binding_1 OR position LIKE :binding_2 OR office LIKE :binding_3 OR start_date LIKE :binding_4 OR salary LIKE :binding_5)

    Everything else appears to work though. Hopefully this helps.
    (and if anyone can contribute on that filter function I'd be grateful)

  • khardykhardy Posts: 5Questions: 0Answers: 0

    Aaand I just realized what I posted was from a modified version 1.10.5 (if that matters to your version)

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    What happens if you run that query directly against the database (using SQL Server management studio or the like)?

    Allan

  • khardykhardy Posts: 5Questions: 0Answers: 0
    edited June 2018

    So I changed:

    static function bind ( &$a, $val, $type )
    {
        $key = ':binding_'.count( $a );
        $a[] = array(
            'key' => $key,
            'val' => $val,
            'type' => $type
        );
    
        return $key;
    }
    

    to

    static function bind ( &$a, $val, $type )
    {
    
        $key = "'$val'";
    
        $a[] = array(
            'key' => $key,
            'val' => $val,
            'type' => $type
        );
    
        return $key;
    }
    

    and now I'm getting:

    DataTables warning: table id=example - An SQL error occurred: SQLSTATE[IMSSP]: Tried to bind parameter number 65536. SQL Server supports a maximum of 2100 parameters.
    Your query is:

    SET NOCOUNT ON SELECT * FROM datatables_demo WHERE ( first_name LIKE '%a%' OR last_name LIKE '%a%' OR position LIKE '%a%' OR office LIKE '%a%' OR start_date LIKE '%a%' OR salary LIKE '%a%') ORDER BY first_name ASC offset 0 rows fetch next 10 rows only

    This query will run just fine in SQL Server Management Studio.

  • khardykhardy Posts: 5Questions: 0Answers: 0

    Ok - definitely the wrong approach. I'll keep experimenting. :)

  • khardykhardy Posts: 5Questions: 0Answers: 0

    Ok - after a fresh restart I finally got it working. Admittedly, I'm still not sure why it wasn't binding the parameters previously, but I can dive into that at a later time.

    Basically, the answer was just here:
    https://datatables.net/forums/discussion/32123/server-side-processing-with-pdo-mssql

    But for those who want a complete file I'll just add it here.

  • pooja_ambrepooja_ambre Posts: 3Questions: 1Answers: 0

    Hi Khardy,

    Thanks for sharing the ssp.class.php! I spent 3-4 days lookin for it. However, I am getting the following error

    {"error":"An SQL error occurred: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Unclosed quotation mark after the character string '[Supplier id] ASC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY '.\nYour query is:\n\nSET NOCOUNT ON SELECT [Supplier id], Trading_name, [Street address], Place, Telephone, BBEE, [Status] FROM vwMpoweredCalculations ORDER BY [[Supplier id]] ASC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY "}

    It would be great help if you can guide me resolve this error.

    Thanks
    Pooja

This discussion has been closed.