samuelschroeder.com
Ruby in the front, Rails in the back.

Recently at work we needed a good, and easy, way to get data stored in our massive ERP system running an OpenEdge database to some of our .Net apps. At first, we just had ASP.Net connect directly to the database through ODBC which was good enough for our prototype. However, if we were to continue using this method it would require our Microsoft developers to learn a totally new and convoluted schema. Plus, they would have to use embedded SQL statements like…

DataTable dt = dbBroker.GetDataTableBySql("SELECT * FROM pub.Customer");

** dbBroker and GetDataTableBySQL are part of our in-house framework but you can get the idea.

Embedded SQL is a code maintenance nightmare in my opinion. I’ve never liked mixing languages like this. Fortunately, OpenEdge comes with a better way call the Open Client Toolkit (sorry, no link). To quote the product literature the Open Client Toolkit is used “to generate .NET, Java, and Web services proxies for accessing business logic.” In our case we used it to create DLLs which we can reference in our Visual Studio projects.

[I will post another article on the specifics of using ProxyGen from the Open Client Toolkit to create .Net DLLs]

This process works much better as our MS developers can just make a call to the methods in the DLLs (I’ll write about this is another article) and not have to worry about the ERP’s crazy schema while our Progress developers can actually create real business logic procedures to aggregate data. The only problem with this scenario is that every time we want to add to the AppServer code we must regenerate the DLLs and make the .Net apps reference them properly. Talk about a pain in the ass.

Here is how we I solved this problem.

The first issue was to find a way to relieve the pain of regenerating and referencing the DLLs. After some experimentation I found that only the procedures called directly from C# needed to be built into the DLL. So we came up with the follow program to act as a broker to all of the other code on the AppServer. Now the proxy DLLs only need to be recreated if this one file is modified. And all the .Net team needs to know is the name of the process they wish to call and the input parameter. What they get back is a DataSet containing one or many tables and their corresponding relationships.

/* ------------------------------------------------------------------ Program: progressproxy.p Description: This program is used to create the generic DLL with ProxyGen. Is is intended to call the another program immediately in which that program will handle all the manipulation. This is done so this program is never touch again and the DLL never has to be created again. ------------------------------------------------------------------ */ /* *************************** Definitions ************************** */ DEFINE INPUT PARAMETER pcProcess AS CHARACTER NO-UNDO. /* Process to call */ DEFINE INPUT PARAMETER pcParams AS CHARACTER NO-UNDO. /* Comma delimited list of parameters */ DEFINE OUTPUT PARAMETER plSuccess AS LOGICAL NO-UNDO. /* Success/Failure of the process call */ DEFINE OUTPUT PARAMETER DATASET-HANDLE pdsGeneric. /* ProDataSet to send back */ /* *************************** Main Block *************************** */ RUN as\proxyrun.p (INPUT pcProcess, INPUT pcParams, INPUT-OUTPUT plSuccess, INPUT-OUTPUT DATASET-HANDLE pdsGeneric).

Pretty simple. I kept it that way to make sure we never have to change it again. (Which we will since the current process only extracts data. Soon we will need a was to insert and update data.)

The following procedure is the guts of our little AppServer broker. It attempts to find the called process and run it. If there is any kind of failure it will return a DataSet willed with information about the error.

/* ------------------------------------------------------------------ Program: proxyrun.p Descrip: Program to call the appropriate sub-routine based on the process passed in. ------------------------------------------------------------------ */ /* *************************** Definitions ************************** */ DEFINE INPUT PARAMETER pcProcess AS CHARACTER NO-UNDO. DEFINE INPUT PARAMETER pcParams AS CHARACTER NO-UNDO. DEFINE INPUT-OUTPUT PARAMETER plSuccess AS LOGICAL NO-UNDO. DEFINE INPUT-OUTPUT PARAMETER DATASET-HANDLE pdsGeneric. DEFINE VARIABLE cError AS CHARACTER NO-UNDO. DEFINE VARIABLE cProgramToRun AS CHARACTER NO-UNDO. /* *************************** Main Block *************************** */ ASSIGN cProgramToRun = TRIM(REPLACE(pcProcess,".p","")). ASSIGN cProgramToRun = "as\" + cProgramToRun + ".r". /* Invalid procedure - send an error dataset */ IF SEARCH(cProgramToRun) EQ ? THEN RUN AS/proxyerrors.p(INPUT pcProcess, INPUT pcParams, INPUT "Unable to locate requested process.", INPUT-OUTPUT DATASET-HANDLE pdsGeneric). /* Run Valid process */ ELSE IF SEARCH(cProgramToRun) <> ? THEN DO: RUN VALUE(cProgramToRun) (INPUT pcParams, INPUT-OUTPUT DATASET-HANDLE pdsGeneric). /* Check for Errors */ ASSIGN cError = RETURN-VALUE. /* Send Error Dataset */ IF cError NE "" AND cError NE ? THEN RUN AS/proxyerrors.p(INPUT pcProcess, INPUT pcParams, INPUT cError, INPUT-OUTPUT DATASET-HANDLE pdsGeneric). /* Successful Process Run */ ELSE ASSIGN plSuccess = TRUE. END. /* Run Valid process */

