query-prepare simple question

[binggoy]

Im having problems with using QUERY-PREPARE. I'm just going to ask how will I use QUERY-PREPARE to populate or put data in my TEMP-TABLE? just a simple sample program would do.

THANKS

 

[Norman Biggar]

CREATE TEMP-TABLE hTemp.
hTemp:ADD-FIELDS-FROM("tablename").
hTemp:TEMP-TABLE-PREPARE("temptable").
ASSIGN hBuffer = hTempEFAULT-BUFFER-HANDLE.

or, if you are using a static temptable replace above with
DEF TEMP-TABLE tt-Temp NO-UNDO
LIKE tablename.
ASSIGN hBuffer = TEMP-TABLE tt-TempEFAULT-BUFFER-HANDLE.

CREATE BUFFER hTable FOR TABLE "tablename".

CREATE QUERY hQuery.
hQuery:ADD-BUFFER(hTable).
hQuery:QUERY-PREPARE("FOR EACH tablename").
hQuery:QUERY-OPEN.
hQuery:GET-FIRST(NO-LOCK).
REPEAT:
IF hQuery:QUERY-OFF-END THEN
LEAVE.
hBuffer:BUFFER-CREATE.
hBuffer:BUFFER-COPY(hTable).
hQuery:GET-NEXT(NO-LOCK).
END.

 

[jongpau]

Hi,

Try something like:

DEF VAR lhQuery AS HANDLE NO-UNDO.
DEF VAR lhBuffer AS HANDLE NO-UNDO.
DEF VAR lhTempTable AS HANDLE NO-UNDO.
DEF VAR lhTempBuffer AS HANDLE NO-UNDO.

lhBuffer = BUFFER Customer:HANDLE.
CREATE TEMP-TABLE lhTempTable.
lhTempTable:CREATE-LIKE(lhBuffer).
lhTempTable:TEMP-TABLE-PREPARE(lhBuffer:TABLE).
lhTempBuffer = lhTempTable :DEFAULT-BUFFER-HANDLE.
CREATE QUERY lhQuery.
lhQuery:ADD-BUFFER(lhBuffer).
lhQuery:QUERY-PREPARE("FOR EACH Customer NO-LOCK":U).
lhQuery:QUERY-OPEN.
lhQuery:GET-FIRST(NO-LOCK).
DO WHILE NOT lhQuery:QUERY-OFF-END:
  lhTempBuffer:BUFFER-CREATE.
  lhTempBuffer:BUFFER-COPY(lhBuffer).
  lhQuery:GET-NEXT(NO-LOCK).
END.

Note: I typed this code straight in here, so I did not do any syntax checking... it's the idea that counts :awink:

HTH

 

[binggoy]

Thank you guys for the reply...

But what if I've only choosen a few fields to be copied to my TEMP-TABLE? Then I can't use BUFFFER-COPY, or can I?

What I've done is something like this. I've made a TEMP-TABLE, for example is the customer table. Then i only want to choose a few fields, name & address. Should I use assign instead of BUFFER-COPY?

Then the procedure will return the TEMP-TABLE to its calling program...

Oh by the way, I'm doing this in an APPSERVER environment.

Thanks again and HAPPY HOLIDAYS in advance...

 

[jongpau]

If your temp-table contains only a subset of the original (say 4 out of 20 fields), you can still use buffer-copy.

If you need to give fields a different name (for instance if your temp-table contains fields from multiple tables and some of these have the same field name), then you will need to assign the field values somewhat like this (again assuming a dynamic temp-table):

DEF VAR lhTarget AS HANDLE NO-UNDO.
DEF VAR lhSource AS HANDLE NO-UNDO.

.. (define and open the query etc)
DO WHILE NOT lhQuery:QUERY-OFF-END:
  lhTempBuffer:BUFFER-CREATE.
  ASSIGN lhTarget = lhTempBuffer:BUFFER-FIELD("CustomerNo")
         lhSource = lhBuffer:BUFFER-FIELD("CustomerID")
         lhTarget:BUFFER-VALUE = lhSource:BUFFER-VALUE
         lhSource = lhBuffer:BUFFER-FIELD(...)
         lhTerget = lhTempBuffer:BUFFER-FIELD(...)
         lhTarget:BUFFER-VALUE = lhSource:BUFFER-VALUE
         ..ETC..
         .
  lhQuery:GET-NEXT(NO-LOCK).
END.

Alternatively you can create a temp-table that holds the handles to all the fields (so sort of a field mapping) and you can then use a for each to assign all the fields. And I bet there are plenty of other possibilities (for instance a combination of buffer-copy and assigns, using arrays with field handles etc etc etc).

HTH

 

[Norman Biggar]

Jongpau,
If you were copying fields with different names on the db and temp tables, why not use the pairs-list option of the BUFFER-COPY method?

hBuffer:BUFFER-COPY(hTable,?,"CustomerID,CustomerNo,dbfield,ttfield,...").

From Progress Documentation

BUFFER-COPY Method
This method copies any common fields, determined by name, data type, and extent-matching, from the source buffer, source-buffer-handle, to the receiving buffer, bh, the object the method is applied to. If there are fields in one buffer that do not exist in the other, they are ignored. This method is used to accommodate temp-tables of joins.

Return Type: LOGICAL

Applies To: Buffer Object Handle

SYNTAX

bh:BUFFER-COPY( source-buffer-handle [ , except-list [ , pairs-list ] ] )

source-buffer-handle
An expression that evaluates to the source buffer handle.

except-list
A character expression that evaluates to a comma-separated list of field names to be excluded from the copy.

pairs-list
A character expression that evaluates to a comma-separated list of field-name pairs to be copied.

NOTE: The order within each field-name pair does not matter; each pair must contain one field name from the source and one field name from the target.

The following example fragment copies the customer table to the buffer, bh, except that customer.sales-rep is copied to a field called cust-sales-rep in the buffer:

bh:BUFFER-COPY(buffer customer:handle,?,"cust-sales-rep,sales-rep").

 

[jongpau]

Thanks Norman,

Looks like I have to get back into the edocs again :awink:

Will look into it first thing tomorrow morning...