Sybase TechWave 2011It was great to see so many former colleagues and customers at SAP TechEd and Sybase TechWave a little over a week ago in Las Vegas.  Thanks especially to those that sat in on my two ‘cloudy’ PowerBuilder talks; I hope they were informative and gave you some ideas about how various features of the Windows Azure cloud can be relevant to your PowerBuilder applications, even those you may have built years ago. 

My slides and demos are available – where else – in the cloud at: http://jimoneil.blob.core.windows.net/presentations/2011/TechWave2011.zip. This post provides a quick overview of each of the demos, in case you’d like to try to run them on your own using the code in the download.  You’ll be able to run some of the samples out-of-the-box, but if you really want to kick the tires of Windows Azure, I recommend downloading the Windows Azure Tools for Visual Studio 2010 and getting your free trial account. Then you can run my samples as well as experiment with your own code.  Let me know what great applications you come up with, and if you run into issues with getting my samples to run, I’d be happy to help.

PowerBuilder Connection to Windows Azure

There’s wasn’t really any code to this, I just demonstrated how to create a database on SQL Azure and then connect to it via the PowerBuilder Database painter.   We used the pipeline to create the EAS Demo DB Customer table in SQL Azure and then view the data in the painter via the ODBC driver.  It’s really that easy to connect, since SQL Azure uses the same native Tabular Data Stream (TDS) protocol that SQL Server does.

The trickiest part is specifying the correct server name (which ends in database.windows.net) and the user name, which must be in the format user@server; you can always get the ODBC connection string (see below) from the Windows Azure portal via the View Connection String… button in the Properties pane for the selected database.  Note: you will not be able to connect to the database noted below since the firewall rules are not set up to support it, and I’ve not given you my password!  If you provision a Windows Azure trial account though, it will take you only minutes to get to this point.

SQL Azure Connection Strings

Upon connecting in the Database painter, you will get an error that the catalog tables cannot be created; this occurs because one or more of them do not include a clustered index, which is a requirement of all tables in SQL Azure.  The tables can be created via the standalone scripts available with the PowerBuilder installation after you update them to specify a clustered index on each table.

If you try the SQL Native Client driver (SNC), you’ll get an error in the Database painter because the driver makes use of cross-database references to tempdb to populate the table list (and likely other metadata), and that construct is not supported in SQL Azure.  If you build your DataWindows with the ODBC driver and execute them with the native driver, that seems to work.  Note, I haven’t tested all data types thoroughly, and this is currently an unsupported database as far as PowerBuilder is concerned.

One last thing to keep in mind is that if you connect from a PowerBuilder Windows application to a SQL Azure database, you’re making a trip to one of the six data centers across the earth (you pick which one when you create the server).  Latency and timeouts are therefore more likely to be an issue than with your on-premises, client-server setups.  Keep that in mind if you design desktop applications to access SQL Azure, and be sure to code defensively!

PowerBuilder and Windows Azure Blob Data

In this sample (PB_and_BlobStorage), I built a simple PowerBuilder 12.5 Classic application to access my Windows Azure blob storage account.  A number of the containers in this account are marked for public access, so it’s very easy to use the GetUrl method in PowerBuilder (which has been around since PowerBuilder 5!) to retrieve the data and populate a DataWindow (I used an XML import template here). 

Windows Azure blob viewer

Try changing the directory to “presentations” in the window above, and you’ll see a list of PowerPoint and other files that I’ve hosted for distribution via Windows Azure.

PowerBuilder Web Service Deployed to Windows Azure

Faux Windows Phone Client to Windows AzureIn this demo (PB_WebService) I faked out a Windows Phone 7 interface with PowerBuilder 12.5 Classic to access a PowerBuilder Web Service that was deployed to Windows Azure.  The service code in pokerhand.pbl was deployed as a .NET Web Service (the older .asmx type, not a WCF service in the case – though that would be possible in the .NET version of PowerBuilder 12.5).  The client code in fauxphoneclient.pbl uses the SOAP client capability of PowerBuilder to invoke that Web Service hosted in Windows Azure.

Unfortunately, at this point PowerBuilder doesn’t have a strong deployment story for Windows Azure, so there’s a bit of manual intervention needed to get the service running in the cloud.  The approach I used involved the following steps, which are ok for demos but not really viable for a production system:

  1. Create an MSI for the PowerBuilder Web Service (an option in the PowerBuilder project painter).
  2. Generate an MSI using the PowerBuilder Runtime Packager to collect the required PowerBuilder runtime files, which, of course, will not be present by default in the cloud.
  3. Incorporate the two MSI files as content files in a Visual Studio 2010 Web Role project (see the vs directory for this demo).  Note, you don’t need to write a lick of C# code, the project is merely the vehicle to move your PowerBuilder files to Windows Azure.
  4. Deploy the Web Role project from Visual Studio, which also pushes the two MSI files to a Virtual Machine in the cloud.
  5. Remote Desktop to the Web Role in the cloud and run the two MSI installers interactively.  This is currently required since the Web Service MSI installer cannot be run in silent mode.

