[ASK] Dynamic Where Clause

[gate_hongs46]

Greetings.

im trying to make a dynamic where for searching data.
Can i code like this:

def var fieldv as char.

fieldv = browse-name:CURRENT-COLUMN:name.

{find-data.i
&TABLE = "customer"
&FIELD = fieldv
&SEARCH = f-search:SCREEN-VALUE}

find-data.i:
FIND FIRST {&TABLE} WHERE {&FIELD} BEGINS {&SEARCH} NO-LOCK NO-ERROR.
if avail {&TABLE} then reposition browse-name to recid recid({&TABLE}) no-error.

it seems to be not working.
anything wrong with my coding.
need help. thx.

 

[RealHeavyDude]

What Progress Version you're on?

Your code is all but dynamic as it uses include file technique which is resovled at compile time, _NOT_ at runtime.

HTH, RealHeavyDude.

 

[gate_hongs46]

What Progress Version you're on?

Your code is all but dynamic as it uses include file technique which is resovled at compile time, _NOT_ at runtime.

HTH, RealHeavyDude.

Progress v9.1c.
Any solution ??

 

[RealHeavyDude]

You should look at dynamic programming, somthing like this:

<code-snipet>
DEFINE VARIABLE cTableName AS CHARACTER NO-UNDO.
DEFINE VARIABLE cFieldName AS CHARACTER NO-UNDO.
DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.
DEFINE VARIABLE lOkay AS LOGICAL NO-UNDO.
ASSIGN cTableName = "Customer"
cFieldName = browse-name:CURRENT-COLUMN:NAME.
CREATE BUFFER hBuffer FOR TABLE cTableName.
ASSIGN lOkay = hBuffer:FIND-FIRST ( "WHERE " + cFieldName + " BEGINS " + f-search:SCREEN-VALUE, NO-LOCK ) NO-ERROR.
IF lOkay THEN
REPOSITION browse-name TO RECID hBuffer:RECID NO-ERROR.
/* Don't forget to cleanup properly ... */
DELETE OBJECT hBuffer.
ASSIGN hBuffer = ?.
</code-snipet>

 

[gate_hongs46]

You should look at dynamic programming, somthing like this:

<code-snipet>
DEFINE VARIABLE cTableName AS CHARACTER NO-UNDO.
DEFINE VARIABLE cFieldName AS CHARACTER NO-UNDO.
DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.
DEFINE VARIABLE lOkay AS LOGICAL NO-UNDO.
ASSIGN cTableName = "Customer"
cFieldName = browse-name:CURRENT-COLUMN:NAME.
CREATE BUFFER hBuffer FOR TABLE cTableName.
ASSIGN lOkay = hBuffer:FIND-FIRST ( "WHERE " + cFieldName + " BEGINS " + f-search:SCREEN-VALUE, NO-LOCK ) NO-ERROR.
IF lOkay THEN
REPOSITION browse-name TO RECID hBuffer:RECID NO-ERROR.
/* Don't forget to cleanup properly ... */
DELETE OBJECT hBuffer.
ASSIGN hBuffer = ?.
</code-snipet>

Your code results an error.
Unable to understand after -- "Assign lOkay". (247)

 

[RealHeavyDude]

My fault.

I've written code like this so many times that I didn't test my own advice. You are correct the code is missing the enclosing quotes or double quotes for the string - because whatever is contained in the SCREEN-VALUE of f-search will not resolve into a variable name ...


ASSIGN lOkay = hBuffer:FIND-FIRST ( 'WHERE ' + cFieldName + ' BEGINS "' + f-search:SCREEN-VALUE + '"', NO-LOCK ) NO-ERROR.

Pls bear with me, RealHeavyDude.

 

[gate_hongs46]

My fault.

I've written code like this so many times that I didn't test my own advice. You are correct the code is missing the enclosing quotes or double quotes for the string - because whatever is contained in the SCREEN-VALUE of f-search will not resolve into a variable name ...


ASSIGN lOkay = hBuffer:FIND-FIRST ( 'WHERE ' + cFieldName + ' BEGINS "' + f-search:SCREEN-VALUE + '"', NO-LOCK ) NO-ERROR.

Pls bear with me, RealHeavyDude.

I think the syntax is still incorrect sir, i have an error like below.
---------------------------
Error
---------------------------
Unknown attribute FIND-FIRST used in widget:attribute phrase. (3406)
** Could not understand line . (196)
---------------------------
OK
---------------------------

Thanks

 

[TomBascom]

Ancient, obsolete and unsupported versions of Progress like 9.1C do not have the FIND-FIRST method.

You would need to do a GET instead.

 

[RealHeavyDude]

Thanks Tom.

As I am working on OE10 since at least 4 years I didn't remember that the FIND-FIRST () method on the buffer object handle not available in 9.1c. Don't know exactly when it was introduced.

Best regards,
RealHeavyDude.

 

[gate_hongs46]

Sorry for the long reply,
Still can't figure out using GETmethod.
Is there another way out of this??
thanks anyway

 

[RealHeavyDude]

Everything you code statically will be resolved at compile time. There is only one way to change the query where at run time: Use dynamic objects - or at least grab the handle of a static query object and use that.

If you don't know how to use dynamic objects or object handles then I think this is beyond the scope of this forum and you should get a training. BTW, dynamic objects and the ability to change them at runtime is one of the big features that were introduced with Progress V9.

Regards, RealHeavyDude.

 

[gate_hongs46]

Bingo, Finally it works..