Datatables Editor 2 - Serverside with Oracle-DB

hoferchr

Dear Alan,

I am using the latest version of Datatables together with the Editor PHP class (serverside). Unfortunately, the simplest filtering and SearchPane functionality are not working with the Oracle driver. In the SearchPane, all the counts return a 0, and filtering doesn't work in either the search bar or the SearchPane. However, when I switch the database in the Editor's config.php file to MySQL and keep the frontend and backend code unchanged, everything works perfectly.

It seems that the Oracle driver of the Editor is not functioning correctly. Could you please test this on your end? I need the serverside functionality as the data volumes are quite large.

Thank you very much, and best regards.
Christian

allan

Hi Christian,

What version of Oracle are you using? Also, could you add ->debug(true) just before the ->process(...) call and then show me the response from the server when the Ajax data is loaded?

Thank you,
Allan

hoferchr

Hi Alan,
thank you for your quick response! We use a Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.21.0.0.0.

Here is a (shortened) json-output.

{
    "data": [
        {
            "DT_RowId": "row_1",
            "NWMT": {
                "CC_DEVICES": {
                    "DE_ID": "1",
                    "DE_HOSTNAME": "HOSTNAME1",
                    "DE_IPADDR": "xxxxx",
                    "DE_NT_ID": "1",
                    "DE_SERIALMODE": null,
                    "DE_TYPE": "cisco_ios",
                    "DE_CONNECTION": "ssh",
                    "DE_MODEL": "Cisco Catalyst 4503-E Switch",
                    "DE_CHASSIS_MODELNAME": "WS-C4503-E",
                    "DE_IMAGE_DESCRIPTION": null,
                    "DE_IMAGE_VERSION": null,
                    "DE_KOMMENTAR": null,
                    "DE_INFRASTRUCTURE": "CNF",
                    "DE_INFRAGROUP": "BMFZ",
                    "DE_INVENTORY": "x",
                    "DE_NLM": "x",
                    "DE_MONSTATUS": "J",
                    "DE_LS_ID": "1"
                }
            }
        },
        {
            "DT_RowId": "row_2",
            "NWMT": {
                "CC_DEVICES": {
                    "DE_ID": "2",
                    "DE_HOSTNAME": "HOSTNAME2",
                    "DE_IPADDR": "xxxxxx",
                    "DE_NT_ID": null,
                    "DE_SERIALMODE": null,
                    "DE_TYPE": "cisco_ios",
                    "DE_CONNECTION": "ssh",
                    "DE_MODEL": "Cisco Catalyst 4506-E Switch",
                    "DE_CHASSIS_MODELNAME": "WS-C4506-E",
                    "DE_IMAGE_DESCRIPTION": null,
                    "DE_IMAGE_VERSION": null,
                    "DE_KOMMENTAR": null,
                    "DE_INFRASTRUCTURE": "CNF",
                    "DE_INFRAGROUP": "BMFZ",
                    "DE_INVENTORY": "x",
                    "DE_NLM": "x",
                    "DE_MONSTATUS": "J",
                    "DE_LS_ID": "1"
                }
            }
        }
        
    ],
    "options": [],
    "files": [],
    "searchPanes": {
        "options": {
            "NWMT.CC_DEVICES.DE_CONNECTION": [
                {
                    "label": null,
                    "total": 0,
                    "value": null,
                    "count": 0
                },
                {
                    "label": "-",
                    "total": 0,
                    "value": "-",
                    "count": 0
                },
                {
                    "label": "conn",
                    "total": 0,
                    "value": "conn",
                    "count": 0
                },
                {
                    "label": "ignore",
                    "total": 0,
                    "value": "ignore",
                    "count": 0
                },
                {
                    "label": "ssh",
                    "total": 0,
                    "value": "ssh",
                    "count": 0
                },
                {
                    "label": "telnet",
                    "total": 0,
                    "value": "telnet",
                    "count": 0
                }
            ],
            "NWMT.CC_DEVICES.DE_MODEL": [
                {
                    "label": "AMS",
                    "total": 0,
                    "value": "AMS",
                    "count": 0
                },
                {
                    "label": "Check Point 6800 QM-30-00",
                    "total": 0,
                    "value": "Check Point 6800 QM-30-00",
                    "count": 0
                },
                {
                    "label": "Cisco 1100 Integrated Services Router",
                    "total": 0,
                    "value": "Cisco 1100 Integrated Services Router",
                    "count": 0
                },
                {
                    "label": "Cisco 1811 Integrated Services Router",
                    "total": 0,
                    "value": "Cisco 1811 Integrated Services Router",
                    "count": 0
                },
                {
                    "label": "Cisco 1841 Integrated Services Router",
                    "total": 0,
                    "value": "Cisco 1841 Integrated Services Router",
                    "count": 0
                },
                {
                    "label": "Cisco 2106 Wireless LAN Controller",
                    "total": 0,
                    "value": "Cisco 2106 Wireless LAN Controller",
                    "count": 0
                },
                {
                    "label": "Cisco 2112 Wireless LAN Controller",
                    "total": 0,
                    "value": "Cisco 2112 Wireless LAN Controller",
                    "count": 0
                }
            ]
        }
    },
    "draw": 1,
    "recordsTotal": "98",
    "recordsFiltered": "98",
    "debug": [
        "Editor PHP libraries - version 2.3.2",
        {
            "query": "SELECT COUNT( \"DE_ID\" ) \"cnt\" FROM  \"NWMT\".\"CC_DEVICES\" WHERE \"NWMT\".\"CC_DEVICES\".\"DE_ID\" < :where_0  ",
            "bindings": [
                {
                    "name": ":where_0",
                    "value": 100,
                    "type": null
                }
            ]
        },
        {
            "query": "SELECT COUNT( \"DE_ID\" ) \"cnt\" FROM  \"NWMT\".\"CC_DEVICES\" WHERE \"NWMT\".\"CC_DEVICES\".\"DE_ID\" < :where_0  ",
            "bindings": [
                {
                    "name": ":where_0",
                    "value": 100,
                    "type": null
                }
            ]
        },
        {
            "query": "\n\t\t\t\tselect * from (select rownum rnum, a.*\n\t\t\t\tfrom (SELECT  \"DE_ID\" \"DE_ID\", \"NWMT\".\"CC_DEVICES\".\"DE_ID\" \"NWMT.CC_DEVICES.DE_ID\", \"NWMT\".\"CC_DEVICES\".\"DE_HOSTNAME\" \"NWMT.CC_DEVICES.DE_HOSTNAME\", \"NWMT\".\"CC_DEVICES\".\"DE_IPADDR\" \"NWMT.CC_DEVICES.DE_IPADDR\", \"NWMT\".\"CC_DEVICES\".\"DE_NT_ID\" \"NWMT.CC_DEVICES.DE_NT_ID\", \"NWMT\".\"CC_DEVICES\".\"DE_SERIALMODE\" \"NWMT.CC_DEVICES.DE_SERIALMODE\", \"NWMT\".\"CC_DEVICES\".\"DE_TYPE\" \"NWMT.CC_DEVICES.DE_TYPE\", \"NWMT\".\"CC_DEVICES\".\"DE_CONNECTION\" \"NWMT.CC_DEVICES.DE_CONNECTION\", \"NWMT\".\"CC_DEVICES\".\"DE_MODEL\" \"NWMT.CC_DEVICES.DE_MODEL\", \"NWMT\".\"CC_DEVICES\".\"DE_CHASSIS_MODELNAME\" \"NWMT.CC_DEVICES.DE_CHASSIS_MODELNAME\", \"NWMT\".\"CC_DEVICES\".\"DE_IMAGE_DESCRIPTION\" \"NWMT.CC_DEVICES.DE_IMAGE_DESCRIPTION\", \"NWMT\".\"CC_DEVICES\".\"DE_IMAGE_VERSION\" \"NWMT.CC_DEVICES.DE_IMAGE_VERSION\", \"NWMT\".\"CC_DEVICES\".\"DE_KOMMENTAR\" \"NWMT.CC_DEVICES.DE_KOMMENTAR\", \"NWMT\".\"CC_DEVICES\".\"DE_INFRASTRUCTURE\" \"NWMT.CC_DEVICES.DE_INFRASTRUCTURE\", \"NWMT\".\"CC_DEVICES\".\"DE_INFRAGROUP\" \"NWMT.CC_DEVICES.DE_INFRAGROUP\", \"NWMT\".\"CC_DEVICES\".\"DE_INVENTORY\" \"NWMT.CC_DEVICES.DE_INVENTORY\", \"NWMT\".\"CC_DEVICES\".\"DE_NLM\" \"NWMT.CC_DEVICES.DE_NLM\", \"NWMT\".\"CC_DEVICES\".\"DE_MONSTATUS\" \"NWMT.CC_DEVICES.DE_MONSTATUS\", \"NWMT\".\"CC_DEVICES\".\"DE_LS_ID\" \"NWMT.CC_DEVICES.DE_LS_ID\" FROM  \"NWMT\".\"CC_DEVICES\" WHERE \"NWMT\".\"CC_DEVICES\".\"DE_ID\" < :where_0  ORDER BY \"NWMT\".\"CC_DEVICES\".\"DE_ID\"  asc  ) a where rownum <= 10)\n\t\t\t\twhere rnum > 0",
            "bindings": [
                {
                    "name": ":where_0",
                    "value": 100,
                    "type": null
                }
            ]
        },
        {
            "query": "SELECT DISTINCT  \"NWMT\".\"CC_DEVICES\".\"DE_CONNECTION\" \"label\", \"NWMT\".\"CC_DEVICES\".\"DE_CONNECTION\" \"value\" FROM  \"NWMT\".\"CC_DEVICES\"  GROUP BY \"NWMT\".\"CC_DEVICES\".\"DE_CONNECTION\" ",
            "bindings": []
        },
        {
            "query": "SELECT DISTINCT  \"NWMT\".\"CC_DEVICES\".\"DE_CONNECTION\" \"value\", COUNT(*) as count FROM  \"NWMT\".\"CC_DEVICES\"  GROUP BY \"NWMT\".\"CC_DEVICES\".\"DE_CONNECTION\" ",
            "bindings": []
        },
        {
            "query": "SELECT DISTINCT  \"NWMT\".\"CC_DEVICES\".\"DE_MODEL\" \"label\", \"NWMT\".\"CC_DEVICES\".\"DE_MODEL\" \"value\" FROM  \"NWMT\".\"CC_DEVICES\"  GROUP BY \"NWMT\".\"CC_DEVICES\".\"DE_MODEL\" ",
            "bindings": []
        },
        {
            "query": "SELECT DISTINCT  \"NWMT\".\"CC_DEVICES\".\"DE_MODEL\" \"value\", COUNT(*) as count FROM  \"NWMT\".\"CC_DEVICES\"  GROUP BY \"NWMT\".\"CC_DEVICES\".\"DE_MODEL\" ",
            "bindings": []
        }
    ]
}

