Protheon Usefull Tools & DB Access for OpenEdge

So you need access to another database from inside your Progress OpenEdge application or access some general purpose tool? Sure, you can use any of the tools created by Progress or access the datasource using ODBC, but .... what if there is a mutch simpeler solution?

We want to introduce Protheon a very simple set of libraries which allow you to connect to any of the supported databases on premise or cloud, retrieve data and use this in your Progress OpenEdge application. Have a look at some examples in a later chapter.

Platform

The libraries are written in c#, include the drivers for the database of your choice (if supported) and are designed and build for the Windows platform. They are dedicated to the OpenEdge platform and therefore use the enviroment supported by Progress (Net 4.7+). As soon as the platform is upgraded to the Core environment we will supply the libraries for that version.

Databases

We currently (2020-Q4) support the following databases: Mysql, MariaDB, Oracle, SqlServer, InfluxDB, MongoDB, Redis, Dynamo, Odbc, OleDb, CouchDB and maybe some new ones! Please inspect the compatibility list for more details.

Tools

We also have some simple components for special purpuses, like RabitMQ, ActiveMQ, Email, JSON/XML tranformation, a special HTTPClient component with optional OpenID features, Bearer or header tokens etc. Another interesting component is a module for downloading, (re)sizing and converting of images (jpeg, bmp, png, gif).

Cloud

Our code is able to connect to the cloud (AWS for now) for accessing your databases using the required and secure authentication methods, again all from your favorite OE code.

OpenEdge or ...

Sure, as stated: Designed for OpenEdge, but any legacy system able to access Microsoft DLL assembles can use this as well. We also have a REST implementation of the software which can be used by other OS's and systems to access the data; this service must be running on a windows machine.

(© 2020) Netideas Consultancy -- Protheon and ProtheonNet is a trademark of Netideas Consultancy, the Netherlands. The company is privately owned and has 30 years experience in Progress OpenEdge development and consultancy. We respect the rights and rules of the US based Progress Software Corporation (PCS)
[ TOP ]

A Simple Example

Easy coding, fast results!

01|  /* (C)2020 Netideas -- Example Protheon code */
02|  
03|  using ProtheonNet.*.
04|  using ProtheonNet.DB.*.
05|  
06|  define variable myDB     as DataConnect  no-undo.
07|  define variable myResult as DataResult   no-undo.
08|  define variable myRecord as DataRecord   no-undo.
09| 
10|  myDB = new DataConnect("myDBConfig",true).
11|  myResult = myDB:getData("select * from customer").
12|                    
13|  do while myResult:next():
14|    myRecord = myResult:getRecord().
15|    display myRecord:Field["firstname"] label "First"
16|            myRecord:Field["lastname"]  label "Last".
17|  end.
18| 
19|  myDB:Disconnect().

The application/libraries are designed to be used inside a Progress OpenEdge environment and follow the guidelines by Progress.

  • Line 3: and 4 refer to the base libraries of Protheon which should be included for easier coding.
  • Line 6: to 8 define some variables for holding the class instances.
  • Line 10: calls the Protheon core with the name of the database to be connected. Parameters for connecting the DB are stored in a seperate config file.
  • Line 11: defines an executes the supplied sql command
  • line 13: starts a loop on the result until no more records are available
  • line 14: gets the record available for processing
  • line 15: access the result record by its field name
  • line 19: closes the db connection (good practice!)

    NOTE: Syntax given in this and other examples may change in final product.

[ TOP ]

Another Example

Making it a little more complex by doing multipe queries

/* Now open (say) 2 queries on any data */
q1 = myDB:GetData("select * from tablea").
q2 = myDB:GetData("select * from tableb").

REPEAT num = 1 TO q1:Count():
   recA = q1:GetRecord(num).  
   DISPLAY recA:Value("maincode")    
           recA:Value("subcode") 
           recA:Value("anyfield") 
           WITH TITLE " Q1 -- parent ".

   q2:Reset().
   REPEAT WHILE q2:Next(): 
     recB = q2:GetRecord(). 
     IF recB:Value("subcode") = recA:Value("subcode") THEN
       DISPLAY recB:Value("record")   
               recB:Value("description") 
               recB:Value(3)
               WITH TITLE " Q2 -- child ".
   END.
END.

myDB:Disconnect().

Some notes on this:

  • Connecting to a datasource can either be defined in external config files or on the DatabaseDB statement.
  • The (optional) contructor value (true or false) set internal counting to the progress standard (1-n).
  • You can have any number of open queries to the same DB and give them names for easy reference.
  • This example works uses the internal array ("Row()" and "Count()")
  • Sure, using nested queries has the same effect (with more DB-IO overhead ...)
  • And inner, outer, left and right joins and other sql features are possible when supported by DB!

    Note: Parameters to connect to services (ie. DB) can be supplied in your code or defined in an external config file (Protheon.json) where (some of) the parameters can be stored encrypted for safe keepings. Encryption tools are provided.

