Jump to content


Photo
- - - - -

Dynamic SQL embedded ina PRO-IV Functions


20 replies to this topic

#1 Lee Walters

Lee Walters

    Member

  • Members
  • PipPip
  • 36 posts
  • Gender:Male

Posted 16 November 2001 - 12:17 PM

Does anybody know how I can dynamically build an SQL statement within a Pro-IV function?

What I'm trying to do is a multi-word text search on a field.

I know exactly how the SQL statement should read, its just getting PRO-IV to actually read it.

I've got

SQL
select * from table
where field like :$SEARCH
ENDSQL

$SEARCH has been built up previously and contains
''%value1%' and field like '%value2%''

giving a full statement of 'where field like '%value1%' and field like '%value2%'

This doesn't work though. I've seen the postings about the SPAN sql but not sure if I can use this in a PRO-IV function itself ?

Any ideas would be good.

Cheers

#2 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 16 November 2001 - 01:46 PM

See my posting of 24/10/01 15:12:23 in reply to Neil Hunter's posting of 23/10/01 10:06:00 titled 'SQL statements'.

Don't get your hopes up though :-(
Nothing's as simple as you think

#3 Lee Walters

Lee Walters

    Member

  • Members
  • PipPip
  • 36 posts
  • Gender:Male

Posted 16 November 2001 - 02:29 PM

Is that the one thats using SEL-SQL.

Tried that and it doesn't work - nice try though!

Anybody else got any ideas ?

#4 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 16 November 2001 - 03:07 PM

Try actually reading the post :-)
Nothing's as simple as you think

#5 Lee Walters

Lee Walters

    Member

  • Members
  • PipPip
  • 36 posts
  • Gender:Male

Posted 16 November 2001 - 04:27 PM

Richard,

I've read the post and I'm still lost.

I've trying to get the embedded SQL code to recognise my scratch variable $SEARCH as the where clause for the SQL but it doesn't. $SEARCH is properly formatted for a SQL statement where clause.

HELP!

#6 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 16 November 2001 - 05:11 PM

OK let me paraphrase.

AFAIAA, in the *general* case the only way to do what you want is to create and gen ProIV source code on the fly.

AFAIAA, you simply can't bind a whole clause as a variable, only an individual value.

If yours is a simple case with a limit on the number of search words, why don't you fix the SQL for the maximum possible number of words and just duplicate some of the words when less of them are specified by the user?

I'm sorry if you rushed off to try SEL-SQL :-)
That post reflected my frustration with the past.
That's why I said don't get your hopes up!
Nothing's as simple as you think

#7 Lee Walters

Lee Walters

    Member

  • Members
  • PipPip
  • 36 posts
  • Gender:Male

Posted 28 November 2001 - 10:35 PM

Richard,

Thanks for youe help - I ended up coding basically what you said - limiting the number of words the user is able to search on and coding from there.

From your last reply in terms of building and genning the code on the fly - any tips or pointers on how to do this ?
A Couple of guys here in work have mentioned this but are not sure on how to go about it .

Thanks

Lee

#8 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 29 November 2001 - 09:34 AM

Hi Lee,

Glad you solved your immediate problem.

Generating source on the fly requires a good understanding of the bootstrap - far more than I could explain here. Also I believe that, in principle, if you want to use ProIV's file definitions for the bootstrap you may need to license them (or at least sign non-disclosure).

One tip however is to think carefully about multi-user issues when generating code ie. names/keys for the generated code and record locking.


Regards, Richard


PS. Thanks for the .MP3.pif file :-) :-)
Nothing's as simple as you think

#9 DARREN

DARREN

    ProIV Guru

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

Posted 03 December 2001 - 10:08 PM

Hi Lee

Just to let you know that PROIV does support (although not officially released yet) dynamic SQL in version 5.0 and above. You can therefore do the following

SQL DYNAMIC
SELECT FROM FILE WHERE :$STATEMENT
ENDSQL

where $STATEMENT is constructed in logic. This is a simple example and there is allot more to this feature than I can write in this thread.

My only comments on the above threads are that source code should 'never' be amended on the fly. For obvious reasons it creates 'unsafe' code. If a developer feels he/she has to do this then :-

a) There is deficiency in the product itself that should be presented to PROIV for review and possible inclusion in a future release

B) The is a better way of doing what the developer may be trying to achieve / the developer may not know of the existing feature that addresses their issue.

The PROIV auto regen is there as a failsafe - nothing more. The feature should not be abused and although technically clever - dynamic code generation is rarely safe. i.e. the complied function has not gone through Q.A.
Things should be made as simple as possible, but not simpler

#10 Lee Walters

Lee Walters

    Member

  • Members
  • PipPip
  • 36 posts
  • Gender:Male

Posted 04 December 2001 - 08:47 AM

Darren,

Thanks for that but it's what I'm already using.

If you read my post you'll see what I really want to do which is have the $STATEMENT built dynamically to hold more than just a value to search for. I want it to hold multiple where statement clauses.

Cheers though

#11 DARREN

DARREN

    ProIV Guru

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

Posted 04 December 2001 - 05:51 PM

That's exactly what you can do with version 5.0 dynamic SQL.
Things should be made as simple as possible, but not simpler

#12 Lee Walters

Lee Walters

    Member

  • Members
  • PipPip
  • 36 posts
  • Gender:Male

Posted 05 December 2001 - 08:41 AM

Darren,

What version are you talking about ? v5r100 ? We're running the NT Version.

Like I said in my post, I've tried building up the $STATEMENT to read 'LIKE 'value1%' AND VARIABLE LIKE 'value2%' and it doesn't recognise it. The $STATEMENT can only hold a value to search against.

#13 Richard Bassett

Richard Bassett

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 696 posts
  • Location:Rural France

Posted 05 December 2001 - 03:14 PM

Lee,

You did notice Darren's post said 'SQL *DYNAMIC*' didn't you... i.e. a new extension to ProIV logic syntax?
Nothing's as simple as you think

#14 Lee Walters

Lee Walters

    Member

  • Members
  • PipPip
  • 36 posts
  • Gender:Male

Posted 05 December 2001 - 04:00 PM

Funnily enough I did yes!

I tried what I wanted to do again and guess what! Its not happening! Maybe that I need to do something different with my code though - I'll give the change a go and let you know how it goes. I think it's got something to do with the way I'm building the WHERE clause.

At the mo I'm using a combination of a hardcoded where clause and the scratch variable i.e. WHERE FIELD1 LIKE :$VAR1 AND FIELD2 <> 'VALUE' AND FIELD3LIKE :$VAR2

$VAR1 is a straight value eh 1 but $VAR2 in an extended where caluse to search on FIELD3 so $VAR2 is looking something like ''%TEXT%' AND FIELD3 LIKE '%TEXT2%'

To Mcuh detail I know and maybe to complicated. I'll try and get the where clause into 1 scratch var and let you know how it goes!

Cheers guys

#15 Lee Walters

Lee Walters

    Member

  • Members
  • PipPip
  • 36 posts
  • Gender:Male

Posted 05 December 2001 - 04:47 PM

Guys, B)

Success!!! I re-wrote my where clause to be totally included in a scratch variable and it's working like a dream.

The help was appreciated guys - cheers!

Lee



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users