Using Connect By in Oracle

When dealing with hierarchical data, Oracle has a Connect By clause that is very useful. I will try and demonstrate through examples in the following sections.

Hierarchical Data

In this example, our data will be structured in a tree-structure. Our example data will be a folder-structure.

 - Root Folder
    - Development
        - 3rd Party Software
        - Licenses
        - Release Docs
    - Sales
        - Clients
            - ChainsawsRus
            - Knitting Fever
        - Presentations

In a database, this folder structure could be represented by

TABLENAME: Folders
-------------------------------------------------|
| FolderID | ParentFolderID | FolderName         | 
-------------------------------------------------|
| 1        | 0              | Root Folder        |
| 2        | 1              | Development        |
| 3        | 2              | 3rd Party Software |
| 4        | 2              | Licenses           |
| 5        | 2              | Release Docs       |
| 6        | 1              | Sales              |
| 7        | 6              | Clients            |
| 8        | 7              | ChainsawsRus       |
| 9        | 7              | Knitting Fever     |
| 10       | 6              | Presentations      |
-------------------------------------------------|

This data example will serve as the primary example through the rest of the article.

Connect By

Let us just jump right into it and make a query on that data.

SELECT Level, F.* FROM Folders F 
START WITH ParentFolderID = 0 
CONNECT BY PRIOR FOLDERID = PARENTFOLDERID 
ORDER SIBLINGS BY FOLDERNAME DESC;

This returns the following result:

Oracle Connect By Example 01

Let us go through the SQL.

Level
The first line references the “Level” pseudo column. Whenever a hierarchical query is performed, this pseudo column as added to the result. The level column contains a number describing the level of the row in the query.

START WITH
The START WITH clause tells oracle how to define the root elements. If we had written:

SELECT Level, F.* FROM Folders F 
START WITH ParentFolderID = 7 
CONNECT BY PRIOR FOLDERID = PARENTFOLDERID 
ORDER SIBLINGS BY FOLDERNAME DESC;

The it would only return: Knitting Fever and ChainsawsRus

CONNECT BY and PRIOR
The CONNECT BY clause defines how to make the structure in the hierarchy. The PRIOR keyword, simply put, tells Oracle what the column should be compared to the parent in the hierarchy. If we instead had executed the following SQL:

SELECT Level, F.* FROM Folders F 
START WITH FolderID = 7 
CONNECT BY FOLDERID = PRIOR PARENTFOLDERID 
ORDER SIBLINGS BY FOLDERNAME DESC;

It would instead consider the root folder as the one with FolderID 7 (Clients) and because the PRIOR keyword was moved in front on PARENTFOLDERID instead, it reverses the query, giving us the following result:

Oracle Connect By Example 02

ORDER SIBLINGS BY
This tells Oracle how to order siblings on the same level. For the purpose of the example I made it descending as it can be seen from the result.

I hope this article has helped someone understand oracle hierarchical queries. For more information, consule the Oracle Database SQL Reference.

Using Ant to start and stop tomcat service

This short guide will describe how to start and stop Tomcat using Ant.

This was tested using Ant 1.9.6 and Tomcat 8.0.30 on a Windows 10 machine.

Requirements

Ant should be installed and the /bin folder should be added to the windows path. Tomcat should be installed and be running as a service in windows.

Ant build.xml

First we need to define a macro in Ant for calling a service

<property name="servicename" value="Tomcat8" />

<macrodef name="service">
    <attribute name="service" />
    <attribute name="action" />
    <sequential>
        <exec executable="cmd. exe">
            <arg line="/c net @{action} '@{service}'" />
        </exec>
    </sequential>
</macrodef>

Note: The call to the command prompt should of course be without a space before “exe” – but my host has mod_security installed which prevents me from saving if i remove the space. Haven’t figured out how to get around this yet…. The same problem occurs in the code below.

First the property servicename is defined setting the value to the name of the Tomcat service.
The “action” attribute can be either start or stop and the “service” attribute is the name of the service. The macro just calls “net start” or “net stop” from a command prompt.

The next thing we need to do, is to write a target for starting Tomcat and one for stopping Tomcat – and lets add restart for good measure.

<target name="startTomcat">           
    <service action="start" service="${servicename}" />		
    <sleep seconds="10"/>
</target>
	
<target name="stopTomcat">
    <service action="stop" service="${servicename}" />
    <sleep seconds="10" />
    <exec dir="." executable="cmd. exe">
    	<arg line ="/c taskkill /f /fi 'services eq ${servicename}' " />
    </exec>
    <sleep seconds="10" />
</target>

<target name="restartTomcat" depends="stopTomcat, startTomcat" />

The startTomcat target is straight-forward. I added a sleep of 10 seconds just to ensure that Tomcat has the needed time to start. Depending on your usage, this might be removed or increased to suit your needs – it is also possible to write minutes=”2″ for it to wait two minutes.

The stopTomcat target is a bit more complex. First I stop the service, then wait 10 seconds to give it enough time to shut down – then i kill the process in case it has not been shut down yet.

Hope this is useful to someone, took me a bit of fiddling to get working :)

Shortcodes in your wordpress theme

Shortcodes is a code reference that is inserted into either templates, pages or posts. When displaying content, wordpress replaces shortcodes with actual code.

