1

Is there any way to store a session variable in OpenEdge SQL similar to how Oracle's RETURNING INTO clause works? I need a column value set by a trigger to be available after running an INSERT without having to do another SELECT.

I'm using 10.2B. I've looked at the SQL Reference and SQL Development handbooks but didn't see anything like this.

Abe Voelker
  • 30,124
  • 14
  • 81
  • 98

2 Answers2

1

How about using PUBLISH and SUBSCRIBE? If your trigger were to PUBLISH the value that you wanted, and you subscribed to it in the appropriate spot in your calling program/class/super procedure, you'd get the info you needed. It isn't as clean as how it would be done in Oracle, but it might get you to where you want to be.

If you want some code, I can provide it...but based on browsing some of your other questions and answers, I don't expect that you need it :-)

Paul Krikke
  • 86
  • 3
  • +1 because I didn't define my requirements well enough, and for all I know this might work when staying strictly within the AVM. However, in my case the only communication I have with OpenEdge is through the JDBC driver to the SQL-92 engine. I'm not running any ABL code at all, it's all Ruby and Java (using JRuby; a Ruby interpreter running on the JVM). :-) https://github.com/abevoelker/do/tree/add-openedge-adapter/do_openedge – Abe Voelker Jun 12 '12 at 13:10
  • Interesting to read that ABL triggers run in the context of the client, while SQL triggers run on the server (source: OpenEdge 11 SQL Development p151). Trusting execution of code that ensures database integrity to the client's runtime seems very insecure to me, but what do I know (never drank the OE Kool-Aid). So anyway I'm guessing that yes, your code should work for ABL clients accessing the DB through the ABL engine, however isn't feasible for clients that only access OE through the SQL engine (which uses Java stored procedures for triggers that are entirely separate from ABL triggers). – Abe Voelker Jun 12 '12 at 17:37
0

There is no such feature and no viable workarounds.

An infeasible workaround is to construct a special stored procedure specifically for performing the INSERT(s), but that is not a general solution as the INSERT command takes a variable number of parameters (not least of which is which columns and data to populate with), while the stored procedure must have a fixed number of parameters.

If there existed a SQL function that would return a special session ID that corresponded to the client's transient connection to the SQL engine, it would be possible to have the client and trigger/stored procedure communicate using a pre-determined table as a request queue (by using the session ID as a key). Unfortunately, according to the documentation there is no such session ID.

The underlying use case for my question was for a way of implementing auto-incrementing primary keys on INSERTs that would communicate back to the client the inserted ID, so that I would not have to force the client to either

  • provide an immediate value for the PK themselves
  • provide a sequence that corresponds to the table that they are updating

Unfortunately, due to the lack of support, I've had to impose restrictions on the client to provide either one of the above (and lacking either, the code assumes a sequence of <tablename>_id_seq exists).

Abe Voelker
  • 30,124
  • 14
  • 81
  • 98