{"id":723,"date":"2014-07-26T11:23:11","date_gmt":"2014-07-26T11:23:11","guid":{"rendered":"http:\/\/blog.silverterra.net\/?p=723"},"modified":"2014-07-26T11:23:11","modified_gmt":"2014-07-26T11:23:11","slug":"support-oracle-with-an-external-application-over-tcp","status":"publish","type":"post","link":"https:\/\/blog.silverterra.net\/?p=723","title":{"rendered":"support oracle with an external application over tcp"},"content":{"rendered":"

Goal: integrate an external application data to my database<\/p><\/blockquote>\n

I had a small desktop application which is asking a web page about information.
\nThe application itself is a simple web robot. Login with credentials, passing through some pages to get the correct information.
\nUsing GET, POST methods as well in the process and at the end of the line dig the data from an incorrect structured web page<\/strong>.<\/p>\n

First I did a model with an integrated WebBrowser control to see how it working. Good because:<\/p>\n

    \n
  1. I can see the process itself<\/il>\n
  2. I can forget all of cookies and credentials<\/il>\n<\/ol>\n

    This is fine to test, but bad idea to production because:<\/p>\n

      \n
    1. the webBrowser rendering the hole page (all elements on it such as: images, stylesheets, js files) – time factor<\/il>\n
    2. the webBrowser core itself is Internet Explorer – this is enough to die<\/il>\n
    3. the webBrowser will process all the javascript codes on the page<\/il>\n<\/ol>\n

      The second step is to do it with low level webRequests. Passing cookies correctly, doing credentials is a new to do, but I can ignore everything else easily I can focus on the process and the pure data and in time!<\/p>\n

      The sturcture is the following<\/strong>
      \nWebDB based web site -> my oracle — (tcp link) –> a c# application<\/p>\n

      the process is (typically 1-2 sec)<\/strong>
      \nwatching process (to check the external program is running or not)<\/p>\n

        \n
      1. ping the external application on the specified tcp port<\/il>\n<\/ol>\n

        asking process (get the data)<\/p>\n

          \n
        1. push the asking button on the web site<\/il>\n
        2. if the external application is available then do the next (if not -> stop)<\/il>\n
        3. prepare the data for the question into a table<\/il>\n
        4. send the request to the external application<\/il>\n
        5. wait for the answer<\/il>\n
        6. read the answer<\/il>\n
        7. show the answer on the page<\/il>\n<\/ol>\n

          on the oracle side<\/strong>
          \nwatching process (to check the external program is running or not)<\/p>\n

          \r\n...\r\nport number := 12456; -- this is the lister port\r\n...\r\nfunction fu_ping_external return varchar2 is\r\nl_req varchar2(32000);\r\nconn utl_tcp.connection;\r\nl_ret varchar2(100);\r\nbegin\r\n  begin\r\n    l_ret := null;    \r\n    conn := utl_tcp.open_connection('localhost',port);\r\n    l_ret := utl_tcp.write_line(conn,'DBF'||'|'||a_sys.session_user);\r\n    utl_tcp.flush(conn);\r\n   BEGIN\r\n     LOOP\r\n       l_ret := l_ret ||(utl_tcp.get_line(conn, TRUE));  -- read result\r\n     END LOOP;\r\n   EXCEPTION\r\n     WHEN utl_tcp.end_of_input THEN\r\n       NULL; -- end of input\r\n   END;\r\n  end;\r\n  \r\n  utl_tcp.close_connection(conn);\r\n  return l_ret;\r\n\r\nend;\r\n<\/pre>\n
          The real answering procedure is similar but with a small wait cycle to keep the control on it.<\/p>\n
          \r\n...\r\n  l_ret := fu_start_listener('STOREINFO'); -- ping the listener (start the process)\r\n\r\n  FOR i IN 1 .. 30 -- wait for 30 sec, then return\r\n  LOOP\r\n    dbms_lock.sleep(1);\r\n    select answre from ... -- check the answer\r\n    IF answer = 1 THEN     -- we have a measurable answer\r\n      ... -- do what you want\r\n      return;\r\n    END IF;\r\n  END LOOP;\r\n\r\n  -- we are over 30 sec and we have no answer \r\n  ... -- do what you want\r\n\r\n...    \r\n<\/pre>\n
          on the c# side<\/strong>
          \nWe need to create a simple web server with TcpListener.<\/p>\n
          \r\npublic class TcpTimeServer\r\n{\r\n\r\n  public string Listen(int portNum)\r\n  {\r\n    string ret = "";\r\n    bool done = false;\r\n\r\n    TcpListener listener = new TcpListener(portNum);\r\n\r\n    listener.Start();\r\n\r\n    while (!done)\r\n    {\r\n      \/\/ Waiting for connection...\r\n      TcpClient client = listener.AcceptTcpClient();\r\n\r\n      \/\/ Connection accepted.\r\n      Application.DoEvents();\r\n\r\n      NetworkStream ns = client.GetStream();\r\n\r\n      byte[] byteTime = Encoding.ASCII.GetBytes(DateTime.Now.ToString());\r\n\r\n      try\r\n      {\r\n        ret = ReadStream(ns);\r\n        ns.Write(byteTime, 0, byteTime.Length);\r\n        ns.Close();\r\n        client.Close();\r\n        done = true;\r\n      }\r\n      catch (Exception e)\r\n      {\r\n        Console.WriteLine(e.ToString());\r\n      }\r\n    }\r\n\r\n    listener.Stop();\r\n\r\n    return ret;\r\n  }\r\n\r\n  public string ReadStream(NetworkStream myNetworkStream)\r\n  {\r\n    \/\/ Check to see if this NetworkStream is readable.\r\n    if (myNetworkStream.CanRead)\r\n    {\r\n      byte[] myReadBuffer = new byte[1024];\r\n      StringBuilder myCompleteMessage = new StringBuilder();\r\n      int numberOfBytesRead = 0;\r\n      \r\n      do \/\/ incoming message may be larger than the buffer size\r\n      {\r\n        numberOfBytesRead = myNetworkStream.Read(myReadBuffer, 0, myReadBuffer.Length);\r\n        myCompleteMessage.AppendFormat("{0}", Encoding.ASCII.GetString(myReadBuffer, 0, numberOfBytesRead));\r\n      }\r\n      while (myNetworkStream.DataAvailable);\r\n\r\n      \/\/ Print out the received message to the console.\r\n      return myCompleteMessage.ToString();\r\n    }\r\n    else\r\n    {\r\n      return "Sorry.  You cannot read from this NetworkStream.";\r\n    }\r\n\r\n  }\r\n\r\n}\r\n<\/pre>\n
          We need to create a new thread for this listener.
          \nThe simpliest way is to do with a BackgroundWorker<\/code> like this:<\/p>\n
          \r\n...\r\n  \/\/ bindable event\r\n  public event delCommandReceived CommandReceived;\r\n...\r\n  private System.ComponentModel.BackgroundWorker bgw1;\r\n...\r\n  this.bgw1.DoWork += new DoWorkEventHandler(bgw1_DoWork);\r\n  this.bgw1.ProgressChanged += new ProgressChangedEventHandler(bgw1_ProgressChanged);\r\n...\r\n  \/\/ here is the worker process\r\n  void bgw1_DoWork(object sender, DoWorkEventArgs e)\r\n  {\r\n    BackgroundWorker bg = sender as BackgroundWorker;\r\n    do\r\n    {\r\n      TcpTimeServer ts = new TcpTimeServer();\r\n      string ret = ts.Listen((int)e.Argument);\r\n      bg.ReportProgress(1, ret);\r\n    } while (true);\r\n    e.Cancel = true;\r\n  }\r\n  void bgw1_ProgressChanged(object sender, ProgressChangedEventArgs e)\r\n  {\r\n    if (this.CommandReceived != null)\r\n      this.CommandReceived((string)e.UserState);\r\n  }\r\n\r\n  \/\/ and the links to manage the class\r\n  public void Start(int portListen)\r\n  {\r\n    this.port = portListen;\r\n    if (!bgw1.IsBusy)\r\n      bgw1.RunWorkerAsync((object)port);\r\n  }\r\n\r\n  public void Stop()\r\n  {\r\n    bgw1.CancelAsync();\r\n  }\r\n\r\n<\/pre>\n
          in the host application<\/p>\n
          \r\n...\r\n  private ucTcpListener ucTcpListener1; \/\/ my tcp server\r\n...\r\n  this.ucTcpListener1.CommandReceived += new delCommandReceived(ucTcpListener1_CommandReceived);\r\n...\r\n  void ucTcpListener1_CommandReceived(string command)\r\n  {\r\n    if (command.StartsWith("DBF"))\r\n      CreateFiles(); \/\/ here is the real trigger\r\n  }\r\n...\r\n<\/pre>\n
          simple…<\/p>\n","protected":false},"excerpt":{"rendered":"
          Goal: integrate an external application data to my database I had a small desktop application which is asking a web page about information. The application itself is a simple web robot. Login with credentials, passing through some pages to get the correct information. Using GET, POST methods as well in the process and at the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,2],"tags":[],"class_list":["post-723","post","type-post","status-publish","format-standard","hentry","category-net","category-oracle"],"_links":{"self":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts\/723","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=723"}],"version-history":[{"count":1,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts\/723\/revisions"}],"predecessor-version":[{"id":724,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=\/wp\/v2\/posts\/723\/revisions\/724"}],"wp:attachment":[{"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=723"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=723"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.silverterra.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=723"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}