MySQL Error

[worqy]

Hi.

I'm trying to make a chat application in Vb what uses MySQL database.

Here is the code for the UpdateChat()sub

    Public Sub UpdateChat()
        ' MySQL Adapter
        Dim MySQLAdapter As New MySqlDataAdapter
        ' MySQL query
        Dim SQLQuery = "SELECT * FROM chat;"
        ' "Commanerd"
        Dim Command As New MySqlCommand
        ' Select connection for "commander"
        Command.Connection = MainForm.connection
        ' Set query
        Command.CommandText = SQLQuery
        ' MySQL adapter runs query
        MySQLAdapter.SelectCommand = Command
        ' MySQL (Data)reader
        Dim MyData As MySqlDataReader = Command.ExecuteReader
        MyData.Read()
        If (MyData("Id") <= ChatId) Then
            AddChatMessage(MyData("Sender"), MyData("Message"))
            ChatId = ChatId + 1
        End If
        MyData.Close()
    End Sub

    Public Sub AddChatMessage(ByVal Sender As String, ByVal Message As String)
        MainForm.txtChat.Text = MainForm.txtChat.Text + vbCrLf + Sender + ": " + Message
    End Sub

Now it gives me there error that a connection is allready open with the Datareader (I havea timer running this sub once in a while).
But shouldn't this be fixed with this line of code:

MyData.Close()

?

[amrita]

1.What is the use of
[ ' MySQL adapter runs query
MySQLAdapter.SelectCommand = Command
]

2. Can you not check if connection is not open at the begining and close the connection at the end of the function.

[noahssite]

Don't you need to Open() the connection with the adapter for any of your code to work?

Also here is a SqlDataReader example, I'm not sure how similiar it is to the MySqlDataReader.
http://msdn.microsoft.com/en-us/libr...ader.read.aspx

[techgnome]

You're missing some code... how does UpdateChat get called?

Also, any code related to the adaptor should be taken out... it's not serving any purpose, you're reading the data through the reader which is accessable from the command object.

-tg

[worqy]

Here is the connection code:

Imports MySql.Data.MySqlClient
Public connection As New MySql.Data.MySqlClient.MySqlConnection

Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        connection.ConnectionString = "server=*****;uid=****;pwd=****;database=***;"
        Try
            connection.Open()

        Catch ex As MySql.Data.MySqlClient.MySqlException
            MessageBox.Show(ex.Message, "Error")
        End Try
    End Sub

and the edited UpdateChat

Public Sub UpdateChat()
        ' MySQL Adapter
        Dim MySQLAdapter As New MySqlDataAdapter
        ' MySQL query
        Dim SQLQuery = "SELECT * FROM chat;"
        ' "Commanerd"
        Dim Command As New MySqlCommand
        ' Select connection for "commander"
        Command.Connection = MainForm.connection
        ' Set query
        Command.CommandText = SQLQuery
        ' MySQL (Data)reader
        Dim MyData As MySqlDataReader = Command.ExecuteReader
        MyData.Read()
        If (MyData("Id") <= ChatId) Then
            AddChatMessage(MyData("Sender"), MyData("Message"))
            ChatId = ChatId + 1
        End If
        MyData.Close()
    End Sub

    Public Sub AddChatMessage(ByVal Sender As String, ByVal Message As String)
        MainForm.txtChat.Text = MainForm.txtChat.Text + vbCrLf + Sender + ": " + Message
    End Sub

The "UpdateChat" is called by a timer every 4 seconds

[techgnome]

The "UpdateChat" is called by a timer every 4 seconds

mmm huh.... that's your problem... the timer fired off faster than the code can execute and the connection/reader is still open when it fires off again...

