PowerBuilder Classic and PowerBuilder .NET can use OData datasources.
OData (the Open Data Protocol) is a Web protocol for querying and updating data that provides a way to unlock your data and free it from silos that exist in applications. OData is based on REST (Representational State Transfer). It uses the standard HTTP protocol to access data using GET, PUT, POST, and DELETE.
At runtime, users can retrieve and manipulate data.
Database Profile Setup - OData Dialog Box
The Connection tab includes basic connection options that you must supply to access the information in the OData service.
- Profile Name
- The name of your database profile.
- Connection Information
- The uniform resource identifier (URI) that represents the OData service.
- Authenticated Access
- Select the type of access:
- Anonymous access – Opens up the service so it can be accessed without providing credentials.
- Integrated Windows authentication – Uses information on the client computer to validate the user's access.
- Supply user ID and password – If you select this option, enter the user name required to connect to the datasource and the password for the specified login ID.
- OData Extended Catalog
- Use this option to define the extended attributes, such as edit styles and validation rules, that can be applied in columns of the tables. After you select this option, you can also specify the properties for each table and column. This information is saved in the registry.
The Certificate tab includes basic connection options that you must supply to access the information in the OData service.
Select one connection option:
- No X509 Certificate
- Select this option when the OData service does not require a certificate.
- Select a certificate from current user's personal store
- Click Change to select a certificate.
- Specify a local certificate file
- Click Browse to select a certificate file on the local machine.
The Proxy Server tab has additional connection options that you can use to manage access to the OData service.
- Use proxy server settings in Tools \ Options dialog
- Select this option when you need proxy or firewall settings to access the OData services. Before you select this option, set the proxy or firewall information in Tools System Options Firewall Setting . Be sure to select Use Above Values as System Defaults.
- Bypass proxy server for local addresses
- Use this option to bypass the proxy server when the OData Service is using a local address.
The Preview tab provides a convenient way to generate correct PowerScript connection syntax in the PowerBuilder development environment for use in your PowerBuilder application script.
As you complete the Database Profile Setup dialog box for OData, the correct PowerScript connection syntax for each select option is built on the Preview tab. You can then copy the syntax you want from the Preview tab into your PowerBuilder application script.
- Copies the selected text in the Database Connection Syntax box to the clipboard. You can then paste the syntax into your PowerBuilder script.
You can connect to the OData service and work with it in the Database painter.
The Database painter supports these features:
- Define an OData datasource with the database profile painter.
- Connect to an OData datasource in the Objects view.
- Add tables to the Object Layout view with drag-and-drop or the context menu.
- Display table and column properties in the Properties view with drag-and-drop or the context menu.
- Drag and drop a table onto the Columns view.
- Invoke the Edit Data feature in the Results view of the Database painter.
Since OData is not a SQL source, some features are not supported:
- The ISQL Session view is not enabled.
- You cannot create, modify, or delete tables.
- You cannot create users or groups of users.
- You cannot embed SQL statements in PowerScript.
Create a DataWindow Using an OData Service
- Select File New from the menu bar and select DataWindow.
- If there is more than one target, select the target where you want the DataWindow to be created from the drop-down list.
- Choose the presentation style for the DataWindow object and click Next.
- Select the OData Service datasource and click Next.
- Select the OData profile and click Next.
- In the SQL painter:
When you complete the query, click OK.
- You can select one table.
- The Sort, Group, and Having tabs are not available.
- The Where and Syntax are available in PowerBuilder Classic and PowerBuilder .NET. The Results tab is available in PowerBuilder .NET.
- In the Where tab you can specify some selection criteria using the WHERE clause for the SELECT statement.
- Review your specifications and click Finish.
At runtime, the DataWindow or DataStore can manipulate OData service data, which includes retrieving, updating, inserting, and deleting the data.
Set the Connection Information for the OData Service
As with other databases, use the SQLCA Transaction object (or user-defined transaction object) to retrieve and display data from the OData service in a DataWindow or DataStore.
- Set the appropriate values for the transaction object.
- Connect to the OData service.
- Set the transaction object for the DataWindow or DataStore.
- Retrieve and update the data.
- When the processes are complete, disconnect from the OData service.
The code looks something like this:
SQLCA.DBMS = "ODT" SQLCA.DBParm = "ConnectString='URI=http://esx2-appserver/TestDataService/Employee.svc'" //connect to the service connect using SQLCA; dw_1.SetTransObject(SQLCA) dw_1.Retrieve() ... //disconnect from the service disconnect using SQLCA;
For more information on using the global Transaction object, see Application Techniques.