20 replies to this topic

[DARREN]

Oh ye of little faith.

You should also be aware of a new environment variable that complements this new functionality. It is called REPARSE and can be set to Y or null (i.e. not defined at all). It is used to tell PROIV to reparse a dynamic SQL statement if the statement is executed more than once in a given function. Setting it to null means that PROIV will use the statement it parsed from the previous execution of the logic. Setting it to Y means that PROIV will rebuild the statement every time it executes the logic. This environment variable 'switch' is used primarily for performance as it tells PROIV to re-use or re-construct the SQL statement. The construct of your application is also important as it governs the setting of the switch. If for example you only had functions that allowed a user to enter values in the upper 'header' section of a screen and then used those values to dynamically select and display records in a paging screen and then exit the screen, REPARSE would be set to null. If however, you had the same scenario, but the user could navigate back up to the top of the screen (many time screen) and re-enter different selection criteria, REPARSE would be set to Y.

If in doubt, set it to Y.

[DARREN]

From what I read from the technical white paper on this, it is not possible to dynamically define the file in the SQL statement. This is because the cursor of data that is returned to PROIV has to be 'de-blocked' by PROIV, that is, PROIV has to read it. The way PROIV achieves this is via a file definition and these are defined and compiled into the function at development time. PROIV file definitions are not dynamic i.e. it is not possible to tell PROIV to use a given file definition in one condition but use another file definition in another (although it is possible to use the ALIAS command to dynamically associate a file definition with a physical file). The definition and inclusion of the file definitions in a function is one of the corner stones of PROIV. It's one of the thing that makes PROIV a RAD (along with the timing cycle).

To overcome your issue, try and thing of another solution. One idea is to conditionally link to a different logical cycle (global or local) depending on the file that you wish to read. Remember that is version 5.0 you have memory files that you can also take advantage of i.e. read data out of a given physical and load it into a memory file (very fast - no I/O). The the data in the memory file can then be referenced anywhere in you application and it persists for the life of the session.

[DARREN]

Not really, other than what I suggested.

To ask a somewhat obvious question - if the data in the two files has the same construct i.e. keys and file variables, why is not in the same file? I would suggest that this is not a normalised database.

[Dan Shannon]

Well spotted Darren - no it's not a normalised database. It's a conversion of a commercial product that was written for PRO-ISAM in the first instance 10+ years ago. That means that the data (many 10s of millions of records) has to be broken up to get around the storage limitations of PRO-ISAM. Then, when you convert to Oracle, the overhead in converting the tables Rob's talking about to normalise them is enormous - and therefore not worth it in cost/benefit analysis terms.

Wouldn't it be nice to use a REF CURSOR to retrieve the data, then mash it into whatever file definition is presented a la type 2 SQL statements? Myriad Oracle tools can do it... and it can always fall over if the file def in PRO-IV doesn't match up to the datatypes returned in the cursor columns.

What is needed for this (and I suspect many other necessarily imperfect Oracle conversions of legacy PRO-ISAM applications) is essentially this:

SQL
$STATEMENT
ENDSQL

And for this to allow selection via a cursor of whatever SQL SELECT statement is contained in $STATEMENT.

I know there are issues with the handling of cursors, but I don't believe the above is impossible.

Dan Shannon