PowerBuilder 15 is here! Well, almost... It's in a "closed" beta, which means that the download is only available to registered SAP customers and employees. (NOTE: PB15 was never released as of this edit. It was released as PB 12.6. PH)
The SCN link above contains a link to the registration page, and SAP is working to migrate all the former Sybase customers' accounts, and provide them the necessary "S" user ids. In the meantime, we've been given the go-ahead to blog all we want about PB15, and I couldn't be more excited!
There are some great new features being planned for PB15, including the following:
- Updated support for the .NET Framework v4.5
- MSS 2012
- Oracle 12
- Windows 8
- OData Support
- Dockable Windows
- 32 and 64 bit deployment
This blog post will cover one of the items listed above - the new support for the OData protocol. In the current beta, this support only exists in the PB.Net IDE, but I've been assured that it will be available in PB Classic by the time v15 goes into General Availability.
What is OData?
OData is emerging as the standard for data exchange across the web. It's essentially a REST-based web service protocol that allows a client application to invoke backend resources over standard HTTP/s. I know that sounds a lot like SOAP/XML, but there are some key differences between SOAP/XML and REST/OData. With SOAP, the Web Sevices Definition Language (WSDL) file defines the methods and data structures that can be invoked from an HTTP client. The WSDL specification for a service is VERY inflexible, and the calls must be structured exactly as specified or they fail. The response payloads are formatted as tightly-structured and verbose XML that must be parsed apart on the client side. PowerBuilder has had support for SOAP web services for many years, both as a datawindow datasource, and as a non-visual web service proxy class. PB15 now introduces support for the OData specification as a datawindow source.
OData is a relatively new standard, and it provides more dynamic capabilities for querying the backend data sources. OData has been described as "ODBC for the Web", and that's a fairly accurate analogy. I wrote a blog post several months ago that covered OData in fairly good detail, so I won't recreate that here.
Creating an OData datawindow
The first step in creating a PowerBuilder datawindow that accesses an OData endpoint, is to find a valid OData endpoint! For this blog, I'll be using a public SAP Netweaver Gateway endpoint on the SAP Developer Center. That URL is https://sapes1.sapdevcenter.com/sap/opu/odata/sap/ZGWSAMPLE_SRV/ Note: use of the SAP Developer Center does require user registration, which can be found here.
That URL is known as the "Service Root" in OData, and it provides a quick list of all the entities that are exposed through the OData endpoint. Accessing an entity (essentially doing a "Select * from <entityName>") is as simple as adding the entityName to the end of the service root URL. For example, to query the SalesOrderCollection entity, change the URL to https://sapes1.sapdevcenter.com/sap/opu/odata/sap/ZGWSAMPLE_SRV/SalesOrderCollection
To retrieve a specific SalesOrder, add its primary key value in parens after the entityname. https://sapes1.sapdevcenter.com/sap/opu/odata/sap/ZGWSAMPLE_SRV/SalesOrderCollection('0500000001')
Step 2: Fire up PB.Net and open the Database Profiles dialog. This is shown below, and you can clearly see the new profile "ODT OData"
Step 3: Make sure "ODT OData" is the selected node, then click the "New..." button. That opens the OData Profile Setup dialog.
Give the connection a name, and paste in the Service Root URL into the "URI" field. Since this particular OData root requires authentication, I've selected "Supply Userid and Password", and filled those values in. There's a "Test Connection" button on the Preview tabpage.
Click OK to save the new profile.
Step 4: Connect to the new datasource, and open the Database Painter (View > Database Painter). The OData sources behave just like regular databases. Expand the "Tables" folder to see the exposed OData entities. Drag a table over to the Object Layout pane to examine its structure. Right-click the table, and select Edit Data > Grid to perform a retrieve and manipulate the data. All of this is being done through HTTP calls to REST endpoints.
Build a Datawindow
At this point, since the OData entities look and feel just like regular database tables, creating an actual datawindow should be basic, "PowerBuilder 101"-level stuff. You'll need a Solution, and a Target, and a PBL (but these should have already been created if you've gotten this far). The only new wrinkle will be the selection of the Data Source. The third panel of the File > New > Datawindow dialog shows the new "OData Service" datasource:
One note: the SQL creation wizard in the datawindow painter allows you to paint JOINs between OData entities, but I don't recommend taking advantage of that... At this stage of the beta, restrict your use of OData to single-table queries.
From this point, the OData entity looks and behaves just like a traditional SQL table or view. The $metadata document provides all the information about column names, data types, and associations to other entities. Creating a datawindow from here is basic PB101 material... I just created a simple tabular datawindow on one of the OData entities, with no retrieval arguments or other filters.
Coding the PowerScript
The final step is coding the PowerScript that makes the connection to the OData resource. This can go anywhere in your code, but it's typically found in the application or window Open event scripts. Here's what my window Open event looks like:
// Profile workflowdemo
SQLCA.DBMS = "ODT"
SQLCA.AutoCommit = False
SQLCA.DBParm = "ConnectString='URI=https://sapes1.sapdevcenter.com/sap/opu/odata/IWFND/RMTSAMPLEFLIGHT/;UID={your ID};PWD={your PW}'"
connect using SQLCA;
tab_1.tabpage_1.dw_1.SetTransObject( SQLCA )
tab_1.tabpage_1.dw_1.Retrieve()
You now have the full power of the datawindow at your disposal, including the Update() method. This uses the same buffer architecture to generate HTTP PUTs for INSERT statements, POSTs for UPDATE statements, and DELETEs for DELETE statements.
Happy Testing!
-Paul-
- SAP Managed Tags:
7 Comments