thanks,
christian

allan

Hi Christian,

Thank you for that. The queries, I think, look okay. I need to update my Oracle environment here - it is rather out of date. I'll let you know what I find.

Allan

hoferchr

Thanks so much for your support!

hoferchr

Hi Alan, did you have already a chance to look at this?
Thank you,
Christian

allan

Apologies, I haven't yet. I plan to this week.

Allan

hoferchr

Hi Alan, if you need any help with your analysis, I could offer to do a MS-Teams session, then we could work with my database?
thanks,
Christian

hoferchr

Hi Alan, I wanted to follow up and see if there are any updates regarding the oracle-db.
Additionally, I have a question: how can I set up case-insensitive search with the current serverside setup?

I call the Datatable with the search / caseInsensitive option:
search: { caseInsensitive: true },
I would appreciate any guidance you can provide.
Thanks and best regards,
Christian

allan

I'm really sorry - I've not been able to get a clear bit of time to get my Oracle DB back up and running. I hope to have a slightly more clear plate soon.

Regarding the caseInsensitive parameter, our server-side libraries don't actually use that at this time. It is a client-side option only. It would be up to the server-side script to decide if the search performed should be case sensitive or not, and if so, how that would then be implemented. It looks like Oracle doesn't support ILIKE so you'd have to use one of the approaches suggested here.

Allan

hoferchr

thank you, the Stackoverflow link pointed me in the right direction. I added in Database/Driver/OracleQuery.php in the connect function these alter session sqls:

$stmt = oci_parse($conn, "ALTER SESSION SET NLS_COMP = 'LINGUISTIC'");
$res = oci_execute($stmt);

$stmt = oci_parse($conn, "ALTER SESSION SET NLS_SORT = 'BINARY_CI'");
$res = oci_execute($stmt);

and now the caseinsensitive search works also in Oracle