[ TOP ]

More Features

Building sql queries is sometimes hard. In The C# world 'we' are using 'Linq' as a standardized way of defining queries, so we've created an initial implementation for OE; how about this code:

myDB  = NEW DataConnect(true).
myDB:Connect(dbtype,dbserver,"3306",dbname,user,pass).   
 
rec = myDB:SqlBuilder("customer c")
          :AddTable("order o")
          :Fields  ("c.custnum,c.custname,o.ordernum")
          :Field   ("(select count(ol.*) from orderline " + 
                    "where ol.order=o.order) as cnt")
          :Where   ("c.custnum=o.custnum")
          :And     ("c.country","=","NL")
          :First().
           
if rec NE ? THEN DO:
  display rec["custname"] rec["ordernum"] rec["cnt"].
  ....


reclist = myDB:SqlBuilder("customer c")
              :Fields("c.*,count(o.*) as cnt")
              :Where ("c.custnum",">",10000)
              :Join  ("outer","order o","o.custnum=c.custnum")
              :Get().
...           
result = myDB:SqlBuilder("customer")
             :Value  ("creditlimit",iMaxLimit)
             :Where  ("creditlimit",">",iMaxLimit)
             :Update().
message "Done: " + result["Ok"].
...
myDB:SqlBuilder("customer")
             :Where  ('creditlimit','=',0)
             :Delete().

Some notes on this:

  • The code is in development but our goal is to implement the full LINQ set.
  • Usable for selects, updates, deletes and creates.
  • Currently available for the main databases but should work on others as well.
  • Integrates nice with our regular code.
  • Full (Licenced) version has Update, Delete, Create and Count methods next to Select/Get.
  • First, Next, Prev, Last returns a DataRecord, Count a long (int64) and others a DataResult
  • A DataResult contains a list of records result:RecordList with additional fields
  • Paging automatically when supplied :Get(iPage,iPageSize)


... and YES, this all works in Progress! *1)
*1) Minimal OpenEdge version 11, running on Windows 10/2013+ (others not tested).



[ TOP ]

Databases

Protheon supports 'standard' Relational databases:

Mysql / MariaDB

One of the most requested feature for connecting is the Mysql database.
  • Mysql/MariaDB driver version 8.0.21 (july 2020), created by Oracle
  • Mysql: All modern versions supported
  • MariaDB: All modern DB versions supported
  • Server can be on any platform (win/lin/mac/Raspberry Pi)


SqlServer

One of the most requested feature for connecting is the SqlServer database.
  • Driver version 4.8.2 (august 2020), created by Microsoft
  • Supports most modern DB versions.


Oracle

One of the most requested feature for connecting is an Oracle database.
  • Driver version 19.8.0 (july 2020), created by Oracle
  • Supports most modern DB versions.


Postgres

Another frequently requested feature for connecting is the Postgres database.
  • Driver version 5.0.0 (june 2020), created by the NPG group
  • Supports most DB versions.


Redis

One of the best No-SQL databases arround.
  • Driver version 0.9.2, (april 2015), created by the OS community
  • Supports most DB versions.


CouchDB

Another No_SQL database, widely used.
  • We use the MyCouch driver version 7.2.1 (august 2020).
  • Needs some special OE tweaking (see doc)
  • Supports most DB versions.


ODBC / OleDB

Generic drivers for most ODBC/OLEDB capable databases like MSAccess, Excel etc etc.
  • We use version 5.0.0 for both connections (august 2020), created by Microsoft.
  • You need to check support yourself!


... many more ...

.. some NOSQL databases:

Redis

One of the best No-SQL databases arround.
  • Driver version 0.9.2, (april 2015), created by the OS community
  • Supports most DB versions.

running on all OS's (windows, linux, macos and even Raspberry Pi!)

Mongo

One of the most requested feature for connecting is the Mongo database.
  • Driver version 2.10.2 (august 2020)


Dynamo

An interesting feature for connecting is the Dynamo DB in the Aws cloud.
  • AWSSDK & AWS dynamo library 3.5.1.5 (augus 2020)
  • Uses secure Aws connectivity


CouchDB

Connecting to the CouchDB database.
  • Used the MyCouch driver version 7.2.1 (august 2020)

    • And specialized databases:

    InfluxDB

    Specialized database for time-measurements especially usable on IOT.
    • Using the REST interface

    running on all OS's (windows, linux, macos and even Raspberry Pi!)

    [ TOP ]

    Tools

    Access to ActiveMQ / RabbitMQ

    Sending messages to queues or topics from your (OE) code, retrieving pending messages from the queue. Simple and limited version. 

    /* -- Sending a message to ActiveMQ -- */

