Jump to content


Photo
- - - - -

Dynamic SQL embedded ina PRO-IV Functions


20 replies to this topic

#16 DARREN

DARREN

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 395 posts
  • Gender:Male
  • Location:Florida,USA

Posted 07 December 2001 - 05:06 PM

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.
Things should be made as simple as possible, but not simpler

#17 Guest_Robert Hogan_*

Guest_Robert Hogan_*
  • Guests

Posted 10 December 2001 - 12:30 AM

Hi Darren,

I was hoping to quiz you a little on the dynamic SQL feature. Will this allow me to do the following:

SQL DYNAMIC
SELECT FROM :$FILE_NAME
END SQL

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?

Cheers,
Rob.

#18 DARREN

DARREN

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 395 posts
  • Gender:Male
  • Location:Florida,USA

Posted 10 December 2001 - 10:18 PM

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.
Things should be made as simple as possible, but not simpler

#19 Guest_Robert Hogan_*

Guest_Robert Hogan_*
  • Guests

Posted 10 December 2001 - 10:41 PM

Darren,

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:
IF THEN $file = file1
ELSE $file = file2
ENDIF


SQL DYNAMIC
SELECT FROM :$file
ENDSQL

In summary, I am trying to achieve the same thing as an ALIAS, but using SQL.

Any thoughts?

Thanks again,
Rob.

#20 DARREN

DARREN

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 395 posts
  • Gender:Male
  • Location:Florida,USA

Posted 10 December 2001 - 10:46 PM

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.
Things should be made as simple as possible, but not simpler

#21 Dan Shannon

Dan Shannon

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 374 posts
  • Gender:Male
  • Location:Australia

Posted 11 December 2001 - 07:57 AM

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



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users