Progress DataDirect: ® OpenAccess for OpenEdge …

1 Progress datadirect : OpenAccess for OpenEdgeApplication ServerNoticesFordetails,seethefollowingt opics: CopyrightCopyright 2014 Progress Software Corporation and/or its subsidiaries or rights materials and all Progress software products are copyrighted and all rights are reservedby Progress Software information in these materials is subject to change withoutnotice, and Progress Software Corporation assumes no responsibility for any errors that mayappear references in these materials to specific platforms supported are subject Making Progress , Corticon, datadirect (and design), datadirect Cloud, DataDirectConnect, datadirect Connect64, datadirect XML Converters, datadirect XQuery, Fathom, MakingSoftware Work Together, OpenEdge , Powered by Progress , Progress , Progress Control Tower, Progress OpenEdge , Progress RPM, Progress Software Business Making Progress , ProgressSoftware Developers Network, Rollbase, RulesCloud, RulesWorld, SequeLink, SpeedScript, StylusStudio, and WebSpeed are registered trademarks of Progress Software Corporation or one of itsaffiliates or subsidiaries in the and/or other countries.

2 AccelEvent, AppsAlive, AppServer,BusinessEdge, Progress Easyl, datadirect Spy, datadirect SupportLink, Easyl, Future Proof, HighPerformance Integration, OpenAccess , Pacific, ProDataSet, Progress Arcade, Progress Pacific, Progress Profiles, Progress Results, Progress RFID, Progress Responsive Process Management, Progress Software, ProVision, PSE Pro, SectorAlliance, SmartBrowser, SmartComponent,SmartDataBrowser, SmartDataObjects, SmartDataView, SmartDialog, SmartFolder, SmartFrame,SmartObjects, SmartPanel, SmartQuery, SmartViewer, SmartWindow, WebClient, and Who MakesProgress are trademarks or service marks of Progress Software Corporation and/or its subsidiaries3 Progress datadirect : OpenAccess for OpenEdge Application Server:Version affiliates in the and other countries. Java is a registered trademark of Oracle and/or itsaffiliates. Any other marks contained herein may be trademarks of their respective 2014 Last updated with new content: Release datadirect : OpenAccess for OpenEdge Application Server:Version this 1:Installing OpenAccess for OpenEdge Application and system OpenAccess for OpenEdge Application Service setup for OpenEdge Application the connection to OpenEdge Application Server.

3 15 Starting the OpenAccess Service for OpenEdge Application Sample Business Entities from ODBC:..17 Querying Sample Business Entities from JDBC:..18 Restrictions and 2:Enabling OE Application for data schema to SQL to be called for CRUD operations on specific resource/business CRUD Operations on Business CRUD Secure CRUD for Handling Connections ..30 Security configurations for OpenEdge Application Server:..31 Security Validation in CRUD operations on business entities:..31 Generating a JSDO JSDO Catalog using OpenEdge Development JSDO Catalog 3:Configuring OpenEdge and datadirect : OpenAccess for OpenEdge Application Server:Version Application Server Connection 4 Execution Business Entity ABL Test Tables in the OpenEdge datadirect : OpenAccess for OpenEdge Application Server:Version ,seethefollowingtopics: Purpose Audience Usingthismanual TypographicalconventionsPurposeProgress datadirect OpenAccess for Progress OpenEdge Application Server ( OpenAccess forOpenEdge Application Server) enables you to access, using SQL, Business Entities deployed inOpenEdge Application Server through drivers (ODBC driver, JDBC driver, and provider).

4 While the drivers for OpenEdge SQL enable you to access data in the OpenEdge RDBMS, OpenAccess for OpenEdge Application Server enables you to access Business for OpenEdge Application Server enables SQL access to Business Entities definedin ABL procedures (.p files) that expose results as TempTable or schema forbusiness entities is defined in the JSDO catalog, which can be generated using Progress DeveloperStudio for OpenEdge or the schema can be configured manually. Having the drivers for BusinessEntities enables efficient and standard integration with third-party tools, such as developmentsoftware, reporting tools, data warehousing tools, Extract-Transform-Load (ETL) tools, and OnlineAnalytical Processing (OLAP) datadirect : OpenAccess for OpenEdge Application Server:Version guide is intended for OpenEdge developers who want to enable ODBC/JDBC connectivityfor the data in their OpenEdge business application developed in OpenEdge Advanced BusinessLanguage (ABL).

5 Users of this guide are expected to be familiar with OpenEdge ABL and OpenEdgeApplication this manualUsing this manual, OpenEdge developers can enable SQL access to their business entitiesimplemented in ABL procedures. Enabling SQL access to business entities requires the procedurefor Create/Read/Update/Delete (CRUD) operations to be implemented using the prescribed interfacedefinition. Minimal code changes in ABL and minimal configuration of OpenAccess Service enableSQL access to business entities. OpenAccess Interface Provider does not require any conventionsThis manual uses the following typographical and syntax conventions:DescriptionConventionBold typeface indicates commands or characters the user types,provides emphasis, or the names of user interface typeface indicates the title of a document, or signifies , bold capital letters indicate OpenEdge key functions andgeneric keyboard keys; for example, GET and , BOLD CAPITALLETTERSA plus sign between key names indicates a simultaneous keysequence: you press and hold down the first key while pressing thesecond key.

