• 0
Sign in to follow this  
Tashology

MySql connection

Question

Hello,

I've been trying to remove the default saving and convert it all to saving through mysql. However whenever I try to create a new account, the client hangs for about 30 seconds and then the client disconnects.

I'm using the mysql.dll, supplied with MySql. Any ideas why it's not working?

Thanks,

Tashology

Share this post


Link to post
Share on other sites

5 answers to this question

Recommended Posts

  • 0

Could you post your code you use? I have an old library for MySQL in C# which I can translate into VB.NET for you to use. I can also make a short video on how to use it with Orion+.

Share this post


Link to post
Share on other sites
  • 0
14 hours ago, The Crzy Doctor said:

Could you post your code you use? I have an old library for MySQL in C# which I can translate into VB.NET for you to use. I can also make a short video on how to use it with Orion+.

Yeah sure. I'm using the MySQL.dll that comes with the MySQL download. I used it to write a C# application before and I've basically ported it over to VB.NET but you're more than welcome to take a look and see what's up.

 

LoadBank:

        Dim connx = "Server=localhost;Database=tashdb;Uid=root;Pwd=%Pa55w0rd;"
        Dim connectx As MySqlConnection
        Try
            'Create the connector
            connectx = New MySqlConnection(connx)
            'Open the database
            connectx.Open()
            'Create the command
            Dim cmdx As MySqlCommand = New MySqlCommand()
            Dim Commandx As MySqlCommand
            Commandx = connectx.CreateCommand()
            cmdx.Connection = connectx

            'Get the user's ID from their name
            cmdx.CommandText = "Select * from mmoUsers where Username=@usern"
            cmdx.Parameters.AddWithValue("@usern", Player(Index).Login)
            cmdx.Connection = connectx

            Dim ready As MySqlDataReader
            ready = cmdx.ExecuteReader()
            While ready.Read()
                bankstring = ready.GetString("Bank")
            End While
            connectx.Close()
        Catch
            Throw
        End Try

 

SaveBank

  Dim conn = "Server=localhost;Database=tashdb;Uid=root;Pwd=%Pa55w0rd;"
        Dim connect As MySqlConnection
        Try
            'Create the connector
            connect = New MySqlConnection(conn)
            'Open the database
            connect.Open()
            'Create the command
            Dim cmd As MySqlCommand = New MySqlCommand()
            Dim Command As MySqlCommand
            Command = connect.CreateCommand()
            cmd.Connection = connect

            cmd.CommandText = "UPDATE mmoUsers SET Bank=@bank WHERE Username=@user"
            cmd.Parameters.AddWithValue("@user", Player(Index).Login)
            cmd.Parameters.AddWithValue("@bank", bankstring)
            cmd.ExecuteNonQuery()

        Catch
            Throw
        End Try

 

Thanks,

Tashology

Share this post


Link to post
Share on other sites
  • 0

I imagine it's because you're creating a brand new connection every time you're doing something, not closing them consistently and creating objects you don't even use. There's so many differences in these two snippets I'd dread to think what the rest of your code looks like. ???

 

Don't destroy and re-create connections unless you absolutely have to. It'll just delay the process considerably. Either have a single connection that does everything, or if you're concerned about performance under a heavy load have a resource pool of connections that get used round-robin to deal with database queries.

Share this post


Link to post
Share on other sites
  • 0

basicly, you open the connection on server start, and close it when it goes down.

 

good luck with it btw, I have a copy somewhere that uses sqllite, and man, its a bitch xD

Share this post


Link to post
Share on other sites
  • 0

Right, you need a database context class that does this all when the server loads, shutdowns, or autosaves. Opening and closing a connection takes alot of resources that you should not be doing in the middle of a game loop. An example of how you would do this ideally when you start, stop or save is:

DbContext context = new DbContext(GameData);
context.Connect();
context.LoadBanks();
Etc.
Etc.
context.Close();

And your context might look something like this:

public class DbContext 
{
    GameData data;
    Connection conn;

    public DbContext(GameData data)
    {
        this.data = data
    }

    public void Connect()
    {
        // Do the stuff to connect.
    }

    public void Close()
    {
        // Do the stuff to loeave.
    }

    public void LoadBanks()
    {
        var query = "SELECT * FROM banks;"
        var result = MySql.Query(query);

        foreach(var row in result)
        {
            data.Banks.Update(row);
        }
    }
}

That is just an idea though, you would have to actually code the Context, GameData, and other related classes correctly, but that is how most people recommend you poll databases, again polling a database requires a fair bit of resources that WILL lag down your game loop.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this