Database Interface can't send message

[GoogleCodeExporter]

when i use database interface, it can't send message success.
i debug the source in getMessagesToSend method found that cmd.executeQuery 
can't return a correct resultset.
it means insert data after the smsserver was start can't be send.

manybe the connection have not commit?

Original issue reported on code.google.com by yzwd...@gmail.com on 25 Sep 2010 at 12:44

[GoogleCodeExporter]

Do you have any logs or anything? Is there any error message available or the 
message just silently fails>

Original comment by T.Delenikas on 26 Sep 2010 at 9:42

• Changed state: Feedback
• Added labels: Type-Support
• Removed labels: Type-Defect

[GoogleCodeExporter]

sorry, no error message. it just run normal.
run the smsserver and then insert a row to sms_out table it can't be send but 
if you insert before the smsserver run is no problem.
i find that Database.java line 271 cmd.executeQuery(...) can't return a correct 
rs.

Original comment by yzwd...@gmail.com on 27 Sep 2010 at 2:16

[GoogleCodeExporter]

Hi,

What is the value of the "settings.outbound_interval=10" setting in the 
SMSServer.conf file?

Original comment by T.Delenikas on 27 Sep 2010 at 6:29

[GoogleCodeExporter]

hi, this is my SMSServer.conf file.

settings.delete_after_processing=yes
settings.send_mode=sync
settings.inbound_interval=60
settings.outbound_interval=60

gateway.0=modem1, SerialModem
modem1.port=COM5
modem1.baudrate=9600
modem1.manufacturer=
modem1.model=
modem1.protocol=PDU
modem1.pin=0000
modem1.inbound=yes
modem1.outbound=yes
modem1.smsc_number=

interface.0=db1, Database
db1.url=jdbc:mysql://localhost:3306/test
db1.driver=com.mysql.jdbc.Driver
db1.username=mysql
db1.password=mysql
db1.type=mysql
db1.tables.sms_in=message_in
db1.tables.sms_out=message_out
db1.tables.calls=smsserver_calls
db1.batch_size=10
db1.retries=5
db1.update_outbound_on_statusreport=no

Original comment by yzwd...@gmail.com on 29 Sep 2010 at 2:48

[GoogleCodeExporter]

Hi,

Only 2 fields are required (as a minimum) in order to send a message from the 
db: the recipient and the text.

You setting is "settings.outbound_interval=60", which means that SMSServer will 
look for new messages (i.e. look in the db) every minute. Could this be the 
issue?

Original comment by T.Delenikas on 29 Sep 2010 at 6:42

[GoogleCodeExporter]

i means insert data after the smsserver was start can't be send.
if the db has data unsend, when i start the smsserver it can be send.

Original comment by yzwd...@gmail.com on 4 Oct 2010 at 5:07

[GoogleCodeExporter]

Original comment by T.Delenikas on 6 Nov 2010 at 8:09

• Changed state: Closed

[GoogleCodeExporter]

I having the same problem. I have checked all the forums no one provides the 
solution as such. Please help!

Original comment by Hazn...@gmail.com on 22 Dec 2010 at 7:25

[GoogleCodeExporter]

Can you post your config file? And some info about your setup and what you are 
trying to do?

Original comment by T.Delenikas on 22 Dec 2010 at 7:27

• Changed state: Feedback

[GoogleCodeExporter]

Problem Description: I am trying to send and receive sms from sms server 
continously. But I am facing the same problem while sending messages. I am 
using Database Interface. Then messages in the smsserver_out are only sent on 
running the smsserver (which looks for new records) but if the record is added 
while the smsserver is working then it is never sent. 

# Set a different balancer than the default.
smsserver.balancer=RoundRobinLoadBalancer

#Set a different router than the default.
#smsserver.router=NumberPoolRouter

