ssp.class.php for PostgreSQL ?

athanasiosem

Hello,

Is there a version of ssp.class.php for PostgreSQL ?

Thank you in advance

tangerine

1.9 has this:
http://legacy.datatables.net/development/server-side/php_postgres

It should work for 1.10, but you might want to freshen it up a bit.

athanasiosem

Using this file, sorting and filtering does not work by default in version 1.10. It needs some modifications.

Thank you for your answer.

stevevance

I tried this on 1.10.0. It doesn't work because there are major differences between 1.9 and 1.10 – the JS variables are quite different in the 1.10 API.

I created a copy of ssp.class.php, naming it ssp.class.psql.php and changed the PDO connection setting to be "pgsql:" instead of "mysql:". I then created a copy of ssp.process.php, naming it ssp.process.psql.php and entered the correct PostgreSQL connection details.

But I found a solution! First, though, let me walk you through more of the errors I encountered... Or get the solution now from my gist on GitHub.
https://gist.github.com/stevevance/ba2ca35d96b820d6908f

When loading the webpage that would display the table of information from the PostgreSQL database I encountered this error:

DataTables warning: table id=tableV - An SQL error occurred: SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "`"
LINE 2:     FROM `violations`
                 ^

It appears that PostgreSQL doesn't like backticks. I removed all instances of the backtick in my new ssp.class.psql.php file. The next error up? The following:

DataTables warning: table id=tableV - An SQL error occurred: SQLSTATE[42601]: Syntax error: 7 ERROR:  LIMIT #,# syntax is not supported
LINE 5:     LIMIT 0, 15
            ^
HINT:  Use separate LIMIT and OFFSET clauses.

Here's the new code for line 76:

$limit = "OFFSET ".intval($request['start'])." LIMIT ".intval($request['length']);

However, now you have to deal with the fact that SQL_CALC_FOUND_ROWS and FOUND_ROWS is only for MySQL and not PostgreSQL. FOUND_ROWS can only be run after SQL_CALC_FOUND_ROWS so we need a new way to get the information DataTables needs to show the data set length after filtering - we can amend the SQL query with

, count(*) OVER() AS total_count

before the "FROM". Then comment out "SELECT FOUND_ROWS()" on line 231 and create a new line with this code:

$recordsFiltered = $data[0]["total_count"];

The function "simple" should have a part that now looks like this:

// Main query to actually get the data
        $psql = "SELECT ".implode(", ", SSP::pluck($columns, 'db')).", count(*) OVER() AS total_count
             FROM $table
             $where
             $order
             $limit";
        $data = SSP::sql_exec( $db, $bindings,$psql);
        
        // Data set length after filtering
        $recordsFiltered = $data[0]["total_count"];

        // Total data set length
        $resTotalLength = SSP::sql_exec( $db,
            "SELECT COUNT({$primaryKey})
             FROM   $table"
        );
        $recordsTotal = $resTotalLength[0][0];

If you can't read the new page, I've posted it on GitHub as a Gist.
https://gist.github.com/stevevance/ba2ca35d96b820d6908f

allan

Excellent! Thanks for sharing this with us :-)

Allan

jpaalmeida

Thanks, but I'm with problem for Filter / Search...column integer or formatted

passage

Corrected. Now it works filtering and searching. http://pastebin.com/xgV4eQVf

luizmartins

Hello picked this over your script yet still has the same error in the search, have any solution for this error has? Not work for me.

DataTables warning: table id=example - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

allan

Did you follow the instructions in the linked tech note? What was the returned data from the server?