I am trying to perform a SODA search of playerName ignoring the upper- and lowercase characters. I have read Oracle manuals, googled, ChatGTP etc. but can still not find the record I search for using QBE and RegEx.

I have checked the chain of commands from calling to executing the code and do not understand why it does not work. I also hardcoded the playerName = “Mexican” (below) for test.

Current response:

{"items":[],"hasMore":false,"count":0}

I am using Unity3d and Unity Cloud Code with c#.

The script:

using Unity.Services.CloudCode.Core;
using System;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

namespace ReadPlayerName
{
public class MyModule
{
    private static readonly HttpClient client = new HttpClient();
    private string url = "https://xxxxxxxxxxxxxxx.oraclecloudapps.com"; // Ensure full URL

    [CloudCodeFunction("ReadPlayerName")]
    public async Task<string> ReadPlayerName(string _playerName, string _document, string _accessToken)
    {
        // Standard HTTP status codes
        const int BAD_REQUEST = 400; // Bad Request
        const int UNAUTHORIZED = 401; // Unauthorized
        const int INTERNAL_SERVER_ERROR = 500; // Internal Server Error
        const int BAD_GATEWAY = 502; // Bad Gateway

        // Check for null or empty parameters and return appropriate HTTP status codes
        if (string.IsNullOrEmpty(_playerName))
        {
            return BAD_REQUEST.ToString(); // Parameter validation error
        }

        if (string.IsNullOrEmpty(_document))
        {
            return BAD_REQUEST.ToString(); // Parameter validation error
        }

        if (string.IsNullOrEmpty(_accessToken))
        {
            return UNAUTHORIZED.ToString(); // Missing access token
        }

        try
        {
            client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", _accessToken);
            
            
            // Construct the case-insensitive regex pattern
            string regexPattern = $"^(?i){Regex.Escape(“Mexican”)}$”;

            // Build the search criteria with case-insensitive regex
            JObject searchCriteria = new JObject
            {
                { “playerName”, new JObject { { "$regex", regexPattern } } }
            };

            string jsonPayload = searchCriteria.ToString();
            var content = new StringContent(jsonPayload, Encoding.UTF8, "application/json");

            HttpResponseMessage response = await client.PostAsync(url + "/ords/peka/soda/latest/PlayerData?action=query", content);

            if (!response.IsSuccessStatusCode)
            {
                // Map the HTTP status code to the response status code
                return ((int)response.StatusCode).ToString();
            }

            return await response.Content.ReadAsStringAsync();
        }
        catch (HttpRequestException)
        {
            return BAD_GATEWAY.ToString(); // Network error or bad gateway
        }
        catch (Exception)
        {
            return INTERNAL_SERVER_ERROR.ToString(); // General server-side error
        }
    }
}
}

The json document from the Oracle json db:

[
  {
"playerName": "Mexican”,
"totClicks": 12,
"playerCreationDate": "2024-06-22 10:23:40",
"playerId": "bIEQZDEqKb6fDs7HNX6Uq6g86J4N",
"isSignedIn": true,
"nrOfMultiplayerMatches": 0,
"nrNotCompletedMultiplayerMatches": 0,
"totSwappedTriplets": 0,
"totMultiMatchTimeSec": 0,
"totSwappedQuadruplets": 0,
"playerNameHash": "Mexican#2629",
"nrNotCompletedSingleplayerMatches": 0,
"singleMatchesScore": 0,
"lastLogin": "2024-06-22 10:23:40",
"speedPlayerLvl": 0,
"scoreSpeedSinglePlayerMatch": 0,
"nrOfSpeedSinglePlayerMatches": 0,
"isLinked": true,
"singleMatchStats_List": [
  "(0)S_0(1)bIEQZDEqKb6fDs7HNX6Uq6g86J4N(2)0(3)0(4)0(5)0(6)8(7)6/22/2024 10:24:37 AM(8)60(9)34.35301(10)True(11)False(12)False(13)True(14)12(15)4(16)0(17)0(18)False(19)0(20)0"
],
"dbJsonId": "D10F90144EE54864AABEBC1F6E0FA3D8",
"totSingleMatchTime": "00:00:34.3530000",
"isOpenForAnonymousMatches": false,
"playerLvl": 0,
"nrEvenMultiplayerMatches": 0,
"isUpdated2Db": true,
"nrLostMultiplayerMatches": 0,
"hasDoneTutorial": true,
"playerNameFacebook": null,
"isOfflineLogin": false,
"nrWonMultiplayerMatches": 0,
"nrOfLogins": 2,
"isAnonymousLogin": false,
"nrOfflineLogins": 0,
"multiplayerMatchStats_List": null,
"totSwappedPairs": 4,
"nrOfSingleMatches": 1,
"isFacebookLogin": false,
"isUnityLogin": true
  }
]