{"id":2129,"date":"2014-11-02T10:06:18","date_gmt":"2014-11-02T09:06:18","guid":{"rendered":"http:\/\/www.blue-bears.com\/blog\/?p=2129"},"modified":"2014-11-02T11:45:54","modified_gmt":"2014-11-02T10:45:54","slug":"unity-et-mysql","status":"publish","type":"post","link":"http:\/\/www.blue-bears.com\/blog\/?p=2129","title":{"rendered":"Unity et MySQL"},"content":{"rendered":"<ul>\n<li>Un post de blog avec fichier \/ package pour importer\/exporter\u00a0 des donn\u00e9es de\/vers MySQL<!--more--><\/li>\n<li><a title=\"http:\/\/forum.unity3d.com\/threads\/connection-to-mysql-db-mysql-client-dll.63008\/\" href=\"http:\/\/forum.unity3d.com\/threads\/connection-to-mysql-db-mysql-client-dll.63008\/\" target=\"_blank\">http:\/\/forum.unity3d.com\/threads\/connection-to-mysql-db-mysql-client-dll.63008\/<\/a><\/li>\n<li>Fonctionne tr\u00e8s bien (Unity 4.5.5.f1) SANS LES DLL livr\u00e9es dans l&rsquo;exemple et le Blog ci-dessous.<\/li>\n<\/ul>\n<p>[pastacode lang=\u00a0\u00bbc\u00a0\u00bb message=\u00a0\u00bbMysQL Int\u00e9gration In Unity\u00a0\u00bb highlight=\u00a0\u00bb\u00a0\u00bb provider=\u00a0\u00bbmanual\u00a0\u00bb]<\/p>\n<pre><code>using UnityEngine;\r\nusing MySql.Data;\r\nusing MySql.Data.MySqlClient;\r\nusing System;\r\nusing System.Data;\r\nusing System.Collections;\r\nusing System.Collections.Generic;\r\n\r\npublic class MySQLCS : MonoBehaviour\r\n{\r\n    \/\/ In truth, the only things you want to save to the database are dynamic objects\r\n    \/\/ static objects in the scene will always exist, so make sure to set your Tag \r\n    \/\/ based on the documentation for this demo\r\n\r\n    \/\/ values to match the database columns\r\n    string ID, Name, levelname, objectType;\r\n    float posx, posy, posz, tranx, trany, tranz;\r\n\r\n    bool saving = false;\r\n    bool loading = false;\r\n    \/\/ MySQL instance specific items\r\n    string constr = &#34;Server=localhost;Database=demo;User ID=demo;Password=demo;Pooling=true&#34;;\r\n    \/\/ connection object\r\n    MySqlConnection con = null;\r\n    \/\/ command object\r\n    MySqlCommand cmd = null;\r\n    \/\/ reader object\r\n    MySqlDataReader rdr = null;\r\n    \/\/ object collection array\r\n    GameObject[] bodies;\r\n    \/\/ object definitions\r\n    public struct data\r\n    {\r\n        public int UID;\r\n        public string ID, Name, levelname, objectType;\r\n        public float posx, posy, posz, tranx, trany, tranz;\r\n    }\r\n    \/\/ collection container\r\n    List&lt;data&gt; _GameItems;\r\n    void Awake()\r\n    {\r\n        try\r\n        {\r\n            \/\/ setup the connection element\r\n            con = new MySqlConnection(constr);\r\n\r\n            \/\/ lets see if we can open the connection\r\n            con.Open();\r\n            Debug.Log(&#34;Connection State: &#34; + con.State);\r\n        }\r\n        catch (Exception ex)\r\n        {\r\n            Debug.Log(ex.ToString());\r\n        }\r\n\r\n    }\r\n\r\n    void OnApplicationQuit()\r\n    {\r\n        Debug.Log(&#34;killing con&#34;);\r\n        if (con != null)\r\n        {\r\n            if (con.State.ToString() != &#34;Closed&#34;)\r\n                con.Close();\r\n            con.Dispose();\r\n        }\r\n    }\r\n\r\n    \/\/ Use this for initialization\r\n    void Start()\r\n    {\r\n\r\n    }\r\n\r\n    \/\/ Update is called once per frame\r\n    void Update()\r\n    {\r\n\r\n    }\r\n\r\n\r\n    \/\/ gui event like a button, etc\r\n    void OnGUI()\r\n    {\r\n        if (GUI.Button(new Rect(10, 70, 50, 30), &#34;Save&#34;) &amp;&amp; !saving)\r\n        {\r\n            saving = true;\r\n            \/\/ first lets clean out the databae\r\n            DeleteEntries();\r\n            \/\/ now lets save the scene information\r\n            InsertEntries();\r\n            \/\/ you could also use the update if you know the ID of the item already saved\r\n\r\n            saving = false;\r\n        }\r\n        if (GUI.Button(new Rect(10, 110, 50, 30), &#34;Load&#34;) &amp;&amp; !loading)\r\n        {\r\n            loading = true;\r\n            \/\/ lets read the items from the database\r\n            ReadEntries();\r\n            \/\/ now display what is known about them to our log\r\n            LogGameItems();\r\n            loading = false;\r\n        }\r\n    }\r\n\r\n    \/\/ Insert new entries into the table\r\n    void InsertEntries()\r\n    {\r\n        prepData();\r\n        string query = string.Empty;\r\n        \/\/ Error trapping in the simplest form\r\n        try\r\n        {\r\n            query = &#34;INSERT INTO demo_table (ID, Name, levelname, objectType, posx, posy, posz, tranx, trany, tranz) VALUES (?ID, ?Name, ?levelname, ?objectType, ?posx, ?posy, ?posz, ?tranx, ?trany, ?tranz)&#34;;\r\n            if (con.State.ToString() != &#34;Open&#34;)\r\n                con.Open();\r\n            using (con)\r\n            {\r\n                foreach (data itm in _GameItems)\r\n                {\r\n                    using (cmd = new MySqlCommand(query, con))\r\n                    {\r\n                        MySqlParameter oParam = cmd.Parameters.Add(&#34;?ID&#34;, MySqlDbType.VarChar);\r\n                        oParam.Value = itm.ID;\r\n                        MySqlParameter oParam1 = cmd.Parameters.Add(&#34;?Name&#34;, MySqlDbType.VarChar);\r\n                        oParam1.Value = itm.Name;\r\n                        MySqlParameter oParam2 = cmd.Parameters.Add(&#34;?levelname&#34;, MySqlDbType.VarChar);\r\n                        oParam2.Value = itm.levelname;\r\n                        MySqlParameter oParam3 = cmd.Parameters.Add(&#34;?objectType&#34;, MySqlDbType.VarChar);\r\n                        oParam3.Value = itm.objectType;\r\n                        MySqlParameter oParam4 = cmd.Parameters.Add(&#34;?posx&#34;, MySqlDbType.Float);\r\n                        oParam4.Value = itm.posx;\r\n                        MySqlParameter oParam5 = cmd.Parameters.Add(&#34;?posy&#34;, MySqlDbType.Float);\r\n                        oParam5.Value = itm.posy;\r\n                        MySqlParameter oParam6 = cmd.Parameters.Add(&#34;?posz&#34;, MySqlDbType.Float);\r\n                        oParam6.Value = itm.posz;\r\n                        MySqlParameter oParam7 = cmd.Parameters.Add(&#34;?tranx&#34;, MySqlDbType.Float);\r\n                        oParam7.Value = itm.tranx;\r\n                        MySqlParameter oParam8 = cmd.Parameters.Add(&#34;?trany&#34;, MySqlDbType.Float);\r\n                        oParam8.Value = itm.trany;\r\n                        MySqlParameter oParam9 = cmd.Parameters.Add(&#34;?tranz&#34;, MySqlDbType.Float);\r\n                        oParam9.Value = itm.tranz;\r\n                        cmd.ExecuteNonQuery();\r\n                    }\r\n                }\r\n            }\r\n        }\r\n        catch (Exception ex)\r\n        {\r\n            Debug.Log(ex.ToString());\r\n        }\r\n        finally\r\n        {\r\n        }\r\n    }\r\n\r\n    \/\/ Update existing entries in the table based on the iddemo_table\r\n    void UpdateEntries()\r\n    {\r\n        prepData();\r\n        string query = string.Empty;\r\n        \/\/ Error trapping in the simplest form\r\n        try\r\n        {\r\n            query = &#34;UPDATE demo_table SET ID=?ID, Name=?Name, levelname=?levelname, objectType=?objectType, posx=?posx, posy=?posy, posz=?posz, tranx=?tranx, trany=?trany, tranz=?tranz WHERE iddemo_table=?UID&#34;;\r\n            if (con.State.ToString() != &#34;Open&#34;)\r\n                con.Open();\r\n            using (con)\r\n            {\r\n                foreach (data itm in _GameItems)\r\n                {\r\n                    using (cmd = new MySqlCommand(query, con))\r\n                    {\r\n                        MySqlParameter oParam = cmd.Parameters.Add(&#34;?ID&#34;, MySqlDbType.VarChar);\r\n                        oParam.Value = itm.ID;\r\n                        MySqlParameter oParam1 = cmd.Parameters.Add(&#34;?Name&#34;, MySqlDbType.VarChar);\r\n                        oParam1.Value = itm.Name;\r\n                        MySqlParameter oParam2 = cmd.Parameters.Add(&#34;?levelname&#34;, MySqlDbType.VarChar);\r\n                        oParam2.Value = itm.levelname;\r\n                        MySqlParameter oParam3 = cmd.Parameters.Add(&#34;?objectType&#34;, MySqlDbType.VarChar);\r\n                        oParam3.Value = itm.objectType;\r\n                        MySqlParameter oParam4 = cmd.Parameters.Add(&#34;?posx&#34;, MySqlDbType.Float);\r\n                        oParam4.Value = itm.posx;\r\n                        MySqlParameter oParam5 = cmd.Parameters.Add(&#34;?posy&#34;, MySqlDbType.Float);\r\n                        oParam5.Value = itm.posy;\r\n                        MySqlParameter oParam6 = cmd.Parameters.Add(&#34;?posz&#34;, MySqlDbType.Float);\r\n                        oParam6.Value = itm.posz;\r\n                        MySqlParameter oParam7 = cmd.Parameters.Add(&#34;?tranx&#34;, MySqlDbType.Float);\r\n                        oParam7.Value = itm.tranx;\r\n                        MySqlParameter oParam8 = cmd.Parameters.Add(&#34;?trany&#34;, MySqlDbType.Float);\r\n                        oParam8.Value = itm.trany;\r\n                        MySqlParameter oParam9 = cmd.Parameters.Add(&#34;?tranz&#34;, MySqlDbType.Float);\r\n                        oParam9.Value = itm.tranz;\r\n                        MySqlParameter oParam10 = cmd.Parameters.Add(&#34;?UID&#34;, MySqlDbType.Int32);\r\n                        oParam10.Value = itm.UID;\r\n\r\n                        cmd.ExecuteNonQuery();\r\n                    }\r\n                }\r\n            }\r\n        }\r\n        catch (Exception ex)\r\n        {\r\n            Debug.Log(ex.ToString());\r\n        }\r\n        finally\r\n        {\r\n        }\r\n    }\r\n\r\n    \/\/ Delete entries from the table\r\n    void DeleteEntries()\r\n    {\r\n        string query = string.Empty;\r\n        \/\/ Error trapping in the simplest form\r\n        try\r\n        {\r\n            \/\/ optimally you will know which items you want to delete from the database\r\n            \/\/ using the following code and the record ID, you can delete the entry\r\n            \/\/-----------------------------------------------------------------------\r\n            \/\/ query = &#34;DELETE FROM demo_table WHERE iddemo_table=?UID&#34;;\r\n            \/\/ MySqlParameter oParam = cmd.Parameters.Add(&#34;?UID&#34;, MySqlDbType.Int32);\r\n            \/\/ oParam.Value = 0;\r\n            \/\/-----------------------------------------------------------------------\r\n            query = &#34;DELETE FROM demo_table WHERE iddemo_table&#34;;\r\n            if (con.State.ToString() != &#34;Open&#34;)\r\n                con.Open();\r\n            using (con)\r\n            {\r\n                using (cmd = new MySqlCommand(query, con))\r\n                {\r\n                    cmd.ExecuteNonQuery();\r\n                }\r\n            }\r\n        }\r\n        catch (Exception ex)\r\n        {\r\n            Debug.Log(ex.ToString());\r\n        }\r\n        finally\r\n        {\r\n        }\r\n    }\r\n\r\n    \/\/ Read all entries from the table\r\n    void ReadEntries()\r\n    {\r\n        string query = string.Empty;\r\n        if (_GameItems == null)\r\n            _GameItems = new List&lt;data&gt;();\r\n        if (_GameItems.Count &gt; 0)\r\n            _GameItems.Clear();\r\n        \/\/ Error trapping in the simplest form\r\n        try\r\n        {\r\n            query = &#34;SELECT * FROM view_demo&#34;;\r\n            if (con.State.ToString() != &#34;Open&#34;)\r\n                con.Open();\r\n            using (con)\r\n            {\r\n                using (cmd = new MySqlCommand(query, con))\r\n                {\r\n                    rdr = cmd.ExecuteReader();\r\n                    if (rdr.HasRows)\r\n                        while (rdr.Read())\r\n                        {\r\n                            data itm = new data();\r\n                            itm.UID = int.Parse(rdr[&#34;iddemo_table&#34;].ToString());\r\n                            itm.ID = rdr[&#34;ID&#34;].ToString();\r\n                            itm.levelname = rdr[&#34;levelname&#34;].ToString();\r\n                            itm.Name = rdr[&#34;Name&#34;].ToString();\r\n                            itm.objectType = rdr[&#34;objectType&#34;].ToString();\r\n                            itm.posx = float.Parse(rdr[&#34;posx&#34;].ToString());\r\n                            itm.posy = float.Parse(rdr[&#34;posy&#34;].ToString());\r\n                            itm.posz = float.Parse(rdr[&#34;posz&#34;].ToString());\r\n                            itm.tranx = float.Parse(rdr[&#34;tranx&#34;].ToString());\r\n                            itm.trany = float.Parse(rdr[&#34;trany&#34;].ToString());\r\n                            itm.tranz = float.Parse(rdr[&#34;tranz&#34;].ToString());\r\n                            _GameItems.Add(itm);\r\n                        }\r\n                    rdr.Dispose();\r\n                }\r\n            }\r\n        }\r\n        catch (Exception ex)\r\n        {\r\n            Debug.Log(ex.ToString());\r\n        }\r\n        finally\r\n        {\r\n        }\r\n    }\r\n\r\n    \/\/\/ &lt;summary&gt;\r\n    \/\/\/ Lets show what was read back to the log window\r\n    \/\/\/ &lt;\/summary&gt;\r\n    void LogGameItems()\r\n    {\r\n        if (_GameItems != null)\r\n        {\r\n            if (_GameItems.Count &gt; 0)\r\n            {\r\n                foreach (data itm in _GameItems)\r\n                {\r\n                    Debug.Log(&#34;UID: &#34; + itm.UID);\r\n                    Debug.Log(&#34;ID: &#34; + itm.ID);\r\n                    Debug.Log(&#34;levelname: &#34; + itm.levelname);\r\n                    Debug.Log(&#34;Name: &#34; + itm.Name);\r\n                    Debug.Log(&#34;objectType: &#34; + itm.objectType);\r\n                    Debug.Log(&#34;posx: &#34; + itm.posx);\r\n                    Debug.Log(&#34;posy: &#34; + itm.posy);\r\n                    Debug.Log(&#34;posz: &#34; + itm.posz);\r\n                    Debug.Log(&#34;tranx: &#34; + itm.tranx);\r\n                    Debug.Log(&#34;trany: &#34; + itm.trany);\r\n                    Debug.Log(&#34;tranz: &#34; + itm.tranz);\r\n                }\r\n            }\r\n        }\r\n    }\r\n\r\n    \/\/\/ &lt;summary&gt;\r\n    \/\/\/ This method prepares the data to be saved into our database\r\n    \/\/\/ \r\n    \/\/\/ &lt;\/summary&gt;\r\n    void prepData()\r\n    {\r\n        bodies = GameObject.FindGameObjectsWithTag(&#34;Savable&#34;);\r\n        _GameItems = new List&lt;data&gt;();\r\n        data itm;\r\n        foreach (GameObject body in bodies)\r\n        {\r\n            itm = new data();\r\n            itm.ID = body.name + &#34;_&#34; + body.GetInstanceID();\r\n            itm.Name = body.name;\r\n            itm.levelname = Application.loadedLevelName;\r\n            itm.objectType = body.name.Replace(&#34;(Clone)&#34;, &#34;&#34;);\r\n            itm.posx = body.transform.position.x;\r\n            itm.posy = body.transform.position.y;\r\n            itm.posz = body.transform.position.z;\r\n            itm.tranx = body.transform.rotation.x;\r\n            itm.trany = body.transform.rotation.y;\r\n            itm.tranz = body.transform.rotation.z;\r\n            _GameItems.Add(itm);\r\n        }\r\n        Debug.Log(&#34;Items in collection: &#34; + _GameItems.Count);\r\n    }\r\n}<\/code><\/pre>\n<p>[\/pastacode]<\/p>\n<p>Cr\u00e9er sur le serveur Web un User (demo dans le code ci-dessus + PSW en clair =&gt; Danger MAXIIIII !!!!!) cr\u00e9er la base MySQL avec le code suivant :<\/p>\n<p>[pastacode lang=\u00a0\u00bbsql\u00a0\u00bb message=\u00a0\u00bbCode Mysql Base Demo\u00a0\u00bb highlight=\u00a0\u00bb\u00a0\u00bb provider=\u00a0\u00bbmanual\u00a0\u00bb]<\/p>\n<pre><code>-- phpMyAdmin SQL Dump\r\n-- version 3.3.7deb7\r\n-- http:\/\/www.phpmyadmin.net\r\n--\r\n-- Serveur: localhost\r\n-- G\u00e9n\u00e9r\u00e9 le : Dim 02 Novembre 2014 \u00e0 11:30\r\n-- Version du serveur: 5.1.73\r\n-- Version de PHP: 5.3.3-7+squeeze19\r\n\r\nSET SQL_MODE=&#34;NO_AUTO_VALUE_ON_ZERO&#34;;\r\n\r\n\r\n\/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT *\/;\r\n\/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS *\/;\r\n\/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION *\/;\r\n\/*!40101 SET NAMES utf8 *\/;\r\n\r\n--\r\n-- Base de donn\u00e9es: &#039;Demo&#039;\r\n--\r\n\r\n-- --------------------------------------------------------\r\n\r\n--\r\n-- Structure de la table &#039;demo_table&#039;\r\n--\r\n-- Cr\u00e9ation: Dim 02 Novembre 2014 \u00e0 11:02\r\n-- Derni\u00e8re modification: Dim 02 Novembre 2014 \u00e0 11:13\r\n--\r\n\r\nDROP TABLE IF EXISTS demo_table;\r\nCREATE TABLE IF NOT EXISTS demo_table (\r\n  iddemo_table int(11) NOT NULL AUTO_INCREMENT,\r\n  ID varchar(45) DEFAULT NULL,\r\n  `Name` varchar(45) DEFAULT NULL,\r\n  levelname varchar(45) DEFAULT NULL,\r\n  objectType varchar(45) DEFAULT NULL,\r\n  posx float DEFAULT NULL,\r\n  posy float DEFAULT NULL,\r\n  posz float DEFAULT NULL,\r\n  tranx float DEFAULT NULL,\r\n  trany float DEFAULT NULL,\r\n  tranz float DEFAULT NULL,\r\n  PRIMARY KEY (iddemo_table)\r\n) ENGINE=MyISAM  DEFAULT CHARSET=latin1;\r\n\r\n-- --------------------------------------------------------\r\n\r\n--\r\n-- Doublure de structure pour la vue &#039;view_demo&#039;\r\n--\r\nDROP VIEW IF EXISTS `view_demo`;\r\nCREATE TABLE IF NOT EXISTS `view_demo` (\r\n`iddemo_table` int(11)\r\n,`ID` varchar(45)\r\n,`Name` varchar(45)\r\n,`levelname` varchar(45)\r\n,`objectType` varchar(45)\r\n,`posx` float\r\n,`posy` float\r\n,`posz` float\r\n,`tranx` float\r\n,`trany` float\r\n,`tranz` float\r\n);\r\n-- --------------------------------------------------------\r\n\r\n--\r\n-- Structure de la vue &#039;view_demo&#039;\r\n--\r\nDROP TABLE IF EXISTS `view_demo`;\r\n\r\nCREATE VIEW demo.view_demo AS\r\nSELECT * FROM demo_table<\/code><\/pre>\n<p>[\/pastacode]<\/p>\n<ul>\n<li>\u00a0Restreindre les privil\u00e8ges MYSQL de l&rsquo;utilisateur demo \u00e0 : Select\/Insert\/Delete\/Update\/ sur les Datas seulement (par sur les tables !!!).<\/li>\n<li>Attacher le script \u00e0 un objet (Terrain dans mon cas)<\/li>\n<li>2 boutons apparaissent en Mode Game=&gt;Play<\/li>\n<li>En SAVE : charge bien les donn\u00e9es des Objets Tagu\u00e9s \u00ab\u00a0Savable\u00a0\u00bb dans la base Demo.<\/li>\n<li>En LOAD : on voit d\u00e9filer les param\u00e8tres r\u00e9cup\u00e9r\u00e9s dans l&rsquo;onglet Console.\n<ul>\n<li>Il suffit de les passer au objets<\/li>\n<\/ul>\n<\/li>\n<li><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Un post de blog avec fichier \/ package pour importer\/exporter\u00a0 des donn\u00e9es de\/vers MySQL<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-2129","post","type-post","status-publish","format-standard","hentry","category-non-classe"],"_links":{"self":[{"href":"http:\/\/www.blue-bears.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/2129","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.blue-bears.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.blue-bears.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.blue-bears.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.blue-bears.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2129"}],"version-history":[{"count":11,"href":"http:\/\/www.blue-bears.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/2129\/revisions"}],"predecessor-version":[{"id":2141,"href":"http:\/\/www.blue-bears.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/2129\/revisions\/2141"}],"wp:attachment":[{"href":"http:\/\/www.blue-bears.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2129"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.blue-bears.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2129"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.blue-bears.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2129"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}