DDevelopment & Design

Subtle feature of CONNECT BY

Hierarchical queries (CONNECT BY … START WITH …) are used when retrieving data from tree-like structures. You use them if for example you want to retrieve all people who are directly or indirectly below a certain person in a company hierarchy. However, if you use joins and add some other conditions in the WHERE clause then the effect of the query is not obvious.

The good description of hierarchical queries can be found on the page

http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/queries4a.htm#2053937 There are 2 important points in this article:
* If the|WHERE| predicate contains a join, Oracle applies the join predicates /before/ doing the |CONNECT| |BY| processing.
* If the |WHERE| clause does not contain a join, Oracle applies all predicates other than the|CONNECT| |BY| predicates _/after/_doing the |CONNECT| |BY|processing without affecting the other rows of the hierarchy. What does it mean? How does it work? In order to explain this I will describe the error I came across before I understood how the hierarchical queries really work. The query was meant to return all direct and not direct superiors for a given customer (some customers work in companies).

SELECT c.* FROM customer_all c
CONNECT BY PRIOR c.customer_id_high = c.customer_id
START WITH c.customer_id = :cust_id

This worked fine. However we had to change the query in order to return billing address of customers. Each customer can have many addresses, but only one of them is a billing one. Such address has the flag CCBILL set to ‘X’. Thus, the query was changed into this one:

SELECT level, c.*, cc.*
  FROM customer_all c,
       ccontact_all cc
 WHERE c.customer_id = cc.customer_id
   AND cc.ccbill = 'X'
CONNECT BY PRIOR c.customer_id_high = c.customer_id
START WITH c.customer_id = :cust_id

That was a mistake. The condition c.customer_id = cc.customer_id was applied before CONNECT BY whearas cc.ccbill = ‘X’ was executed after CONNECT BY. A customer (let’s suppose his/her customer_id = 123456) had two addresses, while his superior had one address and his superior’s superior had also one address. The query was executed in the following way (I omit stage irrelevant to this example): Stage 1: 2 records are found for the client 123456: one for his billing address and one for another address. They have LEVEL=1 Stage 2: CONNECT BY clause is applied to both records with LEVEL=1 from the previous stage. Thus, the superior of 123456 is added twice to the result set and there are two records with LEVEL=2 Stage 3: CONNECT BY clause is applied to both records with LEVEL=2 from the previous stage. Thus the superior of the superior of 123456 is added twice to the result set and there are two records with LEVEL=3 Stage 4: The non-billing address of customer 123456 is removed from the result set, but his superiors records which were added because of this record are not removed, as their address is not a billing one (they do not have any non-billing address). As a result, the query returned 5 records, although it was expected to return 3 records. The records of the superior and superior’s superior were duplicated because of the second address of 123456. The record with the second address was removed but the branch it created was preserved. The solution was to check in CONNECT BY clause if the address in the PRIOR record is the billing one

SELECT level, c.*, cc.*
  FROM customer_all c, ccontact_all cc
 WHERE c.customer_id = cc.customer_id
   AND cc.ccbill = 'X'
CONNECT BY PRIOR c.customer_id_high = c.customer_id
       AND PRIOR cc.ccbill = 'X'
 START WITH c.customer_id = :cust_id

There is a clearer solution available – to make a join with addresses table after executing the hierarchical query:

SELECT *
  FROM (SELECT c. *
          FROM customer_all c,
        CONNECT BY PRIOR c.customer_id_high = c.customer_id
         START WITH c.customer_id = :cust_id) c,
       ccontract_all cc
 WHERE c.customer_id = cc.customer_id
   AND cc.ccbill = 'X'

There is another interesting issue. When I changed the join to ANSI style (JOIN … ON instead of condition in the WHERE clause) then the execution time increased a few hundred thousand times. It seems that Oracle 9 on which I tested this query does not cope well with ANSI join syntax. This is not a surprise. Once we had to rapidly convert many views in a system. They used ANSI-style joins and when they were used in queries using Oracle-style joins then their performance dropped rapidly. We had to convert the views to use Oracle-style joins.

— Previous article

<!--:en-->Accessing Microsoft SQL Server from Oracle<!--:-->

Next article —

<!--:en-->Agile Skills Project at my company<!--:-->

You May Also Like
DDevelopment & Design

Phonegap / Cordova and cross domain ssl request problem on android.

In one app I have participated, there was a use case:
  • User fill up a form.
  • User submit the form.
  • System send data via https to server and show a response.
During development there wasn’t any problem, but when we were going to release production version then some unsuspected situation occurred. I prepare the production version accordingly with standard flow for Android environment:
  • ant release
  • align
  • signing
During conduct tests on that version, every time I try to submit the form, a connection error appear. In that situation, at the first you should check whitelist in cordova settings. Every URL you want to connect to, must be explicit type in: 
res/xml/cordova.xml
If whitelist looks fine, the error is most likely caused by inner implementation of Android System. The Android WebView does not allow by default self-signed SSL certs. When app is debug-signed the SSL error is ignored, but if app is release-signed connection to untrusted services is blocked.



Workaround


You have to remember that secure connection to service with self-signed certificate is risky and unrecommended. But if you know what you are doing there is some workaround of the security problem. Behavior of method 
CordovaWebViewClient.onReceivedSslError
must be changed.


Thus add new class extended CordovaWebViewClient and override ‘onReceivedSslError’. I strongly suggest to implement custom onReceiveSslError as secure as possible. I know that the problem occours when app try connect to example.domain.com and in spite of self signed certificate the domain is trusted, so only for that case the SslError is ignored. 

public class MyWebViewClient extends CordovaWebViewClient {

   private static final String TAG = MyWebViewClient.class.getName();
   private static final String AVAILABLE_SLL_CN 
                               = "example.domain.com";

   public MyWebViewClient(DroidGap ctx) {
       super(ctx);
   }

   @Override
   public void onReceivedSslError(WebView view, 
                                  SslErrorHandler handler, 
                                  android.net.http.SslError error) {

       String errorSourceCName = error.getCertificate().
                                  getIssuedTo().getCName();

       if( AVAILABLE_SLL_CN.equals(errorSourceCName) ) {
           Log.i(TAG, "Detect ssl connection error: " + 
                       error.toString() + 
                       „ so the error is ignored”);

           handler.proceed();
           return;
       }

       super.onReceivedSslError(view, handler, error);
   }
}
Next step is forcing yours app to  use custom implementation of WebViewClient. 

public class Start extends DroidGap
{
   private static final String TAG = Start.class.getName();

   @Override
   public void onCreate(Bundle savedInstanceState)
   {
       super.onCreate(savedInstanceState);
       super.setIntegerProperty("splashscreen", R.drawable.splash);
       super.init();

       MyWebViewClient myWebViewClient = new MyWebViewClient(this);
       myWebViewClient.setWebView(this.appView);

       this.appView.setWebViewClient(myWebViewClient);
       
       // yours code

   }
}
That is all ypu have to do if minSdk of yours app is greater or equals 8. In older version of Android there is no class 
android.net.http.SslError
So in class MyCordovaWebViewClient class there are errors because compliator doesn’t see SslError class. Fortunately Android is(was) open source, so it is easy to find source of the class. There is no inpediments to ‘upgrade’ app and just add the file to project. I suggest to keep original packages. Thus after all operations the source tree looks like:

Class SslError placed in source tree. 
 Now the app created in release mode can connect via https to services with self-signed SSl certificates.