# Lets add a modem
gateway.0=modem1, SerialModem
modem1.port=COM3
modem1.baudrate=115200
modem1.manufacturer=Teltonika
modem1.model=Teltonika TM
modem1.protocol=text
#modem1.pin=0000
modem1.inbound=yes
modem1.outbound=yes
modem1.smsc_number=
modem1.init_string=ATZ\rATZ\rATZ\r

# Dummy gateways used for testing.
#gateway.1=Test1, TestGateway
#Test1.inbound=yes
#Test1.outbound=yes
#gateway.2=Test2, TestGateway
#Test2.inbound=no
#Test2.outbound=yes
#gateway.3=Test3, TestGateway
#Test3.inbound=no
#Test3.outbound=yes
#gateway.4=Test4, TestGateway
#Test4.inbound=no
#Test4.outbound=yes
#gateway.5=Test5, TestGateway
#Test5.inbound=no
#Test5.outbound=yes
#gateway.6=Test6, TestGateway
#Test6.inbound=no
#Test6.outbound=yes
#gateway.7=Test7, TestGateway
#Test7.inbound=no
#Test7.outbound=yes
#gateway.8=Test8, TestGateway
#Test8.inbound=no
#Test8.outbound=yes

# Here is an example of a Microsoft SQL Server database interface.
#interface.0=db1, Database
#db1.url=jdbc:sqlserver://localhost:1433;SelectMethod=cursor;DatabaseName=smslib
#db1.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
#db1.username=smslib
#db1.password=smslib
#db1.type=mssql
#db1.tables.sms_in=
#db1.tables.sms_out=
#db1.tables.calls=
#db1.batch_size=50
#db1.retries=2
#db1.update_outbound_on_statusreport=no

# Here is an example of an Oracle database interface.
#interface.0=db1, Database
#db1.url=jdbc:oracle:thin:smslib/smslib@localhost:1521/XE
#db1.driver=oracle.jdbc.OracleDriver
#db1.username=smslib
#db1.password=smslib
#db1.type=oracle
#db1.tables.sms_in=
#db1.tables.sms_out=
#db1.tables.calls=
#db1.batch_size=50
#db1.retries=2
#db1.update_outbound_on_statusreport=no

# Here is an example of a MySQL database interface.
interface.0=db1, Database
db1.type=mysql
db1.url=jdbc:mysql://localhost:3306/friendfinder?autoReconnect=true
db1.driver=com.mysql.jdbc.Driver
db1.username=root
db1.password=
db1.tables.sms_in=smsserver_in
db1.tables.sms_out=smsserver_out
db1.tables.calls=smsserver_calls
db1.batch_size=50
db1.retries=2
#db1.update_outbound_on_statusreport=yes

# Here is an example of a SimpleInboundFileLogger interface.
#interface.1=file1, SimpleInboundFileLogger, inbound
#file1.filename=f:/logger.txt

# Here is an example of a HTTP interface.
#interface.2=http1, Http
#http1.get_url=http://192.168.1.1/test.php?gatewayId=%gatewayId%&text=%text%&ori
ginator=%originator%
#http1.dlr_url=http://192.168.1.1/dlr.php?gatewayId=%gatewayId%&text=%text%&from
=%from%&refNo=%refNo%
#http1.method=GET

#interface.3=httpServer, HttpServer
#httpServer.port=8080
#httpServer.password.read=0000
#httpServer.password.send=1111

#Interval for processing inbound messages (in seconds)
settings.inbound_interval=600
#Interval for processing outbound queues (in seconds)
settings.outbound_interval=10

#After reading, should SMSServer delete the processed messages from the 
gateways?
#Default is "no" for safety. Set to "yes" if you wish to delete messages.
#If you leave it to "no", SMSServer will process the same messages over and 
over again!
settings.delete_after_processing=no

# Should SMSServer work in sync or async sending mode?
# Async mode forwards messages to gateway queues.
# Values can be "sync" and "async".
settings.send_mode = async

# Allowed dispatch time-fames per priority
settings.delete_after_processing=yes
settings.timeframe.low=0900-2200
settings.timeframe.normal=0000-2359
settings.timeframe.high=0000-2359