6 For example, CTRL+ +KEY2A space between key names indicates a sequential key sequence:you press and release the first key, then press another key. Forexample, ESCAPE KEY2 Syntax:A fixed-width font is used in syntax, code examples, system output,and file italics indicate variables in datadirect : OpenAccess for OpenEdge Application Server:Version bold italic indicates variables in syntax with keywords in syntax and code examples are almost alwaysshown in upper case. Although shown in uppercase, you can typeABL keywords in either uppercase or lowercase in a procedure statements except DO, FOR, FUNCTION, PROCEDURE, and REPEATend with a , FOR, FUNCTION, PROCEDURE, and REPEAT statements can end with either a period or a (.) or colon (:)Large brackets indicate the items within them are optional.[ ]Small brackets are part of ABL.[ ]Large braces indicate the items within them are areused to simplify complex syntax diagrams.

7 { }Small braces are part of ABL. For example, a called externalprocedure must use braces when referencing arguments passed bya calling procedure.{ }A vertical bar indicates a choice.|Ellipses indicate repetition: you can choose one or more of thepreceding datadirect : OpenAccess for OpenEdge Application Server:Version datadirect : OpenAccess for OpenEdge Application Server:Version OpenAccess for OpenEdgeApplication ,seethefollowingtopics: Pre-requisitesandsystemrequirements ProductInstallation OpenAccessServicesetupforOpenEdgeApplica tionServer Testingtheconnectionto OpenEdgeApplicationServer: StartingtheOpenAccessServiceforOpenEdgeA pplicationServer RestrictionsandLimitationsPre-requisites and system requirementsYou should have the OpenEdge and OpenAccess products installed. OpenAccess for OpenEdgeApplication server supports the following application versions:11 Progress datadirect : OpenAccess for OpenEdge Application Server:Version : : : or higherProduct InstallationThe following products should be installed in order to use the OpenAccess for OpenEdge ApplicationServer: OpenEdge OpenAccess OpenAccess for OpenEdge Application ServerThe following sections provide instructions on how to install these InstallationDownload and install a supported version ( ) of OpenEdge (if not installedalready).

8 Refer to the Progress OpenEdge Getting Started: Installation and Configuration guidefor instructions and further InstallationDownload and install Progress OpenAccess ( OpenAccess Server and OpenAccess Client,ODBC /JDBC). Refer to the Progress datadirect : OpenAccess SDK installation guide for instructionsand further OpenAccess for OpenEdge Application ServerTo install OpenAccess for OpenEdge Application the from the Progress Download Center and copyto the OpenAccess Installation location (which is usually C:/ProgramFiles/ Progress / datadirect /oase rver72 for Windows) the command prompt and extract the file using thecommand jar creates the following directories and datadirect : OpenAccess for OpenEdge Application Server:Version 1:Installing OpenAccess for OpenEdge Application ServerDescriptionFiles/DirectoryDirector yThe OpenAccess IP forOpenEdge Application Serverjava library vendor Java Open Clientlibrary files and Third ABL procedure filesfor OpenEdge andOpenEdge of the ABL Samplescan be found in the oasamples folder shouldbe copied to the OpenEdgeworking directory on theApplication Server (example:C:/ OpenEdge /WRK ).

9 The directory containsseparate samples forAnonymous Login mode andSecure Login : directorycontains the samples forimplementing the ApplicationServer security mechanismusing client-principaloe102b oe11ip/oajava/oeabl/oasamplesScripts to create and removeOpenAccess Service forOpenEdge Application Server: oacreate_oeservice.[bat/sh]script files to create service oacreate_oeservice.[bat/sh]script files to create serviceService_setupip/oajava/oeabl/tool sTest the application to verifythe connection to OpenEdgeApplication definition JSDO catalog files for and OpenEdge oe11ip/ the oasamples folder fromip/oajava/oeabl to the OpenEdge Application ServerWorkspace(example:C:/ OpenEdge /WRK ).13 Progress datadirect : OpenAccess for OpenEdge Application Server:Version InstallationOpenAccess Service setup for OpenEdge ApplicationServerOpenAccess Interface Provider connects to the OpenEdge Application Server using theAppServerURL in one of the following methods: Through a NameServer:AppServer:<//Name-server-host> <:Name-server-port>/<AppService-name> Directly to an AppServer:AppServerDC:<//AppServer-host> <:AppServer-port>/<AppService-name>You require the AppServerURL for your deployment before you can proceed with the rest of theinstallation steps.

10 For further information, refer to the guide, Progress OpenEdge Application Server:Developing Appserver must update the following script files (located in ip/oajava/oeabl/tools) for the correspondingoperating system:Windows ( ): USERNAME: Specify the OpenAccess Administrative user account, generally<windows-domain>/<user-name>.This can be empty if OpenAccess AgentServiceAdminAuthMethods is set to integrated_nt OEVersion: Specify the OpenEdge version, oe11 for OpenEdge and oe102b for AppServerURL:The OpenEdge application server's URL SessionModel: Specify the application service's model - the valid options are SessionFree orSessionManaged OASERVER:The OpenAccess Server's installation location JVMLOCATION:The location of the JVM library , OAAppServerPort: The port number for the new OpenAccess service for OpenEdge ApplicationServerUNIX ( ): USERNAME: Specify the OpenAccess Administrative user account PASSWORD: Specify the OpenAccess Administrative user account s password OEVersion: Specify the OpenEdge version, oe11 for OpenEdge and oe102b for AppServerURL:The OpenEdge application server's url, SessionModel.