GAMMU-SMSD-ODBC(7)

🌸 GAMMU-SMSD-ODBC 🌸

okay bestie, so this is the gammu-smsd-odbc(7) page — section 7 of the manual, brought to you by the Gammu girlies 💅. Buckle up.

---

✨ NAME ✨

gammu-smsd-odbc — the gammu-smsd(1) backend that uses the ODBC abstraction layer so you can use literally any supported database as your message storage 🎀

---

💖 DESCRIPTION 💖

Added in version 1.29.92. (yes she's a legend, she's been around 💅)

This ODBC backend stores all the data in any database that ODBC supports — <https://en.wikipedia.org/wiki/Open_Database_Connectivity> 🦋 — and all its parameters are defined by configuration (peep the SMSD Configuration File <#gammu-smsdrc> for the deets on all those config options).

For the tables description, go look at the SMSD Database Structure <#gammu-smsd-tables>.

Fun fact: this backend is based on SQL Service <#gammu-smsd-sql>. that's the blueprint, the main character it's built on 👑

---

🎀 SUPPORTED DRIVERS 🎀

On Microsoft Windows, Gammu uses native ODBC. On other platforms, you can use unixODBC <http://www.unixodbc.org/> 🌈

---

💀 LIMITATIONS 💀

ngl, here's the tea: because of limits of the ODBC interface, Gammu cannot reliably detect which SQL engine it is connected to 😭

In most cases you can fix this by setting the SQL <#option-SQL> setting to the correct dialect ✨

And if that fails? You can also tweak the SQL queries to work in the used SQL server — see SQL Queries <#sql-queries> for more details. But bestie, you should still set SQL <#option-SQL> to the closest matching SQL dialect, fr fr 🫶

---

🦋 CONFIGURATION 🦋

Before running gammu-smsd <#gammu-smsd>, you need to create the necessary tables in the database first 💅 You can use the examples given in the database specific backends parts of this manual to do that.

You specify the data source name (DSN) as Host <#option-Host> in the SMSD Configuration File <#gammu-smsdrc>. The data source is configured depending on your platform 🌸

⚠️ Note: please remember that SMSD might be running in a different context than your user (separate account on Linux or as a service on Windows), so in that case the ODBC DSN needs to be configured system wide (system DSN on Windows, or in global configuration on Linux). don't get caught out by this one 🙈

On Microsoft Windows, you can find instructions on the Microsoft website: <https://support.microsoft.com/kb/305599> ⭐

For unixODBC this is documented in the user manual: <http://www.unixodbc.org/doc/UserManual/> 💜

🛠️ CREATING TABLES ✨

okay so before you even think about starting SMSD, you gotta create the tables it's gonna live in. no tables, no texting, no cap. Gammu ships SQL scripts for a bunch of databases to do exactly that, so you don't have to hand-write any of it:

