OZEKI
[pic]
OZEKI IP SMS
SMS messaging over the IP network using a GSM driver
/ user manual /
Last change: 11th April, 2005.
Document version: 1.0.4
Document length: 48 pages
© Ozeki Informatics Ltd.
Ozeki Proprietary and Confidential
The information in this document is subject to change without notice and describes only the product defined in the introduction of this documentation. This document is intended for the use of Ozeki's customers only for the purposes of the agreement under which the document is submitted, and no part of it may be reproduced or transmitted in any form or means without the prior written permission of Ozeki. The document has been prepared to be used by professional and properly trained personnel, and the customer assumes full responsibility when using it. Ozeki welcomes customer comments as part of the process of continuous development and improvement of the documentation.
The information or statements given in this document concerning the suitability, capacity, or performance of the mentioned hardware or software products cannot be considered binding but shall be defined in the agreement made between Ozeki and the customer. However, Ozeki has made all reasonable efforts to ensure that the instructions contained in the document are adequate and free of material errors and omissions. Ozeki will, if necessary, explain issues which may not be covered by the document.
Ozeki's liability for any errors in the document is limited to the documentary correction of errors. OZEKI WILL NOT BE RESPONSIBLE IN ANY EVENT FOR ERRORS IN THIS DOCUMENT OR FOR ANY DAMAGES, INCIDENTAL OR CONSEQUENTIAL (INCLUDING MONETARY LOSSES), that might arise from the use of this document or the information in it.
This document and the product it describes are considered protected by copyright according to the applicable laws.
Ozeki logo is a registered trademark of Ozeki Informatics Ltd.
Other product names mentioned in this document may be trademarks of their respective companies, and they are mentioned for identification purposes only.
Copyright © Ozeki Informatics Ltd 2005. All rights reserved.
© Ozeki Informatics Ltd. 2 (69) Ozeki Proprietary and Confidential
Contents
1. Contents 3
2. About this document 4
2.1. Summary of changes 4
2.2. References 5
3. Introduction 5
4. System requirements for running the Ozeki Message Server with an GSM driver 5
4.3. 5.1 Architectural elements of Ozeki SMS 6
Hardware and software requirements 8
Installing Ozeki Message Server 8
Step2: Connect your phone to the PC 9
Step3: Installing the application 10
Step5: Starting the GSM modem connection 18
Step6: Sending your first SMS 18
Hardware and software requirements 19
Installing Ozeki Message Server 20
Step 1: Installing the application 20
Starting the Data call connection 29
Receiving your first data file 30
Microsoft SQL Server 32
Tips: 32
MySQL 33
Interbase 34
Oracle 35
Example for sending a message: 36
PostgresSQL 36
About this document
This document describes the UCP interface of the Ozeki IP-SMS software. UCP is a protocol that transfers messages between applications and the Short Message Service Center (SMS Center) of the GSM Service provider over an IP link. This link can be a leased line or the Internet. UCP/EMI is version 3.5 or 4 of the Universal Computer Protocol / External Machine Interface protocol. This document is intended for operator personnel involved in daily operations of the Ozeki IP SMS Software and for application developers.
1 Summary of changes
|Date released |Version |Changes |
| 2005-01-03 |v6.0.0.0 |The first development release of Ozeki Message Server 6 |
|2005-01-14 |v6.0.1.0 | Improvements in Ozeki Message Server v6.0.1.0 |
| 2005-02-01 |v6.0.1.2 |Improvements in Ozeki Message Server v6.0.1.2 |
|2005-02-09 |v6.0.1.4 |Improvements in Ozeki Message Server v6.0.1.4 |
|2005-02-14 |v6.0.1.5 |Improvements in Ozeki Message Server v6.0.1.5 |
|2005-02-20 |v6.0.0.6 |Improvements in Ozeki Message Server v6.0.0.6 |
|2005-02-23 |v6.0.0.8 |Improvements in Ozeki Message Server v6.0.0.8 |
|2005-02-25 |v6.0.0.9 |Improvements in Ozeki Message Server v6.0.0.9 |
|2005-03-06 |v6.0.1.10 |Improvements in Ozeki Message Server v6.0.1.10 |
|2005-03-14 |v6.0.1.11 |Improvements in Ozeki Message Server v6.0.1.11 |
|2005-03-14 |v6.0.2.12 |Improvements in Ozeki Message Server v6.0.2.12 |
|2005-03-17 |v6.0.2.14 |Improvements in Ozeki Message Server v6.0.2.14 |
|2005-03-17 |v6.0.2.15 |Improvements in Ozeki Message Server v6.0.2.15 |
|2005-03-26 |v6.0.2.16 |Improvements in Ozeki Message Server v6.0.2.16 |
|2005-03-27 |v6.0.2.17 |Improvements in Ozeki Message Server v6.0.2.17 |
|2005-03-31 |v6.0.2.18 |Improvements in Ozeki Message Server v6.0.2.18 |
|2005-04-01 |v6.0.3.1 |Improvements in Ozeki Message Server v6.0.3.1 |
|2005-04-02 |v6.0.3.2 |Improvements in Ozeki Message Server v6.0.3.2 |
|2005-04-03 |v6.0.3.3 |Improvements in Ozeki Message Server v6.0.3.3 |
|2005-04-07 |v6.0.3.4 |Improvements in Ozeki Message Server v6.0.3.4 |
2 References
IP SMS technology information
UCP product information
Introduction
If you plan to send or receive a larger number of SMS messages, it is worth to consider using the Internet. Over the Internet (or over a private IP network, such as a leased line), it is possible to connect directly to the GSM operator’s SMS Center (SMSC) Figure 1. The SMSC is responsible for receiving, storing and delivering the SMS messages in a GSM telephone network. The benefits of direct connection to this service are great:
A large number of messages can be sent of received in a short time. The sender phone number can be freely modified. The availability of the service can be monitored.
If you work with great volumes of messages, this solution is cheaper, faster and more reliable than the alternative solution of using a GSM handy connected to a computer.
[pic]
System requirements for running the Ozeki Message Server with an GSM driver
|Operating System |Windows 2000, Xp, 2003 |
| |Internet Explorer 6. (by reason for xml library) |
|Minimal hardware |Pentium III. 1GHz processor |
| |500Mb HDD |
| |256Mb RAM |
|Network |Public Internet or a leased line can be used. The bandwidth should have a minimum capacity of 128kbps in the link |
|connect |to the service provider. |
|Other |Database server (MySql, PostgresSql, Sybase, Oracle, or other SQL based database server) is recommended |
|Software |Ozeki IP SMS UCP installation Package (CD or downloaded file) |
4.2 The most important features
- Delivery reports
- Connection link management
- Can be run as windows service
- Creates a log files
- SQL - SMS gateway
- FILES - SMS gateway
- HTTP- SMS gateway
- IPC – SMS gateway for COM and SOAP/WSDL
- Windows client connection
4.3 The package contains the following components
|The message engine |The main application that handles message queues, permissions and operates the drivers and plugins. |
|The UCP driver |It connects to the SMSC (Short Message Service Center) of the GSM service provider through an IP connection. |
|SMS - SQL gateway |Makes it possible to store the incoming messages a database table. Another table is used to send outgoing |
| |messages. |
|SMS – Files gateway |It provides a way to send and receive short messages (SMS) by simply creating text files. |
|Windows client |Provides a user interface for management and office use. |
5 Technical information
1 Architectural elements of Ozeki SMS
The Ozeki Message server is a multi-user message delivery engine, that can handle various different message types. To make the engine more efficient and the internal architecture has been designed using the following guidelines.
It uses an envelope system inside the new engine. This way it is able to handle various message types such as SMS, E-mail, MMS, Windows MSN messages, ICQ messages, etc. and future message types.
It has a multi-user architecture. Separate message queues and folders are set up for each user. The users cannot disturb each other, by taking up too much system resources. Security was improved and accounting was added, which helps cost control.
Advanced link management. Often message transport is not reliable. For example the network link between the SMSC and the server can be broken or a GSM phone attached to the PC can stop responding after a certain amount of time. The new engine constantly monitors the transport links and it takes action if the link is broken. (It automatically reconnects if necessary and it notifies the administrator, etc)
Advanced message management. The engine tries to send a message several times before it marks it undeliverable. It handles delivery reports, and informs the users/plugins about change in message states. Old messages can be deleted or put in an archive folder automatically to manage disk space resources.
The GUI and the engine are two separate applications. The engine runs as a Windows service. The engine is a small and fast program. It does not spend time with GUI updates or other subsidiary tasks. The GUI communicates with the engine over HTTP, which enables remote configuration.
The drivers and plugins are placed into DLL-s. Context switching between different processes is slow, and memory requirements are high in a multiprocess messaging architecture. In our new messaging software all the functionality is executed into a single process and native DLL calls are used to communicate between the components and the engine. This is much faster then the previously used Microsoft COM/DCOM IPC communication. The drivers and plugins are running in separate threads instead of separate processes.
Run time configuration and component installations. The messaging service does not
have to be stopped to change the configuration or to install/uninstall new drivers or plugins.
5.2 Ozeki SMS interfaces
The primary reason why software developers choose the Ozeki SMS software is because they want to save time and effort when implementing a messaging solution. Ozeki SMS is a reliable service that runs in the background. It provides easy to use interfaces for software developers the make development faster. Software developers can easily find a way to communicate with the Ozeki SMS application. There are several options, they can use. Please take a look at the alternatives.
5.2.1 SQL – SMS gateway
If you use the SQL-SMS gateway interface of Ozeki SMS, you can send and receive SMS messages with the help of SQL queries. To use this interface you need to have a database server (e.g.: Oracle, MSSql, MySql, PostgresSql, Sybase). The OZEKI SMS Software will connect to your database server and check a database table periodically for outgoing SMS messages. To send an SMS message, all you need to do is insert a record into the database table. If an SMS message is received another database table is used. The Ozeki software inserts the message to this database table.
5.2.2 FILE – SMS gateway
The FILE-SMS interface provides a way to send and receive short messages (SMS) by simply creating text files. To use this interface, two directories must be created: one for outgoing and one for incoming messages. The Ozeki SMS software will read the contents of the outgoing directory periodically. If it finds a text file containing a message it sends the message and deletes the file. For incoming a messages the other directory is used. If a message arrives a txt file is created in the incoming directory.
5.2.3 HTTP – SMS gateway
With the HTTP-SMS interface, standard HTTP requests (GET, POST) can be used to send and receive SMS messages. Ozeki SMS can operate in two modes if this interface is used. It can act as an HTTP server or as an HTTP client. If it is used as an HTTP server, the client application can make the HTTP queries to send and download the messages. In the client mode mode Ozeki SMS makes an HTTP request when an incoming message arrives.
5.2.4 IPC – SMS gateway (COM, SOAP)
Ozeki SMS can use various inter-process communication methods to send and receive SMS messages. The server provides a Common Object Model (COM) interface, which means that clients implementing this interface can attach to it to receive events and to be able to call procedures on the server. The Simple Object Access Protocol (SOAP) over HTTP can also be used. This protocol provides a standard web service interface for application developers. The features of this interface can be queried using the WSDL protocol.
6 Installation guide
INSTALLATION GUIDE - GSM Modem Driver
Ozeki Message Sever is a software-based solution for you to benefit from advanced communications functions with the help of a mobile phone. You can easily connect your mobile phone to a compatible PC for sending and receiving short messages (SMS). As soon as the phone is attached, Ozeki Message Server enables you to send, receive and manage SMS messages in various ways. You can use the graphical user interface or one of the built-in plugins to handle the messages.
This guide describes you how to install the Ozeki Message Server software onto your PC and how to start using it. For more details about the use of the software contact our on-line support at the following e-mail address: sms@ozeki.hu.
Hardware and software requirements
To install and run the Ozeki Message Server, you need the following tools/devices available:
|Suitable mobile phone |A list of supported phones can be seen on the following page: |
| |supported phones |
|Data cable |You need a phone-to-pc data cable |
| |or an Infra Red port (IR) |
| |or a Bluetooth connection |
|Windows PC |Intel compatible PC |
| |Windows 95,98,Me, 2000,Xp |
| |20 Mb free hard disk space |
|Ozeki Message Server |Ozeki Message Server Intallation Package (CD or downloaded |
Installing Ozeki Message Server
During the installation, various selections can be made. You will find step-by-step information in the following section. If you want to quit the Setup program without installing or uninstalling, just click Cancel and the Exit Setup window will offer you the possibility of quitting the Setup program. Click Exit Setup to quit Setup . If you want to rerun Setup after having quit the installation before it had been completed, follow the installation steps normally as if you were installing the program for the first time.
Quick installation steps :
|1.|Set up the GSM device. |
|2.|Connect your phone to the computer. |
|3.|Exit all Windows programs. |
|4.|Insert the CD-ROM in the CD-ROM drive. |
|5.|Click the Windows START button. |
|6.|Select the Run command. The Run dialog box appears on the screen. Type in the following command line: |
| |D:\setup |
| |In the above command line, "D:\" is the CD-ROM drive |
|7.|Click OK or press Enter . Wait while the system initializes Setup. The Welcome window of the Setup is |
| |then displayed. |
|8.|Click Next to continue with Setup. |
Step1: Setting up your GSM phone
In order for the SMS server to work properly the following settings should be made on the mobile phone, prior to installation:
- All the PIN codes and security locks should be switched off
- The voice and data calls should be redirected to another phone number
- Delete all SMS messages stored in the phone
- The SMS Service Center should be set
When all these settings are done, you should test your GSM device by sending and receiving SMS messages manually.
Step2: Connect your phone to the PC
In order to send or receive SMS messages from your PC, you need to attach your phone to the PC with a phone-to-pc data cable (Figure 1). The cable is attached to the serial port of your computer. The serial port is located on the back side and has 9 pins.
[pic]
Figure 1 - Attaching the device to the PC with a phone-to-pc data cable.
If you want to use Infrared (IR), you must enable your infrared port on the phone. (Sometimes you have to turn it on in the PC as well). When this is done, you should place the phone within the range of the IR receiver on the PC. When the PC and the phone begin to communicate over IR, you will see an icon in the bottom right hand side of the screen.
[pic]
Step3: Installing the application
The Ozeki Message Server installation is similar to any Windows applications. As a first step, you must exit all Windows applications. After having done it, locate the Setup.exe on the installation CD-ROM. If you have downloaded the application from the Internet, Setup.exe is in your download directory. Start Setup.exe by double-clicking on the icon and follow the instructions:
[pic]
Click Next to start the Installation
[pic]
Read the End User License agreement carefully. If you accept the agreement, select the I accept the agreement radio button and click on Next.
[pic]
Specify the directory you wish to use. Make sure you have at least 20 Megabytes of hard disk space available on the drive you have chosen.
[pic]
Enter the name of the Start Menu Folder and click Next.
[pic]
If you wish to have a desktop icon, place a checkmark into the appropriate checkbox.
[pic]
Wait until the installation completes.
[pic]
When the installation is finished, a shortcut can be found in the Windows Start menu. Use this shortcut to start the application.
[pic]
Start using the service manager
[pic]
Select the Manage Service from the tab.
[pic]
Login into the Ozeki Message Server.
Install drivers
In the following part we will show the easy process of the drivers installation.
[pic]
New drivers can be installed by choosing Installed drivers from the Drivers menu.
[pic]
In the "Available drivers for installation" window click on the GSM Modem install link and the installation process will start.
[pic]
Wait until the installation process finishes.
Step4: Configuring the application
After installation you'll see the GSM Modem 1 appearing on the list of drivers in the "Currently installed drivers" screen. On this form you can start the configuration by clicking on the Configure link.
[pic]
The GSM Modem configuration form opens. On the configuration screen you should provide the necessary GSM modem information to connect to the server.
When the phone configuration form opens you can set all the neccessary settings on this form in order to send and receive messages. As a first step select the phone model you have attached to the computer, then specify the serial port. (If you use IR or Bluetooth, you have to find out the virtual port number and use it instead of the real port.). If use have USB port visit the following URL:
After selecting the phone model you should specify the name of the operator. The operator name is the name of the GSM company your subscription belongs to. (For example it can be Vodafone, T-mobile, Orange, etc.) When this is done, enter the SIM-card number of the phone attached to the computer and the SMS Service center address.
Important: phone numbers should be entered in international format e.g.: +36209937723 !
[pic]
The Service Center address is provided by your GSM service provider. If you don't know the Service Center address of the GSM service provider you can find more information on the following link:
For example, in Hungary you would select one of these numbers:
|T-mobile |+36309888000 |
|Pannon GSM |+36209300099 |
|Vodafone |+36709996500 |
Step5: Starting the GSM modem connection
After the driver has been installed, you need to start it. The startup process means that the driver connects to the mobilephone. You can start the Ozeki Message Server GSM modem driver by clicking on "Connect" in the "Currently installed drivers" screen
[pic]
After you have clicked on the Connect URL, you may use the Refresh this page link to check the connection state.
Step6: Sending your first SMS
Once the application is installed and configured properly, you can send an SMS by clicking on the Create SMS icon. This brings up the SMS composer, where you can specify the phone numbers and the message text. Make sure you specify the phones numbers in international format. e.g.: +36209937723. When the message is written, you can send it by clicking on the Send button.
[pic]
Once you installed the server, you can configure plugins or you can start developing applications. Good luck!
INSTALLATION GUIDE - Datacall Driver
Ozeki Message Server is a software-based solution for you to benefit from advanced communications functions with the help of modem to modem communication. You can easily connect to a modem with Datacall connection for receiving files. As soon as the connection is ready, the Ozeki Message Serves enables you to receive and manage incoming files in various ways.
This guide describes you how to install the Ozeki Message Server software onto your PC and how to start using it. For more details about the use of the software contact our on-line support at the following e-mail address: sms@ozeki.hu.
Hardware and software requirements
To install and run the Ozeki Message Server, you need the following tools/devices available:
|Ozeki Message Server |Ozeki Message Installation Package (CD or downloaded file) |
|Windows PC |Intel compatible PC |
| |Windows 95,98,Me, 2000,Xp |
| |20 Mb free hard disk space |
|Datacall |Modem - compatible ZModem |
Installing Ozeki Message Server
During the installation, various selections can be made. You will find step-by-step information in the following section. If you want to quit the Setup program without installing or uninstalling, just click Cancel and the Exit Setup window will offer you the possibility of quitting the Setup program. Click Exit Setup to quit Setup . If you want to rerun Setup after having quit the installation before it had been completed, follow the installation steps normally as if you were installing the program for the first time.
Quick installation steps :
|1.|Exit all Windows programs. |
|2.|Insert the CD-ROM in the CD-ROM drive. |
|3.|Click the Windows START button. |
|4.|Select the Run command. The Run dialog box appears on the screen. Type in the following command line: |
| |D:\setup |
| |In the above command line, "D:\" is the CD-ROM drive |
|5.|Click OK or press Enter . Wait while the system initializes Setup. The Welcome window of the Setup is |
| |then displayed. |
|6.|Click Next to continue with Setup. |
Step 1: Installing the application
The Ozeki Message Server installation is similar to any Windows applications. As a first step, you must exit all Windows applications. After having done it, locate the Setup.exe on the installation CD-ROM. If you have downloaded the application from the Internet, Setup.exe is in your download directory. Start Setup.exe by double-clicking on the icon and follow the instructions:
[pic]
Click Next to start the Installation
[pic]
Read the End User License agreement carefully. If you accept the agreement, select the I accept the agreement radio button and click on Next.
[pic]
Specify the directory you wish to use. Make sure you have at least 20 Megabytes of hard disk space available on the drive you have chosen.
[pic]
Enter the name of the Start Menu Folder and click Next.
[pic]
If you wish to have a desktop icon, place a checkmark into the appropriate checkbox.
[pic]
Wait until the installation completes.
[pic]
When the installation is finished, a shortcut can be found in the Windows Start menu. Use this shortcut to start the application.
[pic]
Start using the service manager
[pic]
Select the Manage Service from the tab.
[pic]
Login into the Ozeki Message Server.
Install drivers
In the following part we will show the easy process of the drivers installation.
[pic]
New drivers can be installed by choosing Installed drivers from the Drivers menu.
[pic]
In the "Available drivers for installation" window click on the Data call 1 install link and the installation process will start.
[pic]
Wait until the installation process finishes.
Configuring the application
After installation you'll see the Data call 1 appearing on the list of drivers in the "Currently installed drivers" screen. On this form you can start the configuration by clicking on the Configure link.
[pic]
The Data call configuration preferences form opens. On the configuration screen you should provide the necessary login information to connect to the server.
Important: phone numbers should be entered in international format e.g.: +36209937723 !
[pic]
When you fill in the Phone number text box you can modify the phone number.
[pic]
If your modem speed differents from the default value, select the proper boud rate. To receive Binary data place a checkmark in the "Binary data" checkbox.
Starting the Data call connection
After the driver has been installed, you need to start it. The startup process means that the driver connects to the modem. You can start the Ozeki Message Server Data call driver by clicking on "Connect" in the "Currently installed drivers" screen
[pic]
After you have clicked on the Connect URL, you may use the Refresh this page link to check the connection state.
Receiving your first data file
Once the application is installed and configured properly, you can receive an file.
Once you installed the server, you can configure plugins or you can start developing applications. Good luck!
7 SMS SQL gateway
Send and receive SMS messages with the help of a database
|Related pages: |[pic] |
| |SQL CREATE TABLE scripts |
| | |
| |[pic] |
| |Database Plugin Configuration for MySql |
| | |
| |[pic] |
| |Problem with quotes |
| | |
If you use the OZEKI Message Server you can send and receive SMS messages with a database query. In this case the SMS Message Server is connected to the selected database through a standard ODBC connection. The database can be Oracle, Access, MSSql, MS SQL or any other database engine that has an ODBC driver. Through this ODBC connection the Ozeki Message Server inserts the incoming messages into a database table and reads out the messages that should be sent from another table.
[pic]
If you want to send and receive messages this way, first you should create two database tables: ozekimessageout and ozekimessagein . You can find the table definitions for these two tables at the following page: SQL Create Table Script (Ozeki SMS Server)
To send a message you should issue the following SQL statement:
|sql> INSERT INTO ozekimessageout (receiver,msg,id,status) |
|VALUES ('+36203105366','test message',23,'send'); |
It is important to mention that the SMS Server identifies the messages by their ids. You must make sure that you specify a unique id for each message or you should use auto_increment columns.
The incoming messages are placed into the ozekimessagein table. You can see the create definition for this table at the url above as well. To view the incoming messages you can use the select statement.
|sql> SELECT sender,msg,receivedtime FROM ozekimessagein; |
|+--------------+--------------+-------------------+ |
|| sender | msg | receivedtime | |
|+--------------+--------------+-------------------+ |
|| +36203105366 | test message | 01/11/03 08:43:32 | |
|+--------------+--------------+-------------------+ |
|1 row in set (0.00 sec) |
It is interesting that often, without any external application, you can create powerful SMS enabled solutions. You can do this with the help of database triggers. Here is an example:
Autoreply function with a Microsoft SQL Server:
|CREATE TRIGGER autoreply ON ozekimessagein FOR INSERT |
|AS |
|DECLARE @tel VARCHAR(30) |
|SELECT TOP 1 @tel=sender FROM ozekimessagein ORDER BY ID DESC |
|INSERT INTO ozekimessageout (receiver,msg,status) VALUES |
|(@tel,'Thank you for the message','send') |
|GO |
7.2 SQL table definitions for sending and receiving messages with OZEKI Message Server
SQL TABLE DEFINITIONS FOR SENDING AND RECEIVING MESSAGES with OZEKI MESSAGE SEVER
|Related pages: |[pic] |
| |SQL / ODBC overview |
| | |
| |[pic] |
| |Problem with quotes |
| | |
You can use any database with an ODBC driver. All you have to do is create two database tables: ozekimessagesin and ozekimessageout. (The tables can have additional columns.) The SMS Server will insert the incoming messages to the ozekimessagesin table. If you want to send a message, you should insert a record into the ozekimessageout table. The SMS Server checks this table periodically for messages to send.
|Microsoft SQL Server |
|CREATE TABLE ozekimessagein ( |
|id int IDENTITY(1,1), |
|sender varchar(30), |
|receiver varchar(30), |
|msg varchar(160), |
|senttime varchar(100), |
|receivedtime varchar(100), |
|operator varchar(100), |
|msgtype varchar(160) |
|); |
| |
|CREATE TABLE ozekimessageout ( |
|id int IDENTITY(1,1), |
|sender varchar(30), |
|receiver varchar(30), |
|msg varchar(160), |
|senttime varchar(100), |
|receivedtime varchar(100), |
|reference varchar(100), |
|status varchar(20), |
|operator varchar(100), |
|msgtype varchar(160) |
|); |
Example for sending a message:
insert into ozekimessageout (receiver,msg,status) values ('+36209937723','test','send');
Tips:
You can create autoreply function with database triggers:
CREATE TRIGGER autoreply
ON ozekimessagein
FOR INSERT
AS
DECLARE @tel VARCHAR(30)
SELECT TOP 1 @tel=sender FROM ozekimessagein ORDER BY ID DESC
INSERT INTO ozekimessageout (receiver,msg,status) VALUES (@tel,'Thank you for the message','send')
GO
|MySQL |
| |
|CREATE TABLE ozekimessagein ( |
|id int(11) NOT NULL auto_increment, |
|sender varchar(30) default NULL, |
|receiver varchar(30) default NULL, |
|msg varchar(160) default NULL, |
|senttime varchar(100) default NULL, |
|receivedtime varchar(100) default NULL, |
|operator varchar(100), |
|msgtype varchar(160) default NULL, |
|PRIMARY KEY (id) |
|); |
| |
|CREATE TABLE ozekimessageout ( |
|id int(11) NOT NULL auto_increment, |
|sender varchar(30) default NULL, |
|receiver varchar(30) default NULL, |
|msg varchar(160) 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), |
|PRIMARY KEY (id) |
|); |
Note: MySQL ODBC drivers can be downloaded from
|Interbase |
| |
|Installation instructions for Interbase: |
| |
| |
| |
|CREATE TABLE ozekimessageout ( |
| id int NOT NULL, |
| sender varchar(30), |
| receiver varchar(30), |
| msg varchar(160), |
| senttime varchar(100), |
| receivedtime varchar(100), |
| reference varchar(100), |
| operator varchar(120) default NULL, |
| msgtype varchar(160) default NULL, |
| status varchar(20), |
| PRIMARY KEY (id) |
|); |
| |
|CREATE GENERATOR sms_id; |
|CREATE TRIGGER set_id FOR ozekimessageout |
|BEFORE INSERT AS |
| BEGIN |
| New.id = gen_id(sms_id,1); |
| END; |
| |
|CREATE TABLE ozekimessagein ( |
| id int NOT NULL, |
| sender varchar(30), |
| receiver varchar(30), |
| msg varchar(160), |
| operator varchar(120) default NULL, |
| msgtype varchar(160) default NULL, |
| senttime varchar(100), |
| receivedtime varchar(100), |
| PRIMARY KEY (id) |
|); |
|CREATE GENERATOR sms_id_; |
|CREATE TRIGGER set_id_ FOR ozekimessagein |
|BEFORE INSERT AS |
| BEGIN |
| New.id = gen_id(sms_id_,1); |
| END; |
| |
|insert into ozekimessageout (receiver,msg,status) values |
|('+36209937723','Hello world','send'); |
|Oracle |
|CREATE TABLE ozekimessagein ( |
| id int, |
| sender varchar(30) default NULL, |
| receiver varchar(30) default NULL, |
| msg varchar(160) default NULL, |
| operator varchar(120) default NULL, |
| msgtype varchar(160) default NULL, |
| senttime varchar(100) default NULL, |
| receivedtime varchar(100) default NULL |
|); |
|CREATE SEQUENCE X; |
|CREATE TRIGGER ozekimessagein_auto BEFORE INSERT on |
|Ozekimessagein for each row |
|when (new.id is null) |
|begin |
| SELECT x.nextval INTO :new.id FROM DUAL; |
|end; |
|/ |
| |
|CREATE TABLE ozekimessageout ( |
| id int, |
| sender varchar(30) default NULL, |
| receiver varchar(30) default NULL, |
| msg varchar(160) default NULL, |
| senttime varchar(100) default NULL, |
| receivedtime varchar(100) default NULL, |
| operator varchar(120) default NULL, |
| msgtype varchar(160) default NULL, |
| reference varchar(100) default NULL, |
| status varchar(20) default NULL |
|); |
|CREATE SEQUENCE Y; |
|CREATE TRIGGER ozekimessageout_auto BEFORE INSERT on ozekimessageout |
|for each row |
|when (new.id is null) |
|begin |
| SELECT y.nextval INTO :new.id FROM DUAL; |
|end; |
|/ |
Example for sending a message:
Insert into ozekimessageout (receiver,msg,status) values ('+36209937723','test','send');
|PostgresSQL |
|CREATE TABLE ozekimessagein ( |
| id serial, |
| sender varchar(30), |
| receiver varchar(30), |
| msg varchar(160), |
| senttime varchar(100), |
| receivedtime varchar(100), |
| operator varchar(100) |
| msgtype varchar(160) |
|); |
| |
|CREATE TABLE ozekimessageout ( |
| id serial, |
| sender varchar(30), |
| receiver varchar(30), |
| msg varchar(160), |
| senttime varchar(100), |
| receivedtime varchar(100), |
| reference varchar(100), |
| status varchar(20), |
| operator varchar(100) |
| msgtype varchar(160) |
|); |
7.3 Configuring Ozeki Message Server database connection
HOW TO USE THE SQL PLUGIN
Ozeki Message Server enables client software installed on the network, to send and receive SMS messages over a SQL link. In order to make this possible for the clients, the SQL plugin needs to be istalled and configured in Ozeki Message Server.
DATABASE PLUGIN CONFIGURATION FOR MYSQL
Step1
Download the MyODBC database driver.You can download it from
Step2
Install the downloaded MyODBC database driver by executing setup.exe. You need Winzip to unpack the downloaded file.
Step3
Configure your ODBC data source. Go to Control Panel and then click on Administrative Tools and then click on Data Source (ODBC)
[pic]
Click on User DSN Tab (by default it is on that) and click Add as shown in the figure above.
[pic]
Scroll down and select the MySQL ODBC Driver.
[pic]
In the MySQL ODBC driver configuration enter the details as can be seen in the figure above (in the database name enter your database name) and after filling in all the proper fields click on the Test Data Source to check if you have properly configured your Database settings.
Step4
Install the OZEKI Message Server. You can read the installation instructions at the installation manual.
Step5
After the installation, install the Database plugin :
[pic]
Select Installed plugins from the menu
[pic]
In the "Available plugins for installation" window click on the Database plugin install link and the installation process will start.
Step6
Configure the database connection in Ozeki Message Server.
[pic]
After installation you'll see the Database plugin appearing on the list of plugins in the "Currently installed plugins" screen. On this form you can start the configuration by cliciking on the Configure link.
[pic]
On the Database Settings form, click on the Build database connection button. This will bring up the connection selection dialog.
[pic]
Select Connection tab
[pic]
After finishing these settings make sure you test the connection by clicking on the Test Connection button located in the lower right hand corner of the form.
On the connection tab, you can specify the ODBC datasource: mstest, and you can supply the login name and the password:
[pic]
Press OK to finalize your settings.
Step7
Once the database connections string has been built, make sure you place a checkmark in the checkboxes on the bottom of the Database Settings form to control the behaviour of the database plugin. You can decide to send and/or receive SMS messages. If you want to send messages using a database table you also have to specify the time period the SMS server uses to check the outgoing database table.
[pic]
If you would like the Database server of the pluginto start automatically when the Ozeki Message Server software starts, place a checkmark into the "Autoconnect on startup" checkbox and press Ok.
Step8
If the connection is set you might want to verify the SQL templates used to access that database. These templates can be seen and modified if neccessary on the SQL templates tab of the Database Settings form.
[pic]
Step9
After the plugin has been installed, you need to start it. The startup process means that the plugin allocates the configured Database port and starts to listen on it for incoming connections. You can start the Ozeki Message Server Database plugin by clicking on "Connect" in the "Currently installed plugins" screen
[pic]
Step10
You can check the database connection status once the application was started by clicking on the database connection item in the Plugins menu.
[pic]
Step11
If you want to see the SQL statements Ozeki SMS uses to find outgoing messages, please go to Plugins/Database Events. You can also see the SQL queries used to insert and modify the database records here.
[pic]
OZEKI MESSAGE SERVER 6 - SMS PROBLEM WITH QUOTES
When I receive an SMS, sometimes my messages are not inserted into the database.
When a message is received, an SQL string is composed, then it is passed to the database server. The SQL string composition is based on an SQL template defined in the Database Settings form. If an incoming message contains an apostrophe (') or a quotation mark ("), the SQL statement becomes invalid when the message text is inserted into the template.
Our software provides a mechanism to replace the apostrophe (') and the quotation mark (") in the received message before it is inserted into the SQL template. On the Database settings form you can specify what caracter sequence should be used instead of these characters.
Note: On Sybase, MSSql and on MySql, you should substitue the apostrophe (') with backslash and apostrophe (\') and you should do the same for quotation marks.
[pic]
[pic]
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.