{"id":11,"date":"2016-07-29T21:30:00","date_gmt":"2016-07-30T01:30:00","guid":{"rendered":"http:\/\/naplandgames.com\/blog\/2016\/07\/29\/unity-3d-to-ibm-db2-connection-tutorial\/"},"modified":"2018-12-18T09:09:56","modified_gmt":"2018-12-18T14:09:56","slug":"unity-3d-to-ibm-db2-connection-tutorial","status":"publish","type":"post","link":"https:\/\/naplandgames.com\/blog\/2016\/07\/29\/unity-3d-to-ibm-db2-connection-tutorial\/","title":{"rendered":"Unity 3D to IBM DB2 Connection Tutorial"},"content":{"rendered":"
<\/div>\n

\"logo-ibm-db2\"I recently had the challenge of connecting to an IBM DB2 database via Unity. After some struggles with setting up DB2 and getting the user access all correct, it ended up being fairly easy. This quick tutorial will show you how to do it and point out some of the pitfalls you might encounter along the way.<\/p>\n

Step 1 – Set up IBM DB2<\/h3>\n
To try this out you’ll need to be able to connect to a DB2 database. I didn’t have access to any so I set one up on my Windows 10 PC. Any of the DB2 installations should work fine for this, but for my task, I went with IBM DB2 Express-C<\/a>. That’s their free community edition. It’s probably missing some more advanced features, but I really just needed a simple database set up to test the connection to.<\/div>\n
<\/div>\n
\n

The installation isn’t difficult, but the installer UI looks like something out of the 90s for Windows 3.1. Basically, just follow the prompts for the typical installation. You’ll be asked to create a user and password. This actually creates a new user on the Windows OS. It wasn’t clear that would happen at the time of installation and I probably could have gone with my standard Windows user credentials. This is an important factor to note. After setup this account that you choose is the one with default rights to create the database.<\/p>\n

So after lots of digging around, I found out this was a problem I had and was slowing me down. Eventually, I found out that the Windows user you’re logged in as has to belong to a user group that DB2 set up. It sets up two user groups: DB2ADMNS <\/span>and DB2USERS<\/span>. I made it so that my main Windows account belonged to both groups. This can be done via lusrmgr.msc<\/span>. Right click on the Start Menu, select Run and type in lusrmgr.msc\u00a0<\/span>and you’ll get the Local Users and Groups Manager window. In here you can select your username, right click to get to properties, then in the Member Of tab you can add the user to the user group. I think that only DB2ADMNS\u00a0<\/span>group is required, but I did both.<\/p>\n

Once you get that all corrected you may also have to make some adjustments to DB2’s configuration. This is done via the newly installed DB2 Command Window. I always run the Administrator\u00a0version so that I’m sure I have all the access I need. Once you launch this you can run the command db2 get dbm\u00a0cfg. <\/span>This will print out the config details in the terminal window. If this doesn’t work right away then try <\/span>db2start<\/span>, which starts the DB2 service. Now you’ll want to change a few of these settings. specifically: <\/span>SYSADM_GROUP, SYSCTRL_GROUP, SYS_MAINT_GROUP, <\/span>and <\/span>SYSMON_GROUP. <\/span>You’ll want all of these to be set to <\/span>DB2ADMNS <\/span>which is the user group in Windows that has full access to modify the database. Use the following command to set these:<\/span>
\ndb2 update dbm cfg using SYSADM_GROUP DB2ADMNS<\/span>
\nDo this for each setting.<\/span><\/p>\n

Now things should be pretty happy. You can try creating a new database with:
\ndb2 create database YOURDATABASENAME AUTOMATIC\u00a0STORAGE YES<\/span>
\nIf all is set up correctly then you should get a success message.<\/p>\n

Other useful DB2 commands.<\/a><\/p>\n

I don’t overly enjoy working via the command line terminal so I went and downloaded IBM Data Studio. It was a pretty simple setup and I just went with the defaults. From there you can create your databases, tables, import data and run queries. One major annoyance I found with this is that when you type a lowercase name for a table or column name this program will surround it with double quotes. Which means that if you create a table named mytable <\/span>then to query it you’ll need to use “mytable” <\/span>in your queries. Not cool, IBM, not cool. I eventually just named everything with caps, but Data Studio prepares SQL statements for you that you can edit and remove the unnecessary quotes when creating your tables. I still can’t get Data Studio to create a database, so I do that by command line. Not sure what the problem is, I’ve even tried by running it as Administrator and even in the user account that it set up. No go. Command line, no problem. Go figure…<\/p>\n

Also when you set up DB2 you have the option to create a sample database. My installation said this had failed, but after going in to Data Studio the database was present.<\/p>\n

Last caveat: You’ll need to make sure that your user has access privileges to the database. Do that in Data Studio via the database’s properties.<\/p>\n

Step 2 – Connect from Unity<\/h3>\n
Now that you have your DB2 installation all working fine (this took me what felt like forever) we can hop on over to Unity and set up access to the database. This part is pretty simple.<\/div>\n
<\/div>\n
I created a fresh Unity project and added a Plugins folder to the project. We’ll need to add two DLL files to this folder:<\/div>\n
System.Data.dll<\/span><\/div>\n
System.EnterpriseServices.dll<\/span><\/div>\n
Unity-friendly versions of these can be found in your Unity installation folder like this:<\/div>\n
E:Unity_5.3.4p5EditorDataMonolibmono2.0<\/span><\/div>\n
<\/div>\n
Of course, your directory will be different from mine, but the subdirectories should be the same. Just locate those two DLLs and drag and drop them into your project’s plugin folder. I just left the settings on the marked for “Any Platform”, but I’ve only tested on Windows so I’m not sure if these libraries will work on Mac or Linux. I highly doubt they will work on WebGL, maybe<\/i>\u00a0on Android, probably not on iOS (because what does? right?).<\/div>\n
<\/div>\n
Now that’s all done we can finally get to my favorite part: code!<\/div>\n
For a test I simply created a new MonoBehaviour class and attached it to an empty GameObject in my scene. The code below provides an example of making a simple query and reading the results as strings. For a real integration of this you’ll want to convert the results to useable object types. A good example of this can be found here<\/a>. For my purposes it was just good enough to prove a connection worked. And here’s the code:<\/div>\n
\n
using UnityEngine;\r\nusing System;\r\nusing System.Text;\r\n\/\/C:\\Program Files\\IBM\\SQLLIB\\BIN may need to be in environment path.\r\nusing System.Data.Odbc;\r\npublic class DB2Service : MonoBehaviour\r\n{\r\n    void Start()\r\n    {\r\n        Test();\r\n    }\r\n \r\n        void Test()\r\n    {      \r\n        \/\/ Set up a connection string. The format is pretty specific, just change the YOUR...HERE to real values.\r\n        string connectionStringODBC =\r\n            \"Driver={IBM DB2 ODBC DRIVER};Database=YOURDATABASENAMEHERE;Hostname=localhost;Port=50000;Protocol=TCPIP;Uid=YOURUSERNAMEHERE;Pwd=YOURPASSWORDHERE;\";\r\n        \/\/ Make the connection and open it\r\n        OdbcConnection odbcCon = new OdbcConnection(connectionStringODBC);\r\n        odbcCon.Open();\r\n \r\n        \/\/ Try out a simple command\/query - make sure to change SOMEREALTABLENAME to your table's name\r\n        OdbcCommand command = new OdbcCommand(\"SELECT COUNT(*) FROM SOMEREALTABLENAME\", odbcCon);\r\n        int count = Convert.ToInt32(command.ExecuteScalar());\r\n        Debug.Log(\"count: \" + count);\r\n \r\n        \/\/ Try a full select query\r\n        OdbcCommand command2 = new OdbcCommand(\"SELECT * FROM SOMEREALTABLENAME\", odbcCon);\r\n        StringBuilder sb = new StringBuilder();\r\n        using (OdbcDataReader reader = command2.ExecuteReader())\r\n        {\r\n            \/\/ Add the column names to the string builder\r\n            for (int i = 0; i < reader.FieldCount; i++)\r\n            {\r\n                sb.Append(reader.GetName(i));\r\n                if (i < reader.FieldCount - 1)\r\n                    sb.Append(\",\");\r\n            }\r\n \r\n            sb.AppendLine();\r\n \r\n            \/\/ Step through the query's results and add those to the string builder.\r\n            while (reader.Read())\r\n            {\r\n                \/\/ Separate each column with a comma\r\n                for (int i = 0; i < reader.FieldCount; i++)\r\n                {\r\n                    sb.Append(reader.GetString(i).Trim());\r\n                    if (i < reader.FieldCount - 1)\r\n                        sb.Append(\",\");\r\n                }\r\n                sb.AppendLine();\r\n \r\n            }\r\n \r\n            \/\/ Output the results to the console\r\n            Debug.Log(sb.ToString());\r\n        }\r\n \r\n        \/\/ Close up that connection!\r\n        odbcCon.Close();\r\n    }\r\n}<\/pre>\n
 <\/p>\n<\/div>\n
\n
That’s it. Hopefully the code is self-explanatory with all of the comments. I’ve set up a repository of this you can grab from GitHub that has a simple UI so you can do a full build and test it out on various machines or modify it to your own purposes. Check it out here.<\/a><\/p>\n
I hope this saves someone from the many headaches I encountered over the past few days of trying to get this to work. There are a lot of outdated examples on the internet and none specific to Unity. Next up I have to test this with some other database management systems (DBMS) such as MySQL, SQLLite, Oracle, and SQL Server. Should be a fun time setting those up!<\/p>\n
<\/div>\n
\"vuzop_video_cover_image\"<\/a>I was testing this for an additional feature of an app I’m working on called vuzop. It is a data visualization tool that allows you to view your data in 3D or with a VR headset. Check out more information on vuzop here.<\/a><\/p>\n
As always,
\nThanks for reading!<\/p>\n<\/div>\n
<\/div>\n<\/div>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
I recently had the challenge of connecting to an IBM DB2 database via Unity. After some struggles with setting up DB2 and getting the user access all correct, it ended up being fairly easy. This quick tutorial will show you how to do it and point out some of the pitfalls you might encounter along the way. Step 1 – Set up IBM DB2 To try this out you’ll need to be able to connect to a DB2 database. I didn’t have access to any so I set one up on my Windows 10 PC. Any of the DB2 installations…<\/p>\n
Continue readingUnity 3D to IBM DB2 Connection Tutorial<\/span><\/a><\/div>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":82,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"a3_pvc":{"activated":true,"total_views":1949,"today_views":0},"_links":{"self":[{"href":"https:\/\/naplandgames.com\/blog\/wp-json\/wp\/v2\/posts\/11"}],"collection":[{"href":"https:\/\/naplandgames.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/naplandgames.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/naplandgames.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/naplandgames.com\/blog\/wp-json\/wp\/v2\/comments?post=11"}],"version-history":[{"count":6,"href":"https:\/\/naplandgames.com\/blog\/wp-json\/wp\/v2\/posts\/11\/revisions"}],"predecessor-version":[{"id":387,"href":"https:\/\/naplandgames.com\/blog\/wp-json\/wp\/v2\/posts\/11\/revisions\/387"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/naplandgames.com\/blog\/wp-json\/wp\/v2\/media\/82"}],"wp:attachment":[{"href":"https:\/\/naplandgames.com\/blog\/wp-json\/wp\/v2\/media?parent=11"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/naplandgames.com\/blog\/wp-json\/wp\/v2\/categories?post=11"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/naplandgames.com\/blog\/wp-json\/wp\/v2\/tags?post=11"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}