Dynamic SQL embedded ina PRO-IV Functions
Posted 16 November 2001 - 12:17 PM
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.
select * from table
where field like :$SEARCH
$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.
Posted 16 November 2001 - 04:27 PM
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.
Posted 16 November 2001 - 05:11 PM
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!
Posted 28 November 2001 - 10:35 PM
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 .
Posted 29 November 2001 - 09:34 AM
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.
PS. Thanks for the .MP3.pif file :-) :-)
Posted 03 December 2001 - 10:08 PM
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
SELECT FROM FILE WHERE :$STATEMENT
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
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.
Posted 04 December 2001 - 08:47 AM
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.
Posted 05 December 2001 - 08:41 AM
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.
Posted 05 December 2001 - 04:00 PM
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!
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users