bug in sqlbox (was: Re: sqlbox included in head cvs?)
Hi Rene!
Can it be that I am the first person using sqlbox with postgresql?
I found some postgresql related bugs in sqlbox_pgsql.c from
sqlbox_patch-20041031.tar. (Looks like copy and paste bugs)
I would be great if you can review the suggested changes and include
them into your patch.
1.
When you are trying to get the username and password for the postgres
connection from the configuration file, your are looking for the wrong
parameters.
They are called username and password instead of pgsql-username
and pgsql-password. (this differs from the mysql syntax)
The follwing works now:
if (!(pgsql_user = cfg_get(grp, octstr_imm(username
panic(0, SQLBOX: PGSQL: directive 'username' is not specified!);
if (!(pgsql_pass = cfg_get(grp, octstr_imm(password
panic(0, SQLBOX: PGSQL: directive 'password' is not specified!);
2.
Another error when creating the dbpool:
pool = dbpool_create(DBPOOL_PGSQL, db_conf, pool_size);
instead of
pool = dbpool_create(DBPOOL_MYSQL, db_conf, pool_size);
3.
I have to comment the part where you create the tables in case they
don't exist, because this SQL syntax (to check the existenz of a table)
is not supported by postgresql. Also the auto_increment is not supported
by postgresql - this will be handled by a SERIAL/SEQUENCE.
/* create send_sms sent_sms tables if they do not exist */
/* does not work with postgresql
sql = octstr_format(CREATE TABLE IF NOT EXISTS %S ( ...
sql_update(sql);
octstr_destroy(sql);
sql = octstr_format(CREATE TABLE IF NOT EXISTS %S ( ...
sql_update(sql);
octstr_destroy(sql);
*/
IMO, I think it is better to remove the table creation from the sqlbox
and therefore put some CREATE TABLE statements into a separate file for
mysql and postgresql.
These are the SQL statements I used to create the tables (tested with
postgresql 7.2):
CREATE TABLE sent_sms (
sql_id SERIAL primary key,
momt VARCHAR(5) CHECK (momt IN('MO','MT','NULL')) default 'NULL',
sender varchar(20) null,
receiver varchar(20) null,
udhdata varchar(255) null,
msgdata varchar(255) null,
time bigint null,
smsc_id varchar(255) null,
service varchar(255) null,
account varchar(255) null,
id bigint null,
sms_type bigint null,
mclass bigint null,
mwi bigint null,
coding bigint null,
compress bigint null,
validity bigint null,
deferred bigint null,
dlr_mask bigint null,
dlr_url varchar(255) null,
pid bigint null,
alt_dcs bigint null,
rpi bigint null,
charset varchar(255) null,
boxc_id varchar(255) null,
binfo varchar(255) null
);
CREATE TABLE send_sms (
sql_id SERIAL primary key,
momt VARCHAR(5) CHECK (momt IN('MO','MT','NULL')) default 'NULL',
sender varchar(20) null,
receiver varchar(20) null,
udhdata varchar(255) null,
msgdata varchar(255) null,
time bigint null,
smsc_id varchar(255) null,
service varchar(255) null,
account varchar(255) null,
id bigint null,
sms_type bigint null,
mclass bigint null,
mwi bigint null,
coding bigint null,
compress bigint null,
validity bigint null,
deferred bigint null,
dlr_mask bigint null,
dlr_url varchar(255) null,
pid bigint null,
alt_dcs bigint null,
rpi bigint null,
charset varchar(255) null,
boxc_id varchar(255) null,
binfo varchar(255) null
);
Note: postgres will automatically create the sequences
sent_sms_sql_id_seq and send_sms_sql_id_seq. Don't forget to give
proper permissions to the tables and the SEQUENCE for the kannel DB
user. If, for example the database user for kannel is called kannel,
you can grant permissions using:
GRANT INSERT, SELECT, UPDATE, DELETE ON sent_sms TO kannel;
GRANT INSERT, SELECT, UPDATE, DELETE ON send_sms TO kannel;
GRANT INSERT, SELECT, UPDATE ON sent_sms_sql_id_seq TO kannel;
GRANT INSERT, SELECT, UPDATE ON send_sms_sql_id_seq TO kannel;
4.
The changes in 3 lead to problems when inserting into the tables. Just
remove the sql_id when inserting and everything works fine:
sqlbox_pgsql.c: ~ line 200
// values = octstr_format(NULL, %S, %S, %S, %S, %S, %S, %S ...
values = octstr_format( %S, %S, %S, %S, %S, %S, %S ...
// sql = octstr_format(INSERT INTO %S (sql_id, momt, sender ...
sql = octstr_format(INSERT INTO %S (momt, sender ...
5.
If the postgresql database will be restarted/shut down, the sqlbox stops
- this is not good. The sqlbox should try to reconnect after a few
seconds (queuing INSERT would be fine but is not necessary).
Debug output:
2004-11-26 17:45:18 [17818] [1] ERROR: PGSQL: FATAL 1: This connection
has been terminated by the administrator.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
2004-11-26 17:45:19 [17818] [1] ERROR: PGSQL:
Re: sqlbox included in head cvs?
Hello Klaus, An answer to your question, one by one: Sqlbox is not included in CVS as of yet. You can compile with either MySQL or Postgres support alternatively. Both will work. The latest patch is against CVS version of Oktober 31th but should be applicable to current HEAD as well. In case you get errors, please post on the list or contact me directly and I will generate a new patch. Hope this helps, Rene Kluwen Chimit Klaus Darilion wrote: Hi all! I'm using kannel 1.3.2 for connecting to an SMSC and sending/receiving SMS works fine. In the mailing list archive I found some threads about an SQLBOX, which sounds great and I like to use. But I'm little confused about the current status of the sqlbox: Is it already included in the kannel main distribution or do I have to patch? If included: Can I use the available debian packages or do I have to compile myself for postgres support? If patch: Which kannel version with which patch? Can I patch against current CVS or do I have to check out a certain tagged release? Thanks for help, Klaus
Re: sqlbox included in head cvs?
Hi Rene! Thanks for your answers. I will try it step by step. I now compile CVS from source with pgsql support and test the DB-connectivity using DLR storage. If this works, i will test the sqlbox. I reviewed the patch and found some stuff for MSSQL. Sothis patch is not only for sqlbox, but also for additional DB support? thanks for help klaus Rene Kluwen wrote: Hello Klaus, An answer to your question, one by one: Sqlbox is not included in CVS as of yet. You can compile with either MySQL or Postgres support alternatively. Both will work. The latest patch is against CVS version of Oktober 31th but should be applicable to current HEAD as well. In case you get errors, please post on the list or contact me directly and I will generate a new patch. Hope this helps, Rene Kluwen Chimit Klaus Darilion wrote: Hi all! I'm using kannel 1.3.2 for connecting to an SMSC and sending/receiving SMS works fine. In the mailing list archive I found some threads about an SQLBOX, which sounds great and I like to use. But I'm little confused about the current status of the sqlbox: Is it already included in the kannel main distribution or do I have to patch? If included: Can I use the available debian packages or do I have to compile myself for postgres support? If patch: Which kannel version with which patch? Can I patch against current CVS or do I have to check out a certain tagged release? Thanks for help, Klaus
Re: sqlbox included in head cvs?
Klaus Darilion klaus.kannel at pernau.at writes: hi Klaus, just want to ask u something about receiving sms from the smsc. do i need to specify any port for the smsc to connect to my server? or the current port and receving-port variable will be enough? thx for your time. Hi all! I'm using kannel 1.3.2 for connecting to an SMSC and sending/receiving SMS works fine. In the mailing list archive I found some threads about an SQLBOX, which sounds great and I like to use. But I'm little confused about the current status of the sqlbox: Is it already included in the kannel main distribution or do I have to patch? If included: Can I use the available debian packages or do I have to compile myself for postgres support? If patch: Which kannel version with which patch? Can I patch against current CVS or do I have to check out a certain tagged release? Thanks for help, Klaus
Re: sqlbox included in head cvs?
Hi Jason! I'm also a newbie, but this is my working configuration (NowSMS acts as SMSC). # the bearerbox connects to the SMSCs group = smsc smsc = smpp smsc-id = klaussmsc host = 8.12.12.12 # transceiver only supported bym SMPP =3.4 # transceiver-mode = 1 # # these ports are configured at the NowSMS software port = 12000 receive-port = 12000 smsc-username = asdf # smsc password max. 8 letters smsc-password = afsgsdg system-type = VMA #The interface version will be reported to the SMSC #interface-version = 33 interface-version = 34 # numbers which are allowed to send SMS messages to us (to kannel via smsc) address-range = idle-timeout = 300 regards, klaus jason wrote: Klaus Darilion klaus.kannel at pernau.at writes: hi Klaus, just want to ask u something about receiving sms from the smsc. do i need to specify any port for the smsc to connect to my server? or the current port and receving-port variable will be enough? thx for your time. Hi all! I'm using kannel 1.3.2 for connecting to an SMSC and sending/receiving SMS works fine. In the mailing list archive I found some threads about an SQLBOX, which sounds great and I like to use. But I'm little confused about the current status of the sqlbox: Is it already included in the kannel main distribution or do I have to patch? If included: Can I use the available debian packages or do I have to compile myself for postgres support? If patch: Which kannel version with which patch? Can I patch against current CVS or do I have to check out a certain tagged release? Thanks for help, Klaus
Re: sqlbox included in head cvs?
Hello Klaus, Yeah, there's priliminary code for MS-SQL for dlr storage and sqlbox tables (sending and receiving). However, this code has never been tested by myself and AFAIK also not by others. Rene Kluwen Chimit Klaus Darilion wrote: Hi Rene! Thanks for your answers. I will try it step by step. I now compile CVS from source with pgsql support and test the DB-connectivity using DLR storage. If this works, i will test the sqlbox. I reviewed the patch and found some stuff for MSSQL. Sothis patch is not only for sqlbox, but also for additional DB support? thanks for help klaus Rene Kluwen wrote: Hello Klaus, An answer to your question, one by one: Sqlbox is not included in CVS as of yet. You can compile with either MySQL or Postgres support alternatively. Both will work. The latest patch is against CVS version of Oktober 31th but should be applicable to current HEAD as well. In case you get errors, please post on the list or contact me directly and I will generate a new patch. Hope this helps, Rene Kluwen Chimit Klaus Darilion wrote: Hi all! I'm using kannel 1.3.2 for connecting to an SMSC and sending/receiving SMS works fine. In the mailing list archive I found some threads about an SQLBOX, which sounds great and I like to use. But I'm little confused about the current status of the sqlbox: Is it already included in the kannel main distribution or do I have to patch? If included: Can I use the available debian packages or do I have to compile myself for postgres support? If patch: Which kannel version with which patch? Can I patch against current CVS or do I have to check out a certain tagged release? Thanks for help, Klaus
Re: sqlbox included in head cvs?
hi jason yes u require atleast one port to connect to the smsc, if u keep just current port(nothing but the transmitter port if u opt for separate ports for Rx and Tx) and receiving port means (the receiver port), if u go for Transceiver mode to ur smsc then u need to just have the current port with the port number given by ur smsc provider and U need to enable the Transceiver mode = true. On Thu, 2004-11-25 at 20:24, jason wrote: Klaus Darilion klaus.kannel at pernau.at writes: hi Klaus, just want to ask u something about receiving sms from the smsc. do i need to specify any port for the smsc to connect to my server? or the current port and receving-port variable will be enough? thx for your time. * This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorized review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly prohibited and may be unlawful. *
