Links

   Quran Explorer - Interactive Audio Recitations & Translations

Friday, June 27, 2014

Dynamic SQL UPDATE


NEED TO UPDATE table columns with dynamic values (not known in advance).

A. ORACLE 

i.Single-Column update
   [Working example.. just replace table names]

   UPDATE station s
        SET vhf_network_id = 
                (
                SELECT vhf_network_id
                FROM vhf_network v  
                WHERE s.client_license_id = v.client_license_id
                )
        WHERE s.station_charge_id = 5;
 
ii. Multiple update
    The key here is to maintain the order and count of the columns in the source columnlist and destination columnlist

  UPDATE
    SET () = 
          (
          SELECT
          FROM
          WHERE
          )
    WHERE ;





B. POSTGRES EQUIVALENT


   Working example (difference with the above is the absence of INNER SELECT)

  UPDATE station s
      SET vhf_network_id = v.vhf_network_id
    FROM vhf_network v
    WHERE s.client_license_id = vhf_network.client_license_id;


Friday, June 6, 2014

Delivery Reports (DLR) in kannel

Good Mawwwwwning nerdmigos,

You (name hidden) requested my assistance on doing DLR in kannel. There you are, kaz qwako

There are a few approaches but the following (and derivaties) has never let me down.

I dont know if its only me, but on kannel release 1.4.3 you are ONLY able to get DLR when u set dlr-mask to 31


The following approach (or similar) has never let me down...

a. kanne.conf

#TRIGGER
group = sms-service
keyword = trigger
aliases = triger
get-url = "http://192.168.1.45:8080/mwasima/trigger?keyword=%k&client=%s&service=%s&sender=%p&time=%t&dlrmask=31&dbtype=postgres&smsgateway=192.168.1.30"
omit-empty = true


and the following (proxy) function to push sms

b.  IOLogic.java


public static String sendSMS(int smsid, String receiver, String sms, boolean flash, String webapp, String smsgateway){
        String clean_sms = sms.replace(" ","%20");        //we can do better than this !!!!!
       
        String reply = "";

        log.info("ATTEMPTING to send [" + sms + "] to " + receiver);

        dlrurl = encodeURL(webapp + "/dlr?smsid=" + smsid + "&dest=" + receiver  + "&type=%d&dlr=%A");

       
        try{
       
            URL send_sms_url = new URL("http://" + smsgateway + ":13013/cgi-bin/sendsms?username=" + ux +"&password=" + px +(flash==true?"&mclass=0":"")+ "&to=" + receiver +"&text=" + clean_sms + "&dlr-mask=" + dlrmask + "&dlr-url=" + dlrurl);
            log.info("send sms url = " + send_sms_url);

            URLConnection url_con = send_sms_url.openConnection();
            BufferedReader in = new BufferedReader(new InputStreamReader(url_con.getInputStream()));
            String inputLine;

            while ((inputLine = in.readLine()) != null){
                reply += inputLine;
                }

            in.close();
            return reply;
            }
        catch(IOException e){
            log.severe("Error at sendSMS(): " + e.getMessage());
            return "IOException [" + receiver + ", " + sms + "]";
            }
        }




and the finaly the following function to persist DLR

c.  IOLogic.java

    //INSERT DELIVERY REPORTS
    public static void insDLR(DataSource d, int _smsID, String _smsText, String _dest, String _status, String _report){

           try{
              Connection con = null;
               con = d.getConnection();
               con.setAutoCommit(false);

              PreparedStatement ps = con.prepareStatement("INSERT INTO sms_dlr_log(sms_id, sms_text, destination, dlr_status, dlr_report) VALUES(?,?,?,?,?)");  

              ps.setInt(1, _smsID);
              ps.setString(2, _smsText);
              ps.setString(3, _dest);
              ps.setString(4, _status);
              ps.setString(5, _report);

              int u = ps.executeUpdate();

              con.commit();        //explicit commit

              con.setAutoCommit(true);
              con.close();
               }

          catch(SQLException ex){
               log.severe("Message:" + ex.getMessage() + ": Failed to log DLR");
              }
          catch(ClassCastException exx){
               log.severe("Message:" + exx.getMessage() + ": Failed to log DLR");
               }

        }



Easy isnt it ??
Question: what if a Servlet wants to reply directly (instead of calling sendsms url) to the request and at the same time ask for a DLR ?? 
Answer (tongue in cheek): we need to employ x-kannel-headers

In ur doGet() method do the following



public void doGet(HttpServletRequest request, HttpServletResponse response)throws IOException, ServletException {

      this.request = request;
        this.response = response;
        response.setContentType("text/html");

        webapp = "http://" + request.getServerName() + ":" + request.getLocalPort() + request.getContextPath();
    //We need to set accept-x-kannel-headers = true in kannel.conf
    response.addHeader("X-Kannel-DLR-Mask", "31");
        smsbody = request.getParameter("smsbody");       
    receiver = request.getParameter("receiver");

    dlrurl = webapp + "/dlr?smsid=1&dest=" + receiver + "&type=%d&dlr=%A&localsmsid=%I&remotesmsid=%F&smsuser=%n";
        response.addHeader("X-Kannel-DLR-Url", dlrurl);
        this.out = response.getWriter();

       //DO UR MAGIC HERE
    //then finaly send ur reply
        out.println("UR REPLY HERE");
    }



...NOW U CAN HUG URSELF

Thursday, May 8, 2014

Django QuerySet Operators

Supports the following
 
exact
iexact
contains
icontains
in
gt
gte
lt
lte
startswith
istartswith
endswith
iendswith
range
year
month
day
week_day
isnull
search
regex
iregex
 
 
Example: 
queryset = Tender.objects.filter(deadline__gt = datetime.datetime.now())
 

Wednesday, April 30, 2014

Gaffes


Since this blog is about my own experience while coding away my sanity, i think it would be fir enought to share my blunders too... not just wat works!!!!!!!!

In no particular order....

1. Strange PostgreSQL Error message
    "ERROR:  input of anonymous composite types is not implemented "

     This is an error message i had never encountered before but , against my own advice, instead of backtracking and looking for documentation of the error or googling, i went straight ahead to my new ui code and tried to debug.... i wasted almost 8 man hours... a whole official day in other words.

Later on i discovered that i had forgoten to specify a datataype for a db column in the sql script !!!!!!!!


2. New Django Models 'Refusing' to appear on the Admin site
   Was in 'debug' mode for about 2 days

   Found out that i had forgoten to register the new Models at admin.py




Friday, April 11, 2014

Asset Tracking via GPS

Implemented using a combination of Traccar and OpenGTS (and Google Earth)


On OpenGTS side i only needed
track.war (Web interface)
events.war (data retrieval by robots, GoogleEarth etc)
gprmc.war (http interface for (some)GPS devices)

Traccar was the actual 'engine' running the protocols and accepting connections and eventualy pushing them into the DB.


Process:
1. Build OpenGTS then dump the database
2. Update Traccars traccar.cfg to point to the opengts db
3. Run the service and wait for connections


Contact me for a demo link