Original comment by Hazn...@gmail.com on 22 Dec 2010 at 8:21

[GoogleCodeExporter]

I am testing with MS SQL Server and I cannot reproduce this.
Messages are retrieved and sent during startup *and* during normal operation.

Are you getting any errors on the debug log?

Original comment by T.Delenikas on 22 Dec 2010 at 9:11

[GoogleCodeExporter]

No no errors related to this, The OutboundPolling thread executes without 
error. Can it be because of mysql? Because the questioner above is also using 
mysql. :/

Original comment by Hazn...@gmail.com on 22 Dec 2010 at 9:27

[GoogleCodeExporter]

Hello, i modify the file Database.java and added a line in the file.

function getMessagesToSend, at line 366 before all close i just do a commit.

smslib version 3.4.6

    @Override
    public Collection<OutboundMessage> getMessagesToSend() throws Exception
    {
        Connection con = null;
        Collection<OutboundMessage> msgList = new ArrayList<OutboundMessage>();
        while (true)
        {
            try
            {
                OutboundMessage msg;
                Statement cmd;
                PreparedStatement pst;
                ResultSet rs;
                int msgCount;
                msgCount = 1;
                con = getDbConnection();
                cmd = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
                pst = con.prepareStatement("update " + getProperty("tables.sms_out", "smsserver_out") + " set status = 'Q' where id = ? ");
                rs = cmd.executeQuery("select id, type, recipient, text, wap_url, wap_expiry_date, wap_signal, create_date, originator, encoding, status_report, flash_sms, src_port, dst_port, sent_date, ref_no, priority, status, errors, gateway_id from " + getProperty("tables.sms_out", "smsserver_out") + " where status = 'U' order by priority desc, id");
                while (rs.next())
                {
                    if (msgCount > Integer.parseInt(getProperty("batch_size"))) break;
                    if (getServer().checkPriorityTimeFrame(rs.getInt("priority")))
                    {
                        switch (rs.getString("type").charAt(0))
                        {
                            case 'O':
                                switch (rs.getString("encoding").charAt(0))
                                {
                                    case '7':
                                        msg = new OutboundMessage(rs.getString("recipient").trim(), rs.getString("text").trim());
                                        msg.setEncoding(MessageEncodings.ENC7BIT);
                                        break;
                                    case '8':
                                    {
                                        String text = rs.getString("text").trim();
                                        byte bytes[] = new byte[text.length() / 2];
                                        for (int i = 0; i < text.length(); i += 2)
                                        {
                                            int value = (Integer.parseInt("" + text.charAt(i), 16) * 16) + (Integer.parseInt("" + text.charAt(i + 1), 16));
                                            bytes[i / 2] = (byte) value;
                                        }
                                        msg = new OutboundBinaryMessage(rs.getString("recipient").trim(), bytes);
                                    }
                                        break;
                                    case 'U':
                                        msg = new OutboundMessage(rs.getString("recipient").trim(), rs.getString("text").trim());
                                        msg.setEncoding(MessageEncodings.ENCUCS2);
                                        break;
                                    default:
                                        msg = new OutboundMessage(rs.getString("recipient").trim(), rs.getString("text").trim());
                                        msg.setEncoding(MessageEncodings.ENC7BIT);
                                        break;
                                }
                                if (rs.getInt("flash_sms") == 1) msg.setFlashSms(true);
                                if (rs.getInt("src_port") != -1)
                                {
                                    msg.setSrcPort(rs.getInt("src_port"));
                                    msg.setDstPort(rs.getInt("dst_port"));
                                }
                                break;
                            case 'W':
                                Date wapExpiryDate;
                                WapSISignals wapSignal;
                                if (rs.getTime("wap_expiry_date") == null)
                                {
                                    Calendar cal = Calendar.getInstance();
                                    cal.setTime(new Date());
                                    cal.add(Calendar.DAY_OF_YEAR, 7);
                                    wapExpiryDate = cal.getTime();
                                }
                                else wapExpiryDate = rs.getTimestamp("wap_expiry_date");
                                if (rs.getString("wap_signal") == null) wapSignal = WapSISignals.NONE;
                                else
                                {
                                    switch (rs.getString("wap_signal").charAt(0))
                                    {
                                        case 'N':
                                            wapSignal = WapSISignals.NONE;
                                            break;
                                        case 'L':
                                            wapSignal = WapSISignals.LOW;
                                            break;
                                        case 'M':
                                            wapSignal = WapSISignals.MEDIUM;
                                            break;
                                        case 'H':
                                            wapSignal = WapSISignals.HIGH;
                                            break;
                                        case 'D':
                                            wapSignal = WapSISignals.DELETE;
                                            break;
                                        default:
                                            wapSignal = WapSISignals.NONE;
                                    }
                                }
                                msg = new OutboundWapSIMessage(rs.getString("recipient").trim(), new URL(rs.getString("wap_url").trim()), rs.getString("text").trim(), wapExpiryDate, wapSignal);
                                break;
                            default:
                                throw new Exception("Message type '" + rs.getString("type") + "' is unknown!");
                        }
                        msg.setPriority(rs.getInt("priority"));
                        if (rs.getInt("status_report") == 1) msg.setStatusReport(true);
                        if ((rs.getString("originator") != null) && (rs.getString("originator").length() > 0)) msg.setFrom(rs.getString("originator").trim());
                        msg.setGatewayId(rs.getString("gateway_id").trim());
                        msgList.add(msg);
                        getMessageCache().put(msg.getMessageId(), rs.getInt("id"));
                        pst.setInt(1, rs.getInt("id"));
                        pst.executeUpdate();
                        con.commit();
                        msgCount++;
                    }
                }
                                con.commit();
                rs.close();
                cmd.close();
                pst.close();
                break;
            }
            catch (SQLException e)
            {
                try
                {
                    if (con != null) con.close();
                    closeDbConnection();
                }
                catch (Exception innerE)
                {
                }
                getService().getLogger().logError(String.format("SQL failure, will retry in %d seconds...", (sqlDelayMultiplier * (SQL_DELAY / 1000))), e, null);
                Thread.sleep(sqlDelayMultiplier * SQL_DELAY);
                sqlDelayMultiplier *= 2;
            }
        }
        return msgList;
    }

