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;

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