koobas.hobune.stream
How do I properly send binary data (byte[]) to a MySQL database?
Hello!
I'm having some issues regarding sending data (In my case, a replay) from a Unity web client to a MySQL database. The replays are built of an array of floats. Each frame of the replay takes up three floats, x position, y position and z rotation.
How do I properly save this data to a MySQL database?
I usually send data in this way:
Unity:
myForm.AddField("fieldname","MyString");
WWW upload = new WWW("myScript.php",myForm);
php/SQL:
if(isset($_POST("fieldname"))){
$myString = $_POST("fieldname");
$query = "INSERT INTO myTable (myColumn) VALUES('$myString')";
}
However, this is limited to string data or other data saved as string (Such as a float with a value of 2.45f being saved as "2.45"). Saving data as a string is simple, but it takes a LOT of space, since I'm saving frames of a replay, so I have to save it in binary form.
You can convert a float to a latin1 encoded string. A float has 32 bits of information, and a char has 8 bits. This means each float can be converted to a string with 4 characters.
Let's say we have a float value of -4.5367
Splitting this into its 4 bytes, we can interpret them as chars:
BIN DEC Char
________________________
11000000 = 192 = À
10010001 = 145 = ‘
00101100 = 44 = ,
10100101 = 165 = ¥
This means we can use "À‘,¥" to represent this float value.
I got the conversion to work, but sending this doesn't seem to work, as the replays are so long that a character that breaks the code appears almost definitely every single time. (Characters like these: [\,'"´$]), breaking the SQL statement or the php code.
I then tried using myForm.AddBinaryField, saving the floats as a byte array, but the php/SQL code for that one seems really confusing. When I ended up with something I thought would work, it didn't.
I would be really happy if you could help me out with this!
Thanks!
When you have a question, please ask a question and do not post an answer to a 7 year old, already answered question.
You retrieve it from your server just like any other data or file. You create a php file which returns the content of the blob / data.
Sounds like you are using the correct approach, using WWWForm.AddBinaryData. I don't know what problem you are having on the PHP side of things... Perhaps send a byte array to your PHP that contains the bytes 0,1,2,3,4.....ff and check you can decode that on the PHP side. Then try sending the four-byte sequences 00010203, 04050607, 08090a0ab, etc and check you can decode those. Unity Answers is not a PHP site, so try looking at http://stackoverflow.com/questions/10596356/how-to-obtain-the-post-data-in-binary-of-php.
Thanks! I can solve this using $_FILES ins$$anonymous$$d, as the AddBinaryData is sent as a file.
WWW class constructor accepts postData:byte[] where you could pack all your data, and then just parse it on the server side.
This link will help you. http://www.devindia.biz/how-to-post-and-get-json-data-to-a-service-in-unity-2/
I have also done replay saving to mySql. I scaled my floats with 2^18 to get ints that will fit in 32 bit since I never have a float bigger than +-8192.0... (not ideal however if your values span is very large)
Put it into memory with
System.Array.Copy(BitConverter.GetBytes((int)(vPos.x * (65536 * 4))), 0, data, iOffs + 12, 4);
Retreive from memory with
vPos.x = BitConverter.ToInt32(i_pMem, iOffset + 12) / (65536 * 4.0f);
Then I converted the byte array (the binary replay data) to/from base64, and that is what is stored in the db.
From db to binary
byte[] bytes = System.Convert.FromBase64String(www.downloadHandler.text);
From binary to base64
string base64 = System.Convert.ToBase64String(bytes);