Also, you're still using an adaptor when you don't need to... and you may want to consider using the Uses keyword for your connection and command... also, you may not want to always load everything from your table (unless you're also clearing it out at some point).

-tg

[noahssite]

the timer fired off faster than the code can execute

What you can do to solve this problem is - assuming you are handling the Tick event of the Timer - have your timer set up as so:

Public Sub Timer1_Tick(...)
{
Timer1.Enabled = false
//Perform your code here.
Timer1.Enabled = true
}

[worqy]

Okey I understand, but 4 seconds is to slow anyway so I must think out something else.
Any suggestions how to make a Vb 2008 and MySQL chat?

[Jenner]

Yes, make a server program that handles all chat connections and records to the database every time it receives a response. Client programs connect to the server program. The Server program takes any message it receives from it's clients and puts a timestamp on the message, saves a copy to the database, and relays the message to all other clients.

That's chat design 101 right there actually. The server never has to read data from the database unless it has a client requesting a backlog of messages.

[noahssite]

Why do you need to use a Database at all?

In a chat application here is what happens:

User Sends Message
Message Is Sent To Server
Server Parses Message To See Where It Should Go / What To Do With It.
Server Sends Message To User.
User Receives Message.
Message Is Displayed To User.
User Replies By Sending Back A Message. (Repeat Above)

No Database required - unless you want to store a history of the chat. Even in the case of a history, you can append to a XML file.

[Jenner]

I can see the merits of it to store chat-sessions in a central location. A database is as good a place as any for that.

[worqy]

There was two reasons why I used a database:
1) I have used it before i PHP
2) if I make a TCP chat (or any type of chat that uses a server) that I need a PC or a host to keep the server up 24/7. Thats the problem

[noahssite]

1) I have used it before i PHP

So? Do you have a PHP script viewing the contents of the database? If not then it is irrelevant.

2) if I make a TCP chat (or any type of chat that uses a server) that I need a PC or a host to keep the server up 24/7. Thats the problem

How does that have to do with a Database? A Database will not effect whether or not your server is up 24/7.

EDIT: The only reason I see of to have a Database is for History, and if for example a user sends a message to someone that is offline. When the receiving client turns on the program it will check the Database for any messages sent that it has not yet received. Yet even then, if there is a central server running 24/7 like you said, this would be unnecessary. My scenario would only apply to a chat program where the user is both the client and server. (Direct Connections)

[worqy]

So? Do you have a PHP script viewing the contents of the database? If not then it is irrelevant.

I mean, I have used MySQL databases in PHP, thats why I used it in Vb too...


How does that have to do with a Database? A Database will not effect whether or not your server is up 24/7.

EDIT: The only reason I see of to have a Database is for History, and if for example a user sends a message to someone that is offline. When the receiving client turns on the program it will check the Database for any messages sent that it has not yet received. Yet even then, if there is a central server running 24/7 like you said, this would be unnecessary. My scenario would only apply to a chat program where the user is both the client and server. (Direct Connections)

How about this:
This chat is only a part of a game.
The point of the chat would be that is a "general" chat, where every members chats with each other.

So how would the client also act like the server in that case.
I have done a TCP chat before, with a client and a server, and that could have worked very well for my game, BUT then I would need a computer host that could keep up the server 24/7.

[Jenner]

If it's an online, multiplayer game, then ONE of the clients is acting as the game-controller or server. You can't have all computers doing their own things because they're going to disagree. The server is typically the computer of the person hosting the game since you always need at least one person to initiate the game. Even games with general grouping, there's always a "party leader" and it's his machine that's hosting the game. It's his machine that takes input from the other clients and ultimately makes the call of "that bullet hit you, you are dead" regardless of what the client currently thinks.

Your chat needs to work on the same principle. The hosting computer acts as the chat-server. All clients relay through it. It's perfectly normal for a program to act as both a server and a client, in fact, most programs like this do.

[worqy]

If it's an online, multiplayer game, then ONE of the clients is acting as the game-controller or server. You can't have all computers doing their own things because they're going to disagree. The server is typically the computer of the person hosting the game since you always need at least one person to initiate the game. Even games with general grouping, there's always a "party leader" and it's his machine that's hosting the game. It's his machine that takes input from the other clients and ultimately makes the call of "that bullet hit you, you are dead" regardless of what the client currently thinks.

Your chat needs to work on the same principle. The hosting computer acts as the chat-server. All clients relay through it. It's perfectly normal for a program to act as both a server and a client, in fact, most programs like this do.

By this you mean, one of the players clients schould act like a server?
Because else I would need to have a pc that is on all day long hosting the server. Then every client needs to be enable to be a server.
Lets say like this: 3 persons A,B and C
A comes online, nobody else is online
Then he needs to be the server and client.
B comes online, then he will need to connect to A.
C comes online, connects to A.
A logs off, then B or C needs to be the client.

Kinda messy, an how do B connect to A? B can't know A's IP?
This is why I used a database in the first place.

[Jenner]

Ah! I get it now!

