Defining and Using SQL-92 Triggers in an OE Database - Forum - OpenEdge Development - Progress Community

Defining and Using SQL-92 Triggers in an OE Database

 Forum

Defining and Using SQL-92 Triggers in an OE Database

This question is not answered

Hi, Everyone:

I want to write an experimental JDBC client application with OE as the database. One of the basic requirements is that the JDBC client be able to invoke Stored Procedures defined in the OpenEdge database. The eventual goal is to write a full-scale application using Spring and Hibernate for the back end with an Angular front-end. 

Can someone point me in the right direction that helps me understand how to define and use Stored Procedures for an OE database

A simple example for:

  Defining such a Stored Procedure

  How a JDBC client would utilize such a Stored Procedure

Thank you! 

v

All Replies
  • Hi,

    Here is the link to knowledge base article which shows how to create and use store procedures.

    knowledgebase.progress.com/.../P38484

    And here is the link to documentation on stored procedures.

    documentation.progress.com/.../index.html

    Thanks,

    Akthar.

  • I can't really imagine why would anyone use the OE (R)DBMS without the 4GL so just for storage and not business logic since there are plenty of good SQL database engines out there either open source or less expensive than OE... if there is some existing business logic, or the plan is to write that in 4GL, then our JDBC driver make it very easy to expose that as 'stored procedures' - http://acorn.ro/products/abl-jdbc/ 


    Marian Edu

    Acorn IT 
    www.acorn-it.com
    www.akera.io
    +40 740 036 212

  • Thanks @makhtar for the very helpful URLs.

    Thanks @Marian.Edu.

    The need is to eventually migrate from Progress RDBMS with the first phase focusing on converting the application logic from Progress 4GL to Java/JDBC. Where SQL92 Triggers come into the picture is trying to answer to the needs of real-time invocation of logic based on database events that are affected by the Java/JDBC logic. This need stems from the fact that 4GL Triggers are not invoked from application logic that is not written in Progress 4GL.

  • I'm a bit confused by the fact you're mixing triggers with stored procedures but anyway you might go down a slippery road here by keeping business logic in your database since while calling stored procedures through JDBC/ODBC is a standard the language used to implement those is vendor specific. So say you rewrite the business logic code from 4GL to stored procedures/triggers for OE RDBMS then when you move to another database engine you will have to do that again so why bother with the intermediate step?

    If the only thing that stops you from moving to hibernate for the business logic is the fact that the 4GL triggers does not fire when you connect through JDBC then the driver mentioned before could help since that is using the 4GL database engine so the 4GL triggers fires just fine. This does require an application server though so if that is not available already acquiring more OE licenses in the interim might not be the best solution although you probably still need to support the 4GL version for a while and the migration won't just happen over night.


    Marian Edu

    Acorn IT 
    www.acorn-it.com
    www.akera.io
    +40 740 036 212

  • , Since SQL Triggers are also considered a case of Stored Procedure in the world of Progress SQL, I am afraid I conflated the two in my OP.

    In fact, the main objective is to use SQL Triggers for database events (INSERT, UPDATE, DELETE...) via JDBC clients. The use case is a parallel to where you would use 4GL database triggers for database events via a 4GL client.

    Thanks for pointing me to your JDBC driver. Since one of the core objectives is to get away from Progress licenses and move towards an Open Source strategy, this cannot go in the direction you are suggesting, since that involves maintaining the AppServer license. We will, however, be looking to get the DataDirect JDBC driver.  

  • > On May 16, 2019, at 9:36 AM, v205 wrote:

    >

    > We will, however, be looking to get the DataDirect JDBC driver.

    the JDBC driver that comes with the OpenEdge RDBMS /is/ the DataDirect driver. if you dont have it already you can download it from ESD at no charge.

  • @Gus, thank you for pointing that out. I was not sure if they are the same exact driver. Much appreciated.