SQL Error with Datatables JS BIN environment

kthorngren

If orderable is set false on the first column with server side processing the following error occurs:

DataTables warning: table id=example - An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LIMIT 0, 10' at line 5

Here is the test case:
http://live.datatables.net/capiniyu/1/edit

Kevin

colin

This thread should help, it's asking the same thing. And here's your test case updated with the recommendation.

Cheers,

Colin

kthorngren

I don't use server side processing and never paid attention to the columns[i][orderable] parameter. The docs do state this:

Flag to indicate if this column is orderable (true) or not (false). This is controlled by columns.orderable.

I would say thats a bit of a flaw and unexpected behavior as the columns.orderable docs state this:

Note that this option only affects the end user's ability to order a column. Developers are still able to order a column using the order option or the order() method if required.

Strict adherence to the columns[i][orderable] parameter negates the use of order or order() for those columns. I suggest putting a note in the columns.orderable stating this. Along with a workaround like having a hidden column and using something like columns.orderData.

Also maybe a note in the order and order() docs stating the usage of columns.orderable may affect server side processing.

Kevin

allan

Hi Kevin,

Many thanks for flagging this up. I've added an extra example here.

I actually don't like the orderable parameter being sent to the server-side. I've come to think that it isn't up to the client-side to tell the server-side if a column should be searchable or orderable. It is the other way around!

Come v2 I'm considering dropping those two parameters in the server-side processing data sent.

Allan

kthorngren

Great, thanks! I can understand these might be useful well at least the searchable parameter.

Kevin