You don't consider your "database" to be a "server"! You're using the terms interchangeably. When WE say "database", that "database" could be ANYWHERE. Most likely, it's on the SAME machine the game is installed on. When YOU say "database", you're only thinking it's some machine on the web somewhere. That's part of the confusion.

The other part of the confusion, is you don't expect the clients to KNOW each other. See, when I was giving my example, I was giving it like a bunch of friends connecting together to play Doom or Quake, or Warcraft 2. You just want a general chat lobby where everyone playing your game, regardless if they're in games together or not, can hang out and chat.

See, your "database" will have to be running on SOME machine that's on 24/7 and connected to the internet. That's the "server" and it will be on 24/7. You're going to need a 24/7 machine no matter how you look at it if you want this to be a "global" chat. All you need to do is write a server program... and put it on that database machine! Better yet, install an IRC server on that database machine, and just write some code in your clients to interface with it. That would be a whole heck of a lot easier.

[worqy]

Ah! I get it now!

You don't consider your "database" to be a "server"! You're using the terms interchangeably. When WE say "database", that "database" could be ANYWHERE. Most likely, it's on the SAME machine the game is installed on. When YOU say "database", you're only thinking it's some machine on the web somewhere. That's part of the confusion.

The other part of the confusion, is you don't expect the clients to KNOW each other. See, when I was giving my example, I was giving it like a bunch of friends connecting together to play Doom or Quake, or Warcraft 2. You just want a general chat lobby where everyone playing your game, regardless if they're in games together or not, can hang out and chat.

See, your "database" will have to be running on SOME machine that's on 24/7 and connected to the internet. That's the "server" and it will be on 24/7. You're going to need a 24/7 machine no matter how you look at it if you want this to be a "global" chat. All you need to do is write a server program... and put it on that database machine! Better yet, install an IRC server on that database machine, and just write some code in your clients to interface with it. That would be a whole heck of a lot easier.

Now you understand!

No, I don't need a 24/7 machine, if I build a MySQL database based chat (That didn't work).

Thats why I tried to build it in the first place, because a SQL database is online 24/7 (Buying a webhost), but if I make it with a server and a client, like TCP communications, I need a real computer that would be online 24/7!

[Jenner]

A webhost IS a real computer! I can write programs and run them on my webhost! Most will let you install other server software such as an IRC server, code repository or even a custom server application. Granted, sometimes, you need the tech on the other end of the phone to know exactly how you want the host configured, but that's fine.

[worqy]

A webhost IS a real computer! I can write programs and run them on my webhost! Most will let you install other server software such as an IRC server, code repository or even a custom server application. Granted, sometimes, you need the tech on the other end of the phone to know exactly how you want the host configured, but that's fine.

Himm... How can i have missed that?
What host are you using?

So you mean, that you can upload a .exe program on your webhost, and have it running 24/7.
Whats the point with VPS's then?

[Jenner]

Most webhosts will allow a certain amount of flexibilities with services. Granted, if it's a unix machine, you won't be able to just upload an EXE or setup package and get it to run; you'll need Microsoft style hosting. Most webhosts also have restricted access to installation of new software; I know I have to call mine and upload my package to a tech and have him install it. I can certainly get something like an IRC server installed or a custom backend program to check licensing though. You probably won't be able to get away with a high-bandwidth program like a voice-chat server without bumping up your service to VPS. The line is kinda fuzzy with what each host will let you get away with; I don't know, maybe our company just got lucky.

With ours, though we're only paying for standard webhosting, FTP and email, we had them configure PHP to allow external linking (CURL extensions I believe?), install a custom license checking system, install a custom data-pulling program that once a day pulls our inventory data from our local server for the website (because doing it via PHP/Perl was timing out and causing problems) and install a small IRC server for website tech support. On the web-end though, we run in a UNIX environment so those programs are Linux based; I doubt that does you much good.

A VPS service is totally unrestricted and allow you to administrative access (i.e. no need to call the tech) to install software, use your bandwidth for whatever you choose, allow multiple user remote login (terminal services) etc... They're not priced much more than webhosts, and if you really want to, you can install a webserver on them as well! VPSWebServer.com and 24Shells have reasonable VPS hosting at $25-$30/mo.

[noahssite]

If you have a Unix based operating system as your web host you can check out this Open Source* project which, according to their website, will allow you to run Windows applications (Executables) on Unix based operating systems.

http://www.winehq.org/

*I am pretty sure its Open Source, you can double check that.