def var oParam  as Param      no-undo.
def var oResult as DataResult no-undo.

oParam = new Param().
oParam:Set("Prefix"  ,"mq.send").
oParam:Set("mqtype"  ,"activemq").
oParam:Set("mqserver","localhost:61616").
oParam:Set("mqqueue" ,"ruud").
oParam:Set("message" ,"This is the message to send").
       
oResult = Protheon:Execute("activemq",oParam).

message oResult:Ok " - " oResult:Error.

    Email

    Sending out messages in all formats and with any attachments

    File downloads

    Downloading files from any server

    Image processing

    Download images, resize them and store in any of the supported formats.

    Rest calls

    Doing calls to other servers with multiple authentication methods.

    Encryption

    Encrypting strings

    Data conversion

    Converting data from xml to json, csv, text, yaml and sometimes vv. 

    /* retreiving info on IP number */
json = Protheon:Func("rest","ip_data","123.123.123.123").

/* securing data using cryptology */
crypted = Protheon:Func("encrypt","this is the source").

/* ... and decrypting ... */
decrypt = Protheon:Func("decrypt","encrypted_string").

    Protheon supported 'functions' for easy programming:

    We also provide easy functions for simple command, like using cryptograhic, call external (simple) REST services without configuration and many more!
    Frequently used 'REST' interfaces can be configured once and called with minimal configuration, consistently and manageable.

    [ TOP ]

    Platform

    Platform information

    The complete app is written especially for the Windows Platform and should support Windows 10+ (7 not tested...) and/or Windows server 2013+. The design is modular and any module you don't need, can be removed without problems. Also, when adding new modules to the set, you just copy the files to the disk in the defined structure. Updating a library to a new version requires restart of any running Progress Client.

    The set of libraries are build around the NET framework version 4.7.2 which is supported by Progress OE. If Progress upgrades these requirement we will upgrade our libraries as soon as possible. Currently NET framework is a Windows-only platform but if the company will support the CORE platform (which can -in most cases-) run on Linux, MacOS etc we will finalize our "ProtheonCore" solution for these platforms. Our application is already prepared for this!

    There is however an REST of WEB interface to the application as well which allow you to call any of the modules from a simplyfied interface from other (none Windows) platforms. Nice not?

    [ TOP ]

    Availability

    Is it available yet?

    Our product is in it's final stages of development and testing and should be available soon. If you want to participate in beta testing or want to have access to a trail version, please contact us using email: info@protheon.nl

    Currently a limited set of modules are available for download. More will follow in the near future!

    [ TOP ]

    Assembly Installation

    Global

    Protheon consist of multiple "zip" files containing the core and separate optional modules you can download and copy to a directory on your disk. Optional modules can be added or removed at any time. Currenty we have the following (main) zipfiles:
    • Protheon_*.zip - Main libraries (MANDATORY), example protheon_*.zip
    • [database]_*.zip - Database specific files (Optional), i.e. MySql_*.zip
    • [tool]_*.zip - Tool specific files (optional) i.e. ActiveMq_*.zip
    • [function]_*.zip - Function library, i.e. SimpleRest_*.zip
    Example installation:
    • Create a directory "assemblies" inside your program startup directory
    • Copy the files "assemblies.xml" and "assemblies.config" to the assemblies dir
    • Create a subdirectory "protheon" inside the "assemblies" directory as well.
    • Unpack the zips in this protheon directory but preserve the structure.
    After installation, open your OE editor and test: 
    USING ProtheonNet.*.

MESSAGE Protheon:Info() 
        VIEW-AS ALERT-BOX TITLE "Protheon Info".
    Then modify your OE startup script, add (either on the command line or in your startup.pf):
    • -assemblies assemblies
      or use the full path:
    • -assemblies d:/apl/app/myapplication/assemblies
    Your Done!

    Now lets start your OpenEdge 'engine':

    $ cd d:\apl\app\myapplication
