dynamic query and fields with extent.

[webguy]

Hi all,

I am noticing when I try using dynamic queries that include fields that have an extent it will return errors. When i check the output of my message string and run it the query works fine. Is there a specific way you have to specify a field with extent when building your query string?

example:

   /* dynamic query string*/
   assign v-query =
                  "for each customer no-lock where " +
                  "customer.cust-address[1]" + " matches " + "'" + v-address + "':" 
                  
   message v-query.
 
   create query qh-customer.
   qh-customer:set-buffers(buffer customer:handle).
   qh-customer:query-prepare(v-query).
   qh-customer:query-open.
   if qh-customer:is-open then
   repeat:
   qh-customer:get-next.
   if qh-customer:query-off-end
   then leave.
 
   etc...

The output string will run fine in the editor but as a dynamic query I get an error in the logs.

customer cust-address must be a quoted constant or an unabbreviated, unambiguous buffer/field reference for buffers known to query . (7328)

 

[stokefc22]

Hi Webguy,
did you ever solve this issue, as I am suffering with the same thing

 

[SergioC]

Hi, this is only solution I found.

[/FONT]DEFINE VARIABLE i           AS INTEGER.
DEFINE VARIABLE qh          AS WIDGET-HANDLE.
DEFINE VARIABLE bh          AS WIDGET-HANDLE.
DEFINE VARIABLE fhToDisplay AS WIDGET-HANDLE /* EXTENT 10 */.
DEFINE VARIABLE fhToCompare AS HANDLE /* FieldExtent to compare */.
DEFINE VARIABLE icntExtent  AS INTEGER.
DEFINE VARIABLE iLoop       AS INTEGER.
DEFINE VARIABLE FldName     AS CHAR     FORMAT 'X(20)'.
DEFINE VARIABLE FldVal      AS CHAR     FORMAT 'x(20)'.


CREATE BUFFER bh FOR TABLE "SalesRep".


CREATE QUERY qh.
qh:SET-BUFFERS(bh).
qh:QUERY-PREPARE("for each salesrep").
qh:QUERY-OPEN.


FORM FldName TO 22
  FldVal
  WITH FRAME a DOWN.


REPEAT WITH FRAME a STREAM-IO:


  qh:GET-NEXT.


  IF qh:QUERY-OFF-END
    THEN LEAVE.


  fhToCompare = bh:BUFFER-FIELD('MonthQuota').
  IF fhToCompare:BUFFER-VALUE(1 /* MonthQuota[1] */ ) <= 3000
    THEN NEXT.


  DISPLAY bh:NAME @ FldName.


  REPEAT i = 1 TO bh:NUM-FIELDS WITH FRAME a.
    fhToDisplay = bh:BUFFER-FIELD(i).
    iCntExtent = fhToDisplay:EXTENT.


    IF iCntExtent > 0 THEN
    DO iLoop = 1 TO iCntExtent WITH FRAME a:
      DISPLAY fhToDisplay:NAME + '[' + STRING(iLoop) + ']' @ FldName
        fhToDisplay:BUFFER-VALUE(iLoop) @ FldVal
        .
      DOWN.
    END.
    ELSE
      DISPLAY fhToDisplay:NAME @ FldName
        STRING(fhToDisplay:BUFFER-VALUE) @ FldVal
        .
    DOWN.
  END.
  CLEAR FRAME a ALL.
END.


DELETE WIDGET bh.

Regards.

 

[Stefan]

What version are you on? With 10.2B04 the following, which is querying a temp-table, works fine:

DEFINE TEMP-TABLE tt
   FIELD cc AS CHAR EXTENT 2
   .


CREATE tt. ASSIGN tt.cc[1] = "1" tt.cc[2] = "2".
CREATE tt. ASSIGN tt.cc[1] = "2" tt.cc[2] = "3".


DEF VAR hq AS HANDLE.


CREATE QUERY hq.
hq:SET-BUFFERS( TEMP-TABLE tt:DEFAULT-BUFFER-HANDLE ).
hq:QUERY-PREPARE( "FOR EACH tt WHERE cc[1] = '1'" ).
hq:QUERY-OPEN().
DO WHILE hq:GET-NEXT():
   MESSAGE tt.cc[1] SKIP tt.cc[2] VIEW-AS ALERT-BOX.
END.

I do not expect different behavior when using a database table.


Rant: please use code tags when posting code...

 

[SergioC]

Hi Stefan, I did not know what to test, but you're right, your example works well even with a database table.


Excuse my ignorance, How to post code with tags ... (Start with [Code:] and End with [???])

Regards.

 

[Stefan]

Excuse my ignorance, How to post code with tags ... (Start with [Code:] and End with [???])

CODE and /CODE but then in square brackets (just like QUOTE and /QUOTE when quoting a post). If you click on the 'go advanced' button, the 'advanced' editor also has an icon to do this.