Home » Documentation » SMS to Database

SMS to Database

SMS Enabler can automatically save incoming SMS messages directly into a database. Various databases are supported like MySQL, MS Access, MS SQL Server, PostgreSQL, Oracle etc.

In order to configure SMS Enabler to forward incoming SMS messages into your database, you must:

  1. create a table named SMS_IN in your database (SMS_IN will store received messages);
  2. specify the connection parameters to your database in the Settings dialog box in SMS Enabler.

Below you will find information on how to configure SMS Enabler for particular database types.

Using SMS Enabler with:

  1. MySQL (using a direct connection to MySQL)
  2. MySQL (using a PHP script on a website)
  3. Microsoft Access database
  4. Microsoft SQL Server
  5. PostgreSQL
  6. Other databases

 

Using SMS Enabler with MySQL database

Install a MySQL Connector/ODBC 5.1 (32-bit version) on the computer where SMS Enabler is installed. You should install a 32-bit version of MySQL Connector, even if you are on Windows 64 bit. You can download it here.

In your MySQL database, create a table named SMS_IN into which incoming SMS messages will be saved. You can add it to your existing database or create it in a new database. The SMS_IN table must have the following three columns:

sms_text varchar - the text of the SMS message.
sender_number varchar - the sender's number
sent_dt datetime - the date and time when the sender sent the message

Here is a sample SQL statement you can use to create SMS_IN table:

CREATE TABLE SMS_IN(id int NOT NULL auto_increment,
 sms_text varchar(1600),
 sender_number varchar(50),
 sent_dt datetime,
 PRIMARY KEY (id)) default charset=utf8;

The ID column is optional and is added for convenience; you can add other columns, if necessary. In the above example, the default character set is set to UTF8. It is not necessary to use UTF8, though it is highly recommended to do this to avoid character encoding problems.

Next, you should configure database connection parameters in the Settings dialog box in SMS Enabler. Open SMS Enabler's Settings dialog box and select the SMS to Database page. Check Save received SMS messages into a database checkbox. Click Create connection string and select MySQL in the menu opened. A dialog box will open where you can enter the connection parameters to your MySQL database.

 

Using SMS Enabler with Microsoft Access database

Create a table named SMS_IN in your database to store incoming SMS messages. You can create it in your existing database or create a new database for it. Add the following 3 columns to the SMS_IN table:

Column nameTypeDescription
sms_textMemomessage content
sender_numberTextsender's number
sent_dtDate/timethe time when the sender sent the message

Add other columns, if necessary. You can download a sample database here: smsenabler.mdb.zip

Next, you should configure database connection parameters in SMS Enabler's Settings dialog box. Open the Settings dialog box in SMS Enabler, and select the SMS to Database page. Check Save received SMS messages into a database checkbox. Click Create connection string, and select Microsoft Access in the menu opened. A dialog box will open where you can specify the path to your database file.

You can use Access database in *.mdb (Office 2000, 2003), or *.accdb (Office 2007, 2010, 2013) format.

 

Using SMS Enabler with Microsoft SQL Server

In your MS SQL database, create a table named SMS_IN for incoming SMS messages. You can add it to your existing database or create it in a new database. The SMS_IN table must have the following 3 columns:

sms_text - to hold the text of the SMS message
sender_number - to hold the sender's number
sent_dt - the date and time when the sender sent the message

You can use this SQL statement to create SMS_IN table:

CREATE TABLE SMS_IN (
 ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
 SMS_TEXT nvarchar(1600),
 SENDER_NUMBER nvarchar(50),
 SENT_DT datetime,
 TS datetime DEFAULT (getdate()));

Type for the 'sms_text' and 'sender_number' columns can also be varchar, ntext, text, i.e. any variable-length string type. The ID and TS columns are optional and are added for convenience; you can add other columns, if needed.

Next, you need to specify the connection parameters to your database in the Settings dialog box in SMS Enabler. Open SMS Enabler's Settings dialog box and select the SMS to Database page. Check Save received SMS messages into a database checkbox. Click Create connection string and select Microsoft SQL Server in the menu opened. A dialog box will open where you can enter the connection parameters to your MS SQL database.

 

Using SMS Enabler with PostgreSQL database

Install a 32-bit version of the PostgreSQL ODBC driver (psqlODBC) on the computer where SMS Enabler is installed. You can download it here.

In your PostgreSQL database, create a table named SMS_IN for received SMS messages. You can add it to your existing database or create a new database for it.

Use this SQL statement to create the SMS_IN table:

CREATE TABLE SMS_IN (
 id serial NOT NULL PRIMARY KEY,
 sms_text varchar(1600),
 sender_number varchar(50),
 sent_dt timestamp without time zone);

SMS data is stored in the following columns:

sms_text - the text of the SMS message.
sender_number - the sender's number
sent_dt - the date and time when the sender sent the message

You can add other columns, if necessary, but make sure these other columns allow NULL values or have default values defined.

Configure database connection parameters in the Settings dialog box in SMS Enabler. For this open SMS Enabler's Settings dialog box and select the SMS to Database page. Check Save received SMS messages into a database checkbox. Click Create connection string and select PostgreSQL in the menu opened. A dialog box will open where you can enter the connection parameters to your PostgreSQL database.

 

Using SMS Enabler with other databases

For SMS Enabler to be able to connect to your database, an ODBC or OLEDB driver for your database type must be installed on the computer where SMS Enabler runs. For some databases, you will need to install the driver yourself, while for other databases, the drivers are part of a Windows installation. For example, 'Microsft Oracle ODBC driver' is part of a Windows installation.

In your database, you must create a table named SMS_IN into which incoming SMS messages will be saved. You must add 3 columns to this table:
sms_text - text of the SMS message
sender_number - sender's number
sent_dt - the date and time when the sender sent the message
'sms_text' and 'sender_number' must be of a variable-length string type. 'sent_dt' can be of either a string type or a date/time type. 'sent_dt' is inserted into the SMS_IN table as string in 'YYYY-MM-DD HH:MM:SS' format, so if your database can implicitly convert such string into a date/time data type, you can use a date/time data type for 'sent_dt'. You can add other columns to the SMS_IN table, if necessary.

Next, you need to specify the connection parameters to your database in the Settings dialog box on the SMS to Database page in SMS Enabler. The main parameter is Connection string. Here is a very useful website, where you can find connection string examples for various database types: http://connectionstrings.com. Note: you should use examples for OLEDB or ODBC. You should not use connection string examples for .NET Framework.