$ prowin -assemblies assemblies -pf db.pf

    [ TOP ]

    Pricing

    well.... how mutch?

    We have a freeware version of the application which does only 'read' requests from the database with a limit of 10 records. This version can be used to test our code and implement the features in your application. If you want to have access to any of the modules, you can obtain a licencekey for the module, install it an have access to the full component. A unlimited version (all modules, all tools) is also available.

    Whenever you buy a licence you can use this module on any number of machines in the same network and company, it's up to you to install it on the Appserver, on the Client or anywhere else. Even installing and using the REST or WEB interface requires the same licence.

    Any licences is valid for the lifespan of the OE licence it is created for and can be used by up to 50 systems within the same company. If Progress releases a new (main) version a new licence of Protheon should be obtained.

    Base pricing is as follows:

    Module Type Description End-user Price
    Any Relational DB (mysql, postgres, sqlserver ...) € 199
    Any No-Sql Database (mongo, couch, redis ...) € 129
    Any specialized DB (Influx) € 219
    Additonal Tool (any) € 99
    Simple Function (any) € 49
    Full Licence (any DB, Any Tool) € 999

    All prices excluding vat for EU.
    Re-sellers prices available on request.
    (We are open for discussion).

    [ TOP ]

    Downloads

    As described in the Installation notes, we have the following download for you.

    Download Description Version Available
    Mandatory & General
    Protheon_101.zip Protheon Main library and required toolsV1.0.1Q4 2020
    Databases (Relational)
    Mysql_101.zip Protheon Mysql library & filesV1.0.1Q4 2020
    SqlServer_101.zip Protheon SqlServer libraryV1.0.1Q4 2020
    Oracle_101.zip Protheon Oracle libraryV1.0.1Q4 2020
    Postgres_100.zip Protheon Postgres libraryV1.0Q1 2021
    Odbc_100.zip Protheon ODBC libraryV1.0Q1 2021
    OleDB_100.zip Protheon OleDB libraryV1.0Q1 2021
    Databases (No-SQL & Cloud)
    Redis_100.zip Protheon Redis libraryV1.0.1Q4 2020
    Mongo_100.zip Protheon Mongo libraryV1.0Q1 2021
    Influx_100.zip Protheon Influx libraryV1.0Q2 2021
    Couchdb_100.zip Protheon CouchDB libraryV1.0Q2 2021
    AwsDynamo_000.zip Protheon Dynamo library (AWS)V1.0t.b.a.
    AwsMysql_000.zip Protheon Mysql library (AWS)V1.0t.b.a.
    AwsSqlServer_000.zip Protheon SqlServer library (AWS)V1.0t.b.a.
    Protheon Tools
    email_101.zip Protheon Easy Email libraryV1.0.1 Q1 2021
    activemq_100.zip Protheon Easy ActiveMQ libraryV1.0 Q2 2021
    rabbit_100.zip Protheon Easy Rabbit libraryV1.0 Q2 2021
    image_100.zip Protheon Image libraryV1.0Q2 2021
    convert_100.zip Protheon Data Convert libraryV1.0 Q3 2021
    download_100.zip Protheon Download libraryV1.0 Q3 2021
    httpclient_100.zip Protheon Full HTTPClient libraryV1.0 t.b.a.
    Protheon Func Libraries
    func_rest_101.zip Protheon REST-Func FeatureV1.0.1 Q1 2021
    D.I.Y.: Examples for 'build your own'
    DBSample_10.zip Sample code to create your own DB driver (On Request only) - Q1 2021
    ToolSample_10.zip Sample code to create your own Tool (On Request only) - t.b.a.
    FuncSample_10.zip Sample code to create your own Function (On Request only) - t.b.a.
    Follow the instructions in the zip.
    If you REALY need a module not yet supported or released, please contact us!

    NOTE: You are downloading a DEMO version of the software. Full access to all features can be obtained by buying a licence for one or more modules and installing this licence on your system. One licence is valid for one module and can be installed on up to 50 systems within the same company. Please look at the pricing details and get in contact with info@protheon.nl for more details and orders.

    [ TOP ]

    Documentation

    We have the following (PDF) documentation for you to download:

    Base

    Databases

    Tools

    Generic

    Notes
    **1) -- Work in progress, available when module is released (see download section)
    **2) -- 1st version (beta) available for review
    [ TOP ]

    Build Your Own ...

    Want to use Protheon to build your own libraries?

    Sure, you can build your own libraries using your base classes. More details will be available on request (info@protheon.nl). But:

    • You need a windows-10 development PC
    • A working MS studio or 'Rider' licence (latest release)
    • Active knowledge of the C# platform
    • Access to the developer guidelines for Protheon (on request)


    Depending on the type module you want to create, development can be simple or complex.
    You can download sample DB/Tool or Function examples at our download section.

    [ TOP ]

    Feature requests

    Need access to a non-supported DB or special module?

    Sure, we can provide the driver for your database as long as the following guide lines are met:

    • DB should have a C# NET (4.7+) driver
    • Runtime DB should be available for our developers (download, freeware etc)
    • Driver must be well documented and maintained
    • Solution can be public or private.


    Send us an email (info@protheon.nl) and we will investigate.
    Pricing depending on solution.

    [ TOP ]

    Contact

    Need contact with us? Please send an email to info[at]protheon.nl.

    [ TOP ]