PowerBuilder and R get together

The other day I was thinking about writing a blog using PowerBuilder, but couldn't decide which one other technology I should integrate it...of course...R came to my mind...

My journey started around 4 days ago...when I start looking for ways to call R from an external language...last time I used Rook and Heroku to call R from SAP Mobile Platform as explained in my blog Consuming R from SAP Mobile Platform, but this time I knew that I needed to do something different.

My first thought was to use Rserve which is an R Server used by the SAP HANA Studio to connect to R as explained in my blog When SAP HANA met R - First kiss, so I downloaded the REngine files which are two .jar Java files.

In order to connect to Rserve we need to set it up...so basically with the Rserve package installed on my RStudio I simply need to create a small file...

Call_Rserve.R
library("Rserve") Rserve()

When you run this...the R Server will start as a process that can be seen in the Task Manager.

With the files ready, I went to PowerBuilder and simply add them to the Java classpath like I did for the SAP HANA jdbc connector as explained in my blog PowerBuilder - The new kid on Developer Center's block, it didn't work...the files we in the classpath but I couldn't call any method...so I decided to keep looking for alternatives and then I find Rcaller, a single .jar Java file that instead of using Rserve, call the R executable directly...it didn't work either...thing is...in PowerBuilder (with the exception of the SAP HANA jdbc connector) you need to use either an EAServer or EJB capable server like JBoss...I haven't use any of them before...and also you need to build the .jar Java file yourself as you need some internal information.

I had the impression that I was going to a dead end...but then I think..."Hey...I'm using PowerBuilder.NET!" which means obviously that it relies on the .NET Framework...meaning that I could use .NET dll files...but I only had .jar Java files...

Doing some more research I came to find IKVM which an application that allows you to convert any .jar Java file into a nice .NET dll file...the usage is very simple...

Using IKVM
Open CMD C:\>ikmvc -out:Rcaller.dll -target:module Rcaller.jar

This will produce a .dll file called Rcaller.dll...I thought I was right on track so I include this file in my project references...it didn't work as sadly, IKVM is still on development and not everything from Java has been translated to .NET, like for example the Java.IO interface...I then convert both REngine .jar Java files into an .dll but had the same luck...some conversions were missing...

Back to Google and keep looking...this time...straight for .NET implementations and I found R.NET which also uses the R executable to make the integration...it look very promising...however, R.NET is still in development and for some reason...I couldn't make it work as somehow the .dll file couldn't be found by the .NET engine...I thought...Ok...maybe it's PowerBuilder's fault...let's try straight on a real .NET environment...so I installed Visual Studio Express 2012 for Desktop...same luck...the .dll couldn't be found...

I was tired and angry...nothing seem to work...but then I get back to the Rserve page and realized that it was a .NET project called RserveCLI that used Rserve to do the integration with R....when downloading the project...there's no available .dll but that wasn't a problem...as I simply compile it on VS Express and got a shiny .dll waiting to be tested...so I create a new C# Console project and test it...it worked perfectly...

When I tried to use it on PowerBuilder.NET for the first time...it didn't work...so...as you may imagine...I was even more tired and even more angry...and for some unexplainable reason...I end deleting a single tiny file from the .NET framework that simply screw everything...I couldn't run PowerBuilder or VS Express any more...thought luck...another journey to fix my mess...I uninstalled all the .NET framework references...VC++ compilers...Runtimes...etc...it took me almost a full day to finally have everything back in place...however...up to this day...VS Express died completely...I can't even install it any more...as the installer show me the VS splash screen as then disappears with no visible error message...

Anyway...at least PowerBuilder was working again...and I finally overcome my issues with the RserveCLI dll...

The program that I'm going to show you, is for sure very simple...as due to the nature of the integration...and the fact that RserveCLI is still on development...there's really not much that we can do...but still...I believe it will help to illustrate two interesting points...

• PowerBuilder.NET can interact with .NET dll files
• R can be used by a long range of programming languages

