Jump to content


Photo
- - - - -

Problems with SQL DYNAMIC inV5 r109


6 replies to this topic

#1 John Robson

John Robson

    Member

  • Members
  • PipPip
  • 11 posts
  • Gender:Male
  • Location:Melbourne, Australia

Posted 13 February 2003 - 10:54 PM

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

#2 DARREN

DARREN

    ProIV Guru

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

Posted 14 February 2003 - 02:15 AM

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

#3 John Robson

John Robson

    Member

  • Members
  • PipPip
  • 11 posts
  • Gender:Male
  • Location:Melbourne, Australia

Posted 14 February 2003 - 03:22 AM

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

#4 DARREN

DARREN

    ProIV Guru

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

Posted 17 February 2003 - 11:56 PM

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

#5 Dan Shannon

Dan Shannon

    ProIV Guru

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

Posted 19 February 2003 - 09:08 PM

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...

#6 DARREN

DARREN

    ProIV Guru

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

Posted 19 February 2003 - 09:49 PM

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

#7 Dan Shannon

Dan Shannon

    ProIV Guru

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

Posted 19 February 2003 - 10:01 PM

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!



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users