Datatables Editor 2 - Serverside with Oracle-DB

Datatables Editor 2 - Serverside with Oracle-DB

hoferchrhoferchr Posts: 6Questions: 0Answers: 0

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

Replies

  • allanallan Posts: 63,205Questions: 1Answers: 10,415 Site admin

    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

  • hoferchrhoferchr Posts: 6Questions: 0Answers: 0

    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

  • allanallan Posts: 63,205Questions: 1Answers: 10,415 Site admin

    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

  • hoferchrhoferchr Posts: 6Questions: 0Answers: 0

    Thanks so much for your support!

  • hoferchrhoferchr Posts: 6Questions: 0Answers: 0

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

  • allanallan Posts: 63,205Questions: 1Answers: 10,415 Site admin

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

    Allan

  • hoferchrhoferchr Posts: 6Questions: 0Answers: 0

    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

  • hoferchrhoferchr Posts: 6Questions: 0Answers: 0

    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

  • allanallan Posts: 63,205Questions: 1Answers: 10,415 Site admin

    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

Sign In or Register to comment.