Plex 2E

Expand all | Collapse all

SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

  • 1.  SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

    Broadcom Employee
    Posted Feb 26, 2016 05:00 AM

    Hi,

     

    I have received this request from a customer and wanted to have some feedback from you.

     

    I request your feedback on the possibility to improve the PLEX when generating queries SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

     

    The example received:

     

    Currently Plex generates queries SELECT FOR UPDATE, like this:

    SELECT T1.deptno, T1.dname, T1.loc FROM T1 public.dept

    where (T1.deptno = 10) and (T1.loc = 'NEW YORK') FOR UPDATE OF T1.deptno, T1.dname, T1.loc

     

    ERROR: FOR UPDATE must specify unqualified names relation

    LINE 2: ... no = 10) and (T1.loc = 'NEW YORK') FOR UPDATE OF T1.deptno ...

     

     

    To get around this error in the database engine PostGreSQL / EnterpriseDB is necessary, remove the following:

     

     

    SELECT T1.deptno, T1.dname, T1.loc FROM T1 public.dept

    where (T1.deptno = 10) and (T1.loc = 'NEW YORK') FOR UPDATE OF T1.deptno, T1.dname, T1.loc

     

    and thus the query generated in:

    SELECT T1.deptno, T1.dname, T1.loc FROM T1 public.dept

    where (T1.deptno = 10) and (T1.loc = 'NEW YORK') FOR UPDATE

     

    What is your oppinion?

     

    Thank you in advance, Antonio.



  • 2.  Re: SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

    Posted Feb 26, 2016 06:56 AM

    Please read 'Row Locking in SQL Implementations' in CA Plex which outlines pessimistic concurrency support and alternative methods if you choose not to use For update option

     

    Please note both Java and C# implementations do not generate the 'FOR UPDATE OF' clause as at a pattern layer level. The following are the only patterns that use this option.

    SurrogateSystem.Update.NextSurrogate

    RelationalTable.Update.CheckedUpdate

    RelationalTable.Update.DeleteRow

    RelationalTable.Update.UpdateRow

     

    Considering PostGreSQL / EnterpriseDB are 'new' why would you not being using C# or Java to access?

    Please note where ever the customer has explicitly used the For Update option the code would need to be revisited.

     

    If the customer still wants to use the WinC variant why not follow the Generating WCF Services for Non-C# Functions precedent of adding a new build file ini setting:

    [Service generation options]

    Create C# interface classes=1

    such as

    [For Update generation options]

    DropOFExtension=1

     

    I found this an interesting article The FOR UPDATE clause and it outlines as new DB are produced inevitable they will contain syntax that add/depart from X/Open and SQL Access Group SQL CAE specification. CA Plex to remain relevant will need a tried and trusted method to adapt to these changes. Now the build file ini option does pose problems if you are mixing target DB in your local and currently not having these options visible in the G&B panel. But offers least impact to users of the For Update (OF) option is supported. (Had the model at generation time had a clearer idea of the target DB then we could use conditioning meta)

     

    Exerts form CA Plex Help:

    SQL Grammar Conformance Levels

    ODBC defines a core grammar that corresponds to X/Open and SQL Access Group SQL CAE specification, a minimum grammar that meets a basic level of ODBC conformance, and an extended grammar that provides for common DBMS extensions to SQL.

    Extended SQL Grammar functionality:

    SELECT {...} FROM {...} WHERE {...} FOR UPDATE OF {...}



  • 3.  Re: SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

    Posted Mar 03, 2016 01:25 PM

    Hello all,

    I'm from the customer that raise this issue. I don't exactly how it was presented, but let me contribute somehow to this subject since I'm part of the team that develops with Plex.

    Let me start by saying the we have implemented our own pattern libraries. In that patterns we created a single fetch function that has "For Update" clause with the intention of locking the record. This code is platform independent, so our java functions (we only use java generator) are generated with the "For update of" clause in the SQL.

    I our application we implement pessimistic concurrency, So we have hundreds of single fetch functions with For Update across our application (that is huge!).

     

    We use Oracle DB and the "FOR UPDATE OF colunm_name1, colunm_name2 ..." works fine.

    But now we want to deploy our application in new environments using PostgreSQL and are facing this problem. We need the "FOR UPDATE" clause but without the list of columns.

    Furthermore, since our application is a product that we sell to our customers, we need to have the code independent from the database used for each customer.

     

    Note the following:

    1) we do not lock more than one record with the same SELECT instruction. All functions with "For update" clause have table's primary keys in the where clause.

    2) for our application it's not relevant if all the record are locked or only some columns of the record. So, all the record can be lock and our application will work nicely.

     

    So, an ini option or G&B option will work perfectly for us (and for other users, I'm sure) even if we mixed Oracle and Postgres in the same application.~

     

    I hope this helps.

    Thank you

    Bruno



  • 4.  Re: SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

    Posted Mar 03, 2016 02:43 PM

    Thanks Antonio/Bruno, that was helpful to fill in the blanks.

     

    I suggested the ini option which is a G&B option it is just not visible in the G&B settings window.

     

    BUT we should acknowledge this ini option is short sighted and will continue to build technical debt into the tool. It is a band aid, a fragile solution. What happens when you have two settings that are dependent on each other you will be 'coding' in the ini file!

     

    Target DB should be in the model and you can use meta and or action diagram or variants to control what is generated.

     

    The ini file option offers NO impact to other sites so of course it is attractive but go into with this eyes wide open to the affect of going down this path.



  • 5.  Re: SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

    Posted Mar 03, 2016 03:41 PM

    Hi Bruno,

     

    Presenting you with two possibilities :

     

    1. Create Variant for PostgreSQL : In the single fetch check for variant and skip the column names. This will require re-gen of all impacted single fetch functions and maintain 2 or( n ) sets of single fetch function for each variant.

     

    2. Create a table that stores the type of database: With in the single fetch , check the type of database and condition to use column names or not.

    This would be some amount of hard coding in the single fetch. Most likely would be maintaining just one set of functions. (requiring re-gen of all single fetch functions)

     

     

    Regards

    AA



  • 6.  Re: SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

    Posted Mar 03, 2016 04:00 PM

    Hi AA,

     

    Your Option 1. Does not work as the customer still wants FOR Update option but does not want it implemented with Extended SQL Grammar functionality of the 'OF' extension as this is not supported by PostgreSQL . You can not 'skip the column names"

     

    Regards



  • 7.  Re: SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

    Posted Mar 03, 2016 04:54 PM

    Hi George,

     

    Where you want to exclude column names and still use FOR Update, you may have to use EXEC SQL. (This has limitation of using database access statements.)

     

    Regards

    AA



  • 8.  Re: SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

    Posted Mar 03, 2016 05:02 PM

    And that is why it is a bust for your solutions for a whole system being hand written using EXEC SQL and hand edited for maintenance and if a another site needs this they need to hand edit, and then another site etc.

     

    The solution was asked for by CA and I interpreted it as how should we support this in a robust manner.



  • 9.  Re: SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

    Posted Mar 03, 2016 05:18 PM

    You could automate some of that effort using Model API's. While CA comes up with more elegant solution. some work around.

     

    Another painful method is to edit the generated source and see if it compiles.



  • 10.  Re: SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

    Posted Mar 03, 2016 05:54 PM

    Now model API is a workaround

    • Generate the source statements to be placed in source objects which are then executed by EXEC SQL
    • Or my prefered method like a few of my StellaTools already do! is to create a G&B tool that generates selected objects, then manipulates the generated source, then builds the manipulated source.

     

    But my experience is that CA Plex sites have little to no experience or appetite for Model API.

     

    Lastly this is not my project I am just advising on what could be done to support different DB as CA reached out to us for comment.

     



  • 11.  Re: SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

    Posted Mar 03, 2016 05:28 PM

    Well George, Your projects will stall , if you expect CA to provide or support in robust manner.

     

    Ask CA how long will it take to support , displaying all image types in a Grid. Plex has elegant picture converter tool in its runtime.

     

    Plex converts only for selected types...

     

    Answer for other types is , Plex does not support.



  • 12.  Re: SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

    Posted Mar 03, 2016 05:48 PM

    ashfaq.m.ahmed wrote:

     

    Well George, Your projects will stall , if you expect CA to provide or support in robust manner.

     

    Ask CA how long will it take to support , displaying all image types in a Grid. Plex has elegant picture converter tool in its runtime.

     

    Plex converts only for selected types...

     

    Answer for other types is , Plex does not support.

     

    This particular point will keep for another day.



  • 13.  Re: SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

    Posted Mar 03, 2016 05:59 PM

    Hi Antonio/Bruno/George,

     

    You may consider providing another "FOR Update" statement, which drops the column names.

     

    Condition it within the Single Fetch to use either of the "FOR Update" statement.

     

    Database configuration or Variant configuration would decide which "FOR Update" statement to use.

     

    (Prefer single instance of the object handling both types. With variant config need to maintain multiple set of same object)

     

    Regards

    AA



  • 14.  Re: SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

    Posted Mar 04, 2016 04:49 AM

    Hello,

    thank you Ahmend and George for your inputs.

    We don't consider changing generated source code or re-implement functions with ExecSQL as valid options.

    Since we do need the FOR UPDATE anyway, CA must do something.

    Could be an ini option for start. I’m not fan of this approach but we know sometimes is the only way for CA to provide a solution in an acceptable timeframe.

    The proper solution, as I see it, could be one of the following:

      1) a new instruction in action diagram for this FOR UPDATE without OF column_names

      2) a new value for VW language SYS triple to distinguish from the current JDBC value

     

    thank you all once again.

    Regards

    Bruno



  • 15.  Re: SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

    Posted Mar 04, 2016 05:07 AM

    Hi Bruno,

     

    This will take route of enhancements/Voting/Priority List. Hope it isn't years to get another variant of FOR UPDATE statment.

     

    All the Best !!!!

     

    Regards

    AA



  • 16.  Re: SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

    Posted Mar 04, 2016 05:41 AM

    But Bruno don't lose business because of the FOR Update issue.

     

    Create a list of the affected fucntions...I used StellaTools to seach all action diagrams for the text "FOR UPDATE"

     

    Then generate them

     

    Then write a script to run over all the *ObFnc.java files and replace the string using regular expression

     

    "FOR UPDATE OF mycolumn1, mycolumn2 ";

    with

    "FOR UPDATE";

    Then Build



  • 17.  Re: SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

    Posted Mar 04, 2016 06:01 AM

    Hi,

    For now is not a question of losing business or not.

    So, changing source codes is not an option right now.

    Changing source codes was the first thing that crossed my mind when we faced this problem. But was (and still is) a “no way!” solution.

    But thank you anyway.

     

    Note that we use, we like and we believe in CA Plex. So having this addressed in the tool is a desirable way to go.

    Thank you



  • 18.  Re: SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

    Posted Mar 04, 2016 06:08 AM

    Bruno,

     

    Do as I did while waiting for something to be supported, create a Model API addin that did the job for me. Your situation is perfect for model api addin. Never say CA Plex does not support something, Go and find a solution, if not you will lose the tool itself and be in the world of 3GL

     

    If you think well CA should support this you could be right but you must also remember the other alternative is writing this in a 3GL language and as I sit here learning Java, i can only admire and miss obsydian for its 4GL capabilities. So my advice Bruno is write a model api tool in the mean time to automate the workaround until CA Support your ticket. 3GL does not bare thinking about as it is sooooooooooooooooooooo painful when the 4GL genie is out of the bottle.



  • 19.  Re: SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

    Posted Mar 04, 2016 05:11 AM

    Sorry but I would not want CA Plex to go down this route either.

     

    We don't need another "FOR Update" statement, we already have one. It is just the statement generates the wrong code for a particular DB. Introducing a new statement would be against what CA Plex action diagram stands for: Psuedo Code, Platform agnotistic etc.

     

    The simple fact is PostGreSQL / EnterpriseDB supports The FOR UPDATE clause with a slightly different syntax than its SQL DB predecessors.

     

    The real issue is how can the CA Engineering team code for the PostGreSQL with in the generator so that when Extended SQL Grammar functionality of FOR Update clause is met it can drop the Columns when PostGreSQL. BUT when generating a function the DB is NOT taken into consideration only the function's language and acccess method (Vw Language) as far as I can tell.

    • So either we rework CA Plex to 'know' about what particular DB you are targeting....maybe a continuation triple to the langauge triple to say make this view use JDBC accessing a PostGreSQL DB. Then there would be a hook for the generator to drop the columns.
    • Or have a gen and build setting to drop the columns in FOR Update Clauses. Previously these new settings have been added for the generators BUT the G&B settings window has not been updated which must not be the case. We should not be hacking INI files for G&B settings which we should be maintaining them in the G&B window with appropriate help text etc.

     

    I prefer the first option.



  • 20.  Re: SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

    Posted Mar 04, 2016 05:39 AM

    Or may be enhancement of the existing FOR UPDATE statment with additional optional options.

     

    Fetch EQ keyvariable, [,For Update [.DropFields]][, variable<maximumrecordsetsizefield>|value]

     

    Or different language triple (as mentioned by You & Bruno)

     

    Or FNC option name NME triple

     

    Or will this be deemed as Non-Standard SQL Grammar ?



  • 21.  Re: SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

    Posted Mar 04, 2016 06:01 AM

    Hello,

    Yes George, you are right: CA Plex should be platform agnostic as much as possible.

    The suggest having a variant for the existing FOR UPDATE because they are in fact different:

    If you use FOR UPDATE all the record of all tables in the select are lock for update.

    If you use FOR UPDATE OF columnx, … only tables with columns identified in the list of columns after “OF” are in fact locked.

     

    But since CA Plex generator includes automatically all view columns when For update statement is used in action diagram, either way all records for all table in a join are locked.

    So this new suggestion is not that good.

     

    Ahmed, after this, you suggestion of having .DropFields option seems to much.

     

    Maybe a different language triple is the best option.



  • 22.  Re: SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

    Posted Mar 18, 2016 06:08 AM

    So  Antonio you asked for feedback. What are your thoughts on the feedback?



  • 23.  Re: SELECT FOR UPDATE, so that allows the use of the code generated by PLEX, with other database engines.

    Posted Mar 24, 2016 01:43 AM

    SO ANTONIO WHAT IS THE OUTCOME. DON'T COME HERE AND EXPECT PEOPLE TO PUT IN THEIR FREE TIME AND EXPECT NOT TO PARTICIPATE IN RETURN.

     

    I'm tired of helping people on this forum who do not reciprocate.