Case sensitive query

OscarC

I am dealing with an existing database and an existing login system that I can not modify.
So, I need to have a query to an Oracle table that would be case insensitive.
Is there a way to have a aoData.push that is not case sensitive?
Or am I looking in the wrong direction when I am trying to use aoData.push?

allan

aoData.push? That's internal in DataTables - I'm not clear why you would need to modify that.

Can you show me a bit more of your code / query / data please?

Allan

OscarC

Sure.
This is where the dataTable is created:

    oTable=$('#APHIS_RELEASE_SITES').dataTable( {
        "bJQueryUI": true,
        "sPaginationType": "full_numbers",
        "sDom": '<"H"Tfr>t<"F"ip>',
        "sAjaxSource": sAjaxSrc,
        "fnServerParams": function ( aoData ) {
            aoData.push( { "name": "user_id", "value": user_id } );
        },
        "bAutoWidth" : false,
        "aoColumns": [
            {   "sClass": "center",
                "bSortable": false,
                "bSearchable": false,
                "mData": "CONTROL",
                "mRender": function (val, type, row) {
                                return '<img src="/images/details_open.png">';
                            }               
            },
            {
                "mData": "SITE_ID" //OBJECTID
            },
etc, etc.

the problem is that the criteria for selecting data is user_id, in aoData.push, based in the username, who is at this point logged it. See line 7.
At some point, time ago, the system was created in a way that allowed users to have user names with uppercase and lowercase.
Forward three years later and now, for some other reasons there is a business decision to not enforcing the upper-lower case login rules any longer. So, now there are users who have a user name = Allan and logged in with username=allan and obviously they cannot see their data.
so I would like to have a aoData.push where user_id does not care if is Allan, or allan.

I know, and I understand that this is a business decision that does not make a lot of sense from my perspective.

allan

Oh I see! You are using the old fnServerParams (ajax.data is the "modern" way of doing it).

I know, and I understand that this is a business decision that does not make a lot of sense from my perspective.

Heh - got a new CTO who's user name was already in use? ;-).

From DataTables point of view, I think what you are doing is fine. It would really be up to the server-side script to handle the case insensitivity. You are giving it a value of user_id (which I presume is coming from a global variable set based on the session). All DataTables can do is pass that through to the server. The server is then querying the database (presumably) and any case sensitivity would be handled there.

Allan

OscarC

My server side script goes like this:

/*
 * Editor server script for DB table APHIS_PRERELEASE_SITES_1
 * Automatically generated by http://editor.datatables.net/generator
 */

include( "lib/DataTables.php" );
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Validate;

Editor::inst( $db, 'APHIS_RELEASE_SITES', 'OBJECTID' ) 
    ->fields(
        Field::inst( 'OBJECTID' ),
        Field::inst( 'STATUS' ),
        Field::inst( 'USERID' ),
        Field::inst( 'CONTROL' ),
etc
etc
    ->where('USERID',$_REQUEST['user_id'])
    ->process( $_POST )
    ->json();

I still fail to see where I can handle the user_id = Allan = allan
I just have $_REQUEST['user_id'].
Any pointers?

allan

->where( function ($q) {
  $q->where( 'USERID', $_REQUEST['user_id'], 'ILIKE' );
}

should do it.

Allan