October | 2015 |

For our Oracle BizTalk interface we use an XSD file to describe the master detail table sets. Surely there are enough tools to generate the XSD file, so here is another one which I created to fit our needs. It’s easily adjustable.

It starts from the base table, searches for foreign key references which map to the primary key using the following query:

    select dco1.owner
         , dco1.table_name
         , dcc.column_name
    from   dba_constraints  dco1
         , dba_cons_columns dcc
    where  dco1.constraint_type = 'R'
    and    dco1.r_constraint_name in (select dco2.constraint_name
                                      from   dba_constraints dco2
                                      where  dco2.owner      = '[OWNER]'
                                      and    dco2.table_name = '[TABLE]'
                                      and    dco2.constraint_type = 'P')
    and    dcc.owner = dco1.owner
    and    dcc.table_name = dco1.table_name
    and    dcc.constraint_name = dco1.constraint_name;

This way, other foreign keys to unique attributes are not included in the XSD file generation, just the one’s who refer to the primary key are included.

When references are found it calls itself and so on (a maximum depth can be supplied).

For the BizTalk implementation only NUMBER or STRING element types are relevant, but it is easy to adjust the attribute generation using the CASE statement:

    select column_name as element_name
         , case when dtc.data_type = 'NUMBER' then 'type="xs:int"/>' else 'type="xs:string"/>' end as element_type
    from   dba_tab_cols dtc
    where  dtc.owner      = '[OWNER]'
    and    dtc.table_name = '[TABLE]'
    order  by dtc.column_id;

The package generates a CLOB which you can insert into a table or write to a file. In the example the CLOB is inserted into a table:

CREATE TABLE TMP_CLOB (message CLOB, datetime_col DATE);

The package:
Package PCK_GenXsd.pks specification.
Package PCK_GenXsd.pkb body.

Call it:

begin
  PCK_GenXsd.PR_GenXsd('NICENAME','OWNER','STARTINGTABLE');
end;
/

After some inspiring Friday afternoon discussion, the following came to mind as nice to have.

Wouldn’t it be nice to have a CTRL+C and CTRL+V in one action, meaning your selected text is put on the clipboard and the previous clipboard is put on screen. In other words, a CTRL+Exchange or CTRL+Swap.

Probably there are dozens of tools like this and surely relatively easy to create in Visual Basic, in C# or in any other Windows (scripting) language, fact is I’m not a Windows developer (yet ;). But with no administrator rights, no possibility to install anything (USB not working either) and a tough e-mail scanner, our options are limited. But “Oracle SQL Developer” is installed (including a JRE).

Secondly, this should work globally in Windows across applications so the system tray comes in mind. Unfortunately, access is denied (later I will create the same for an at home solution in the system tray for sure).
And third, the Java application (while minimized) should be able to capture a CTRL+[key] globally and then exchange it and paste it back (e.g. CTRL+Q).
At last, it should not be too difficult and time consuming, since we are a week before sprint delivery.

Luckily, I came across this nice (although very old) post by Sergei Biletnikov:
http://biletnikov-dev.blogspot.nl/2009/09/global-hotkeys-for-java-applications_25.html
in which Sergei proposes a solution for the global capture of hotkeys using a .dll in Java

Link to the .dll files still works:
https://code.google.com/p/jkeyboard-hook/downloads/list

Loading a .dll in Java is pretty easy, but it should be in one .jar file since the executable should be in the form of “java -jar clipboardexchanger.jar”. For the later-to-build-system-tray-version other options are available. But for now some more Java combining forces are required, this time with Adam Heinrich and:
https://github.com/adamheinrich/native-utils
which allows us to load a .dll from a classpath while developing and from inside a .jar file when deployed.

What should it do by example:

“hello” is currently in the clipboard
“world” is selected somewhere in Notepad
User presses CTRL+Q
Current clipboard is loaded to remember (“hello”)
Perform CTRL+C (“hello” is replaced by “world” in clipboard)
Current clipboard is loaded to remember (“world”)
Put remembered (“hello”) back into the clipboard
Perform CTRL+V to paste, user sees “hello”
Put remembered “world” into clipboard
Other applications can use CTRL+V to paste “world” as well

When you download Sergei’s example and .dll files and add Adam’s native utils to load the .dll, you can add the following code in the
onGlobalHotkeysPressed() method:

public void onGlobalHotkeysPressed() {
  // get our current clipboard content
  String current = getClipboardContents(false); // "hello"

  // perform CTRL+C to fill clipboard with selected text
  r.keyPress(KeyEvent.VK_CONTROL);
  r.keyPress(KeyEvent.VK_C);
  r.delay(50);
  r.keyRelease(KeyEvent.VK_C);
  r.keyRelease(KeyEvent.VK_CONTROL);

  // read the system clipboard again to get the selected text, "world" is now known inside our application
  String selected = getClipboardContents(false);

  // put "hello" back into clipboard to be able to perform CTRL+V on it
  setClipboardContents(current, false);

  // perfor CTRL+V to paste "hello"
  r.keyPress(KeyEvent.VK_CONTROL);
  r.keyPress(KeyEvent.VK_V);
  r.delay(50);
  r.keyRelease(KeyEvent.VK_V);
  r.keyRelease(KeyEvent.VK_CONTROL);

  // put originally selected "world" into clipboard
  setClipboardContents(selected, true);
}

With “r” earlier declared:

java.awt.Robot r
try {
  r = new Robot();
} catch (AWTException e) {
  System.out.println("AWT Robot error");
}

As you can see, I use an old java.awt.Robot class to perform system tasks. Well, why not.

The “getClipboardContents” and “setClipboardContents” are widely known to get and set the system’s clipboard. I added a boolean to the method just to indicate if they should do something to the system tray. They are not really necessary. The methods (simplified):

/**
* Get the clipboard.
*
* @return any text found on the Clipboard; if none found, return an empty String
*/
private static String getClipboardContents(boolean showResult) {
  String result = "";
  Clipboard clipboard = Toolkit.getDefaultToolkit().getSystemClipboard();
  Transferable contents = clipboard.getContents(null);
  boolean hasTransferableText = (contents != null) && contents.isDataFlavorSupported(DataFlavor.stringFlavor);
  if (hasTransferableText) {
    try {
      result = (String) contents.getTransferData(DataFlavor.stringFlavor);
    } catch (UnsupportedFlavorException | IOException ex) {
      result = "";
      System.out.println(ex);
      ex.printStackTrace();
    }
  }
  if (showResult) {
    System.out.println("clipboard contains: " + result);
  }
  return result;
}

/**
* Place a String on the clipboard.
*/
private static void setClipboardContents(String str, boolean showResult) {
  StringSelection stringSelection = new StringSelection(str);
  Clipboard clipboard = Toolkit.getDefaultToolkit().getSystemClipboard();
  clipboard.setContents(stringSelection, stringSelection);
  if (showResult) {
    System.out.println("putting in clipboard: " + str);
  }
}

Finally, you’ll have to create a .jar file. If you’re using an IDE this should be rather easy. After creating the .jar, just add the .dll to the directory where the classes are. The NativeUtils.loadLibraryFromJar method will search in those.

With the following files:
GlobalKeyboardHook.java
GlobalKeyboardListener.java
NativeUtils.java
ClipboardExchanger.java (main method)

Or just download the .jar files (32bit and 64bit versions) and run the inside a command prompt box:
java -jar clipboardexchanger32.jar
and
java -jar clipboardexchanger64.jar

Monthly Archives: October 2015

Generate XSD from base table with foreign key to primary key references

Clipboard swap in Java (Ctrl+Exchange or Ctrl+Swap)