Search Unity

  1. Megacity Metro Demo now available. Download now.
    Dismiss Notice
  2. Unity support for visionOS is now available. Learn more in our blog post.
    Dismiss Notice

Connection to MySQL DB - mysql client dll ??

Discussion in 'Scripting' started by BooBi, Oct 7, 2010.

  1. BooBi

    BooBi

    Joined:
    Jan 18, 2010
    Posts:
    534
    Hi,

    I need to work out how to connect a database and send and receive information from an external server (with the database in).

    Like a store with automatic changes of prices or something like that. And a form to complete as well.

    Last year I've made a online form in Quest (booo quest booooo) connected to a database, so pretty much the same...
    It will be on standalone and, in a second time, on Webplayer, So I'll need to script it with all the security stuff right now. If you have some advises...

    In this topic I'll post all my question about theses developments.


    I already found these topics:

    http://forum.unity3d.com/threads/15746-Unity-and-Database-Access

    http://answers.unity3d.com/question...-send-and-recieve-data-from-to-a-mysql-server


    First question (I'm really not an expert in mysql...):

    Where can I download/create/find these dll :

    System.Data.dll and MySql.Data.MySqlClient.dll


    (i'm scripting in C#)

    Thank's for the help

    Boobi
     
    saulojoab likes this.
  2. zumwalt

    zumwalt

    Joined:
    Apr 18, 2007
    Posts:
    2,287
    What you need and where to find them: (Windows)
    (Attached Zip for the following 3 files for sqlite)
    Mono.Data.Sqlite.dll
    System.Data.dll
    sqlite3.dll

    (Attached Zip for the following 2 files for mysql)
    Mysql.data.dll
    System.Data.dll

    Place those libraries in your Assets folder, so now do you need to know "how" to use them or do you understand the code needed to make it work?
     

    Attached Files:

    Last edited: Oct 7, 2010
    fran8919 likes this.
  3. LamentConfig

    LamentConfig

    Joined:
    Sep 28, 2010
    Posts:
    292
    Personally, I'd be very hesitant allowing a webplayer/stand alone access the mysql db directly. This is because you really can't trust anything coming from a client. Just be aware of the issue :) I'd use a script ( php, python, whatever ) for the client to connect to, so the script can perform sanity checks, and check that everything is within expected ranges ( and cleans everything ), and then have the script connect directly to the database. If you are just performing read only stuff, thats awesome, and no issues with that, just make sure the mysql user that the webplayer/stand alone is connecting only has read only rights.

    http://dev.mysql.com/doc/refman/5.1/en/grant.html <-- mysql rights :)
     
  4. BooBi

    BooBi

    Joined:
    Jan 18, 2010
    Posts:
    534
    Thanks for the file and the advises!

    @LamentConfig:

    I'll connect this with php files because I'll need to be able to read (for the prices and models,...), and also to add information in my DB.
    If you have more advise for the security.

    For the security is possible to use a direct connection for the read only and and php file to add new data ? Is it recommendable ? Or should I work only in php ?
    ->amazon-> php books-> visa XD

    @zumwalt:

    Thank's a lot, and if it's not to much to ask yes I'd like some explications or a tutorial because I found some piece of script to use these files, but I want to understand exactly what I'm doing, otherwise I'll have problem later when I'll want to change something in my script...

    What exactly are they ? Did I need to edit them to connect them to my DB ? Or are they just a needed dll to able unity to connect to any DB ?



    Thank's both for your help
     
    Last edited: Oct 8, 2010
  5. zumwalt

    zumwalt

    Joined:
    Apr 18, 2007
    Posts:
    2,287
    Well, those are just the libraries that you need in order to utilize either sqlite or mysql. There is no issue with having the client talk directly to the server other than latency as long as you understand the different methods of creating sql commands and data integrity. I do not use an intermediate bridge like php or what not, I just talk client to server because I have methods that avoid injection issues and I encrypt the data to and from the server. I will spend some time today and throw together a demo project that uses MySQL and document the code then throw it here in the threads for you. The key to avoiding injection for any insert/update or otherwise is to learn and understand parameters for command objects.
     
  6. zumwalt

    zumwalt

    Joined:
    Apr 18, 2007
    Posts:
    2,287
  7. BooBi

    BooBi

    Joined:
    Jan 18, 2010
    Posts:
    534
    Wooow cheers mate !!! that's more than helpfull !

    I'm finishing the graphical part of my viewer today and I'll take a look at this !

    Once again that's really help me a lot, I'm the only one in charge of the unity/3drt projects in my company and I'm developing everything from the scratch by myself, so that will save a lot of time thank's !
     
  8. zumwalt

    zumwalt

    Joined:
    Apr 18, 2007
    Posts:
    2,287
    No problem, I wasn't sure where to begin with it so I figured I would start at the point where someone has downloaded and installed MySQL, has it up and running and simply needs to begin, I also wasn't sure if you were using C# or Unityscript, and since I didn't want to hold up any longer on posting it, I put what I had finished in the threads. Everything works for a local instance so a simple switch of the instance information and it should work for any instance that you have the information for.
     
  9. BooBi

    BooBi

    Joined:
    Jan 18, 2010
    Posts:
    534
    hum...

    I've finish all my viewer, so now I'll connect it to my database...

    First line, first problem, I can't connect myself remotely to the mysql server.

    In the SQL Development no problem I'm connected and I can see my tables...

    In the Server Administration, I'm using the sames parameters, and I have the error "could not establish SSH connection : Error reading SSH protocol banner."
    it asks me a ssh password and I don't have any ssh password...

    I'm a 3drt developer and graphic designer, but I'm really rubbish at servers and web stuffs... I don't even know what's SSH except something useful for the security.

    I understand all the Mysql workbench part but I just can't connect myself as an admin so no create the user so...

    If you have any clue

    Thank's a lot

    BooBi
     
    Last edited: Oct 15, 2010
  10. BooBi

    BooBi

    Joined:
    Jan 18, 2010
    Posts:
    534
    Ok

    I'm doing this to try without a specific user, I created the tables, change the script with the information of my database.

    and I've a problem it says that the access is denied... don't i need to add the port number as well ?

    Why can i connect me with my database ? I'm using the sames information to connect in php myadmin and in the mysql workbench ?

    I don't understand why and that really p*** me off... I really like coding, but the web-server stuffs :X
     
  11. BooBi

    BooBi

    Joined:
    Jan 18, 2010
    Posts:
    534
    I've got this in my unity console now...

    System.NullReferenceException: Object reference not set to an instance of an object
    at MySQLCS.InsertEntries () [0x00012] in F:\Unity\Unity_Tests\06_Database_Connection\Assets\MySQLCS.cs:119
    UnityEngine.Debug:Log(Object)
    MySQLCS:InsertEntries() (at Assets/MySQLCS.cs:154)
    MySQLCS:OnGUI() (at Assets/MySQLCS.cs:94)

    and this

    System.NullReferenceException: Object reference not set to an instance of an object
    at MySQLCS.DeleteEntries () [0x0000c] in F:\Unity\Unity_Tests\06_Database_Connection\Assets\MySQLCS.cs:230
    UnityEngine.Debug:Log(Object)
    MySQLCS:DeleteEntries() (at Assets/MySQLCS.cs:242)
    MySQLCS:OnGUI() (at Assets/MySQLCS.cs:92)



    I noticed that I use my previous schema (which i used in quest) and if I want to create a new schema I can't access denied, but I can create a table or a view.

    My boss says that we have a shared server and the guys from the servers companies says that we need a dedicated server to change the users, so maybe that's why I can only acces etc.. is that true ?

    We've created a new database in mysql 4 this time but the workbench doesn't work with it and I can't create a View in php MyAdmin.
     
    Last edited: Oct 15, 2010
  12. zumwalt

    zumwalt

    Joined:
    Apr 18, 2007
    Posts:
    2,287
    Need to backup one step, forget Unity for a moment, if the workbench has no access to the database on your web server, you have an issue because this is using the default MySQL port (3306 I think), your web host might have the MySQL ports blocked or they might use different ones. If the code works without any problems to a local development box, then it is not the code, take a look at this web link:

    http://www.connectionstrings.com/mysql

    This has a ton of information as to what can go in the string, including the port, so if you have a different port for the MySQL web server, you need to add the port variable into the connection string, check with the ISP to see if the MySQL port is open or not. That web link explains ALL the connection options I think, look through it and see if any of that helps with your current issue.
     
  13. BooBi

    BooBi

    Joined:
    Jan 18, 2010
    Posts:
    534
    ok thank's now it's connected, i was the port (in unity)

    I've got this now in the console :

    MySql.Data.MySqlClient.MySqlException: Duplicate entry '0' for key 1
    at MySql.Data.MySqlClient.MySqlStream.ReadPacket () [0x00000] in <filename unknown>:0
    at MySql.Data.MySqlClient.NativeDriver.GetResult (System.Int32 affectedRow, System.Int32 insertedId) [0x00000] in <filename unknown>:0
    UnityEngine.Debug:Log(Object)
    MySQL:InsertEntries() (at Assets/MySQL.cs:154)
    MySQL:OnGUI() (at Assets/MySQL.cs:94)


    I still can't connect myself in the workbench admin because of the SSH password...

    Do you have any clue about dedicated servers or shared ?


    My day is almost done, I'll work on that monday I'm getting close (really slowly but close...).

    Thank's again for your help.
     
  14. zumwalt

    zumwalt

    Joined:
    Apr 18, 2007
    Posts:
    2,287
    Without knowing what your 'INSERT' command looks like and not knowing if you set the AA (auto increment) for the primary key, this simply looks like your insert is trying to add a record with a key of 0 when there is already a record with the key of 0.
     
  15. BooBi

    BooBi

    Joined:
    Jan 18, 2010
    Posts:
    534
    Yeah!

    It works now !! Thank's a lot, I'll check to create a new user, but it's connected and it sends and receives information

    Cheers
     
  16. sriram90

    sriram90

    Joined:
    Oct 14, 2010
    Posts:
    94
    Hi,

    Am a begineer to unity, i'm trying to create a new database using sqlite3, but probably it's not working....

    can any one say to create database command, i have already tried all those commands like,

    1.$ sqlite3 db_name
    2.create database db_name;
    3. sqlite3 db_name.sql

    but all those commands are not working in my command shell prompt....
     
  17. Veg4s

    Veg4s

    Joined:
    Mar 21, 2012
    Posts:
    1
    Hi,
    sorry for my english.
    I 've been trying to make a program with an Sql Database interaction, but there are some errors.
    I have actually Unity 3.5, i hope you could help me
     
  18. DarkSlash

    DarkSlash

    Joined:
    Sep 30, 2011
    Posts:
    127
    Im using JS and I got this error:

    on this line:

    if (con.State.ToString() != "Open") { con.Open(); }


    what I need to do?
     
  19. GroX0r

    GroX0r

    Joined:
    Mar 26, 2013
    Posts:
    1
    Hello..

    Im using C#, error: Assets/MySQL.cs(71,33): error CS0019: Operator `!=' cannot be applied to operands of type `System.Data.ConnectionState' and `System.Data.ConnectionState'

    Line:

    Libraries:

    MySQL.Data
    System.Data
    System.Drawing

    Help me please.. Thanks

    PD: Versión 4.1.0f4

    Thanks.
     
  20. eliltion

    eliltion

    Joined:
    Feb 26, 2017
    Posts:
    1
    Zumwalt você salvou minha vida
     
  21. bhayes1

    bhayes1

    Joined:
    Apr 9, 2017
    Posts:
    1
    Hey guys I have been searching everywhere. There are many answers offered but none have working links to the libraries needed. A lot of tutorials I cant even begin because I cant reference the libraries I need.

    How do I get them? And do I need separate libraries to have MySQL work after being deployed on an android device?
     
  22. Ikaro88

    Ikaro88

    Joined:
    Jun 6, 2016
    Posts:
    296
    i need that information too!