How can I store the individual values of EulerAngles on a Database then re-apply

Questions & Answers
1

I am creating an MMORPG and I’ve got a mysql database which is all designed to copy the design on Unity… at least almost entirely…

I have a Transform table with foreign keys to entries on a Vector3 table which stores X, Y, and Z float values. This much all works. I have stored procedures for updating the location and rotation of the player, as well as for pulling the values.

the issue I’m having is in which values to store IN the database for Rotation… I have spent the last 5+ hours looking online for help… I’m aware that Euler can be represented in many ways and mean the same thing and understand a gimbal lock so please don’t clutter a response with that stuff. Very simply put, I’ve tried storing the x,y,z from Transform.localRotation and from Transform.localEulerAngles (it needs to be local, as it is the camera on the player, child’ed to a child of the player (perhaps this is part of the issue? - using global I’ve also tried and it also fails.)

here’s the code for storing and for getting the rotation (I have a custom Command Method to clean up the number of code lines for my simpler database procedure calls.)

    private void Update()
    {
        if (Input.GetKeyDown(KeyCode.U))
        {
            print("Updating Rotation on Server to : " + character.transform.GetChild(3).GetChild(0).eulerAngles + "

");
PlayerData.Command(“UpdateEulerRotation”, new MySqlParameter {
new MySqlParameter(“inUsername”, PlayerData.GetUsername()),
new MySqlParameter(“inPassword”, PlayerData.GetPassword()),
new MySqlParameter(“inCharacterName”, PhotonNetwork.player.NickName),
new MySqlParameter(“inRotX”, character.transform.GetChild(3).GetChild(0).localEulerAngles.x),
new MySqlParameter(“inRotY”, character.transform.GetChild(3).GetChild(0).localEulerAngles.y),
new MySqlParameter(“inRotZ”, character.transform.GetChild(3).GetChild(0).localEulerAngles.z)
});
}

        if (Input.GetKeyDown(KeyCode.S))
        {
            character.GetComponent<FirstPersonController>().enabled = false;
            PlayerData.Command("GetCharacterEulerRotation", new MySqlParameter[]
            {
                new MySqlParameter("inUsername", PlayerData.GetUsername()),
                new MySqlParameter("inPassword", PlayerData.GetPassword()),
                new MySqlParameter("inCharacterName", PhotonNetwork.player.NickName)
            }, true);

            character.transform.GetChild(3).GetChild(0).localEulerAngles. = new Vector3(
                (float)PlayerData.ResponseTable.Rows[0]["Vector3_X"],
                (float)PlayerData.ResponseTable.Rows[0]["Vector3_Y"],
                (float)PlayerData.ResponseTable.Rows[0]["Vector3_Z"]
                );
            character.GetComponent<FirstPersonController>().enabled = true;
        }
    }

To shorten any concerns, as I stated previously, the “talking to the database” part is working fine. I get back the X, Y, and Z float values individually, but whenever I apply them, the rotation is WAY off.

I will resort to creating an entirely new table to represent quaternions if I must, but I would really prefer to keep it condensed into one vector3 table.

2

First of all you’re using wayy too much “character.transform.GetChild(3).GetChild(0)” which will hit the performances since you’re basically “searching” for the correct child in every line, you should really cache this Transform into a local or class member variable instead of “searching” for the component at every line.

Then you could store the values as string (like VARCHAR(45) in MySql):

Transform characterTfm;
Vector3 rotationVec;

void Update()
{
	if (characterTfm == null)
	{
		characterTfm = character.transform.GetChild(3).GetChild(0);
	}

	if (Input.GetKeyDown(KeyCode.U))
	{
		// ...
		rotationVec = characterTfm.localEulerAngles;
		PlayerData.Command("UpdateEulerRotation", new MySqlParameter[] {
			 new MySqlParameter("inUsername", PlayerData.GetUsername()),
			 new MySqlParameter("inPassword", PlayerData.GetPassword()),
			 new MySqlParameter("inCharacterName", PhotonNetwork.player.NickName),
			 new MySqlParameter("inRotX", rotationVec.x.ToString()),
			 new MySqlParameter("inRotY", rotationVec.y.ToString()),
			 new MySqlParameter("inRotZ", rotationVec.z.ToString())
			 });
		// ...
	}

	if (Input.GetKeyDown(KeyCode.S))
	{
		// ...
		var row = PlayerData.ResponseTable.Rows[0];
		characterTfm.localEulerAngles. = new Vector3(
						 float.Parse(row["Vector3_X"].ToString()),
						 float.Parse(row["Vector3_Y"].ToString()),
						 float.Parse(row["Vector3_Z"].ToString())
						 );
		// ...
	}
}

Anyway it mostly depends on how the rotation is handled by your input controller, that is yiou should know if you need to get/set the local rotation of this child transform or the global rotation of your root player object.

Also remember that it’s not a good idea to let the clients access directly to the MySql database in a MMORPG, instead only your server should access and manipulate it.

3

For my particular situation, it actually had to do with how I was doing my error handling… I have a helper class with a helper method for MySql called Command which puts the MySql request into a try block and returns a string, either returning the MySql Exception if one occurred, or returning a formatted string for debugging, of the response table. (in case I want to see it, but don’t want to store it.)

This is my old method, I’ve updated it since this post. This method is a wrong approach.

internal static string Command(string command, MySqlParameter[] parameters, bool storeResponseTable = false)
{
    if (!string.IsNullOrEmpty(command))
    {
        try
        {
            MySqlCommand cmd = new MySqlCommand(command, Connection) { CommandType = CommandType.StoredProcedure };

            if (parameters.Length > 0)
            {
                for (int i = 0; i < parameters.Length; i++)
                {
                    cmd.Parameters.Add(parameters*);*

}
}
cmd.ExecuteNonQuery();
using (MySqlDataReader responseReader = cmd.ExecuteReader())
{
if (responseReader.HasRows)
{
if (storeResponseTable)
{
ResponseTable = new DataTable(“Stored Response Table”);
ResponseTable.Load(responseReader);
responseReader.Close();
return FormatDataTableToString(ResponseTable);
}
else
{
using (DataTable responseData = new DataTable())
{
responseData.Load(responseReader);
responseReader.Close();
return FormatDataTableToString(responseData);
}
}
}
else
return null;
}
}
catch (MySqlException e)
{
return "MYSQL ERROR: " + e.Message;
}
}
Yeah, anyhow, when I logged out the return of this Command helper method I found my issue… .
“MYSQL ERROR: Unknown column ‘Transform_EulerRotation’ in ‘field list’”
It is a MySql Stored Proc issue… since it ‘error’ed out’, I never got back the new results of the rotation values that were stored, and thus was applying my last response table (the position) to the rotation of the player.
Thank you to @Bunny83 for the response and kindness in his assistance.
I’ve accepted his answer as I feel anyone in a similar situation will find greater help seeing his answer than my niche issue. :stuck_out_tongue: :slight_smile:
Stay Classy Y’all.