6 replies to this topic

[John Robson]

Has anyone come accross problems with SQL DYNAMIC on version 5 rev 109.
It seems that the dynamic WHERE clause will only be evaluated once within the function. If the WHERE clause is changed and the SELECT executed again the data set is not changed and the original results are returned.

Has anyone else encountered this or EVEN come up with a work around.

Cheers

John

[DARREN]

The environment variable REPARSE needs to be set to Y i.e.

REPARSE=Y

This can be set as an environment variable or set in the [ENVIRONMENT] section of the pro4.ini.

Search other posts on this site for more info on dynamic SQL

[John Robson]

Brilliant, that works like a charm Darren.
Does this variable affect non dynamic sql statements aswell?
If we set this variable will we see a performance hit?

CHeers

JOhn

[DARREN]

This variable only affects DYNAMIC SQL statements. You 'may' see a 'slight' performance hit, but nothing that is really measurable.

I believe that in the Version 5.5 SQL interface re-write; the performance hit on the use of this variable is so negligible that it has become almost redundant. I would however recommend that it is always set if you are using DYNAMIC SQL.

[Dan Shannon]

That's nice - we reported this problem to PRO-IV support 6 months ago (couldn't get DYNAMIC SQL to work) and this is the first I've heard of this REPARSE variable...

[DARREN]

A post on this very board on 7th Sept 2001 described in detail the REPARSE variable. I also believe that the DYNAMIC SQL functionality was not officially released and documented until version 5.5. Chapter 6 of the 5.5. environment guide - SQL Environment - sub chapter - Full function Mode - makes explicit reference to the REPARSE environment variable.

[Dan Shannon]

So does it work in 5.0r109 then? Or is it not officially released because it doesn't work on all platforms? I think I should be spoon-fed the truth!