The demo code and service should be operational, so you can certainly run the application as is; if you have problems let me know.  If you are interested in following the steps I mentioned above, you’ll need your own Windows Azure account to perform them. 

Note that with a few tweaks to the PowerBuilder MSI installers – primarily the support of silent installation – the deployment can be completely automatic as well as resilient in the event of VM reboots or fail-overs in Windows Azure (which is not the case using the mechanism above).  If  you just can’t wait for native support in PowerBuilder and would like further details about about a more production-ready approach to setting up a PowerBuilder Web Service in the cloud (using the Windows Azure VM role), please contact me and I can elaborate.

PowerBuilder and the Service Bus

This demo (PB_ServiceBus), as I expected, is where all audience control was lost!  The scenario here involves a PowerBuilder 12.5 NET application that hosts a small WCF service (implemented by n_AlertService) with a simple interface to accept a color value and a message.  When that service is invoked (via a separate ASP.NET application running at http://alertjim.cloudapp.net/ in Windows Azure), a new window pops up on the client machine displaying the message within a window of the desired background color. 

Service Bus example output

That may sound underwhelming, but the key here is that the service can be invoked from anywhere even though the machine hosting the service is behind a firewall and not otherwise publically accessible!   For instance, if you run the PowerBuilder application on your home network behind your firewall, behind your ISP’s firewalls, etc., and then text your buddy across the country to visit the public site http://alertjim.cloudapp.net/, he can send a message directly to your machine!

If you look at the PowerBuilder code, behind the cb_listen button on w_window, you’ll see the code to host the service with an endpoint URI constructed as follows:

endpointUri[1] = ServiceBusEnvironment.CreateServiceUri("sb", "techwave", "AlertService")

That translates to a URL of sb://techwave.servicebus.windows.net/AlertService, which is the same endpoint that the Visual Studio web application talks to as a client with the following code (note especially Line 4):

 1: Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
 2:  
 3:     ServiceBusEnvironment.SystemConnectivity.Mode = ConnectivityMode.AutoDetect
 4:     Dim serviceUri = ServiceBusEnvironment.CreateServiceUri("sb", "techwave", "AlertService")
 5:     Dim sharedSecretServiceBusCredential = New TransportClientEndpointBehavior()
 6:     sharedSecretServiceBusCredential.CredentialType = TransportClientCredentialType.SharedSecret
 7:     sharedSecretServiceBusCredential.Credentials.SharedSecret.IssuerName = "owner"
 8:     sharedSecretServiceBusCredential.Credentials.SharedSecret.IssuerSecret = "REDACTED"
 9:  
 10:     Dim cf = New ChannelFactory(Of IAlertChannel)(New NetTcpRelayBinding(), New EndpointAddress(serviceUri))
 11:     cf.Endpoint.Behaviors.Add(sharedSecretServiceBusCredential)
 12:  
 13:     Dim channel As IAlertChannel
 14:     channel = cf.CreateChannel()
 15:     channel.Open()
 16:  
 17:     channel.AlertJim(ddlbColors.SelectedValue, txtMessage.Text)
 18:  
 19:     channel.Close()
 20:     cf.Close()
 21:  
 22: End Sub

That endpoint is simply a namespace hosted in the Windows Azure cloud and created declaratively using the Windows Azure Portal – yes it’s that easy!

Service Bus configuration in Windows Azure portal

Note that only one application can establish the endpoint listener at a time, so if someone else happens to be running the PowerBuilder example at exactly the same time you try, you’ll be greeted with the following dialog.  Accommodating this scenario isn’t hard, but it will require that you set up a Service Bus endpoint in your own account to experiment further.

Exception raised when trying to create another listener 

PowerBuilder and RESTful data

My PowerBuilder and RESTful data application (PB_Netflix) focused on using the new functionality to create a REST client in PowerBuilder 12.5 .NET.  My good friend and PowerBuilder guru, Yakov Werde, has written some excellent articles on how to exercise this functionality, so I’ll refer you to that for the mechanics.   The specific service I used is publically available from Netflix, http://odata.netflix.com/

PowerBuilder Netflix sample

PowerBuilder and OData

While Netflix is an OData source, the mechanism I used for the prior demo didn’t really exploit on the fact it is, in fact, OData.  OData itself is a huge gateway to amazing number of data providers including SharePoint 2010, SAP Netweaver Gateway, and a number of free and subscription data services at the Windows Azure DataMarket.  Although PowerBuilder itself does not yet have explicit OData support (that was cited as a proposed feature for PowerBuilder 15), it’s *just* HTTP and XML (or JSON), so there are no real technical barriers.  

One way to incorporate an OData source today in your PowerBuilder .NET application:

  • Create a .NET Class Library and generate a proxy class using the Add Service Reference.. functionality for the REST endpoint (e.g., http://odata.netflix.com/v2/Catalog). 
  • Add methods to this class library to perform the desired queries on the underlying data source.
  • Add the assembly generated from Visual Studio as a reference to your PowerBuilder 12.5. NET application.
  • Instantiate the .NET class you created in the second step above, and invoke the desired query method.
  • Access the data returned via the .NET proxy classes.

I’ve done just that with a demo (PB_DataMarket) that leverages crime statistics from Data.govNote to run the sample, you’ll have to get your own (free) account on the Windows Azure DataMarket, subscribe to the data set, and modify the code behind the command button to refer to your Live ID and account key that is assigned when you subscribe to the DataMarket.

OData sample

 

The code in the Visual Studio project is pretty straightforward, and of course, you can add additional query functionality and parameters.  When PowerBuilder .NET supports extension methods, you should be able to write the code below directly in PowerScript (at the moment, I’m finding PowerBuilder crashes whenever I try to write this analogous code directly in the IDE).

 1: using System;
 2: using System.Collections.Generic;
 3: using System.Linq;
 4: using System.Net;
 5:  
 6: namespace VSProxy
 7: {
 8:     public class CrimeData
 9:     {
 10:         private DataMarket.datagovCrimesContainer context;
 11:  
 12:         public CrimeData(String uid, String key)
 13:         {
 14:             context = new DataMarket.datagovCrimesContainer(
 15:                 new Uri("https://api.datamarket.azure.com/Data.ashx/data.gov/Crimes"));
 16:             context.Credentials = new NetworkCredential(uid, key);
 17:         }
 18:  
 19:         public List<VSProxy.DataMarket.CityCrime> CrimeByState(String state)
 20:         {
 21:             return context.CityCrime.
 22:                 Where(s => (s.State == state) && (s.Year == 2008)).    
 23:                 ToList<VSProxy.DataMarket.CityCrime>();
 24:         }
 25:     }
 26: }

 

The PowerBuilder code to populate the WPF graph DataWindow looks like this (after pulling in a reference to the .NET assembly created by the Visual Studio project).  Note how Line 6 invokes the method defined at Line 19 in the C# script above:

 

 1: VSProxy.CrimeData crimeProxy
 2: crimeProxy = create VSProxy.CrimeData("jim.oneil@live.com", &
 3:     "REDACTED")
 4:  
 5: System.Collections.Generic.List <VSProxy.DataMarket.CityCrime> crimeList
 6: crimeList = crimeProxy.CrimeByState(ddlb_state.Text)
 7:  
 8: VSProxy.DataMarket.CityCrime crimeRecord
 9: int i
 10: int rowNum
 11:  
 12: dw_crime.Reset()
 13:     
 14: // only show first 10 records (pagination left to the reader :)
 15: for i = 1 to Min(10, crimeList.Count)
 16:     crimeRecord = crimeList[i]
 17:     rowNum = dw_crime.InsertRow(0)
 18:     dw_crime.SetItem(rowNum, "city", crimeRecord.City)
 19:     dw_crime.SetItem(rowNum, "violentcrime", crimeRecord.ViolentCrime)
 20:     dw_crime.SetItem(rowNum, "population", crimeRecord.Population)
 21:     dw_crime.SetItem(rowNum, "rate", &
 22:         Double(crimeRecord.ViolentCrime * 1000) / crimeList[i].Population)
 23: next
 24: dw_crime.Visible = True

 

Windows Azure DataMarketOf all the technologies I covered during my two talks, I have to say this one excites me the most.  There are a host of free services in the DataMarket, and you can use a similar process for pulling any of that data in and mashing it up with your own data to offer some incredible new functionality and value in your applications.  If you are still using PowerBuilder Classic, it should be possible to access this same .NET proxy assembly with a COM Callable Wrapper (CCW)

Alternatively, OData is really just HTTP and Atom/JSON, so it can be consumed ‘natively’ in PowerBuilder Classic as well. Unfortunately, there isn’t a convenient wrapper class to handle all of the HTTP calls necessary, so it would be a bit of work to pull it off, but it would be a great open-source project to contribute back to the PowerBuilder and OData community! There are similar libraries already out there for Python, Ruby, Objective C and other languages.

If you try some of these demos out, let me know what you think.  None are of production quality at this point, but hopefully they’ll get you started exploring what PowerBuilder and Windows Azure can accomplish together.