This program will ask you to fill two arrays, Array A and Array B...each with four elements (somehow...and for some reason that I'm still trying to understand...when calling R from PowerBuilder, you need to pass even bi-dimensional arrays...being the smallest of [2,2]...)

Let's take a look at the layout...

So...enough talk...let's go to the Source Code...

First, we're going to define a Global Variable as we will use it in a lot of places...

Global Variables
RserveCli.RConnection conn

Then, we going to call the main windows (w_window) open event.

w_window.Open
#if DEFINED PBDOTNET then @System.Net.IPAddress ipadd byte ip[4] = {127,0,0,1} ipadd = create @System.Net.IPAddress(ip) @System.Int32 port port = create System.Int32 port = 6311 conn = create RserveCli.RConnection(ipadd,port,"","") #end if

Here, we are saying that we're going to use .NET styled code, so it should be ignored by the PowerBuilder compiler...as we're using Rserve on our local machine, we use the localhost IP address and assign the Rserve default port which is 6311. We define a connection to the server using the RConnection method. If you wonder why I'm using the @ it's because we want to make sure that we're calling the System from .NET and from PowerBuilder...I know that I said the code should ignored by the PowerBuilder compiler...but better safe than sorry....

We have a button called cb_load which is going to load the arrays defined on the screen.

cb_load.Clicked
double varA[2,2], varB[2,2] varA[1,1] = double(Array_A_1.Text) varA[1,2] = double(Array_A_3.Text) varA[2,1] = double(Array_A_2.Text) varA[2,2] = double(Array_A_4.Text) varB[1,1] = double(Array_B_1.Text) varB[1,2] = double(Array_B_3.Text) varB[2,1] = double(Array_B_2.Text) varB[2,2] = double(Array_B_4.Text) #if DEFINED PBDOTNET then ::conn["A"] = RserveCli.Sexp.Make(varA) ::conn["B"] = RserveCli.Sexp.Make(varB) #end if

Here, we simply create two arrays and fill them. We call ::conn with the extra "::" to let the compiler know that it's a global variable. When we use the Sexp.Make we're telling that we want to create an R vector using the provided array. So one vector called A and other called B.

Now that we have the arrays loaded in memory, we can proceed with the other buttons.

cb_sum.Clicked
#if DEFINED PBDOTNET then txtResult.Text = ::conn["A+B"].ToString() #end if

This is very simple, we're just saying...send this "A+B" command to R and give me the result as a String. In R, A+B will produce a factor sum...meaning that instead of creating a new factor containing the values of A and B, it's going to take the first element from A and sum it to the first element of B, it will do the same with the rest of the elements. You will see this more clear later.

cb_multiply.Clicked
#if DEFINED PBDOTNET then txtResult.Text = ::conn["A*B"].ToString() #end if

This is basically the same, with the exception that it will multiply instead of sum.

cb_min.Clicked
#if DEFINED PBDOTNET then RserveCli.Sexp varC = ::conn["min(c(A,B))"] txtResult.Text = varC.ToString() #end if

This is a little bit more interesting...we're going to create an Sexp variable and then perform a small operation in R...by doing "c(A,B)" we going to take all the element from B and them to A, so A it's going to contain both values...using "min()" we're to get the smallest value.

cb_max.Clicked
#if DEFINED PBDOTNET then RserveCli.Sexp varC = ::conn["max(c(A,B))"] txtResult.Text = varC.ToString() #end if

Here it's the same story but we're going to get the biggest value instead of the smallest.

cb_mean.Clicked
#if DEFINED PBDOTNET then RserveCli.Sexp varC = ::conn["mean(c(A,B))"] txtResult.Text = varC.ToString() #end if

Same story again...R is really easy to work with...we calculate the mean which will be simply the sum of all elements divided by the number of elements.

cb_summary.Clicked
#if DEFINED PBDOTNET then RserveCli.Sexp varC = ::conn["summary(c(A,B))"] string varS = "Min: " + varC[0].ToString() + " / 1st Qu: " + varC[2].ToString() + " / Median: " & + varC[2].ToString() + " / Mean: " + varC[3].ToString() + " / 3rd Qu: " & + varC[4].ToString() + " / Max: " + varC[5].ToString() txtResult.Text = varS #end if

Here, we're going to obtain the summary of mixing A and B. The summary is a complex variable that will return us the Min and Max values, the 1st and 3rd Quadrants, the Median and the Mean values. By simply calling it as an array, we can extract all the values...

Finally...and to allow our program to run...we need to call the last piece of code...

wfpapp.Open
open(w_window)

wpfapp is the name of our application (by PowerBuilder default). Here we say, open our main windows called w_window (another PowerBuilder default)...

Let's run the application and see how it works...

We are simply filling the text boxes with some dummy data...Array A will contain 1,2,3,4 and Array B will contain 5,6,7,8.

When we press the Sum button...1 and 5 will be summed, 2 and 6 will be summed, 3 and 7 will be summed and finally, 4 and 8 will be summed.

When we press the Multiply button, the same thing will happen, but the numbers will be multiplied instead of being summed.

You already know but will happen with Minimum, Maximum and Mean...so we move ahead straight to Summary. It will return the Min and Max, the 1st and 3rd Quadrants and the Median and Mean values.

As you can see...this is a very simple example...but believe me...after full 4 days of work...I'm glad that I could finally make it work...and you thought that was job was easy? Think again -;) 4 stressful days and nights...

Greetings,

Blag.