Original comment by yzwd...@gmail.com on 22 Dec 2010 at 9:30

[GoogleCodeExporter]

Oh, no, i use mysql too...

Original comment by yzwd...@gmail.com on 22 Dec 2010 at 9:32

[GoogleCodeExporter]

No worries :)

I don't currently have a MySQL around, but I'll grab one from Amazon to test 
this.

Original comment by T.Delenikas on 22 Dec 2010 at 9:38

• Changed state: Started

[GoogleCodeExporter]

yzwdong did your fix, still isn't working. I fear it is something about mysql 
... Waiting for T.Delenikas to find out :)

Original comment by Hazn...@gmail.com on 22 Dec 2010 at 9:54

[GoogleCodeExporter]

yzwdong have you resolved your issue?

Original comment by Hazn...@gmail.com on 22 Dec 2010 at 11:32

[GoogleCodeExporter]

Well, everything works ok with MySQL as well...
I am scratching my head now!

Original comment by T.Delenikas on 22 Dec 2010 at 1:04

• Changed state: Feedback

[GoogleCodeExporter]

yes, after i added the line.

Original comment by yzwd...@gmail.com on 23 Dec 2010 at 3:08

[GoogleCodeExporter]

Thanks a lot T.Delenikas for going a long way to test it for me. Still am 
unable to resolve it, can this be because of some open database connections 
with smslib database? Because i am using a web client to send data into the 
smsserver_out table.

Original comment by Hazn...@gmail.com on 23 Dec 2010 at 7:36

[GoogleCodeExporter]

Please send me your Database.java

