SQL Server 2012+ SSP.class

theflarenet

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!

mfranklin3570

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

allan

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

theflarenet

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.

allan

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

Allan

khardy

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)

khardy

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

allan

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

Allan

khardy

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.

khardy

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

khardy

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_ambre

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