Dynamic SQL embedded ina PRO-IV Functions
Posted 07 December 2001 - 05:06 PM
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.
Posted 10 December 2001 - 12:30 AM
I was hoping to quiz you a little on the dynamic SQL feature. Will this allow me to do the following:
SELECT FROM :$FILE_NAME
The inability to do this is a constant source of frustration.
Also you mentioned that this feature has not officially been released. Do you have an ETA on this?
Posted 10 December 2001 - 10:18 PM
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.
Posted 10 December 2001 - 10:41 PM
I can understand that this would be a problem if we were changing the file definition. What I am proposing is to choose between a series of files which have identical file definitions. So, assuming file1 and file2 have identical file definitions, but are different tables, I am trying to do the following:
ELSE $file = file2
SELECT FROM :$file
In summary, I am trying to achieve the same thing as an ALIAS, but using SQL.
Posted 10 December 2001 - 10:46 PM
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.
Posted 11 December 2001 - 07:57 AM
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:
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.
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users