DLR Sqlite3 Usable?

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

DLR Sqlite3 Usable?

Filipe Carvalho
   
    Hi,

    I've configure Kannel to store the DLR with a remote PostGresql and
all works fine..

    Because I do not want to be concern with the availability of this
remote postgresql DB, I'm trying to change to a local sqlite db (the SMS
volume is low... around 6k in a week)

    Change the configuration as explained in the example
   
   
http://kannel.org/download/1.4.4/gateway-1.4.4/doc/examples/dlr-sqlite3.conf

    First problem

    2016-12-16 12:27:01 [22051] [6] DEBUG: DLR[sqlite3]: Looking for DLR
smsc=xxxxx, ts=161216122710, dst=xxxxxx, type=1
    2016-12-16 12:27:01 [22051] [6] PANIC: octstr_format format string
syntax error.

    Then I do a correction in dlr_sqlite3.c line 227

    like = octstr_format("AND %S LIKE '%%?3'", fields->field_dst);

    Then

    2016-12-16 14:24:13 [22784] [6] DEBUG: sql: SELECT mask, service,
url, source, destination, boxc FROM dlr WHERE smsc=?1 AND ts=?2 AND
destination LIKE '%?3' LIMIT 1
    2016-12-16 14:24:13 [22784] [6] ERROR: SQLite3: bind or column index
out of range
    2016-12-16 14:24:13 [22784] [6] WARNING: DLR[sqlite3]: DLR from
SMSC<XXXX> for DST<XXXX> not found.

    Changing to double quotes...

    2016-12-16 14:26:11 [23135] [6] DEBUG: DLR[sqlite3]: Looking for DLR
smsc=XXXX, ts=161216142619, dst=XXX, type=1
    2016-12-16 14:26:11 [23135] [6] DEBUG: sql: SELECT mask, service,
url, source, destination, boxc FROM dlr WHERE smsc=?1 AND ts=?2 AND
destination LIKE "%?3" LIMIT 1
    2016-12-16 14:26:11 [23135] [6] ERROR: SQLite3: bind or column index
out of range
    2016-12-16 14:26:11 [23135] [6] WARNING: DLR[sqlite3]: DLR from
SMSC<XXXXX> for DST<XXXX> not found.


    Then I've try the same logic as pgsql code

    if (dst)
        like = octstr_format("AND %S LIKE '%%%S'", fields->field_dst,dst);
    else
        like = octstr_imm("");

    (...)

    gwlist_append(binds, (Octstr *)smsc);      /* ?1 */
    gwlist_append(binds, (Octstr *)ts);        /* ?2 */
    //    if (dst)
    //        gwlist_append(binds, (Octstr *)dst);   /* ?3 */


     It works, but another problem:

    2016-12-16 14:32:34 [23479] [6] DEBUG: DLR[sqlite3]: Looking for DLR
smsc=XXXXX, ts=161216143243, dst=XXXX, type=1
    2016-12-16 14:32:34 [23479] [6] DEBUG: sql: SELECT mask, service,
url, source, destination, boxc FROM dlr WHERE smsc=?1 AND ts=?2 AND
destination LIKE '%XXXX' LIMIT 1
    2016-12-16 14:32:34 [23479] [6] DEBUG: DLR[sqlite3]: created DLR
message for URL
<<a href="http://127.0.0.1:9000/?sms=%a&ts=%T&sender=%q&receiver=%Q&smsc-id=%i&dlr-value=%d&fid=%F">http://127.0.0.1:9000/?sms=%a&ts=%T&sender=%q&receiver=%Q&smsc-id=%i&dlr-value=%d&fid=%F>
    2016-12-16 14:32:34 [23479] [6] DEBUG: removing DLR from database
    2016-12-16 14:32:34 [23479] [6] PANIC: octstr_format format string
syntax error.

    I can continue to making this kind of changes... but this happens
just with me? Does anyone have this working fine?

    So useful information:

    Kannel 1.4.4
    CentOS release 6.6 (Final)
    sqlite-devel-3.6.20-1.el6.x86_64

    Best Regards,