This is the code for creating the Error DataSet.

/* ------------------------------------------------------------------ Program: proxyerrors.p Descrip: This program is used to create the errors for the generic webservices. ------------------------------------------------------------------ */ /* *************************** Definitions ************************** */ DEFINE INPUT PARAMETER pcProcess AS CHARACTER NO-UNDO. DEFINE INPUT PARAMETER pcParams AS CHARACTER NO-UNDO. DEFINE INPUT PARAMETER pcErrorMsg AS CHARACTER NO-UNDO. DEFINE INPUT-OUTPUT PARAMETER DATASET-HANDLE pdsGeneric. DEFINE TEMP-TABLE proxyErrors FIELD processCalled AS CHARACTER FIELD inputParams AS CHARACTER FIELD errorMessage AS CHARACTER. DEFINE DATASET dsErrors FOR proxyErrors. /* *************************** Main Block *************************** */ ASSIGN pdsGeneric = DATASET dsErrors:HANDLE. CREATE proxyErrors. ASSIGN proxyErrors.processCalled = pcProcess proxyErrors.inputParams = pcParams proxyErrors.errorMessage = pcErrorMsg. RETURN.

Here is an example of a method to return a specific customer. For you Progress people out there it is using the Sports200 database.

/*------------------------------------------------------------------------ File : as/getCustomer.p /*----------------------------------------------------------------------*/ /* *************************** Definitions ************************** */ DEFINE INPUT PARAMETER pcParams AS CHARACTER NO-UNDO. DEFINE INPUT-OUTPUT PARAMETER DATASET-HANDLE pdsGeneric. DEFINE VARIABLE cCustNum AS CHARACTER NO-UNDO. /* Temp table to aggregate data for dataset fill */ DEFINE TEMP-TABLE ttAgrCustomer LIKE Customer. /* DataSet Structure to be filled */ DEFINE TEMP-TABLE ttCustomer LIKE ttAgrCustomer. DEFINE DATASET dsCustomer FOR ttJCustomer . DEFINE QUERY qryCustomer FOR ttAgrCustomer . DEFINE DATA-SOURCE srcCustomer FOR QUERY qryCustomer . BUFFER ttCustomer:ATTACH-DATA-SOURCE(DATA-SOURCE srcCustomer:HANDLE). /* These are needed to make the validParams function work */ &GLOBAL-DEFINE numParams 1 &GLOBAL-DEFINE paramNames Customer Number {AS/helper.i} /* *************************** Main Block *************************** */ /* Incorrect Parameters */ IF NOT validParams(INPUT pcParams, INPUT {&numParams}) THEN RETURN {&invalidParams}. /* Parse Parameters */ ASSIGN CustNum = ENTRY(1, pcParams). /* Find Job */ FIND FIRST Customer WHERE Customer.CustNum = cCustNum NO-LOCK NO-ERROR. /* Found Customer */ IF AVAILABLE(Customer) THEN DO: CREATE ttAgrCustomer. BUFFER-COPY Job TO ttAgrCustomer. END. /* Found Customer*/ RUN LoadDataSet. /* ********************** Internal Procedures *********************** */ PROCEDURE LoadDataSet : /*------------------------------------------------------------------------------ Purpose: Parameters: <none> Notes: ------------------------------------------------------------------------------*/</none> /* Load DataSet */ QUERY qryJob:QUERY-PREPARE("FOR EACH ttAgrJob"). ASSIGN pdsGeneric = DATASET dsJob:HANDLE. DATASET dsJob:FILL(). END PROCEDURE.

**I did not include the source for the helper.i file as the other function used is validParams() which just checks to make sure the comma delimited list of parameters contains the expected number of parameters.

You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.