Original comment by Hazn...@gmail.com on 23 Dec 2010 at 8:16

[GoogleCodeExporter]

I didn't do anything special. Used the stock SMSLib with a similar config file 
and an Amazon RDS database.

There must be something related to the MySQL setup that we are missing... Why 
does this "commit" fix things? Are you running InnoDB (I did)?

Original comment by T.Delenikas on 23 Dec 2010 at 8:22

[GoogleCodeExporter]

hi, i also running InnoDB, and i also use jsp to send data into the message out 
table.
just added the "commit" fix can run normal.

Original comment by yzwd...@gmail.com on 23 Dec 2010 at 9:26

[GoogleCodeExporter]

Finally Praise be to the Lord of the Worlds. I got it running. The problem was 
quite stupid, I had changed my database engine for all smslib tables to InnoDB, 
and it was causing problem. Now everything is swift and sound. Thanks to all of 
you guys too :)

Original comment by Hazn...@gmail.com on 24 Dec 2010 at 7:21

[GoogleCodeExporter]

So, if you don't have InnoDB enabled, an explicit commit is required?

Original comment by T.Delenikas on 24 Dec 2010 at 8:48

[GoogleCodeExporter]

Nope its the other way around I guess, I did'nt do commit.

Original comment by Hazn...@gmail.com on 24 Dec 2010 at 10:34

[GoogleCodeExporter]

I had the same issue, adding commit solves the issue

Original comment by yuriyvik...@gmail.com on 15 Jan 2011 at 11:00

[GoogleCodeExporter]

Original comment by T.Delenikas on 15 Jan 2011 at 7:55

• Changed state: Accepted
• Added labels: Component-SMSLib, Milestone-v3.5.1, Priority-High, Type-Defect
• Removed labels: Type-Support

[GoogleCodeExporter]

r2280 - Thank you all :)

Original comment by T.Delenikas on 15 Jan 2011 at 8:08

• Changed state: Completed

[GoogleCodeExporter]

may be you should also add some config parameter for autocommit to mysql 
interface cause of:
dbCon.setAutoCommit(false);
in getDbConnection() method

Original comment by yuriyvik...@gmail.com on 16 Jan 2011 at 3:16

[GoogleCodeExporter]

Why should I do that?
I mean, I want autocommit=false in order to handle it via explicit commit 
statements. Am I missing something?

Original comment by T.Delenikas on 16 Jan 2011 at 3:22

[GoogleCodeExporter]

Sure, you are right

Original comment by yuriyvik...@gmail.com on 16 Jan 2011 at 3:50

[GoogleCodeExporter]

Dears..
Am using SQL Server, instead, and also sms can't be sent. No error is reported.
Is there anything wrong?

Regards...

Original comment by arsenio....@gmail.com on 26 Jan 2011 at 10:18

[GoogleCodeExporter]

I am using SQL Server for years.
Something else must be wrong. Are you getting any errors? Are inbound messages 
stored in the database?

Original comment by T.Delenikas on 29 Jan 2011 at 12:18

[GoogleCodeExporter]

Hi,
Sorry to hijack this thread. I have a similar issue. I am using smsserver with 
Huawei e220 on linux and ive used both RxTx and javacomm3 on different 
occasions (with smslib 3.4.6). In my situation I have 2 machines. On one 
machine, everything works fine, either with rxtx or with javacomm.

On the other machine, the sms is sent multiple times. With comm3, Whenever is 
is sent, the message status is either updated to Q or U. The message is sent 3 
times before being marked as F (failed). With RxTx the message is sent 
continuously until I stop the smsserver (I didn't check the status updates).

On the other hand, the problematic machine is a P3 so I suspect hardware 
issues. I will try out the new version 3.5 and see....

Original comment by frank.ch...@gmail.com on 24 Feb 2011 at 8:10

[GoogleCodeExporter]

Original comment by T.Delenikas on 22 Apr 2011 at 8:25

• Changed state: Fixed