Modern web applications and API with OpenEdge, SQL, ODBC and PHP

Posted on by

Progress OpenEdge Applications

SQL engine in OpenEdge is a modern tool to expose OpenEdge data via ODBC and JDBC directly to API and web applications. SQL engine is fast, mature and requires minimal setup. To me, it seems like SQL is a very viable alternative to ABL in building web applications.

Character (CHUI) or desktop-based (GUI) applications are written with Progress. These applications are typically written in ABL language. Modern web/mobile applications are very relevant today. PHP, Rub, Python, Node, Java and C# are some of the many languages used to write web applications. APIs typically power such modern web applications. Such applications need data from databases. Progress has created REST Adapters, WebHandlers, Open Client and other tools to expose OpenEdge data via APIs. Those are not the only tools available! SQL is a viable option to expose Progress OpenEdge data to modern web applications.

Modern web applications

SQL Client Access

You can get SQL Client Access drivers from Progress’s Electronic Software Delivery (ESD) site. Drivers are available for Windows and Linux. Modern programming languages can use SQL drivers with ODBC to connect to OpenEdge. You can use C#, PHP, Python, Ruby, Java and other modern languages to communicate with OpenEdge over ODBC or JDBC.

Goal

Build web applications on Linux using PHP language and OpenEdge backend. You’ll be able to use topics below to create yourself web applications and APIs with your own favorite modern programming language.

Why use SQL with OpenEdge?

Here are some reasons to use SQL with OpenEdge.

  1. SQL support with OpenEdge is very good for business operations
  2. SQL is a language most other databases uses. It is a standard language to communicate with databases
  3. Setup of SQL with OpenEdge is easy
  4. SQL is executed on server side; it is fast
  5. SQL uses cost-based optimization, whose query plan can be reviewed easily
  6. Authentication model of SQL is secure by its very nature; ABL is an open system
  7. Your web team is likely to understand SQL more compared to ABL
  8. You can outsource (not necessarily off-shore) web development

ABL is NOT dead

It is important to note that ABL is not a dead language. Progress is constantly improving ABL and adding modern features to it. In comparison with Python, PHP, Ruby, Java, C# and other languages, ABL definitely falls short. However, ABL interaction with OpenEdge database is perhaps the best.

Topics

Topics will be added as they are written. Below are a few helpful topics.

  1. Installing SQL Client Access drivers on Linux server
  2. Set up ODBC connection to OpenEdge
  3. Create/Insert data in OpenEdge using SQL and ODBC
  4. Retrieve/Select data from OpenEdge using SQL and ODBC
  5. Update data in OpenEdge using SQL and ODBC
  6. Delete data from OpenEdge using SQL and ODBC
  7. Use DBeaver client to write ad-hoc SQL queries
  8. Diving deeper in creating data in OpenEdge
  9. Advanced techniques to retrieve data from OpenEdge
  10. Complex updates to data in OpenEdge
  11. Advanced ways to delete data from OpenEdge
  12. Transaction control with SQL in OpenEdge – commit and rollback
  13. Is OpenEdge a Relational Database Management System?
  14. Creating tables with SQL in OpenEdge
  15. Creating sequences with SQL in OpenEdge
  16. Viewing Query Plans in SQL and OpenEdge
  17. Inspecting database metadata in SYSPROGRESS schema with SQL

Each of the topics above will help you understand that SQL is a viable means of building web applications with OpenEdge.