Products
Ozeki 10
SMS
Ozeki NG SMS Gateway
SMS Gateway
Quick Start Guide
SMS with Client Software
HTTP API
SMS from/to Database
PHP SMS API
Solutions
C# SMS API
VB.NET SMS API
SQL Logging
System Database
MySQL
Oracle
MSSQL 2008
MSSQL 2012/2014
Import messages
Switch from file
SMPP
Message Server 6
SMS Wall
IP SMS Gateway
Android SMS
Excel SMS
Bulk Messenger
Cluster
Developer tools
Robots
Hardware
Technology
Company


Ozeki 10 SMS Gateway
MySQL MySQL | MSSQL 2008 MSSQL 2008

Oracle configuration

As a default Ozeki SMS Gateway stores messages in files. Storing large amount of texts in files can slow down you system. It is highly recommended to switch to the System Database option, which will definitely speed up SMS Gateway. Here you can see how to add your Oracle database.

If you are uncertain how to import messages from your files, you can look back and see how to switch from file to database. It is a short site to scroll through. The diagrams will help you understand.

Steps of configuration

From the 'Edit' top menu, please select 'Server preferences'. A window demonstrated on Figure 1 will appear. Select 'System database settings' tab and enable 'Use a database server instead of file system to store messages' option. Set the connection type to OleDb.

Keep in mind that you should provide the connection string as well.

Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=admin;Password=qwe123
Code 1 - Example connection string. Modify it if necessary


Figure 1 - Setting the System Database

You will find 9 different tabs. From the codes below you can copy-paste the content to each tabpage.

'Create' tab:
DECLARE N NUMBER;BEGIN SELECT COUNT(*) INTO N FROM ALL_TABLES WHERE
TABLE_NAME='$utablename';IF N = 0 THEN EXECUTE IMMEDIATE 'CREATE TABLE
"$utablename" (MESSAGEID VARCHAR(60) DEFAULT NULL,CREATEDATE
VARCHAR(100) DEFAULT SYSDATE,SERIALIZEDMESSAGE CLOB DEFAULT NULL)'
;END
IF;END;
Code 2 - Creates database table

'Load index' tab:
SELECT MESSAGEID FROM "$utablename"
Code 3 - SELECT -s all message ID -s from your table

'Load' tab:
SELECT MESSAGEID,SERIALIZEDMESSAGE FROM "$utablename" WHERE MESSAGEID IN ($idlist)
Code 4 - Loads message with the help of the message ID

'Save' tab:
DECLARE vClobVal VARCHAR2(32767) := '$serializedmessage';BEGIN INSERT
INTO "$utablename" (MESSAGEID,SERIALIZEDMESSAGE) VALUES ('$messageid', vClobVal); END;
Code 5 - INSERT -s serialised message into table

'Delete' tab:
DELETE FROM "$utablename" WHERE MESSAGEID IN ($idlist)
Code 6 - DELETE -s message from table where ID is in the ID list

'Move' tab:
INSERT INTO "$utablename" (MESSAGEID, CREATEDATE,SERIALIZEDMESSAGE)
SELECT MESSAGEID, CREATEDATE, SERIALIZEDMESSAGE FROM "$usourcetable"
WHERE MESSAGEID IN ($idlist)
Code 7 - INSERT -s message from source table into target table

'Find old' tab:
SELECT MESSAGEID FROM "$utablename" WHERE
CREATEDATE<DATEADD(second,-$maxage, getdate())
Code 8 - Find old messages with a simple SELECT

'Delete old' tab:
DELETE FROM "$utablename" WHERE CREATEDATE<DATEADD(second,-$maxage, getdate())
Code 9 - Delete old messages from the table

'Update' tab:
DECLARE vClobVal VARCHAR2(32767) := '$serializedmessage';BEGIN UPDATE
"$utablename" SET SERIALIZEDMESSAGE=vClobVal WHERE
MESSAGEID='$messageid'; END;
Code 10 - UPDATE message records having the preferred message ID

Copyright © 2000- - Ozeki Ltd | info@ozeki.hu
Home > Products > SMS > SMS Gateway > System Database > Oracle
Legal | Privacy policy | Terms of use
Page: 5794 | Login | 3.214.184.250