How to setup an SQL to Chat Server
This guide will provide you every information you need to setup a system where you can send and receive chat messages from and SQL database using the Ozeki Chat Server. This way, you could exploit the full potential of the SQL Database management language on your stored messages. You could search through them, sort them and have other applications to work with them. The Chat server that you are setting up now could manage your corporate communication. It could send text, files, pictures or even videos to any chat partner. Follow this step-by-step tutorial to have a working Chat Server with the support of SQL Database. You can use most of the frequently used SQL softwares. To complete this tutorial, you will need to have a basic understanding if the SQL language. The process should not take longer than 10 minutes.
On Figure 1, you could see how the SQL Database server will be implemented into the Chat Server. It will serve the reason of a huge storage unit for your outgoing and incoming messages. You application will take the incoming messages from the SQL Database and put the outgoing messages into the SQL Database. The Chat server will also work from it.
Database servers supported by Ozeki Chat Server
You can use an MSSQL Express database for chat messaging. Follow these pages to send or receive chat messages through Ozeki Chat server's Database User. All you have to do is insert or read data rows from the appropriate SQL table by setting INSERT INTO and SELECT statements.
See how to send chat message from an MSSQL Express database
The connection between Oracle Database and Ozeki Chat server is a perfect solution for chat messaging. Ozeki Chat server's Database User makes sure that incoming chat messages can be inserted while outgoing chat messages can be selected from database tables.
See how to send chat message from an Oracle SQL database
You can combine Ozeki Chat server with a MySQL database to send, receive and store chat messages. On these pages you can see how to install and configure the connection with the proper connection string and how to create the recommended database structure.
See how to send chat message from a MySQL database
This chapter gives you great opportunity to see how to send and receive chat messages through pre-created PostgreSQL database tables. You just need to connect to them with a Database User of Ozeki Chat server. Do not forget to provide the ODBC driver connection string for the user.
See how to send chat message from a PostgreSQL database
See how to send and receive chat messages through an SAP SQL Anywhere server with the Database User of Ozeki Chat server. Here you can find a connection string and short CREATE TABLE statements to get started. You can send messages by inserting new message records.
See how to send chat message from a SAP SQL Anywhere database
You can simply start to send and receive chat messages through Ozeki Chat server's Database User by using Microsoft Access database tables. All you have to do is insert or read data rows from the appropriate SQL table by configuring INSERT INTO and SELECT statements for the user.
See how to send chat message from a Microsoft Access database
Step 1 - Setup a database connection
The first thing you need to do is to setup a new Chat user for the connection with MySQL. To do this, first, open the Chat Server located on the desktop of the Ozeki OS. If you have opened it, proceed to the next step. You could find the icon easily with the help of Figure 2.
Now on this window, you can get to the list of the users that are currently able to login to the chat server. Do this by clicking the Users button on the top-left corner of the window. You can see the button with the help of Figure 3.
Here, you can see all the users who have access to your chat server. On the top of the page, there is a button titled Create new Chat user. Click it and it will take you to the user configuration window. You can see where to click with the help of Figure 4.
There are two types of chat user that can be added to the Ozeki Chat server. You can see both of them on the side menu on the right side. The first is the traditional user who can use the computer to send messages to their chat partners, or use it to send messages to automated systems. The other one which is the Chat service, is a non-human service which can receive messages from other applications, or users, and can handle automated systems triggered by messages. Make sure to choose the Chat service, because this will be the communication tunnel between you and the MySQL database of your choice. You can see the two option on Figure 5.
The Ozeki Chat is a versatile application which can work well together with many database management software. On Figure 6, you can see some of them. In this case, please select the one your will be using to manage your databases. In the case of this example, we chose MySQL.
The last step of the creation of the database application is to connect it to your database server by filling the fields of the Connection Settings, that you can see on Figure 7. Here you must give all details about the database that you want to connect to. The first two fields are to identify the database server. If you left them to default when setting up the database server, you can leave the values here default as well. Then you must type the name of the database that you want to connect to and your user ID with a password that you use within the database server. If you filled all fields, you could just click OK to create the database application.
Step 2 - Setup Database tables
Now you have a Chat User that can be used to communicate with the MySQL application. Now you need to create the tables, the Chat will use to communicate with the database. You could do this from the Ozeki OS Control Panel. To do that, open the control panel and search for connections. If you click it, you will find the connections that the Ozeki Gateway works with. Search for the one starting with SQL. If you have it, click the Details button, as you can see on Figure 8.
By default, the Chat server uses the ozekimessagout table to send messages, and the incoming messages will be inserted into the ozekimessagein table. To have a working system, you need to create these tables. Go to the SQL tab on the top of the details panel. here you will be able to give SQL commands to the MySQL Server. You don’t have to write the code for the tables, since we provide you the code which you only have to copy-paste into the SQL tab and click the execute button, as you can see on Figure 9.
CREATE TABLE ozekimessagein (
id int(11) NOT NULL auto_increment,
sender varchar(255) default NULL,
receiver varchar(255) default NULL,
msg text default NULL,
senttime varchar(100) default NULL,
receivedtime varchar(100) default NULL,
operator varchar(100) default NULL,
msgtype varchar(160) default NULL,
reference varchar(100) default NULL,
PRIMARY KEY (id)
) charset=utf8;
ALTER TABLE ozekimessagein ADD INDEX (id) ;
CREATE TABLE ozekimessageout (
id int(11) NOT NULL auto_increment,
sender varchar(255) default NULL,
receiver varchar(255) default NULL,
msg text default NULL,
senttime varchar(100) default NULL,
receivedtime varchar(100) default NULL,
reference varchar(100) default NULL,
status varchar(20) default NULL,
msgtype varchar(160) default NULL,
operator varchar(100) default NULL,
errormsg varchar(250) default NULL,
PRIMARY KEY (id)
) charset=utf8;
ALTER TABLE ozekimessageout ADD INDEX (id);
Step 3 - Add MySQL connection to the Chat client
Now you have the right tables and the communicating Chat user. To start using it, you need to add the SQL Chat service as a chat partner inside the Ozeki Chat. To do that, open the chat and navigate to your chat partner list. On the top, between the Home and the About button, you will find the Add button. Click it and the Find Friends window will pop-up. You can find the button easily with the help of Figure 10.
To Find Friend, you need to start typing their name into the search field. It will look for a partner in the server users. If you find the SQL Chat Service, please add it with the button located on the left of the partners name. You can see the chat service and the add button on Figure 11.
Step 4 - Send chat message to a database
Finally you can send message form the Chat client to the database server. Open the conversation and send your first message. You will see the message appeas in the Chat conversation (Figure 13).
If you have everything set-up, you test message will be visible in the MySQL application. Every incoming message should be inserted into the ozekimessagein table. To check these messages, you can start a query in your database application. So, open the application, and run a Select command what will query the incoming messages and Execute it. If the execution was successful, you will be able to see the incoming message, as you seen on Figure 14.
Step 5 - Send chat message from a database
With all connections set up properly in Ozeki Chat server, now you can start managing chat messages from a database. Now, you will be able to see, how you can send an chat message by using only one SQL command. By default, Chat server uses ozekimessageout table to send messages.
Sending a message can be done by simply just inserting the message into the ozekimessageout table. The Chat server periodically checks this table and sends the newly added messages. To insert a message into this table, just type the corresponding INSERT command as you can see it in Figure 16. In the textbox, you can change the values which represent the receiver and the message itself. When you finished with writing the message, just Execute it and your message is about to be sent.
You can alter the receiver address with different symbols, which will affect the sending mode. On Figure 16, you could see that we used a % character as the end of the address. It means that the Chat server will send the message to all of the stations of the other user. If a user is logged in on multiple devices, all of them will get the message.
Finally you are able to see that message will received in the Chat client conversation as the Figure 17 shows.
More information
+44 1206 854 512