Case sensitive query

Case sensitive query

OscarCOscarC Posts: 19Questions: 5Answers: 0

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?

Answers

  • allanallan Posts: 61,453Questions: 1Answers: 10,055 Site admin

    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

  • OscarCOscarC Posts: 19Questions: 5Answers: 0

    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.

  • allanallan Posts: 61,453Questions: 1Answers: 10,055 Site admin

    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

  • OscarCOscarC Posts: 19Questions: 5Answers: 0

    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?

  • allanallan Posts: 61,453Questions: 1Answers: 10,055 Site admin
    ->where( function ($q) {
      $q->where( 'USERID', $_REQUEST['user_id'], 'ILIKE' );
    }
    

    should do it.

    Allan

This discussion has been closed.