A simple example would be a shortcode for inserting an adsense add.

Defining Shortcodes

In your functions.php file, add the following code:

function get_inline_ad($atts) {
    return '<span class="widget-title">Sponsored Link:</span>
	<br>
	<script async src="//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js"></script>
	<!-- Unit name from adsense -->
	<ins class="adsbygoogle" 
	    style="display:inline-block;width:468px;height:60px" 
	    data-ad-client="ca-pub-1234567890123456" 
	    data-ad-slot="1234567890">
	</ins>
	<script>
	    (adsbygoogle = window.adsbygoogle || []).push({});
	</script>';
}
add_shortcode('sc_adsense_inline', 'get_inline_ad');

A method named get_inline_ad is created that returns the google adsense code. You can go to http://google.com/adsense, create and setup an account and then create a new ad unit – from that you can get the adsense code to insert.

The last line is registering the shortcode ‘sc_adsense_inline’ and telling wordpress to replace it with whatever the method ‘get_inline_ad’ returns.

Using Shortcodes

Edit an existing post or add a new post, then either in the visual view or the text view, try and write [sc_adsense_inline]. It should then look like the ad below (remember to disable adblockers):

After saving, view the post. It should now display an ad in the place where you wrote the shortcode.

 

Git commands via ssh in a Windows Scheduled task

How to set up a scheduled task in windows that can run git commands and clone from a remote server using ssh instead of http. The examples in this article is using bitbucket.org as git repository.

Install Git

First download and install git. A windows version of git can be downloaded from https://git-scm.com/. During installation, choose the following options: Use Windows Prompt, use windows default console.

Git commands via ssh

Run the Git GUI program. Click help->show SSH key. If none is shown, generate a key and do not enter a passphrase. When the key has been generated, copy the public key to the clipboard.
Then go to bitbucket.org, click on your avatar and choose settings. Open the menu item named SSH Keys and click Add Key. Give the key a label and paste in the public key from the previous step – then save.The go to the repo you want to manage and copy the ssh-url. It can be found in the top right corner, you might need to select ssh from the dropdown instead of https. On your windows machine, open a command prompt and run the following command:

c:\> git ls-remote git@bitbucket.org:myaccount/myrepo.git

If it says git is an unknown command, add the git/cmd folder til the path environment variable and run the command again. You need to close the prompt first and open a new one for the updated environment variables to take effect.Answer yes to storing the key, then try to run the command again and verify that this time you are not prompted.

Create batch file

Create a file git.bat with the following content

git clone git@bitbucket.org:myaccount/myrepo.git > git.log 2>&1

This command will clone the repo and write output to git.log. The last section of the command pipes the error log into the standard log which is written to git.log.

Run the batch file and verify that it works as intended.

Setup scheduled task

Create a scheduled task in windows that run the batch file. Remember to specify the “Start In” setting to have control of which folder the batch is executed within and specify to run even when logged out.

Now the most important step:
Copy the folder c:\users\<youraccount>\.ssh to c:\users\default\.ssh

This is important since when NOT logged in when a scheduled task is run, it cannot find the ssh keys unless they are in the default profile folder.

 

 

CrossContext and RequestDispatcher in webapps

This article will describe how to use a crosscontext requestdispatcher to communicate between two servlets in different webapps.

Settings the example

Lets say you have two webapps loaded in tomcat:

  • CustomerPortal (loaded at http://myserver.com/CustomerPortal)
  • EmployeePortal (loaded at http://myserver.com/EmployeePortal)

In the CustomerPortal you want to be able to show an Employee Datasheet by calling:

http://myserver.com/EmployeePortal/employee?id=<employeeid>

and in the EmployeePortal you want to show a Customer Datasheet by calling:

http://myserver.com/CustomerPortal/customer?id=<customerid>

 

Allowing the communication with CrossContext

For each project, add a file named context.xml to the META-INF library that contains the following:

<Context crossContext="true" />

If your webapp already has a context.xml file, just add the crossContext parameter to the context-tag.

Now you have allowed the webapps to communicate. Next step is to do the actual communication.

Perform communication with RequestDispatcher

For doing the communication, we need a RequestDispatcher.

ServletContext sc = this.getServletContext().getContext("/CustomerPortal");
RequestDispatcher disp = s1.getRequestDispatcher("customer?id=" + customerId);               
                
try {
    disp.include(request, response);
}  
catch(Exception e) {
    e.printStackTrace();
}

The above code will call CustomerPortal/customer?id=xxx and include the code received in the current spot of the page. The example is easily changed for calling the EmployeePortal.

Handle the response

In case you want it to be a bit more flexible and handle the response, the following code can be used instead

ServletContext sc = this.getServletContext().getContext("/CustomerPortal");
RequestDispatcher disp = s1.getRequestDispatcher("customer?id=" + customerId); 
StringWriter sw = new StringWriter();
final PrintWriter pw = new PrintWriter(sw);
HttpServletResponse responseWrapper =
    new HttpServletResponseWrapper(response) {
        @Override
        public PrintWriter getWriter() throws IOException {
            return pw;
        }
    };
dispatcher.include(request, responseWrapper);
System.out.println("html from customerportal: " + sw.toString());