• Creating tables for MySQL (#mysql-create) — 🐬
• Creating tables for PostgreSQL (#pgsql-create) — 🐘
• Creating tables for SQLite (#sqlite-create) — 🪶
• Oracle — 🔮 (this one gets its own whole subsection, she's extra)

---

🔮 Oracle

bestie, for Oracle you just use the schema that ships with Gammu. it's long, it's giving main-character energy, but it's literally the whole thing so here it is, verbatim, don't touch nothin':

--
-- Database tables for Gammu SMSD on Oracle Database
--
-- Oracle treats empty strings as NULL, so text columns which can contain an
-- empty SMSD value are nullable even where other database schemas use NOT NULL.
--

WHENEVER SQLERROR EXIT SQL.SQLCODE

BEGIN
  FOR object_to_drop IN (
    SELECT object_name
      FROM user_objects
     WHERE object_type = 'TABLE'
       AND object_name IN (
         'GAMMU', 'INBOX', 'OUTBOX', 'OUTBOX_MULTIPART', 'PHONES', 'SENTITEMS'
       )
  ) LOOP
    EXECUTE IMMEDIATE
      'DROP TABLE ' || DBMS_ASSERT.SIMPLE_SQL_NAME(object_to_drop.object_name) ||
      ' CASCADE CONSTRAINTS PURGE';
  END LOOP;

  FOR object_to_drop IN (
    SELECT object_name
      FROM user_objects
     WHERE object_type = 'SEQUENCE'
       AND object_name IN ('INBOX_ID_SEQ', 'OUTBOX_ID_SEQ')
  ) LOOP
    EXECUTE IMMEDIATE
      'DROP SEQUENCE ' || DBMS_ASSERT.SIMPLE_SQL_NAME(object_to_drop.object_name);
  END LOOP;
END;
/

CREATE TABLE gammu (
  Version NUMBER(5) DEFAULT 0 NOT NULL PRIMARY KEY
);

INSERT INTO gammu (Version) VALUES (18);

CREATE SEQUENCE inbox_ID_seq START WITH 1 INCREMENT BY 1
  MAXVALUE 9223372036854775807 NOCACHE;

CREATE TABLE inbox (
  UpdatedInDB TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
  ReceivingDateTime TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
  InsertIntoDB TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
  Text CLOB,
  SenderNumber VARCHAR2(20 CHAR),
  Coding VARCHAR2(255 CHAR) DEFAULT 'Default_No_Compression' NOT NULL,
  UDH CLOB,
  SMSCNumber VARCHAR2(20 CHAR),
  Class NUMBER(10) DEFAULT -1 NOT NULL,
  TextDecoded CLOB,
  ID NUMBER(19) NOT NULL PRIMARY KEY,
  MessageID NUMBER(19) DEFAULT 0 NOT NULL,
  SequencePosition NUMBER(10) DEFAULT 1 NOT NULL,
  PartCount NUMBER(10) DEFAULT 1 NOT NULL,
  RecipientID VARCHAR2(255 CHAR),
  Processed NUMBER(1) DEFAULT 0 NOT NULL,
  Status NUMBER(10) DEFAULT -1 NOT NULL,
  CONSTRAINT inbox_coding_check CHECK (Coding IN
    ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression')),
  CONSTRAINT inbox_id_check CHECK (ID BETWEEN 0 AND 9223372036854775807),
  CONSTRAINT inbox_message_id_check CHECK (MessageID BETWEEN 0 AND 9223372036854775807),
  CONSTRAINT inbox_processed_check CHECK (Processed IN (0, 1))
);

CREATE INDEX inbox_message ON inbox(MessageID, SequencePosition);
CREATE INDEX inbox_insert ON inbox(InsertIntoDB);

CREATE OR REPLACE TRIGGER inbox_biu
BEFORE INSERT OR UPDATE ON inbox
FOR EACH ROW
BEGIN
  IF INSERTING AND :NEW.ID IS NULL THEN
    SELECT inbox_ID_seq.NEXTVAL INTO :NEW.ID FROM dual;
  END IF;
  IF UPDATING THEN
    :NEW.UpdatedInDB := CURRENT_TIMESTAMP;
  END IF;
END;
/

CREATE SEQUENCE outbox_ID_seq START WITH 1 INCREMENT BY 1
  MAXVALUE 9223372036854775807 NOCACHE;

CREATE TABLE outbox (
  UpdatedInDB TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
  InsertIntoDB TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
  SendingDateTime TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
  SendBefore VARCHAR2(8 CHAR) DEFAULT '23:59:59' NOT NULL,
  SendAfter VARCHAR2(8 CHAR) DEFAULT '00:00:00' NOT NULL,
  SendDays NUMBER(3) DEFAULT 127 NOT NULL,
  Text CLOB,
  DestinationNumber VARCHAR2(20 CHAR),
  Coding VARCHAR2(255 CHAR) DEFAULT 'Default_No_Compression' NOT NULL,
  UDH CLOB,
  Class NUMBER(10) DEFAULT -1,
  TextDecoded CLOB,
  ID NUMBER(19) NOT NULL PRIMARY KEY,
  MultiPart NUMBER(1) DEFAULT 0 NOT NULL,
  RelativeValidity NUMBER(10) DEFAULT -1,
  SenderID VARCHAR2(255 CHAR),
  SendingTimeOut TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP,
  DeliveryReport VARCHAR2(10 CHAR) DEFAULT 'default',
  CreatorID VARCHAR2(255 CHAR),
  Retries NUMBER(10) DEFAULT 0,
  Priority NUMBER(10) DEFAULT 0,
  Status VARCHAR2(255 CHAR) DEFAULT 'Reserved' NOT NULL,
  StatusCode NUMBER(10) DEFAULT -1 NOT NULL,
  CONSTRAINT outbox_coding_check CHECK (Coding IN
    ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression')),
  CONSTRAINT outbox_delivery_check CHECK (DeliveryReport IN ('default','yes','no')),
  CONSTRAINT outbox_id_check CHECK (ID BETWEEN 0 AND 9223372036854775807),
  CONSTRAINT outbox_multipart_check CHECK (MultiPart IN (0, 1)),
  CONSTRAINT outbox_status_check CHECK (Status IN
    ('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending',
     'DeliveryUnknown','Error','Reserved'))
);

CREATE INDEX outbox_date ON outbox(SendingDateTime, SendingTimeOut);
CREATE INDEX outbox_sender ON outbox(SenderID);

CREATE OR REPLACE TRIGGER outbox_biu
BEFORE INSERT OR UPDATE ON outbox
FOR EACH ROW
BEGIN
  IF INSERTING AND :NEW.ID IS NULL THEN
    SELECT outbox_ID_seq.NEXTVAL INTO :NEW.ID FROM dual;
  END IF;
  IF UPDATING THEN
    :NEW.UpdatedInDB := CURRENT_TIMESTAMP;
  END IF;
END;
/

CREATE TABLE outbox_multipart (
  Text CLOB,
  Coding VARCHAR2(255 CHAR) DEFAULT 'Default_No_Compression' NOT NULL,
  UDH CLOB,
  Class NUMBER(10) DEFAULT -1,
  TextDecoded CLOB,
  ID NUMBER(19) DEFAULT 0 NOT NULL,
  SequencePosition NUMBER(10) DEFAULT 1 NOT NULL,
  Status VARCHAR2(255 CHAR) DEFAULT 'Reserved' NOT NULL,
  StatusCode NUMBER(10) DEFAULT -1 NOT NULL,
  PRIMARY KEY (ID, SequencePosition),
  CONSTRAINT outbox_mp_coding_check CHECK (Coding IN
    ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression')),
  CONSTRAINT outbox_mp_id_check CHECK (ID BETWEEN 0 AND 9223372036854775807),
  CONSTRAINT outbox_mp_status_check CHECK (Status IN
    ('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending',
     'DeliveryUnknown','Error','Reserved'))
);

CREATE TABLE phones (
  ID VARCHAR2(255 CHAR),
  UpdatedInDB TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
  InsertIntoDB TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
  TimeOut TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
  Send VARCHAR2(3 CHAR) DEFAULT 'no' NOT NULL,
  Receive VARCHAR2(3 CHAR) DEFAULT 'no' NOT NULL,
  IMEI VARCHAR2(35 CHAR) NOT NULL PRIMARY KEY,
  IMSI VARCHAR2(35 CHAR),
  NetCode VARCHAR2(10 CHAR) DEFAULT 'ERROR',
  NetName VARCHAR2(35 CHAR) DEFAULT 'ERROR',
  Client VARCHAR2(255 CHAR),
  Battery NUMBER(10) DEFAULT -1 NOT NULL,
  Signal NUMBER(10) DEFAULT -1 NOT NULL,
  Sent NUMBER(10) DEFAULT 0 NOT NULL,
  Received NUMBER(10) DEFAULT 0 NOT NULL,
  CONSTRAINT phones_send_check CHECK (Send IN ('yes','no')),
  CONSTRAINT phones_receive_check CHECK (Receive IN ('yes','no'))
);

CREATE OR REPLACE TRIGGER phones_bu
BEFORE UPDATE ON phones
FOR EACH ROW
BEGIN
  :NEW.UpdatedInDB := CURRENT_TIMESTAMP;
END;
/

CREATE TABLE sentitems (
  UpdatedInDB TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
  InsertIntoDB TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
  SendingDateTime TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
  DeliveryDateTime TIMESTAMP(0),
  Text CLOB,
  DestinationNumber VARCHAR2(20 CHAR),
  Coding VARCHAR2(255 CHAR) DEFAULT 'Default_No_Compression' NOT NULL,
  UDH CLOB,
  SMSCNumber VARCHAR2(20 CHAR),
  Class NUMBER(10) DEFAULT -1 NOT NULL,
  TextDecoded CLOB,
  ID NUMBER(19) DEFAULT 0 NOT NULL,
  SenderID VARCHAR2(255 CHAR),
  SequencePosition NUMBER(10) DEFAULT 1 NOT NULL,
  Status VARCHAR2(255 CHAR) DEFAULT 'SendingOK' NOT NULL,
  StatusError NUMBER(10) DEFAULT -1 NOT NULL,
  TPMR NUMBER(10) DEFAULT -1 NOT NULL,
  RelativeValidity NUMBER(10) DEFAULT -1 NOT NULL,
  CreatorID VARCHAR2(255 CHAR),
  StatusCode NUMBER(10) DEFAULT -1 NOT NULL,
  PRIMARY KEY (ID, SequencePosition),
  CONSTRAINT sentitems_id_check CHECK (ID BETWEEN 0 AND 9223372036854775807),
  CONSTRAINT sentitems_status_check CHECK (Status IN
    ('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending',
     'DeliveryUnknown','Error')),
  CONSTRAINT sentitems_coding_check CHECK (Coding IN
    ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression'))
);

CREATE INDEX sentitems_date ON sentitems(DeliveryDateTime);
CREATE INDEX sentitems_tpmr ON sentitems(TPMR);
CREATE INDEX sentitems_dest ON sentitems(DestinationNumber);
CREATE INDEX sentitems_sender ON sentitems(SenderID);

CREATE OR REPLACE TRIGGER sentitems_bu
BEFORE UPDATE ON sentitems
FOR EACH ROW
BEGIN
  :NEW.UpdatedInDB := CURRENT_TIMESTAMP;
END;
/

COMMIT;

and yes, that exact same script is also installed with the other SQL examples as docs/sql/oracle.sql — so if you lose it, it's right there in the repo 🫶

okay and here's the ✨crucial✨ little Oracle detail: Gammu passes UTF-8 through the ANSI ODBC interface, which means you have to configure the Oracle client that SMSD uses for the AL32UTF8 character set:

export NLS_LANG=.AL32UTF8

💀 real talk: set this in the environment of the SMSD service, not just in your interactive shell. your terminal being cute and correct does not mean the daemon is. iykyk.

---

🌸 EXAMPLE

here's what a config actually looks like, bestie — copy the vibe, keep the values exact:

[smsd]
service = sql
driver = odbc
host = dsn_of_your_database
sql = sql_variant_to_use
user = username
password = password

See also:

• SMSD Configuration File (#gammu-smsdrc) — go read it, it's the whole manual for the config file 💖

---

👑 Author

Michal Čihař <michal@cihar.com> — the icon who wrote this.

🎀 Copyright

2009-2015, Michal Čihař <michal@cihar.com>

---

✨ and that's the whole page, bestie ✨ this one's from Gammu 1.45.0, dated Sep 16, 2026, and it lives at GAMMU-SMSD-ODBC(7). go forth and text responsibly 💅🦋