koobas.hobune.stream
The question is answered, right answer was accepted
Photon SQL Matchmaking - Operation Failed ReturnCode -2(SQL logic error or missing database near "LIMIT"
The error in full is:
Operation failed: OperationResponse 225: ReturnCode: -2 (SQL logic error or missing database near "LIMIT": syntax error). Parameters: {} Server: MasterServer UnityEngine.Debug:LogError(Object) NetworkingPeer:OnOperationResponse(OperationResponse) (at Assets/Photon/Photon Unity Networking/Plugins/PhotonNetwork/NetworkingPeer.cs:1621) ExitGames.Client.Photon.PeerBase:DeserializeMessageAndCallback(Byte[]) ExitGames.Client.Photon.EnetPeer:DispatchIncomingCommands() ExitGames.Client.Photon.PhotonPeer:DispatchIncomingCommands() PhotonHandler:Update() (at Assets/Photon/Photon Unity Networking/Plugins/PhotonNetwork/PhotonHandler.cs:157)
I'm trying to create a matchmaking system using the SQL Lobbytype in Photon. Not having 'LIMIT' in my code at all I'm at a loss for what the problem could be. I've tried not using button input and simply stating C0 = DUEL or C1 = Map1 but it's giving me the same error. This only occurs when the second player tries to connect, the first player simply gets the 'no match found' message before it creates a new room. Could anyone explain to me what this 'LIMIT' is?
Here's my relevant code:
/// <summary>
/// Find a game within the right properties using Structured Query Language.
/// </summary>
public void MakeSqlMatchMakingJoinAttempt()
{
if (m_JoinAttempt < 10)
{
string lobbyFilter = CreateSqlSearchString();
PhotonNetwork.JoinRandomRoom(null, 10, MatchmakingMode.FillRoom, new TypedLobby("sqlLobby", LobbyType.SqlLobby), lobbyFilter);
}
else
{
// Create a new room when none is found within 10 attempts.
CreateSqlMatchMakingRoom();
}
}
/// <summary>
/// Create a room.
/// </summary>
public void CreateSqlMatchMakingRoom()
{
string roomName = "RoomName" + Random.Range(1, 9999).ToString(); // TODO: Number rooms by instance for better management.
// Set room options for the room to be created.
RoomOptions roomOptions = new RoomOptions();
roomOptions.IsOpen = true;
roomOptions.IsVisible = true;
roomOptions.MaxPlayers = 10;
roomOptions.CustomRoomProperties = new ExitGames.Client.Photon.Hashtable();
roomOptions.CustomRoomProperties.Add("C0", selectedModes[Random.Range(0, selectedModes.Count)]);
roomOptions.CustomRoomProperties.Add("C1", selectedMaps[Random.Range(0, selectedMaps.Count)]);
roomOptions.CustomRoomPropertiesForLobby = new string[] { "C0", "C1" };
TypedLobby sqlLobby = new TypedLobby("sqlLobby", LobbyType.SqlLobby);
PhotonNetwork.CreateRoom(roomName, roomOptions, sqlLobby);
Debug.Log(roomOptions.CustomRoomProperties);
}
/// <summary>
/// Used by the Gamemode Toggles to add gamemodes to the search query.
/// </summary>
/// <param name="gamemode"></param>
public void SelectMode(string gamemode)
{
string query = gamemode;
// If the mode is already selected, unselect it.
if (selectedModes.Contains(query)) { selectedModes.Remove(query); }
// Otherwise add the selection to the query.
else { selectedModes.Add(query); }
foreach (string q in selectedModes)
{
Debug.Log(q);
}
}
/// <summary>
/// Used by the Map Toggles to add maps to the search query.
/// </summary>
/// <param name="gamemode"></param>
public void SelectMap(string map)
{
string query = map;
// If the mode is already selected, unselect it.
if (selectedMaps.Contains(map)) { selectedMaps.Remove(map); }
// Otherwise add the selection to the query.
else { selectedMaps.Add(query); }
foreach (string q in selectedMaps)
{
Debug.Log(q);
}
}
public string CreateSqlSearchString()
{
// Combine the selections of gamemodes, maps and playercount into a single string and include a calculation for the player's skillrating.
string searchQuery = "( C0 = " + string.Join(" OR C0 =", selectedModes.ToArray()) + ") AND "
+ "( C1 = " + string.Join(" OR C1 =", selectedMaps.ToArray()) + ")";
//AND " + TODO: Skillrating.
// Create string to determine highest and lowest skillRating to search for.
// Return the SearchQuery used in finding or creating a new room/match.
return searchQuery;
}
I've changed my code a bit and I no longer have the LI$$anonymous$$IT error, but the game still fails to connect two players to the same room. ![This is after I've connected with a build and created a room, it fails to find a room and then makes a new one.][1] [1]: https://i.imgur.com/3CBVJkj.png
The first log being the search query string and the last being the CustomRoomProperties.
I'm not familiar with Photon. However "sqlFilter" should be a filter string. We don't know how that string is used and the documentation isn't very clear about that. However what you pass as search string doesn't make much sense in anyway. First of all you're missing the closing bracket for your "selectedMaps" string. So the statement will of course cause an error since you don't have matching brackets. Second from what i read in other posts the string you pass is simply added as WHERE clause to the query. However in a where clause you have to compare a column with a value. You just concat several string values with "OR". This doesn't make much sense.
You would need to compare the "C1" column with each of your desired map names and "C0" with your desired modes. Also note that you did not enclose the actual string values in quotation marks. So it should be something like this:
string searchQuery = "( C0 = \"" + string.Join("\" OR C0 = \"", selectedModes.ToArray())
+ "\") AND " + "( C1 = \"" + string.Join("\" OR C1 = \"", selectedMaps.ToArray()) + "\")";
The LIMIT clause most likely comes after the where clause in the actual SQL query. Note that the fact that you get such an error from the server gives me shivers. That means the whole server concept is not protected against SQL injection. Of course allowing the user to pass custom where clauses is useful and versatile but since a user sends in this information it can't be trusted. If there's not an actual sanity check for the string a user could erase or delete the whole database.
So it's the \\ that I'm missing? I'm not quite sure what it does exactly.
I've added the C0 = bit to the string arrays so I don't have to repeat myself on that. ' From what I understand I want a WHERE C0 = a OR C0 = b OR C0 = c for as many options as I have selected.
No. It's important to actually wrap your values in quotation marks. So it has to be
C0 = "your value" or C0 = "another value"
Didn't you see the escaped " i've put into the strings? When you want to include a quotation mark inside a string literal you have to escape it with a backslash like this: \"
I see. Now I've made it so it gives such input through the buttons in the menu. If you toggle the Duel button it adds "DUEL" to the query making it C0 = "Duel". But it still won't connect two players to the same room. I've changed my code here as well, do you see the problem?
