OK, here is what worked...... (by changing sql query)
It is needed to change sqlbox_yourdb.h (create and insert queries):
////////////////
CREATE query should be like this:
///////////////
#define SQLBOX_MYSQL_CREATE_LOG_TABLE "CREATE TABLE IF NOT EXISTS %S ( \
sql_id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY, \
momt ENUM('MO', 'MT', 'DLR') NULL, sender VARCHAR(20) NULL, \
receiver VARCHAR(20) NULL, udhdata BLOB NULL, msgdata TEXT NULL, \
time DATETIME NULL, smsc_id VARCHAR(255) NULL, service VARCHAR(255)
NULL, \
account VARCHAR(255) NULL, id BIGINT(20) NULL, sms_type BIGINT(20) NULL,
\
mclass BIGINT(20) NULL, mwi BIGINT(20) NULL, coding BIGINT(20) NULL, \
compress BIGINT(20) NULL, validity BIGINT(20) NULL, deferred BIGINT(20)
NULL, \
dlr_mask BIGINT(20) NULL, dlr_url VARCHAR(255) NULL, pid BIGINT(20)
NULL, \
alt_dcs BIGINT(20) NULL, rpi BIGINT(20) NULL, charset VARCHAR(255) NULL,
\
boxc_id VARCHAR(255) NULL, binfo VARCHAR(255) NULL, meta_data TEXT)"
#define SQLBOX_MYSQL_CREATE_INSERT_TABLE "CREATE TABLE IF NOT EXISTS %S
( \
sql_id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY, \
momt ENUM('MO', 'MT') NULL, sender VARCHAR(20) NULL, \
receiver VARCHAR(20) NULL, udhdata BLOB NULL, msgdata TEXT NULL, \
time DATETIME NULL, smsc_id VARCHAR(255) NULL, service VARCHAR(255)
NULL, \
account VARCHAR(255) NULL, id BIGINT(20) NULL, sms_type BIGINT(20) NULL,
\
mclass BIGINT(20) NULL, mwi BIGINT(20) NULL, coding BIGINT(20) NULL, \
compress BIGINT(20) NULL, validity BIGINT(20) NULL, deferred BIGINT(20)
NULL, \
dlr_mask BIGINT(20) NULL, dlr_url VARCHAR(255) NULL, pid BIGINT(20)
NULL, \
alt_dcs BIGINT(20) NULL, rpi BIGINT(20) NULL, charset VARCHAR(255) NULL,
\
boxc_id VARCHAR(255) NULL, binfo VARCHAR(255) NULL, meta_data TEXT)"
////////////////
INSERT query should be like this:
/////////////////
#define SQLBOX_MYSQL_INSERT_QUERY "INSERT INTO %S ( sql_id, momt,
sender, \
receiver, udhdata, msgdata, time, smsc_id, service, account, sms_type, \
mclass, mwi, coding, compress, validity, deferred, dlr_mask, dlr_url, \
pid, alt_dcs, rpi, charset, boxc_id, binfo, meta_data ) VALUES ( \
NULL, %S, %S, %S, %S, %S, FROM_UNIXTIME(%S), %S, %S, %S, %S, %S, %S, %S,
%S, %S, %S, \
%S, %S, %S, %S, %S, %S, %S, %S, %S)"
////////////////
On Sat, 2012-02-25 at 11:34 +0700, Willy Mularto wrote:
> Oopss.. Sorry, I refer to the previous version then. Yes it is correct, the
> SQL is in the header files for SVN/latest version. Thanks for your correction
> Rene.
>
>
>
> On Feb 24, 2012, at 11:18 PM, Rene Kluwen wrote:
>
> > I think the SQL statements are gathered in the header files, not the source
> > files.
> >
> > So for the mysql engine, you need sqlbox_mysql.h and then in particular the
> > line that says SQLBOX_MYSQL_INSERT_QUERY.
> >
> > == Rene
> >
> > -----Original Message-----
> > From: users-boun...@kannel.org [mailto:users-boun...@kannel.org] On Behalf
> > Of Willy Mularto
> > Sent: Friday, 24 February, 2012 09:56
> > To: Knut Olafson
> > Cc: users@kannel.org
> > Subject: Re: Using SQLBOX - unix timestamp
> >
> > You need to do a little hack here. Go to SQLBox source code
> > (gw/sqlbox_yourdb.c) and find the line where it injects the INSERT query,
> > from here you must be able to figure it out (needs skill SQL)
> >
> >
> >
> > On Feb 24, 2012, at 3:45 PM, Knut Olafson wrote:
> >
> >>
> >> Hi there,
> >> I have the same problem. I would like to have datetime in readable format
> > in
> >> db instead of timestamp. Reading this I have changed that column in db
> > from
> >> bigint(20) to datetime and now, have something like this (for all
> > entries):
> >> 0000-00-00 00:00:00
> >> How to resolve this ??
> >> Thanks.
> >> A.
> >>
> >>
> >> Willy Mularto-2 wrote:
> >>>
> >>> change the type to datetime ;)
> >>>
> >>>
> >>> On Mar 21, 2011, at 5:47 PM, ha...@aeon.pk wrote:
> >>>
> >>>> Hi,
> >>>>
> >>>> While using sqlbox, the column 'time' in sent_sms column of kannel
> >>>> database is having timestamp values in unix time. Is there a parameter
> > in
> >>>> config by which I could control it to paste values in normal timestamps
> >>>> in the table?
> >>>>
> >>>> Thanks,
> >>>> H
> >>>
> >>> Willy Mularto
> >>> F300HD+MR18DE (NLC1725)
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>
> >> --
> >> View this message in context:
> > http://old.nabble.com/Using-SQLBOX---unix-timestamp-tp31199396p33383570.html
> >> Sent from the Kannel - User mailing list archive at Nabble.com.
> >>
> >>
> >
> > Willy Mularto
> > F300HD+MR18DE (NLC1725)
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
> Willy Mularto
> F300HD+MR18DE (NLC1725)
>
>
>
>
>
>
>
>
>
